Complex Qry, with results in SQL Select

  1. 5 months ago

    Complex Statements, with results in SQL Select :

    INSERT INTO ord_sales_gen (OrdNo, OrdDate, FK_Corp_ID, FK_Valuta)
    SELECT OrdNo+1, '2017-07-16', 7, 1
    FROM ord_sales_gen
    WHERE id=LAST_INSERT_ID();
    SELECT OrdNo FROM ord_sales_gen WHERE ID=LAST_INSERT_ID()

    This instruction that works fine in MySql, cannot be used in Xojo Mysql Qry.

    Anyone can help?

  2. Norman P

    Jul 16 Xojo Inc
    Edited 5 months ago

    perform this as 2 separate statements
    and probably put both in a transaction so you can rollback as necessary

  3. I Cannot, if another user does same operation at same time would be having a dangerous result.
    Very remote possibility but exists.

  4. Norman P

    Jul 16 Xojo Inc
    Edited 5 months ago

    Right
    Use a transaction
    Thats what they are for

    Issuing this as one "compound" statement also doesn't insure that two users doing the same thing will not collide

  5. I know I looked for a nice compact way, would be fine.
    Many thanks.

  6. Norman P

    Jul 16 Xojo Inc

    transactions are the right way to make sure things get inserted "as expected" and are the right way to handle multi user potential conflicts and errors

    for reading data its not a big deal since there can be many readers at once - but for insert , update, delete they are an absolute MUST to get the right results

or Sign Up to reply!