f



SQL Errors: Where is the error?

     I have just finished -- I hope I have finished -- some changes to
one program of mine that has some hairy SQL.  One of the SQL statments
is 40 lines long.  Granted it could be shorter vertically, but it is
still 852 characters long.

     Occasionally -- read "All too often" -- while wrestling with this
program, there would be an error in my SQL syntax.  VFP is not
terribly helpful with such error messages as 
          Syntax error.
          Command is missing required clause.
These are not terribly useful.  Sometimes, I have hunted for minutes
before realising that, say, a comma was missing.

     What do you do to quickly find errors in long SQL statements?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.
0
Gene
12/23/2007 4:08:17 AM
comp.databases.xbase.fox 802 articles. 0 followers. Post Follow

10 Replies
1104 Views

Similar Articles

[PageSpeed] 17

If it can be re-written so as to incorporate some independent intermediate 
queries, do so until each is running smoothly. Then add them back in, one at 
a time. You may find that you gain some speed if you don't add everything 
back in.
Another thing I do is to put long clauses into #DEFINE's and/or macro subs. 
This lets me hide some of the complexity while debugging.

"Gene Wirchenko" <genew@ocis.net> wrote in message 
news:o1nrm3dfulbe7g56mj01d9j5uml3vd8mdp@4ax.com...
>     I have just finished -- I hope I have finished -- some changes to
> one program of mine that has some hairy SQL.  One of the SQL statments
> is 40 lines long.  Granted it could be shorter vertically, but it is
> still 852 characters long.
>
>     Occasionally -- read "All too often" -- while wrestling with this
> program, there would be an error in my SQL syntax.  VFP is not
> terribly helpful with such error messages as
>          Syntax error.
>          Command is missing required clause.
> These are not terribly useful.  Sometimes, I have hunted for minutes
> before realising that, say, a comma was missing.
>
>     What do you do to quickly find errors in long SQL statements?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>     I have preferences.
>     You have biases.
>     He/She has prejudices. 


0
Lew
12/24/2007 3:59:19 AM
That's been a bugbear in using SQL from day one since VFP sees the query as 
one command. There's not a lot you can do about it.

Careful and consistent formatting can help you spot places where there's a 
missing comma or semicolon because they just won't look right. Sometimes. 
<g>

I've been known to set a breakpoint on the line immediately preceding the 
errant query so the environment is set up for it, and while suspended I copy 
the query to the command window and start pruning until I get something that 
runs. Often, just the act of looking at the query outside the program 
environment makes the error jump out at me.

I share your pain.

Dan

Gene Wirchenko wrote:
>     I have just finished -- I hope I have finished -- some changes to
> one program of mine that has some hairy SQL.  One of the SQL statments
> is 40 lines long.  Granted it could be shorter vertically, but it is
> still 852 characters long.
>
>     Occasionally -- read "All too often" -- while wrestling with this
> program, there would be an error in my SQL syntax.  VFP is not
> terribly helpful with such error messages as
>          Syntax error.
>          Command is missing required clause.
> These are not terribly useful.  Sometimes, I have hunted for minutes
> before realising that, say, a comma was missing.
>
>     What do you do to quickly find errors in long SQL statements?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>     I have preferences.
>     You have biases.
>     He/She has prejudices. 


