SQL updates with ' and " in the string

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Chucky
Forum Newbie
Posts: 2
Joined: Sun Jul 01, 2007 6:08 pm

SQL updates with ' and " in the string

Post by Chucky »

HI,

I am trying to copy address fields in our database. For some reason previously address went into addr3 i need to move these to addr2. the issue i am having is that address in the system have ' " ie "STRATHLEIGH'

how can i make an update that will not throw errors on these being in the sql and not endin the strings where they shouldnt?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Chucky
Forum Newbie
Posts: 2
Joined: Sun Jul 01, 2007 6:08 pm

Post by Chucky »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


the looks great but i dont have access to PHP in this system.  its coding in report writer,  which is very limited in what it can do.  really only have create the sql execute it.   we can use function manipulation but it has to be designed in house.  below is code extract..

[syntax="sql"]#VAR stat                 : SHORT
#VAR client,addr2,addr3  : STRING
#VAR sql,sql2             : STRING
#VAR handle,handle2       : LONG
#
#
#SET sql = "Select D2z_client, D2z_Address_Line_2, D2z_Address_Line_3 from "+DAT_PATH+"/ssfpD2 "
//#SET sql = sql + " where D2z_client IN ('       190105','       201328','       265851','       362843')"
//#SET sql = sql + "where D2z_client IN ('      1349775') "
#SET sql = sql + "AND TRIM(D2z_Address_Line_2) = '' and TRIM(D2z_Address_Line_3) <> ''"
#
#SET stat = SQL_EXEC_DIRECT(handle,sql)
#
^FROM(8,"CLIENT ID")^^FROM(30,"OLD ADDRESS 2")^^FROM(75,"NEW ADDRESS 2 Prev Address 3")^
______________________________________________________________________________________________________________
#DO WHILE SQL_FETCH(handle) = 0
-->54  
#  SET stat = SQL_GET_STRING_COL(handle,1,client)
#  SET stat = SQL_GET_STRING_COL(handle,2,addr2)
#  SET stat = SQL_GET_STRING_COL(handle,3,addr3)
^TRIM(client)^^FROM(20,addr2)^^FROM(68,addr3)^
#
#  SET sql2 = "UPDATE "+DAT_PATH+"/ssfpd2 "
#  SET sql2 = sql2 + "SET D2z_Address_Line_2 = '" +addr3+ "', D2z_Address_Line_3 = '" +addr2+ "' "
#  SET sql2 = sql2 + "WHERE D2z_client = '" +TRIM(client)+ "' "
-->63  ^sql2^ 
#  SET stat = SQL_EXEC_DIRECT(handle2,sql2)
#ENDDO
#

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Well, you have to escape all of the quotes with a backslash (\). I've no idea how to do it automatically for you. Sorry.
Post Reply