|
Home > Archive > Visual FoxPro SQL Queries > January 2006 > Strange problem with SQLEXEC
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Strange problem with SQLEXEC
|
|
|
| Hi,
i work with foxpro 9 and mysql 5.0. As i work in a bookstore i have to add
thousands of titles everyday to my database. I use the sqlexec command to do
this wich works very fasts. To be sure that a quote ( ' ) is handled
correctly, i put a \ before it. This works fine, except when there is a
questionmark somewhere else after the \'
In my example below, you can see Edu\'Actief, Uitgeverij. Lateron you see
(last line) 'Anything else ? / 3 + ....'
Now the problem: I get the error: SQL parameter is missing. When i put a
word or a letter directly after the questionmark, i'm asked for input. If i
remove the question mark, of the \' in Edu\'Actief everything works fine. If
there isn't a question mark after the added \' in the commandline everything
is working fine also. Can anyone tell me how i can solve this???
Thanks!
Rotsj
SQLEXEC(m.lnconn, "INSERT INTO artikel (ean, isbn, ondertitel, reeks_nr,
reeks_nm, " + ;
"fysieke_vv, aant_delen, aant_paginas, geillustreerd_ind, bibl_dr_nr, " + ;
"auteur_titel, comm_oms, comm_dr_nr, boek_srt, nur_kd, bindwijze,
vervangen_door, " + ;
" eigenaar_relatie_id,
eigenaar_relatie_nm,
sbo_kd, stadium_cyclus,
versch_dat_eerste_dr
uk, " + ;
"verw_versch_dat, titelbeschrijving, formaat_lengte, Formaat_breedte,
formaat_hoogte, gewicht, " + ;
"auteur, redacteur, co_auteur, vertaler, illustrator, sec_auteur,
corporatie, bewerker, " + ;
"verpakkingsaantal, mutatiedatum) " + ;
"VALUES (9789057665943,'9057
665948','','','','Bo
ek','','141','J','1'
, " + ;
"'Ketelaars, P.J.E.*Anything else ? / 3 +
CD+CD- R','','0','S','110',
'GEB','0', " + ;
" '7200094','Edu\'Acti
ef, Uitgeverij','04','V'
,'022002','', " + ;
"'Anything else ? / 3 + CD+CD-ROM / druk 1',
'','','','','','',''
,'','','','','','0',
'20050720')")
| |
| Jeroen van Kalken 2006-01-17, 8:25 pm |
| On Tue, 17 Jan 2006 22:22:15 +0100, "Rotsj" <r.knipscheer@home.nl>
wrote:
That because VFP doesn't understand the comncept of '
therefore its quote's get out of sync and it interpretes the ? as a
request for a parameter.
Instead of just putting a \ before it, change it to something like
'+chr(34)+'
>Hi,
>i work with foxpro 9 and mysql 5.0. As i work in a bookstore i have to add
>thousands of titles everyday to my database. I use the sqlexec command to do
>this wich works very fasts. To be sure that a quote ( ' ) is handled
>correctly, i put a \ before it. This works fine, except when there is a
>questionmark somewhere else after the '
>In my example below, you can see Edu'Actief, Uitgeverij. Lateron you see
>(last line) 'Anything else ? / 3 + ....'
>Now the problem: I get the error: SQL parameter is missing. When i put a
>word or a letter directly after the questionmark, i'm asked for input. If i
>remove the question mark, of the ' in Edu'Actief everything works fine. If
>there isn't a question mark after the added ' in the commandline everything
>is working fine also. Can anyone tell me how i can solve this???
>
>Thanks!
>Rotsj
>
>SQLEXEC(m.lnconn, "INSERT INTO artikel (ean, isbn, ondertitel, reeks_nr,
>reeks_nm, " + ;
>"fysieke_vv, aant_delen, aant_paginas, geillustreerd_ind, bibl_dr_nr, " + ;
>"auteur_titel, comm_oms, comm_dr_nr, boek_srt, nur_kd, bindwijze,
>vervangen_door, " + ;
>" eigenaar_relatie_id,
eigenaar_relatie_nm,
sbo_kd, stadium_cyclus,
> versch_dat_eerste_dr
uk, " + ;
>"verw_versch_dat, titelbeschrijving, formaat_lengte, Formaat_breedte,
>formaat_hoogte, gewicht, " + ;
>"auteur, redacteur, co_auteur, vertaler, illustrator, sec_auteur,
>corporatie, bewerker, " + ;
>"verpakkingsaantal, mutatiedatum) " + ;
>"VALUES (9789057665943,'9057
665948','','','','Bo
ek','','141','J','1'
, " + ;
>"'Ketelaars, P.J.E.*Anything else ? / 3 +
>CD+CD- R','','0','S','110',
'GEB','0', " + ;
>" '7200094','Edu'Acti
ef, Uitgeverij','04','V'
,'022002','', " + ;
>"'Anything else ? / 3 + CD+CD-ROM / druk 1',
> '','','','','','',''
,'','','','','','0',
'20050720')")
>
| |
| Jack Jackson 2006-01-18, 3:24 am |
| It sounds like the ODBC driver is being confused by the ', thinking
it ends the string, and therefore it interprests the ? as parameter.
Since it works otherwise, I am assuming that ' is the correct syntax
to put a quote inside a string for MySQL. A more common convention is
to use two quotes in a row. If MySQL will accept that, you could try
that.
On Tue, 17 Jan 2006 22:22:15 +0100, "Rotsj" <r.knipscheer@home.nl>
wrote:
>Hi,
>i work with foxpro 9 and mysql 5.0. As i work in a bookstore i have to add
>thousands of titles everyday to my database. I use the sqlexec command to do
>this wich works very fasts. To be sure that a quote ( ' ) is handled
>correctly, i put a \ before it. This works fine, except when there is a
>questionmark somewhere else after the '
>In my example below, you can see Edu'Actief, Uitgeverij. Lateron you see
>(last line) 'Anything else ? / 3 + ....'
>Now the problem: I get the error: SQL parameter is missing. When i put a
>word or a letter directly after the questionmark, i'm asked for input. If i
>remove the question mark, of the ' in Edu'Actief everything works fine. If
>there isn't a question mark after the added ' in the commandline everything
>is working fine also. Can anyone tell me how i can solve this???
>
>Thanks!
>Rotsj
>
>SQLEXEC(m.lnconn, "INSERT INTO artikel (ean, isbn, ondertitel, reeks_nr,
>reeks_nm, " + ;
>"fysieke_vv, aant_delen, aant_paginas, geillustreerd_ind, bibl_dr_nr, " + ;
>"auteur_titel, comm_oms, comm_dr_nr, boek_srt, nur_kd, bindwijze,
>vervangen_door, " + ;
>" eigenaar_relatie_id,
eigenaar_relatie_nm,
sbo_kd, stadium_cyclus,
> versch_dat_eerste_dr
uk, " + ;
>"verw_versch_dat, titelbeschrijving, formaat_lengte, Formaat_breedte,
>formaat_hoogte, gewicht, " + ;
>"auteur, redacteur, co_auteur, vertaler, illustrator, sec_auteur,
>corporatie, bewerker, " + ;
>"verpakkingsaantal, mutatiedatum) " + ;
>"VALUES (9789057665943,'9057
665948','','','','Bo
ek','','141','J','1'
, " + ;
>"'Ketelaars, P.J.E.*Anything else ? / 3 +
>CD+CD- R','','0','S','110',
'GEB','0', " + ;
>" '7200094','Edu'Acti
ef, Uitgeverij','04','V'
,'022002','', " + ;
>"'Anything else ? / 3 + CD+CD-ROM / druk 1',
> '','','','','','',''
,'','','','','','0',
'20050720')")
>
| |
| Christian Ehlscheid 2006-01-18, 3:24 am |
| Hello,
if you use query parameters instead of literal values in your INSERT
statement can solve your problem easily and don't have to escape special
characters inside strings.
e.g.
LOCAL lcSQL
lcSQL = "INSERT INTO artikel (ean, isbn, ondertitel, reeks_nr,...)" + ;
" VALUES (?lcEan, ?lcIsbn, ?lcOndertitel, ?lnReeksNo, ...)"
LOCAL lnEan, lnIsbn, lcOndertitel, lnReeksNo
lcEan = 9789057665943
lnIsbn = '9057665948'
lcOndertitel = "Whateveryouwant including special chars '?\@03945903850"
and so on ...
?SQLEXEC(m.lnConn,lcSQL)
you've written you insert many titles, it would be much easier to wrote
a small programm which creates a cursor with the same structure as your
MySQL table, set it up to use tablebuffering, then find the
added/modified records in a loop with GETNEXTMODIFIED and update each
row programmatically
e.g. something like
SQLEXEC(m.lnCon,'SELECT * FROM yourTable WHERE 1=2','workCursor')
CURSORSETPROP("Buffering",5,'workCursor')
BROWSE && do editing
LOCAL lnRec, lcSQL
lnRec = 0
lcSQL = "INSERT INTO yourTable (ean, ...) VALUES (?workCursor.ean, ...)"
lnRec = GETNEXTMODIFIED(lnRe
c,'workCursor')
DO WHILE lnRec != 0
SQLEXEC(m.lnConn,lcSQL)
ENDDO
TABLEUPDATE(.T.,.T.,'workcursor') && commit changes to local cursor
(won't update the backend)
Regards
Christian
| |
|
| Thank you, Jeroen and Jack,
the '+chr(34)+' solution did work in vfp, but didn't in mysql. The double
quote option did the trick.
Thanks for help!
Rotsj
"Rotsj" <r.knipscheer@home.nl> schreef in bericht
news:dqjn56$edm$1@ne
ws6.zwoll1.ov.home.nl...
> Hi,
> i work with foxpro 9 and mysql 5.0. As i work in a bookstore i have to add
> thousands of titles everyday to my database. I use the sqlexec command to
do
> this wich works very fasts. To be sure that a quote ( ' ) is handled
> correctly, i put a \ before it. This works fine, except when there is a
> questionmark somewhere else after the '
> In my example below, you can see Edu'Actief, Uitgeverij. Lateron you see
> (last line) 'Anything else ? / 3 + ....'
> Now the problem: I get the error: SQL parameter is missing. When i put a
> word or a letter directly after the questionmark, i'm asked for input. If
i
> remove the question mark, of the ' in Edu'Actief everything works fine.
If
> there isn't a question mark after the added ' in the commandline
everything
> is working fine also. Can anyone tell me how i can solve this???
>
> Thanks!
> Rotsj
>
> SQLEXEC(m.lnconn, "INSERT INTO artikel (ean, isbn, ondertitel, reeks_nr,
> reeks_nm, " + ;
> "fysieke_vv, aant_delen, aant_paginas, geillustreerd_ind, bibl_dr_nr, " +
;
> "auteur_titel, comm_oms, comm_dr_nr, boek_srt, nur_kd, bindwijze,
> vervangen_door, " + ;
> " eigenaar_relatie_id,
eigenaar_relatie_nm,
sbo_kd, stadium_cyclus,
> versch_dat_eerste_dr
uk, " + ;
> "verw_versch_dat, titelbeschrijving, formaat_lengte, Formaat_breedte,
> formaat_hoogte, gewicht, " + ;
> "auteur, redacteur, co_auteur, vertaler, illustrator, sec_auteur,
> corporatie, bewerker, " + ;
> "verpakkingsaantal, mutatiedatum) " + ;
> "VALUES (9789057665943,'9057
665948','','','','Bo
ek','','141','J','1'
, " +
;
> "'Ketelaars, P.J.E.*Anything else ? / 3 +
> CD+CD- R','','0','S','110',
'GEB','0', " + ;
> " '7200094','Edu'Acti
ef, Uitgeverij','04','V'
,'022002','', " + ;
> "'Anything else ? / 3 + CD+CD-ROM / druk 1',
> '','','','','','',''
,'','','','','','0',
'20050720')")
>
>
|
|
|
|
|