0
Dan
12/24/2007 4:14:17 PM
Dan Freeman wrote:
> That's been a bugbear in using SQL from day one since VFP sees the query as 
> one command. There's not a lot you can do about it.
> 
> Careful and consistent formatting can help you spot places where there's a 
> missing comma or semicolon because they just won't look right. Sometimes. 
> <g>
> 
> I've been known to set a breakpoint on the line immediately preceding the 
> errant query so the environment is set up for it, and while suspended I copy 
> the query to the command window and start pruning until I get something that 
> runs. Often, just the act of looking at the query outside the program 
> environment makes the error jump out at me.
> 
> I share your pain.
> 
> Dan
> 
> Gene Wirchenko wrote:
>>     I have just finished -- I hope I have finished -- some changes to
>> one program of mine that has some hairy SQL.  One of the SQL statments
>> is 40 lines long.  Granted it could be shorter vertically, but it is
>> still 852 characters long.
>>
>>     Occasionally -- read "All too often" -- while wrestling with this
>> program, there would be an error in my SQL syntax.  VFP is not
>> terribly helpful with such error messages as
>>          Syntax error.
>>          Command is missing required clause.
>> These are not terribly useful.  Sometimes, I have hunted for minutes
>> before realising that, say, a comma was missing.
>>
>>     What do you do to quickly find errors in long SQL statements?
>>
>> Sincerely,
>>
>> Gene Wirchenko
>>
>> Computerese Irregular Verb Conjugation:
>>     I have preferences.
>>     You have biases.
>>     He/She has prejudices. 
> 
> 
If you think VFP's handling is bad, try SQL Server with it's "Error near 
keyword 'WHERE'" type stuff.  I find it almost never is actually 
anywhere near where the error actually is.

-- 
Cy Welch
Senior Programmer
MetSYS Inc
http://www.metsysinc.com
0
Cy
12/24/2007 9:55:15 PM
Gene -

I take a different approach.
I programmatically build SQL Select statements,
and run each piece of it (select, into, where, join, etc)
through a sql pre-processor util that i built some time ago.

i have different flavours of it now, for native vfp, ms-sql, mySql,
postGreSql and oracle.
i think the first iteration of it - i built it in about 2 months of spare
time fiddling.

you may want to take a hard look at making yer own sql pre-processor util.

Regards [Bill]
--
===================
William Sanders / EFG  VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net  www.viasqlserver.net

"Gene Wirchenko" <genew@ocis.net> wrote in message
news:o1nrm3dfulbe7g56mj01d9j5uml3vd8mdp@4ax.com...
>      I have just finished -- I hope I have finished -- some changes to
> one program of mine that has some hairy SQL.  One of the SQL statments
> is 40 lines long.  Granted it could be shorter vertically, but it is
> still 852 characters long.
>
>      Occasionally -- read "All too often" -- while wrestling with this
> program, there would be an error in my SQL syntax.  VFP is not
> terribly helpful with such error messages as
>           Syntax error.
>           Command is missing required clause.
> These are not terribly useful.  Sometimes, I have hunted for minutes
> before realising that, say, a comma was missing.
>
>      What do you do to quickly find errors in long SQL statements?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>      I have preferences.
>      You have biases.
>      He/She has prejudices.


0
swdev2
12/25/2007 5:25:08 AM
I make sure my SQL is broken up so everything is on its own line, such as 
this ...

SELECT Something.*, ;
               SomethingElse.* ;
    FROM Something ;
        LEFT OUTER JOIN Something ELSE ;
            ON SomethingElse.Field = Something.Field ;
    ORDER BY xyz


If I have a problem with the SQL .. I cut and paste the code into a JUNK.PRG 
file and run it. FoxPro will highlight the line where the error occurs, 
although sometimes the error is the line above what is highlighted. But at 
least it helps me get close to where the error is.


"Gene Wirchenko" <genew@ocis.net> wrote in message 
news:o1nrm3dfulbe7g56mj01d9j5uml3vd8mdp@4ax.com...
>     I have just finished -- I hope I have finished -- some changes to
> one program of mine that has some hairy SQL.  One of the SQL statments
> is 40 lines long.  Granted it could be shorter vertically, but it is
> still 852 characters long.
>
>     Occasionally -- read "All too often" -- while wrestling with this
> program, there would be an error in my SQL syntax.  VFP is not
> terribly helpful with such error messages as
>          Syntax error.
>          Command is missing required clause.
> These are not terribly useful.  Sometimes, I have hunted for minutes
> before realising that, say, a comma was missing.
>
>     What do you do to quickly find errors in long SQL statements?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>     I have preferences.
>     You have biases.
>     He/She has prejudices. 

0
Cathy
12/26/2007 1:35:27 PM
"Lew" <lew@fastmail.fm> wrote:

>If it can be re-written so as to incorporate some independent intermediate 
>queries, do so until each is running smoothly. Then add them back in, one at 
>a time. You may find that you gain some speed if you don't add everything 
>back in.

     I have tried that, but the biggest problems I have are with
