|
| SQL Insert And Apostrophe |
| Iniciado por guest, 12,abr. 2018 23:29 - 4 respuestas |
| |
| | | |
|
| |
| Publicado el 12,abril 2018 - 23:29 |
Hi All
I am needing some advice constructing the contents of a variable to be passed to SQL in an insert statement where the contents of the variable contains an apostrophe.
I have tried replacing "'"with "''" using:
Replace(MyString,"","''")
I am still getting a failed insert when using HExecuteSQLQuery.
Any ideas on the best way to handle this?
Cheers
André |
| |
| |
| | | |
|
| | |
| |
| Publicado el 13,abril 2018 - 00:52 |
Hi André,
AFAIK, the best way is to use hadd instead of an insert statement, but if you insist, and if I remember correctly you have to DOUBLE your single quote.
Now that may depend of the type of DB you are targeting...
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 13,abril 2018 - 07:09 |
Hi Fabrice
I have tried the double quote and "\'" and of course neither work. The db does take it from any other tool - doubling that is - WX is doing something strange in the black box. HAdd was tanking the sequence setting in the db for automatic ID fields and I could never get around that with HAdd so I went for HExecuteSQLQuery where I can control which fields are being passed. I have now removed the automatic ID from the analysis and will try the HAdd again. I may be between a rock and a hard place here.
Cheers
André |
| |
| |
| | | |
|
| | |
| |
| Publicado el 14,abril 2018 - 00:09 |
Andre
Pls see below sample if it works out for you:
sSingleQ is string = "''"
s2SingleQ is string = "''"
sDesc is string
sSqlStmt is string
sDesc = [
ADJUSTABLE HEAD-HOOK SPANNER 2" TO 4-3/4' DIA.
]
sDesc = Replace(sDesc, sSingleQ, s2SingleQ)
sSqlStmt = [
insert into myProduct (Desc) values ('%1')
]
sSqlStmt=stringBuild(sSqlStmt, sDesc)
trace(sSqlStmt)
//Result insert into myProduct (Desc) values ('ADJUSTABLE HEAD-HOOK SPANNER 2" TO 4-3/4' DIA.')
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 14,abril 2018 - 12:44 |
Hi King
Will test that.
In the meantime I have removed the PK from the analysis and reverted to HAdd and WX is not trashing the sequence value and of course the apostrophe issue is resolved. So far so good.
But will definitely try your code as well.
Cheers
André |
| |
| |
| | | |
|
| | | | |
| | |
|