Thread: INSERT INTO

Bart Degryse a ecrit le 16/03/2007 10:03:
> I don't use ASP but in PHP I would do something in the line of
>  
> $valuetoinsert = "SANT'ANGELO LODIGIANO";
> $query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'", 
> "''", $valuetoinsert) . "')";
>  
> I'm sure ASP has also a string replacement function
> 

This doesn't answer Shavonne's question but, instead of str_replace, you 
should better use pg_escape_string :  http://www.php.net/manual/en/function.pg-escape-string.php

Regards.


-- 
Guillaume.

i took a look at the link Guillaume gave me. But it also explains on PHP
i'm using ASP :(


----- Original Message ----- 
From: "Guillaume Lelarge" <guillaume@lelarge.info>
To: "Bart Degryse" <Bart.Degryse@indicator.be>
Cc: <pgsql-sql@postgresql.org>; "Shavonne Marietta Wijesinghe" 
<shavonne.marietta@studioform.it>
Sent: Friday, March 16, 2007 10:16 AM
Subject: Re: [SQL] INSERT INTO


> Bart Degryse a ecrit le 16/03/2007 10:03:
>> I don't use ASP but in PHP I would do something in the line of
>>  $valuetoinsert = "SANT'ANGELO LODIGIANO";
>> $query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'", 
>> "''", $valuetoinsert) . "')";
>>  I'm sure ASP has also a string replacement function
>>
>
> This doesn't answer Shavonne's question but, instead of str_replace, you 
> should better use pg_escape_string :
>   http://www.php.net/manual/en/function.pg-escape-string.php
>
> Regards.
>
>
> -- 
> Guillaume. 

Shavonne Marietta Wijesinghe a ecrit le 16/03/2007 11:31:
> i took a look at the link Guillaume gave me. But it also explains on PHP
> i'm using ASP :(
> 

Yes, that's why I said that it didn't answer your question. Sorry if 
this wasn't clear.

After a bit of googling, I found the replace function. Something like that :
<%= replace(string,"'","''") %>
might work.

But remember I don't use ASP.

Regards.


-- 
Guillaume.

> When i have a string that has a ' inside of it the record is not inserted in to TABLE2. I know
> that writing it twice will fix it but how can i ask my ASP code to do it..
> 
> INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');

If you are using PostgreSQL >= 8.0 you can use dollars sign quoting like the following:

INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$);

or even a custom quotation identifier:

INSERT INTO TABLE2 (TE_INDI) VALUES ($myquote$SANT'ANGELO LODIGIANO$myquote$);

Would this work in ASP?

Regards,
Richard Broersma Jr.

> INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$);

My mistake, a single $ will not work you need two like $$:

INSERT INTO TABLE2 (TE_INDI) VALUES ($$SANT'ANGELO LODIGIANO$$);


Regards,
Richard Broersma Jr.

Thanks alot

^_____________^

Shavonne Wijesinghe
http://www.studioform.it


----- Original Message ----- 
From: "Richard Broersma Jr" <rabroersma@yahoo.com>
To: "Richard Broersma Jr" <rabroersma@yahoo.com>; "Shavonne Marietta 
Wijesinghe" <shavonne.marietta@studioform.it>; <pgsql-sql@postgresql.org>
Sent: Friday, March 16, 2007 2:08 PM
Subject: Re: [SQL] INSERT INTO


>> INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$);
>
> My mistake, a single $ will not work you need two like $$:
>
> INSERT INTO TABLE2 (TE_INDI) VALUES ($$SANT'ANGELO LODIGIANO$$);
>
>
> Regards,
> Richard Broersma Jr. 

Shavonne Marietta Wijesinghe wrote:
> Thanks alot

What happens if you try to insert a string with $$ on it?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
> I have 2 tables. TABLE1 and TABLE2.
>
> TABLE1 has about 400 records with details (client code, name, surname,
> address, date of birth) of my clients.
> TABLE2 is filled with some of the client details and other extra details
> (client code, address, telephone, etc)
>
> So in my ASP page i have a select that gets the client details from TABLE1
> using the client code and inserts them in to TABLE2
>
> When i have a string that has a ' inside of it the record is not inserted in
> to TABLE2. I know that writing it twice will fix it but how can i ask my ASP
> code to do it..

I'm sadly tasked with maintaining a little ASP code from time to time.  If you need to do it through a script, what
you'relooking for is the Replace function, as already mentioned in the thread: 

Replace( InputString, "'", "''" )

However since we're already on pgsql-sql, assuming you're not doing a whole lot more than pulling from one table and
insertinginto another, you may want to consider seeing if you can roll it into a single SQL statement: 

> INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');

INSERT INTO TABLE2 (TE_INDI) VALUES SELECT TE_INDI FROM TABLE1 WHERE (...)

No quoting or server->client->server worries to deal with at all...

Best of luck,- Josh Williams