Padding Single Quotes in SQL Data Before Updating to an ADO Database
From Support
| If you have a question or seek clarification, please call Technical Support. |
[edit]
Problem:
You are sending data to the PC with embedded single quotes and want a way to automatically place double quotes into the data before the data is inserted into a SQL Server table.
[edit]
Solution:
As of V1.20 of the RPG2SQL Integrator library, there is a new function called SQL_Quote which can be used to automatically pad all single quotes with double quotes before issuing a SQL INSERT or UPDATE. A string variable of up to 2048 bytes can be passed to SQL_Quote and the resulting string data is passed back to the RPG program.
Listed below is a sample SQL Insert statement that uses SQL_Quote to pad data:
*---------------------------------------------------------------------
* ** Run Insert SQL Query to Insert Record
* ** Uses SQL_Quote to pad any fields where single
* ** quotes might appear.
*---------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLExec(SQL_Socket:
C 'insert into NameAddress' +
C '(First,' +
C 'Last,' +
C 'Address1,' +
C 'Address2,' +
C 'City,' +
C 'State,' +
C 'Zip,' +
C 'Phone,' +
C 'Fax,' +
C 'Email,' +
C 'Date1,' +
C 'Dollars) ' +
C 'VALUES(' +
C quot + SQL_Quote('James') + quot + ',' +
C quot + SQL_Quote('Johnson') + quot + ','
C quot + '111 Main Street' + quot + ',' +
C quot + 'Apt 5' + quot + ',' +
C quot + 'Mpls' + quot + ',' +
C quot + 'MN' + quot + ',' +
C quot + '55555' + quot + ',' +
C quot + '111-111-1111' + quot + ',' +
C quot + '222-222-2222' + quot + ',' + C quot + 'james@johnson.com' + quot + ',' +
C quot + '12/25/2002' + quot + ',' +
C '123456.78' +
C ')')
