SQLtable & MySql: Problem with Seek & Assign

wriedmann · Post by **wriedmann** » Mon Sep 05, 2022 12:24 pm

Hi Gianluca,
I'm not sure if the SQLTable class needs a movement call before being used.
I have now searched the programmers guide of VO 2.7, but there the SQLTable class is not even mentioned.
Maybe you can give a look to the SQLTable sources to see what is going on - maybe copying a renamed class and using that in the debugger.
Wolfgang

g.bunzel@domonet.de · Post by **g.bunzel@domonet.de** » Mon Sep 05, 2022 3:10 pm

Gianluca,

in your 'OrderBy()' you use a Symbol as argument:
oDBArticok:OrderBy( #CODICE )

In the VO helpfile, these arguments should be strings:
Syntax
<oSQLTable>:OrderBy(<cOrder> [, <cOrder>]) ---> NIL

Arguments
<cOrder> [, <cOrder>]

The list of strings comprising the ORDER BY clause. The list will be concatenated, separated by commas. The strings can contain any valid ORDER BY syntax that is accepted by the driver, including ASC and DESC keywords.

Edit my message - Had a look at the SDK:
OrderBy() is also working with Symbols:

cOrder := NULL_STRING
// any parameters?
IF ( PCount() != 0 )
FOR wCount:=1 UPTO PCount()
cOrder += cSeparator + __GetSymString( _GETMPARAM( wCount ) )
cSeparator := ","
NEXT
ENDIF

Gerhard

Jamal · Post by **Jamal** » Mon Sep 05, 2022 4:40 pm

Gianluca,

I am not sure about the SqlTable behavior, but from what I know that the SQL engine utilizes indexes if they exist in the select statement ORDER BY; so there is no need for SQLTable:OrderBY() method.
Instead of SetRelation, the SELECT with JOIN statement is more powerful.
This is much easier in .NET.

Jamal

ic2 · Post by **ic2** » Mon Sep 05, 2022 8:17 pm

This may be totally unrelated, but:

I recently used UNION in VO for a quite complicated set of statements, used via ADS, and UNION failed with unpredictable while I couldn't see anything wrong. Eventually I gave up and used a very long SELECT query with 5 or's in it. Not only it worked but it was many times faster than a DO WHILE loop and/or filter in 'normal' VO/ADS would be.

So I'd try a JOIN but should the result be illogical too then it may be not your fault.

Dick

gianluca.pinoli · Post by **gianluca.pinoli** » Tue Sep 06, 2022 7:39 am

Hi,
I know that the best way is to migrate to SQL (and this is my goal), but i was investigating a shortcut to replace DBF with MySQL without relevant code modification.
SQLTable/SQLSelect seem not to be the best way for me, so i'll continue with my previos way, editing every single function/method
Thanks to all

Gianluca