#27 Incorrect data returned for nested query

closed-invalid
Mark Hessling
None
5
2009-11-26
2009-11-14
Don Hughes
No

Consider the following select:

SELECT *, DATE_FORMAT(Date, '%Y5m%d') as Datef FROM Table1;

If there is another SQL function performed inside the Sqlfetch loop such as

DELETE FROM Table1 WHERE Date='some value';

the values in the outer loop stop changing.

Here is a listing of the records retreived with inner call commeted out:
/www/work /www/work
*** Looking for changes.
20091113 2009-11-13 11:00 19:00
20091115 2009-11-15 07:00 15:00
20091117 2009-11-17 11:00 19:00
20091118 2009-11-18 11:00 19:00
20091119 2009-11-19 11:00 19:00
20091120 2009-11-20 11:00 19:00

Same with a delete called from within the sqlfetch loop:
/www/work /www/work
*** Looking for changes.
20091113 2009-11-13 11:00 19:00
20091115 2009-11-15 07:00 15:00
20091117 2009-11-17 11:00 19:00
DELETE FROM Schedule WHERE Date="2009-11-17" AND Start="11:00"
20091117 2009-11-17 11:00 19:00
20091117 2009-11-17 11:00 19:00
20091117 2009-11-17 11:00 19:00

Discussion

  • Mark Hessling
    Mark Hessling
    2009-11-14

    Don, can you post the code that executes the two loops?
    Thanks, Mark

     
  • Don Hughes
    Don Hughes
    2009-11-24

    Test code

     
    Attachments
  • Don Hughes
    Don Hughes
    2009-11-24

    Variable dump

     
    Attachments
  • Don Hughes
    Don Hughes
    2009-11-24

    I have attached the relevant bits of code.
    I also left out some important information:
    Linux SuSE 11.2
    mysql client 5.1.36
    regina 3.3 from SuSE
    RexxSQL 2.5-2 locally compiled.

    and the two sql commands use two different handles. Also, in the original, the two command were accessing the same table, but for the test above, I actually used two different tables. A dumpvars after the second call only showed tails from the first handle.

     
  • Mark Hessling
    Mark Hessling
    2009-11-25

    Hi Don,

    I haven't tried to replicate the problem, nor am I certain of what is causing the problem, however the following hopefully will result in the desired behaviour.

    All commands are executing within the context of the second connection, so the DELETE statement is affecting the current cursor. You need a call SQLDEFAULTto ensure the commands are execting within the currect connection context. Also it is advisable to explicitly set AUTOCOMMIT OFF so that deletions don't affect the SQLFETCH.

    I'd suggest a structure like:

    SQLCONNECT conn1
    /* context now conn1 */
    SQLCONNECT conn2
    /* context now conn2 */
    ...
    Call SQLVARIABLE 'AUTOCOMMIT'
    Call SQLDEFAULT conn1
    /* context now conn1 */
    ...
    SQLOPEN
    Do While SQLFETCH() > 0
    ...
    If delete condition Then Do
    Call SQLDEFAULT conn2
    /* context now conn2 */
    Call SQLCOMMAND "delete..."
    Call SQLDEFAULT conn1
    /* context now conn1 */
    End
    ...
    End

    Call SQLDEFAULT conn2
    /* context now conn2 */
    Call SQLCOMMIT
    ...

    Hope that helps

     
  • Mark Hessling
    Mark Hessling
    2009-11-25

    Oops. The call to SQLVARIABLE should be:
    Call SQLVARIABLE 'AUTOCOMMIT', 0

     
  • Mark Hessling
    Mark Hessling
    2009-11-25

    • assigned_to: nobody --> rexx
     
  • Don Hughes
    Don Hughes
    2009-11-26

    My mistake. I have apparently not been making a proper distinction between connection name and statement name in my programs. You can close this as user error.

     
  • Mark Hessling
    Mark Hessling
    2009-11-26

    • status: open --> closed-invalid
     
  • Mark Hessling
    Mark Hessling
    2009-11-26

    Usage error, not bug