PC SOFT

FORUMS PROFESSIONNELS
WINDEV, WEBDEV et WINDEV Mobile

Accueil  |  Messages récents  | Connexion  |   | Français  FR
Accueil → WINDEV 2024 → SQL Insert And Apostrophe
SQL Insert And Apostrophe
Débuté par André Labuschagné, 12 avr. 2018 23:29 - 4 réponses
#1
Posté le 12 avril 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é
#2
Posté le 13 avril 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
#3
Posté le 13 avril 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é
#4
Posté le 14 avril 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
#5
Posté le 14 avril 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é
→ Revenir à WINDEV 2024