dBASE SQL Servers - SQL apostrophe issue

Author

SQL apostrophe issue

Ronnie MacGregor

2006-02-10, 8:23 pm

Hi folks

Hit a snag on the first trial live run copy of thousands of Patient details
from dbf to MSSQL where there is an apostrophe in a dbf field value.

This of course is interpreted (probably by the BDE since it's not a MSSQL error
message) as a "missing right quote" !

Does anybody have a proven dbl escape type routine for finding and replacing
single quotes with two single quotes.

The dUFLP doesn't seem to have one (that I can find), but I have a recollection
of Bowen posting this type of function.

....... or I'll just have to write one <g> ..... in the morning when I can see
and think straight <g>.

Robert ?
André ?
Jan ?

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Ronnie MacGregor

2006-02-11, 7:23 am

On Fri, 10 Feb 2006 23:06:59 -0000
Ronnie MacGregor said :

> Does anybody have a proven dbl escape type routine for finding and replacing
> single quotes with two single quotes.

Never mind .... no doubt I've re-invented the wheel <g> :

function ReplaceApostrophe(cT
ext)
local nCount
for nCount = 1 to len(cText)*2 step 2
nPos = at( ['], cText, nCount)
if nPos == 0
exit
else
cText := stuff( cText, nPos, 1, "''" )
endif
endfor // next
return cText

..... unless anyone has a better approach,

..... or a Romainesque one liner <g>.

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Robert Bravery

2006-02-11, 7:23 am

Hi Ronnie,
A few things here
What is the data/field type of the problematic column.
Is the data already in the server, and you have trouble reading it or are
you trying to enter data that contains an apostrophe
If youre trying to enter data, how are you doing it, via a sqlupdate
command, or are you letting dbase handle the saves.
Do you want to change the underlying data or the way dbase views or
interprets it or the view of you form.
The error message seems to be a BDE one, Specifically 11830.
I have tried this with differnt connections to MSSQL, ODBC supplied for
MSQL2000, ODBC supplied for MSQL2005, Native Links supplied with dbase, with
no problems. Basically I opened a table, changed both a char and a varchar
field, added a ' and saved without a problem. Retrieved the data the ' was
there. Looked through SQL Manager, and the ' qas there

Are you doing anything different

I tried to enter an ' in the sqlmanageer and got this error "the value you
entered is not consistant with the datatype or length of the
colum", Could not use the update command. But, with the update command I
used two single quotes, and it stored as a single apostrophe.
eg
update mytable set mytextcolumn = ' this is''nt an apostrophe but it is'
as you would know the single quote ' is a string identifier/delimiter in
MSSQL

ANyway, I suspect youre doing something like
sqlexec("update company set description ='THE COLD CHAIN - KLERKSDORP -
DELETE'D' where companyid =2485")
this give a dberror() of 11830 and a dbmessage() of "missing right quote"
but
sqlexec("update company set description ='THE COLD CHAIN - KLERKSDORP -
DELETE''D' where companyid =2485")
works, notice two single quotes note double quote
I get the same error using:
d = new database("rassql")
d.executesql("update company set description ='THE COLD CHAIN - KLERKSDORP -
DELETE'D' where companyid =2485")
but this works
d.executesql("update company set description ='THE COLD CHAIN - KLERKSDORP -
DELETE''D' where companyid =2485")
so you need to use two single quotes instead of double.
But if youre using a reference to something you would have to change and
replace before updateing
so
x= " this isn't what you think"
d.executesql("update company set description ="+x+" where companyid =2485")
does'nt work
but this does work
x= " this isn't what you think"
d.executesql("update company set description ='"+STUFF(x, at("'",x), 1,
"''")+"' where companyid =2485")
and
sqlexec("update company set description ='"+STUFF(x, at("'",x), 1, "''")+"'
where companyid =2485")

Hope this helps

Robert

"Ronnie MacGregor" <No_Sp@m.Thanks> wrote in message
news:MPG. 1e572cec9dc051ce9897
13@news.dbase.com...
> Hi folks
>
> Hit a snag on the first trial live run copy of thousands of Patient
details
> from dbf to MSSQL where there is an apostrophe in a dbf field value.
>
> This of course is interpreted (probably by the BDE since it's not a MSSQL
error

> message) as a "missing right quote" !
>
> Does anybody have a proven dbl escape type routine for finding and
replacing
> single quotes with two single quotes.
>
> The dUFLP doesn't seem to have one (that I can find), but I have a
recollection
> of Bowen posting this type of function.
>
> ...... or I'll just have to write one <g> ..... in the morning when I can
see
> and think straight <g>.
>
> Robert ?
> André ?
> Jan ?
>
> --
> Ronnie MacGregor
> Scotland
>
> Ronnie at
> dBASEdeveloper
> dot co dot uk
>
> www.dBASEdeveloper.co.uk
>
>
>

Robert Bravery

2006-02-11, 7:23 am

HI,
Does time go backwards in Scotland.
You first post showed as 1.06
youre secnd post, reply to first, showed as 1.00

Robert

"Ronnie MacGregor" <No_Sp@m.Thanks> wrote in message
news:MPG. 1e57d442811baabc9897
14@news.dbase.com...
>
> On Fri, 10 Feb 2006 23:06:59 -0000
> Ronnie MacGregor said :
>
replacing[color=dark
red]
>
> Never mind .... no doubt I've re-invented the wheel <g> :
>
>
> function ReplaceApostrophe(cT
ext)
> local nCount
> for nCount = 1 to len(cText)*2 step 2
> nPos = at( ['], cText, nCount)
> if nPos == 0
> exit
> else
> cText := stuff( cText, nPos, 1, "''" )
> endif
> endfor // next
> return cText
>
>
>
> .... unless anyone has a better approach,
>
> .... or a Romainesque one liner <g>.
>
> --
> Ronnie MacGregor
> Scotland
>
> Ronnie at
> dBASEdeveloper
> dot co dot uk
>
> www.dBASEdeveloper.co.uk
>
>
>

Ronnie MacGregor

2006-02-11, 7:23 am

On Sat, 11 Feb 2006 14:22:53 +0200
Robert Bravery said :

> Does time go backwards in Scotland.

In this little part of Scotland, I can tell you that time is passing at least
twice as fast as it should. Ten years backwards would suit me fine <g>.

I didn't see your reply prior to posting my last reply, so I have to ask if SA
messages detour through the ether for a few hours before landing <g>.

As you probably have figured out, I am transferring data from dbf to MSSQL and
needed to deal with surnames containing an apostrophe :

O'Berst
O'Brien
O'Donnell
O'Hagen
O'Hare
O'Mara
O'Neill
O'Rourke

These are mostly of Irish derivation, maybe you're not faced with this problem
in SA so much.

Your suggested code :

> d.executesql("update company set description ='"+STUFF(x, at("'",x), 1,
> "''")+"' where companyid =2485")

looks as if it will only deal with a single occurrence of a ['] but this is not
enough. Consider the following text :

I can't find any evidence of this employee's 'behaviour' being filed
in this company's records to date.

I'm sure you see the problem.

This is what my little function is designed to deal with. Just feed any text
string through the function prior to either an update or insert.

The ultimate test for this function was to get a string of six [''''''] into
MSSQL intact, and it is handled fine.

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Roland Wingerter

2006-02-11, 9:23 am

Ronnie MacGregor wrote[color=darkred]

>
> On Fri, 10 Feb 2006 23:06:59 -0000
> Ronnie MacGregor said :
>
------
I see it's too late now, but there's something in the dUFLP!

set proc to :dUFLP:stringEx.cc
? new stringEx().strTran("O'Leary's wife doesn't like her husband's
attitude", "'", "''")

Roland

Robert Bravery

2006-02-12, 7:23 am

Well, we are a bit backward here in africa. What with strikes and downing
tools and go slow, I would'nt be surprised if my data packed hita go slow
somehwere.
But then again you scots are O'So'strange
I had thought about that only after I send the reply. But yes you would have
a function to replace all single ' with two ' and this could be called in
the update statement. Or you could have an SP and call the sp pass it the
text and have it do the crrections and then update the data. That would be
more universal, and it could be used globally.
But anyway, was I on the correct track as to your requirements.

Robert

"Ronnie MacGregor" <No_Sp@m.Thanks> wrote in message
news:MPG. 1e57f0d8a9e946119897
15@news.dbase.com...
>
> On Sat, 11 Feb 2006 14:22:53 +0200
> Robert Bravery said :
>
>
> In this little part of Scotland, I can tell you that time is passing at
least

> twice as fast as it should. Ten years backwards would suit me fine <g>.
>
> I didn't see your reply prior to posting my last reply, so I have to ask
if SA
> messages detour through the ether for a few hours before landing <g>.
>
> As you probably have figured out, I am transferring data from dbf to MSSQL
and
> needed to deal with surnames containing an apostrophe :
>
> O'Berst
> O'Brien
> O'Donnell
> O'Hagen
> O'Hare
> O'Mara
> O'Neill
> O'Rourke
>
> These are mostly of Irish derivation, maybe you're not faced with this
problem
> in SA so much.
>
> Your suggested code :
>
>
> looks as if it will only deal with a single occurrence of a ['] but this
is not
> enough. Consider the following text :
>
> I can't find any evidence of this employee's 'behaviour' being filed
> in this company's records to date.
>
> I'm sure you see the problem.
>
> This is what my little function is designed to deal with. Just feed any
text
> string through the function prior to either an update or insert.
>
> The ultimate test for this function was to get a string of six ['''''']
into
> MSSQL intact, and it is handled fine.
>
> --
> Ronnie MacGregor
> Scotland
>
> Ronnie at
> dBASEdeveloper
> dot co dot uk
>
> www.dBASEdeveloper.co.uk
>
>
>

Robert Bravery

2006-02-12, 7:23 am

Well there you go

RObert

"Roland Wingerter" <ich@hier.de> wrote in message
news:TwyqZSxLGHA.592@news-server...
> Ronnie MacGregor wrote
> ------
> I see it's too late now, but there's something in the dUFLP!
>
> set proc to :dUFLP:stringEx.cc
> ? new stringEx().strTran("O'Leary's wife doesn't like her husband's
> attitude", "'", "''")
>
> Roland
>
>

Ronnie MacGregor

2006-02-12, 7:23 am

On Sat, 11 Feb 2006 15:01:00 +0100
Roland Wingerter said :

> I see it's too late now, but there's something in the dUFLP!
>
> set proc to :dUFLP:stringEx.cc
> ? new stringEx().strTran("O'Leary's wife doesn't like her husband's
> attitude", "'", "''")

So there is !

Hmmmm.... I think in this instance job specific code is much tighter than
generic code.

Thanks.

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Ronnie MacGregor

2006-02-12, 7:23 am

On Sun, 12 Feb 2006 12:01:50 +0200
Robert Bravery said :

> I had thought about that only after I send the reply. But yes you would have
> a function to replace all single ' with two ' and this could be called in
> the update statement. Or you could have an SP and call the sp pass it the
> text and have it do the crrections and then update the data. That would be
> more universal, and it could be used globally.

I'll get to SP stuff in time no doubt, but for now my feeling was that it was
better to do this in dbl before the data hit the sql statement.

> But anyway, was I on the correct track as to your requirements.

Yes, you not only identified this problem, but covered other snippets of
interest too.

Thanks.

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Roland Wingerter

2006-02-12, 8:23 pm

Ronnie MacGregor wrote

>
> Hmmmm.... I think in this instance job specific code is much tighter than
> generic code.
-------
I agree.

> Thanks.
-------
You are welcome.

Regards

Roland

Jan Hoelterling

2006-02-13, 11:23 am

"Ronnie MacGregor" <No_Sp@m.Thanks> wrote in message
news:MPG. 1e572cec9dc051ce9897
13@news.dbase.com...
> Jan ?
>
> --
> Ronnie MacGregor
> Scotland

Sorry I was too late - just got in from a long weekend and noticed you've
already solved this yourself!

Jan

Ronnie MacGregor

2006-02-13, 8:23 pm

On Mon, 13 Feb 2006 10:11:00 -0600
Jan Hoelterling said :

> Sorry I was too late - just got in from a long weekend

No problem ... hope you had a good time.

> and noticed you've
> already solved this yourself!

The best way to solve a problem is to fix it yourself ...... with a little help
from friends <g>.

--
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Sponsored Links