×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

"SQL: Statement Too Long" Error

"SQL: Statement Too Long" Error

"SQL: Statement Too Long" Error

(OP)
Our VB6 app uses the Visual FoxPro Driver (6.00.8167.00) to work with a .DBF database.  A recordset is created via rs1.Open "SELECT * FROM......"  We then fill an Array with the 87 recordset fields and do record processing in the Array, which results in some (not all) changes of the original Array values.  At the end of record processing the following code is executed:

For i = 0 To 86
   If rs1.Fields(i) <> PlyrArray(i) Then
      rs1.Fields(i) = PlyrArray(i)
   End If
Next i
rs1.Update

We get the "SQL: Statement Too Long" error on the rs1.Update statement.  What could be causing this error?  Thanks.

RE: "SQL: Statement Too Long" Error

Hi SWare,

In Visual FoxPro, a single statement is limited to 8,192 characters. I don't know if that's the same with the driver that you are using, but I would think it is.

(By the way, is this an ODBC driver or an OLE DB provider? It probably doesn't affect your problem, but in general the OLE DB provider is the better choice. Using ODBC could be a problem if you are accessing a recent version of a FoxPro database.)

Can you check the length of the statement that your update command is actually sending? My guess is that if it contains 87 fields, and if the values being inserted are large, then you could be breaking the 8,192-character limit. But there's no way for me to confirm that.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

RE: "SQL: Statement Too Long" Error

(OP)
Mike,

Thanks for your input.  Each record is 75 characters so there are 6,525 characters involved (75 x 87).  That would not seem to violate the limit of 8,192.  How do I check the length of the statement actually being sent by the Update command?

I believe it's the ODBC Driver.  The connection string includes: "Provider=MSDASQL;Driver=(Microsoft Visual FoxPro Driver)....."  We had previously tried the OLE DB Driver (Provider-vfpoledb) but got Multi-Step errors.

RE: "SQL: Statement Too Long" Error

It's not just the number of characters multiplied by the number of fields. You also have to allow for all the separators and other characters that make up the Update statement.

That said, you're probably right that it won't exceed the 8,192-limit.

Don't worry too much about the drive issue. The fact that you can even access the database suggests that that is not the problem.

You asked how to check the length of the Update statement. Given that the statement in question is being generated within VB, that's something you would have to ask a VB expert.

My approach would be to try updating a much smaller number of fields. Gradually increase that number until the error occurs. Doing that would at least tell you whether there was anything wrong with your overall logic, or whether it is indeed some maximum that's being exceeded.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

RE: "SQL: Statement Too Long" Error

(OP)
Thanks again Mike.  We'll continue to "fiddle."  If anyone else has suggestions they will be appreciated.

RE: "SQL: Statement Too Long" Error

As Mike said 8192 is the limitation for the whole statement not just values it includes:

CODE

UPDATE MyTable SET Fld1 = Val1, Fld2 = Val2, ......., Fld87 = Val87 WHERE ......
You could reach the limit if your field names are long and you have long where clause.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

(OP)
Thanks Borislav.  I guess I'm confused between the use of rs1.Update and UPDATE MyTable SET Fld1 =.......  Does not the former result in the internal generation of a statement like the latter?  If so, how can I see what is generated?

RE: "SQL: Statement Too Long" Error

Sware,

Quote:

  I guess I'm confused between the use of rs1.Update and UPDATE MyTable SET Fld1 =.......  Does not the former result in the internal generation of a statement like the latter?

Yes, that' probably correct.

UPDATE MyTable SET ... is standard SQL syntax. It is likely that your rs1.Update is generating this standard SQL to send to the driver.

rs1.Update is nothing to do with VFP. It is something within VB. That's why I suggested you ask a VB person. In fact, your best bet would be to find out how to view the internal statement that this rs1.Update (whatever it is) is generating. Perhaps someone in a VB-related forum can tell you how to do that.

Once you have seen the generated UPDATE statement, let us know, and we can take it from there.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

RE: "SQL: Statement Too Long" Error

IF you use ODBC driver to connect to VFP you could TRACE what is the command you sent. Control Panel->Administrative Tools->Data Sources (ODBC). Go to Tracing page and press "Start Tracing now" button (you could change the default LOG file first). After you run rs.Update, Stop Tracing and check the Log file to see what is the command generated from the record set. This is only valid for ODBC connections , didn't test it with OLEDB.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

Another way:
I ran into this many many moons ago, with a VB Point Of Sale system using VFP databases. the solution was “Let Fox Talk to Fox…” no ODBC etc.
Create a COM (Public) class in VFP. Put your array, table update etc in it.
In VB do a createobject(), call the method that does the processing etc. That’s it…

RE: "SQL: Statement Too Long" Error

Agree, the only annoyance is that you must have VFP run-times installed.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

(OP)
Being a VFP and database neophyte I don't understand how to implement the solution offered by Imaginecorp.  However, I think I've found the root cause of the problem and it's NOT the length of the Update statement generated by the VB rs1.Update statement.  I did the trace recommended by Borislav.  The generated Update statement is less than 1800 characters so it doesn't violate the 8192 limit.

I've discovered that the length of a FEW of the 87 array data elements used to change recordset fields does not match the length of the corresponding recordset field (in all such cases the data length is less than the field length).  I think I read in one of the several forums I've visited that the unequal lengths situation can cause the "SQL: Statement Too Long" error (although, as is often  the case, the title of the error and related documentation certainly don't reveal that possibility).

So, unless I'm missing something else, I think the solution is for me to find out how I screwed up in a few cases the length of the data stored in the recordset fields before issuing the rs1.Update statement.  Additional comments on this possible solution will be appreciated.

RE: "SQL: Statement Too Long" Error

Sware, what Database you use for BackEnd?
VFP6, VFP8 or VFP9?
Keep in mind that the LAST ODBC driver for VFP was written for VFP 6. if you use any new field types (introduced in later versions) you may have troubles.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

(OP)
I'm using VFP6.

RE: "SQL: Statement Too Long" Error

Borislav:
I am sorry but I disagree, you do Not need VFP Runtime or any VFP related files installed on the machine to run a COM (DLL) object. Maybe, my use of the word COM rather than a DLL was misleading.

Sware:
In a VB front end, unless VB has improved, it is better to do all your processing in a VFP created object and have it update a VFP database, rather than using OLE DB or ODBC. Its cleaner and more efficient. Creating a COM (DLL) object is really very simple. Its a class declared OlePublic built as a DLL then registered. But you do have to have some knowledge of VFP:

VFP Help: Visual FoxPro and Advanced COM

RE: "SQL: Statement Too Long" Error

(OP)
Blind Alley!  I have modified my processing code and verified that the lengths of the array data elements placed in the recordset for all 87 fields are the same as the lenghts of the recordset fields.  But, I still get the "SQL: Statement Too Long" error when the rs1.Update statement is executed.

Any other ideas?  Thanks.

RE: "SQL: Statement Too Long" Error

Imaginecorp,
When you create a COM class in VFP no matter how you compile it - DLL or EXE you need VFP runtimes to be installed to get that COM to work.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

I respectively disagree.
Yes you may need runtime etc, if building the .DLL on the fly within a VFP program like the code below. But once created it can be copied to another machine that has no clue about Fox, But it has to be manually registered (Regsvr32). In VB a simple CreateObject() will work.

Sware:
if you are willing to try this and have a machine that does not have fox installed. Create the dll on a fox machine first, then copy the .dll AND the .tlb file to the non fox machine.
I do not know what the "rs" object is, but put your VFP Select and Processing code in the procedure processarray.
 
Register the .dll with run command. Then in VB access the dill and the procedure.

ox = Createobject("vbserver.for_vb")
? ox.processarray()    

CODE --> Build DLL

****Cut and paste into a program
**** CALL IT FOR_VB
**** Then run it

If Program() != "FOR_VB"
    ?"this file MUST BE NAMED 'for_vb.prg'"
    Return
Endif
If File("for_vb.dll")
    Declare Integer DllUnregisterServer In for_vb.Dll
    DllUnregisterServer()
    Clear Dlls
Endif

Build Project vbserver From for_vb
Build Dll vbserver From vbserver recomp

*uncomment to test this object
*!*    ox = Createobject("vbserver.for_vb")    && create the server object
*!*    ?ox.processarray()    &&& get the result

Define Class for_vb As Session OlePublic
    Procedure processarray()
        ****Put your processing code here
        **** and remove the following, but return something so you will know it worked
            
        Select 0
        Use Home(1)+'samples\data\customer'
        Select * From customer Where country = "France" Into Array carray
        Return Alen(carray,1)

    Function Error(nError, cMethod, nLine)
        Comreturnerror(cMethod+'  err#='+Str(nError,5)+;
            '  line='+Str(nLine,6)+' '+Message(),_vfp.ServerName)
Enddefine

RE: "SQL: Statement Too Long" Error

respectively = Respectfully

RE: "SQL: Statement Too Long" Error

http://fox.wikis.com/wc.dll?Wiki~VFP9RuntimeFiles~VFP

CODE

vfp9r.dll      Yes (3)      --"--      --"--     STDLL and EXE support
vfp9t.dll     Yes (3)     --"--     --"--     MTDLL support

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

Yes: If there is an interface.

If all the DLL does is process something and update a table without displaying anything, then No.

Unfortunately, all of our machines have VFP, but will test this later on a machine without VFP and report back.

RE: "SQL: Statement Too Long" Error

I also have only computers with VFP installed on them, but I can't imaging 300K DLL could do the whole works as 300K EXE.
If this MTDLL is supposed to work independently VFP linker must add all functionality in it and it wont be so small smile

BUT!!!!!!
I totally agree with you, Let Fox talk to Fox, some additional 6MB on disk WORTH IT.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

RE: "SQL: Statement Too Long" Error

Borislav:

Since your post, I have reading the help, none of them talk about runtime files, but I have a sneaking suspicion you are right, based upon bits and pieces for In-process Dll Support. Which I read as Forms etc. involved. If the DLL is only processing data, there should be no need for runtime files.

I was looking at some of my old stuff, and it was in VFP 5. A company I did some consulting work had a POS system in VB where they were having a problem with updating to the main app (Fox Dos which we ported over to 5). I wrote the DLL, put it on their machine which did not have Foxpro installed, and it worked like a charm. All the DLL did was accepting an Array with Values, updated a table then returned a “success/failure” message to VB.  

Was it different in VFP5? Could it have been that the VB drivers made it work? It does not make sense to me to have to install runtime files for a single DLL. Automation is not supposed to work this way.  A DLL, as you know, is basically a very small "exe" which should be totally independent, wouldn’t you think?

But this has got me peaked, I am trying to locate a machine that has only VB and will try the DLL out and will report back.

In the meantime, hopefully someone here will try this. Whoever does; please change the code in the “processarray” procedure to return something which VB understands.

Sware: Would you like to try this out. If I am correct your app will work like a charm and I will even write the DLL for you based on your requirements... How is that for cheap labor.

RE: "SQL: Statement Too Long" Error

(OP)
Okay y'all.  What follows is the part of the FoxPro Trace that shows the UPDATE statement generated by the FoxPro driver from the rs1.Update statement in my VB code.

I'm confused by the 27 of the 87 fields that appear before the WHERE clause.  They appear to be a random selection of fields.  What follows the WHERE clause are all 87 of the fields in the order in which they appear in a record (including duplicates of the fields before WHERE).  Further, I don't understand the WHERE aspect in general - what is its purpose?  Further yet, there's an indication of a SQL error at the strat of the trace material and in indication oof a Statement Too Long error at the end - why both?

Anyhow, comments and analysis welcome!
----------------------------------------------------------

VB6             1014-11cc    EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)
        HSTMT               043D3630
        WCHAR *             0x0427D828 [      -3] "UPDATE `GHM_V70.DBF` SET `rounds_ytd`=?,`usga_index`=?,`trnd_index`=?,`lowest_rnd`=?,`first_nine`=?,`hand_calc`=?,`index_calc`=?,`gs_sr01`=?,`gs_sr02`=?,`gs_sr03`=?,`gs_sr04`=?,`gs_sr05`=?,`gs_sr06`=?,`gs_sr07`=?,`gs_sr08`=?,`gs_sr09`=?,`gs_sr10`=?,`gs_sr11`=?,`gs_sr12`=?,`gs_sr13`=?,`gs_sr14`=?,`gs_sr15`=?,`gs_sr16`=?,`gs_sr17`=?,`gs_sr18`=?,`gs_sr19`=?,`gs_sr20`=? WHERE `gs_id`=? AND `play_over`=? AND `play_stat`=? AND `play_dues`=? AND `play_flags`=? AND `p_sex`=? AND `p_type`=? AND `p_crse`=? AND `p_card`=? AND `name`=? AND `rounds_ytd`=? AND `round_ytdo`=? AND `rounds_slr`=? AND `usga_home`=? AND `usga_index`=? AND `tot_diff`=? AND `nine_home`=? AND `nine_index`=? AND `nine_diff`=? AND `usga_avg_d`=? AND `init_index`=? AND `trnd_home`=? AND `trnd_index`=? AND `trnd_diff`=? AND `trnd9_home`=? AND `trnd9_inde`=? AND `trnd9_diff`=? AND `hand_date`=? AND `lowest_rnd`=? AND `first_nine`=? AND `hand_scrs`=? AND `nine_p_scr`=? AND `hand_calc`=? AND `index_calc`=? AND `gs_id_pw`=? AND `ih_01`=? AND `ih_02`=? AND `ih_03`=? AND `ih_04`=? AND `ih_05`=? AND `ih_06`=? AND `ih_07`=? AND `ih_08`=? AND `ih_09`=? AND `ih_10`=? AND `ih_11`=? AND `ih_12`=? AND `gs_sr01`=? AND `gs_sr02`=? AND `gs_sr03`=? AND `gs_sr04`=? AND `gs_sr05`=? AND `gs_sr06`=? AND `gs_sr07`=? AND `gs_sr08`=? AND `gs_sr09`=? AND `gs_sr10`=? AND `gs_sr11`=? AND `gs_sr12`=? AND `gs_sr13`=? AND `gs_sr14`=? AND `gs_sr15`=? AND `gs_sr16`=? AND `gs_sr17`=? AND `gs_sr18`=? AND `gs_sr19`=? AND `gs_sr20`=? AND `gs_sr21`=? AND `gs_sr22`=? AND `gs_sr23`=? AND `gs_sr24`=? AND `gs_sr25`=? AND `gs_sr26`=? AND `gs_sr27`=? AND `gs_sr28`=? AND `gs_sr29`=? AND `gs_sr30`=? AND `gs_sr31`=? AND `gs_sr32`=? AND `gs_sr33`=? AND `gs_sr34`=? AND `gs_sr35`=? AND `gs_sr36`=? AND `gs_sr37`=? AND `gs_sr38`=? AND `gs_sr39`=? AND `gs_sr40`=?\ 0"
        SDWORD                    -3

        DIAG [S1000] [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long. (812)

RE: "SQL: Statement Too Long" Error

SWare,

That's very helpful. I think I can see what's happening now.

You asked about the purpose of the WHERE clause. This is needed so that the database can detect a multi-user violation. It would take some time to explain the reason for this, and anyway it won't help with the solution.

It looks to me like the error was caused, not by the statement being too long, but the WHERE clause being too complex. If that's right, the solution is easy.

Go back to your original code:

For i = 0 To 86
   If rs1.Fields(i) <> PlyrArray(i) Then
      rs1.Fields(i) = PlyrArray(i)
   End If
Next i
rs1.Update

and break it into smaller chunks, for example:

For i = 0 To 43
   If rs1.Fields(i) <> PlyrArray(i) Then
      rs1.Fields(i) = PlyrArray(i)
   End If
Next i
rs1.Update
For i = 44 To 86
   If rs1.Fields(i) <> PlyrArray(i) Then
      rs1.Fields(i) = PlyrArray(i)
   End If
Next i
rs1.Update

In other words, you're updating the fields in two separate stages.

If that doesn't solve it, break it down even further in the same way.

Perhaps you could give that a try and report back.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close