Welcome, Guest
Username: Password: Remember me
Visual Objects

Please use this forum to post questions about Visual Objects and Vulcan.NET
  • Page:
  • 1

TOPIC:

SQLtable & MySql: Problem with Seek & Assign 31 Aug 2022 18:53 #23555

  • gianluca.pinoli
  • gianluca.pinoli's Avatar
  • Topic Author


  • Posts: 35
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 31 Aug 2022 21:03 #23561

    • ic2
    • ic2's Avatar


  • Posts: 1555
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 01 Sep 2022 10:35 #23578

    • gianluca.pinoli
    • gianluca.pinoli's Avatar
    • Topic Author


  • Posts: 35
  • 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()
    *oDBArticok:Seek(#CODICE,"0001009",true) // <---- SEEK
    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
    *oDBArticok:etic := "X" // <---- ASSIGN
    *? "Assign Done"

    oDBArticok:Skip()
    ? "Skip Done"
    end

    oDBArticok:Close()

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


    Regards
    Gianluca

    Please Log in or Create an account to join the conversation.

    Last edit: by gianluca.pinoli.

    SQLtable & MySql: Problem with Seek & Assign 01 Sep 2022 12:42 #23580

    • ic2
    • ic2's Avatar


  • Posts: 1555
  • 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:

    groups.google.com/g/comp.lang.clipper.vi...search?q=skip%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

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    SQLtable & MySql: Problem with Seek & Assign 01 Sep 2022 13:12 #23581

    • gianluca.pinoli
    • gianluca.pinoli's Avatar
    • Topic Author


  • Posts: 35
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 01 Sep 2022 22:35 #23596

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3199
  • Hi Gianluca,
    I have only used the SqlSelect class.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 02 Sep 2022 09:57 #23601

    • gianluca.pinoli
    • gianluca.pinoli's Avatar
    • Topic Author


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

    CLASS ArticokSQL INHERIT SQLTable

    Regards
    Gianluca

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 02 Sep 2022 18:30 #23603

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3199
  • Hi Gianluca,
    I have never used the SQL Editor, and stopped using the DBServer Editor many years ago.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 04 Sep 2022 06:38 #23618

    • Jamal
    • Jamal's Avatar


  • Posts: 297
  • 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:
    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
    
    

    Please Log in or Create an account to join the conversation.

    Last edit: by Jamal.

    SQLtable & MySql: Problem with Seek & Assign 05 Sep 2022 12:31 #23644

    • gianluca.pinoli
    • gianluca.pinoli's Avatar
    • Topic Author


  • Posts: 35
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 05 Sep 2022 14:24 #23646

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3199
  • 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
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 05 Sep 2022 17:10 #23649

    • g.bunzel@domonet.de's Avatar


  • Posts: 83
  • 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

    Please Log in or Create an account to join the conversation.

    Last edit: by .

    SQLtable & MySql: Problem with Seek & Assign 05 Sep 2022 18:40 #23652

    • Jamal
    • Jamal's Avatar


  • Posts: 297
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 05 Sep 2022 22:17 #23656

    • ic2
    • ic2's Avatar


  • Posts: 1555
  • 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

    Please Log in or Create an account to join the conversation.

    SQLtable & MySql: Problem with Seek & Assign 06 Sep 2022 09:39 #23663

    • gianluca.pinoli
    • gianluca.pinoli's Avatar
    • Topic Author


  • Posts: 35
  • 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

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1