Apostrophe in a string

[SBTBILL]

I need to update an SQL table from VFP 6. I can not change editions as the SBT/ACCPAC version in use is PRO 6.0 which runs in it. Upgrading is not a current option.

I'm sending a set mailaddress=mmailaddress command as part of an SQL statement.

We have a few distributors who live on streets like B'Bob

I store the street address into a variable using the following:

STORE NVL(rs.FIELDS("MAILADDRESS1").VALUE,"") TO m1mailaddress

This generally works fine.

The update is created like this:

lcsql=lcsql+"ship_address='"+ALLTRIM(m1mailaddress)+"', "

This results in code that looks similar to this:

UPDATE DISTRIBUTORS.DBO.DISTRIBUTORS SET MAILADDRESS1 = '1919 SOUTH B'BOB STREET' WHERE DIST_ID=1093

The issue here is when a string contains something like B'Bob it crashes.

A suggesting has been made to check every string for an apostrophe and replace it with a double apostrophe. This works in SQL Query Analysier, haven't tried it in Fox but sounds like this would cause a speed hit.

Any suggestions?

Bill Couture
sbtbill@aol.com

http://www.sbtbill.com

 

[GriffMG]

There are a couple of ways of doing this...

If you KNOW there might be an apostrophe then build your string in a different way (use a different delimiter).

for example:

lcsql=lcsql+[ship_address="]+ALLTRIM(m1mailaddress)+[", ]

But then you're stuffed if your string contains double quotes or a SQL string!

So, if you want to be sure of getting your string into the database - use an SQL parameter (sorry, but I'm showing a VFP OLE DB variant, if SQL is different I'm sure someone will update it!):

Set cnMyConn = Server.CreateObject("ADODB.Connection")
Set cmdMyCmd = Server.CreateObject("ADODB.Command")
cnMyConn.Open Session("ConnectionString")
cmdMyCmd.activeconnection = cnMyConn
cmdMyCmd.commandtype = 1
cmdMyCmd.Parameters.Append cmd.CreateParameter("@A",201,1,Len(strWhichMayBeDodgy), strWhichMayBeDodgy)
cmdMyCmd.commandtext = "Update MyTable Set MyField= ? where SomeThing=1093"
cmdMyCmd.execute()

This approach means you can insert absolutely any text into the database - or binary data, but it requires a bit more work!

HTH

Regards

Griff
Keep [Smile]ing

 

[skoko]

instead of:

UPDATE DISTRIBUTORS.DBO.DISTRIBUTORS SET MAILADDRESS1 = '1919 SOUTH B'BOB STREET' WHERE DIST_ID=1093

try:

UPDATE DISTRIBUTORS.DBO.DISTRIBUTORS SET MAILADDRESS1 = "1919 SOUTH B'BOB STREET" WHERE DIST_ID=1093

 

[SBTBILL]

The darn apostrophe issue turns out to be a big one with SQL checked that forum and am seeing things about using a double single '. Tried switching quote and single quote doesn't do any good. Also I don't know if the apostrophe will be there as this comes in the form of a variable.

Bill Couture

 

[DSummZZZ]

...check every string for an apostrophe and replace it ... sounds like this would cause a speed hit.

It may, but it's better than corrupted data. It also depends on how many records are getting updated.

You maybe substitute it on the fly:

lcsql=lcsql+"ship_address='"+[COLOR=blue]Chrtran([/color]ALLTRIM(m1mailaddress)[COLOR=blue], "'", "`")[/color]+"', "

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Bill,

One thing you haven't mentioned is what type of database this is. You mentioned "SQL tables". What back end are you using? This is important, because the method of handling apostrophes varies quite a lot.

For example, in SQL Server, you need to insert an extra apostrophe. That is, if the string is O'Brian, you need to send it as O''Brian -- that's two single apostrophes, not a double-quote mark. It will actually be stored and retrieved as a single apostrophe, which is what you want.

But that is not necessarily the case with other back ends.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[SBTBILL]

It's MS SQL Server. Am working on the double apostrophe method. In the real world have a number of fields to check.

Dang and people say xbase tables are old fashoned. I haven't needed to deal with this stuff since GWBASIC and then they had line input.

Next thing is that O'Brien is going to go on line and fix the spelling of his name. My predecessors shoved the double apostrophe into some of the data. My test data crashs their ASP app, too.

Bill Couture

 

[Mike Lewis]

Bill,

that O'Brien is going to go on line and fix the spelling of his name. [/i

It shouldn't be up to the user to have to worry about that. You should be able to write a bit of generic code that will generate the double apostrophe for an UPDATE or INSERT statement without the user being aware of it.

In my Data Manager class, I have some code that takes a record from a VFP cursor or view, and generates an UPDATE statement from it for all of the changed fields. That code takes care of converting data types and so forth, and also deals with the double apostrophe question. It's not too difficult to cook up something like that.

Dang and people say xbase tables are old fashoned.

So what does that prove? They might be old, but they perform their function perfectly well, provided you understand their limitation. (SQL is even older than xBase, but that's not a reason for not using it.)

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com