Page 1 of 1
SQL updates with ' and " in the string
Posted: Sun Jul 01, 2007 6:14 pm
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?
Posted: Sun Jul 01, 2007 6:25 pm
by superdezign
Posted: Sun Jul 01, 2007 6:37 pm
by Chucky
feyd | Please use 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
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]
Posted: Sun Jul 01, 2007 6:46 pm
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.