SQLtable & MySql: Problem with Seek & Assign

[gianluca.pinoli]

Hi,
to speedup the migration from DBF to MySQL I'm tryng to replace DBserver with SQLtable in CAVO 2.8sp4.
I'm having some trouble assigning value to a field, but only after a seek.
this is my test code:

*********************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
//oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "" // <---- ASSIGN
oDBArticok:Skip()
end
oDBArticok:Close()

*********************************************************************

This work fine, but if i uncomment the seek i've an Error Code: 50 [ ACCESS VIOLATION ]

I can't understand what is my error.

Gianluca

[ic2]

Hello Gianluca,

Just to be complete: do I understand that the error occurs in the line with the arrow, the assign, and not at the seek itself?
In that case it looks like the loop comes across a record which it won't reach without the seek, although I would expect the other way around.

Can you find out if it always fails at the same record, and if it actually updates other records (with or without the seek), if that record indeed isn't reached without the seek and if the seek actually is succesful? In which case the question is why you can't enter an empty value in that record.

Also you could test what happens when you would use an actual SQL UPDATE statement instead of this loop.

Dick

[gianluca.pinoli]

Hello Dick,
the error fires when Skip is invoked, and in every record.
Just to test i've replaced the empty value with 'X' but the rerult is the same

***************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "X" // <---- ASSIGN
? "Assign Done"
oDBArticok:Skip() // <---- ERROR
? "Skip Done"
end

oDBArticok:Close()

**********************************************************************
In this case the record is found but the update do not work.
in the output console you can read:

0001009
Assign Done

then the application give 5333 error.

the problem seem to be the "commit" after a seek.
Boot the following variants of the code works fine:

1) Update without seek
*****************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
[strike]*oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK[/strike]
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "X" // <---- ASSIGN
? "Assign Done"
oDBArticok:Skip()
? "Skip Done"
end

oDBArticok:Close()

Pause()
self:EndDialog(0)

****************************************************

2) seek without update
****************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK
while !oDBArticok:EoF
? oDBArticok:CODICE
[strike] *oDBArticok:etic := "X" // <---- ASSIGN
*? "Assign Done"[/strike]
oDBArticok:Skip()
? "Skip Done"
end

oDBArticok:Close()

Pause()
self:EndDialog(0)
*****************************************************************


Regards
Gianluca

[ic2]

Hello Gianluca,

Your update did not answer all my questions; you might want to check these and others (or me) can suggest a cause better.

If a simple skip causes a 5333 error this must have been caused by the assign before it; especially if the 2 other program fragments work. However, the 2nd shouldn't work either as the seek should not influence the rest of the code. Hence my question if you can find out if the program without seek goes through the same records as the program with a seek.

Although I have never used xBase like code with MySQL as a server and knowing that the VO SQL class is not the most stable VO class I would check if there's something going on with the records you try to update, which then should go wrong too if these are eventually updated in the program without seek. Also you write about a commit after the seek but I don't see that in your code?

As a commit forces writing to disk, it probably gets wrong here although I'd expect the assign to fail already. Foe what I can see it must be something with your SQL table. What happens when you do the same update with e.g. Workbench and a UPDATE statement?

Finally, you might find some info in the comp.lang newsgroup. I used:

https://groups.google.com/g/comp.lang.c ... kip%205333

and came across some comparable loops where the garbage collector eventually caused a 5333.

Not really a solution but hopefully it helps you getting closer to one.

Dick

[gianluca.pinoli]

Hello,
SQLtable connect via ODBC to MySQL db, so no VO SQL class is involved.
I can update the record used in the sample code both using Workbench (UPDATE articok SET etic='' WHERE codice='0001009') and with the following code, updating every record in the table:

***********************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "X" // <---- ASSIGN
? "Assign Done"
oDBArticok:Skip()
? "Skip Done"
end

oDBArticok:Close()
******************************************************************************

So i suppose that both Mysql DB and ODBC work fine.

Simply adding a seek (soft or not) couse the problem.
*****************************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "X" // <---- ASSIGN
? "Assign Done"
oDBArticok:Skip() // <---- ERROR
? "Skip Done"
end

oDBArticok:Close()
************************************************************************

I've also tested adding a commit, but the problem persist

************************************************************************
local oDBArticok as ArticokSQL

oDBArticok := ArticokSQL{}
oDBArticok:OrderBy( #CODICE )

oDBArticok:GoTop()
oDBArticok:Seek(#CODICE,"000000001",true) // <---- SEEK
while !oDBArticok:EoF
? oDBArticok:CODICE
oDBArticok:etic := "X" // <---- ASSIGN
? "Assign Done"
oDBArticok:Commit() // <---- ERROR
? "Commit Done"
oDBArticok:Skip()
? "Skip Done"
end

oDBArticok:Close()
*************************************************************************


Has anyone used SQLtable in the past?
Can this kind of operation be performed with SQLtable?

Regards
Gianluca

[wriedmann]

Hi Gianluca,
I have only used the SqlSelect class.
Wolfgang

[gianluca.pinoli]

Hi Wolfgang,
I used the cavo Menu->Tools->SQL Editor.
It creted the class inheriting SQLTable

CLASS ArticokSQL INHERIT SQLTable

Regards
Gianluca

[wriedmann]

Hi Gianluca,
I have never used the SQL Editor, and stopped using the DBServer Editor many years ago.
Wolfgang

[Jamal]

Hi Gianluca,

Forgot about the SQL Editor generated class and explicitly create an a SQL connection using the SQLConnection class, then open the table using the SQLTable Class. Hopefully this works for you.

For example:

Code: Select all

LOCAL oConn AS SQLConnection
LOCAL oYourSqlTable AS SQLTable

 oConn := SQLConnection{}

 oConn:Connect(cDataSource, cUserID, cPassword}   // pass your database credentials

 // Check for errors

 IF IsNil(oConn:ErrInfo)

       //  Open sql table
       oYourSqlTable := SQLTable{"YourTable", oConn} 
       // The rest of your code - OrderBy, Seek, etc

     oConn:Disconnect()
 ELSE
         oConn:ErrInfo:ShowErrorMsg()
 ENDIF

[gianluca.pinoli]

Thanks Jamal,
but i've the same problem even using your suggestion.

I've found out that the problem seem to be SQLTable.
Using SQLSelect seem to work, but obviously orderby and SetRelation are not available, and in the past whe used it very often.

Anyone has a clue why SQLTable has this behavior?
If not i'll have to deal with SQLSelect.

Regards
Gianluca