Order of SQLxxx commands

  • I am extracting info from a MySQL database in order to build a .sql file which can be used to reconstruct the database in case of failures (a kind of a dump like PHPMyAdmin supports as well).

    I have the following logic outline:
    - connect to database (sqlconnect)
    - find all user tables (sqltables)
    - loop through all tables
    -- find column descriptors (sqlcolumns)
    -- prepare select statement (sqlprepare)
    -- open cursor to execute statement (sqlopen)
    --- loop through rows (sqlfetch)
    -- close cursor (sqlclose)
    -- dispose workarea (sqldispose) optional

    The script runs until the sqlcolumns statement is executed for the second table. The error is that the command is reported to be out if synch (wrong order).

    I don't see anything in the documentation about the sqlcolumns not allowed at any stage.
    I could try to use SQLDescribe instead but the output does not give me the essential column_description (which contains info like auto_increment).

    Could anyone please shed some light on this?
    Thanks in advance.

    • After careful rereading the documentation I was able to resolve my problem. The documentation in Chapter Using RexxSQL has a table where it is clearly stated that the proper sequence for using SQLDescribe is
      - sqlprepare
      - sqldescribe
      - sqldispose

      And then another sequence can be started.
      I rewrote my program accordingly which runs successfully now.