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?
SQL updates with ' and " in the string
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
feyd | Please use
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]
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]- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm