Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to insert a string with single quotes in the text

How to insert a string with single quotes in the text

From
"Louise Cofield"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Greetings:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am creating a Libpq program that reads a text file, and inserts values into a Postgres table.  An
exampleof my insert statement looks like this:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SPRINTF(str, “INSERT INTO itm_table (itm_num, itm_desc) values (‘%s’, ‘%s’);”, sitmnum,
sitmdesc);</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The insert blows up when it hits an item description that contains a single quote in the text. 
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Other than stripping the offending quote-marks from  the text prior to processing, does anybody have
anyideas how I can deal with this?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I appreciate all help!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Louise</span></font></div>

Re: How to insert a string with single quotes in the

From
Oliver Elphick
Date:
On Fri, 2003-09-19 at 22:02, Louise Cofield wrote:
> I am creating a Libpq program that reads a text file,
...
> The insert blows up when it hits an item description that contains a
> single quote in the text.  

You also have to look out for backslashes.

> Other than stripping the offending quote-marks from  the text prior to
> processing, does anybody have any ideas how I can deal with this?

The libpq library includes the function PQescapeString, as described in
the manual.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "But my God shall supply all your need according to his     riches in glory
byChrist Jesus."     Philippians 4:19

Re: How to insert a string with single quotes in the text

From
Bruno Wolff III
Date:
On Fri, Sep 19, 2003 at 15:02:07 -0600, Louise Cofield <lcofield@box-works.com> wrote:
> 
> I am creating a Libpq program that reads a text file, and inserts values
> into a Postgres table.  An example of my insert statement looks like
> this:
> 
> SPRINTF(str, "INSERT INTO itm_table (itm_num, itm_desc) values ('%s',
> '%s');", sitmnum, sitmdesc);
> 
> Other than stripping the offending quote-marks from  the text prior to
> processing, does anybody have any ideas how I can deal with this?

Scan the input and put backslashes in front of backslashes and single quotes.