"SQL: Statement Too Long" Error

[Sware]

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.

 

[Imaginecorp]

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.

 

[bborissov]

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

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

 

[Imaginecorp]

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.

 

[Sware]

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)

 

[Mike Lewis]

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