Re: single quotes in insert

[Timothy Harris <harrist@UCS.ORST.EDU>]

Nathan was right on the money.  Thank you, thank you, thank you!

Now, MITI, what are the odds that, in the future, you could add an
Oracle-like 'REPLACE' command to your baseline functions!?(is REPLACE an
ANSI standard SQL function?)

Tim Harris
Systems Analyst
Oregon State University

On Wed, 19 Jun 1996, Nathan Treadway wrote:

> > I'm building the $value_list from each record of the file.  Each value
> > has a comma between it and I'm enclosing the character data in single
> > quotes(I'm looking at the data dictionary to get the table definition).
> > The problem is, some of the data fields in the flat file contain single
> > quotes and that screws up the insert statement since it takes the single
> [...]
> > Here's the meat of the SQR code I'm using to insert:
> > ***begin SQR code********
> >    LET $cmd_line = 'INSERT INTO '
> >                  || $table_owner
> >                  || '.'
> >                  || $table_name
> >                  || ' VALUES ('
> >                  || $value_list
> >                  || ')'
> >
> > BEGIN-SQL ON-ERROR=error_handler
> > [$cmd_line]
> > END-SQL
> >
> > ***end SQR code******
> >
>
> Since you are using dynamic SQL, you actually need to have the doubled-up
> single quotes within the $cmd_line variable itself, so that Oracle can
> properly parse the SQL command.
>
> That is, your $cmd_line variable should contain something like
>
>   insert into scott.temp values ('can''t swim');
>
> I would guess that the easiest way to to that would be to double all
> of the "incoming" quotes as you build the contents of $value_list.
> Unfortunately SQR doesn't have a nice function for doing this, so you have
> to do something like this:
>
> --------------------------------------------------------------------------
> begin-procedure double_substring(:$string,$substring)
>
> ! This procedure will look for occuences of $substring in $string and
> ! double them.  For example:
> !     with a substring of  '   :  can't  becomes   can''t
> !     with a substring of  '   :  'can'  becomes   ''can''
> !     with a substring of  an  :  can't  becomes   canan't
>
> let #len = length($substring)
> move  1 to #ind
> move '' to $result
>
> while #ind > 0
>   let #ind = instr($string,$substring,1)
>   if #ind > 0
>      let $result = $result || substr($string, 1, #ind+#len -1) || $substring
>      let $string = substr($string, #ind+#len, 99999)
>   end-if
> end-while
> let $string = $result || $string
> end-procedure
> --------------------------------------------------------------------------
>
> So, say you are reading from the tab-delimited file and then parsing the
> incoming lines into separate fields, each of which in turn is temporarily
> stored in $value_in.  Before you append $value_in to $value_list, add the
> line
>
>    do double_substring($value_in, '''')
>
>
> I think that is all you need to do, but if that doesn't work, "show" the
> value of $cmd_line and make sure that it's a valid SQL string (i.e. that it
> is accepted by SQL*Plus).
>
> Note that you probably need to do this as you are building $value_list
> because you don't want to double the single quotes that your program itself
> adds around character data.
>
> Hope this helps.
>
>
>                                         Nathan
>
>
> ----------------------------------------------------------------------------
> Nathan Treadway    | Ray Ontko & Co. | info@ontko.com (auto-reply server)
> nathant@ontko.com  | Richmond, IN    | ftp.ontko.com, http://www.ontko.com/
>