joins.  Suddenly, a column name is no longer unique.

>Another thing I do is to put long clauses into #DEFINE's and/or macro subs. 
>This lets me hide some of the complexity while debugging.

     I was doing that and went back to straight code as much as
possible.  Neither approach is an ideal solution, so I may bounce back
again.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.
0
Gene
12/31/2007 6:17:37 PM
"swdev2" <wsanders@dotnetconversions.bob.com> wrote:

>I take a different approach.
>I programmatically build SQL Select statements,
>and run each piece of it (select, into, where, join, etc)
>through a sql pre-processor util that i built some time ago.
>
>i have different flavours of it now, for native vfp, ms-sql, mySql,
>postGreSql and oracle.
>i think the first iteration of it - i built it in about 2 months of spare
>time fiddling.
>
>you may want to take a hard look at making yer own sql pre-processor util.

     Details, please?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.
0
Gene
12/31/2007 6:18:28 PM
Gene Wirchenko <genew@ocis.net> wrote:

[snip]

>     What do you do to quickly find errors in long SQL statements?

     Apparently, the answer is pretty much what I already do (though
the preprocessor idea sounds interesting).

     Newsgroups are great for finding out answers to nasty questions
(or that there is no hope).

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.
0
Gene
12/31/2007 6:21:35 PM
Well Gene -
Just remember -
different pieces of a sql statement have some kind of delimitation.
if it's field list, you gots a comma until the last field
if it's line  continuances, you gots a semi-colon
for somethings, you might have a + sign, depending on how yer concactenating
the sql statement

on top of that, I took advantage of the <<text>> <<endtext>> bits inside of
the utility, at the end part,
and did a bunch of evals inside of it.

I can't say it's a pretty chunk of code , but it does check for proper
punctuation and ORDER of things, then rebuilds it into
whichever sql standard it's about to be processed on (VFP, Oracle, mySql,
ms-sql).

Regards [Bill]

--
===================
William Sanders / EFG  VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net  www.viasqlserver.net

"Gene Wirchenko" <genew@ocis.net> wrote in message
news:iicin31t2aic7isc7q62dsjds5vofa7agl@4ax.com...
> "swdev2" <wsanders@dotnetconversions.bob.com> wrote:
>
> >I take a different approach.
> >I programmatically build SQL Select statements,
> >and run each piece of it (select, into, where, join, etc)
> >through a sql pre-processor util that i built some time ago.
> >
> >i have different flavours of it now, for native vfp, ms-sql, mySql,
> >postGreSql and oracle.
> >i think the first iteration of it - i built it in about 2 months of spare
> >time fiddling.
> >
> >you may want to take a hard look at making yer own sql pre-processor
util.
>
>      Details, please?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>      I have preferences.
>      You have biases.
>      He/She has prejudices.


0
swdev2
1/2/2008 5:05:45 AM
Sorry, forgot the calling mechanism.
Say I built some sql string, cause I had to -
like - lcSql = [whole bunch o stuff here]

then I'd set it up for...
lcSql = sqlCheck(lcSql)

then
=sqlexec(lnHandle,lcSql, [cursResults])

Regards [Bill]

--
===================
William Sanders / EFG  VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net  www.viasqlserver.net

"Gene Wirchenko" <genew@ocis.net> wrote in message
news:iicin31t2aic7isc7q62dsjds5vofa7agl@4ax.com...
> "swdev2" <wsanders@dotnetconversions.bob.com> wrote:
>
> >I take a different approach.
> >I programmatically build SQL Select statements,
> >and run each piece of it (select, into, where, join, etc)
> >through a sql pre-processor util that i built some time ago.
> >
> >i have different flavours of it now, for native vfp, ms-sql, mySql,
> >postGreSql and oracle.
> >i think the first iteration of it - i built it in about 2 months of spare
> >time fiddling.
> >
> >you may want to take a hard look at making yer own sql pre-processor
util.
>
>      Details, please?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>      I have preferences.
>      You have biases.
>      He/She has prejudices.


0
swdev2
1/2/2008 5:08:46 AM
Reply: