help on xbasic sql insert statement that handles names with a quote - I'm drawing a blank - on it, I must be losing it.

[peteconway]

names like O'Connor -

assistance appreciated.

Pete

[Lance Gurd]

Hi Pete,

in normal use I would have said use the "" (Backslash) character but I thought I would use the SQL insert genie (right click in the xbasic editor) to see how Alpha would write the code and it used arguments and came up with the following

dim cn as sql::connection
dim flag as l
flag = cn_open(cn,"::Name::Jobs")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "INSERT INTO clients(LAST_NAME,COMP_NO) VALUES (:LASTNAME,:COMPNO)"
dim args as sql::arguments
args.add("LASTNAME","o'conner")
args.add("COMPNO",convert_type("1","N"))
'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()

[Russell Craycraft]

https://w3schools.sinsixx.com/js/js_...acters.asp.htm

[christappan]

I usually use another single quote. O'Connor --> O''Connor

[madtowng]

Chris' option works in mysql and sql server(sql express).
My preferred option is to change the character to a pipe, then change the pipe back to the original character when retrieving the data.

[christappan]

I guess that works too, madtowng. Just to be clear, Alpha is automatically escaping the characters when it runs queries in lists and grids and data-bound UXs, etc. And I'm pretty confident it handles them when they're passed to a query via arguments in XBasic. But if you're writing a query in SSMS or MySQL Workbench, or you're writing a query directly in XBasic, you'll need to handle them yourself.

[madtowng]

Seems strange to see what Alpha does.
[code]<SQLArguments>
<SQLArgument>
<Name>lastname</Name>
<Data Type="C">O&apos;Brien</Data>
<IsNull Type="L">0</IsNull>
<Usage>Input</Usage>
</SQLArgument>
</SQLArguments>
[/code

[peteconway]

Thanks everyone - I'll look at all the options and report back( once Santa has done his Job tomorrow (Australia)) - I though I would be alone on this problem, I'm glad I got these responses, was doubting myself - and thinking the whole thing is weird that there isn't an obvious xbasic function to accommodate what is a very common occurrence..

Happy Holidays to you all.

[Jay Talbott]

One final thought, (although I have not tested it yet):
Can you do a stringtran and replace the apostrophe with CHR(39) as in:

STRTRAN(LAST_NAME,"'","+CHR(39)+")

Again, I have not tested it, but it may/should work.

Merry Christmas,
Jay

[Al Buchholz]

Thanks everyone - I'll look at all the options and report back( once Santa has done his Job tomorrow (Australia)) - I though I would be alone on this problem, I'm glad I got these responses, was doubting myself - and thinking the whole thing is weird that there isn't an obvious xbasic function to accommodate what is a very common occurrence..

Happy Holidays to you all.

Isn't it already tomorrow in Australia?
Santa2021_10pc.jpg

[glenschild]

Isn't it already tomorrow in Australia?
Santa2021_10pc.jpg

Would it not be "today" in Oz no matter what? "tomorrow" will always be "tomorrow"? :-)

Pedantic Englishman!!!

[peteconway]

Cheers - guys, Jay I think that will work..
Pete.. speak tomorrow

[madtowng]

Hey Pete,

Assuming you use arguments in your sql, this is one time where Alpha does things right.
if your code is " select lastname from clients where lastname = :lastName " and you
configured the args to include args.Set("lastName","O'bri"), alpha does it's trickery as shown in post 7
to make the insert work as expected.

[peteconway]

The answer was hard to find but easy to implement, you just replace the single ' to '' double.
Thanks everyone, I have been totally drained from solving issues (bug work arounds) in the last few months and was burnt out, had a months break now - all good.

dim firstName as C = ALLTRIM(e.dataSubmitted.FirstName) Sally
dim lastName as c = STRTRAN(e.dataSubmitted.LastName,"'","''") O'Toole
sql = "INSERT INTO [Authoriser] (LastName,FirstName) "

sql = sql + "VALUES ('"+ lastName + "','" + firstName)

Sally O'Toole