none
Odbc.Query - How to run multiple SQL statements? RRS feed

  • Question

  • I'd like to run multiple SQL statements in an ODBC connection, for example:

    CREATE TEMP TABLE map(buch_schl_qb VARCHAR(255), key_neg INTEGER, buch_schl_akt VARCHAR(255))

    INSERT INTO map (buch_schl_qb, key_neg, buch_schl_akt) VALUES ('A', 4, 'b')

    SELECT FIRST 5* FROM map

    Is it possible to do that in Power Query?

    I tried calling Odbc.Query using a semicolon as seperator like

    Odbc.Query("Driver={IBM INFORMIX ODBC DRIVER};Server=inf_k876;Database=ab_c", "CREATE TEMP TABLE map(buch_schl_qb VARCHAR(255), key_neg INTEGER, buch_schl_akt VARCHAR(255)); INSERT INTO map (buch_schl_qb, key_neg, buch_schl_akt) VALUES ('A', 4, 'b')")

    but this didn't work.

    Many thanks for your help!

    Monday, April 6, 2015 12:56 PM

Answers

  • Power Query simply passes your text to the ODBC driver. It does not try to understand the text in any real fashion. So if there's a syntax supported by the driver that allows you to use multiple statements, you can. But we wouldn't, for instance, try to split your statement into three other statements and run them one-at-a-time.
    • Proposed as answer by Curt Hagenlocher Monday, April 6, 2015 1:16 PM
    • Marked as answer by Radibor Monday, April 6, 2015 5:57 PM
    Monday, April 6, 2015 1:16 PM

All replies

  • Power Query simply passes your text to the ODBC driver. It does not try to understand the text in any real fashion. So if there's a syntax supported by the driver that allows you to use multiple statements, you can. But we wouldn't, for instance, try to split your statement into three other statements and run them one-at-a-time.
    • Proposed as answer by Curt Hagenlocher Monday, April 6, 2015 1:16 PM
    • Marked as answer by Radibor Monday, April 6, 2015 5:57 PM
    Monday, April 6, 2015 1:16 PM
  • Curt is right, and also keep in mind that at the moment Power Query is designed as a read-only tool. You can pass SQL statements in Odbc.Query that will modify your database, but the exact effect of these is unspecified and unsupported. I would recommend therefore that you use another tool in order to do this work, such as SSMS.

    Monday, April 6, 2015 4:49 PM
    Moderator

  • I have a local mapping table stored in an Excel file. It would be useful to create this mapping table as a temporary table in the database session and have the database perform the JOIN operation (@Tristan: The SQL commands above don't modify the database. All temp tables are deleted after the end of the session). Otherwise Power Query would need to do it locally.

    Using R and the same ODBC driver the following works

    # Open the database session

    ch = odbcConnect("my_db")

    # Send multiple SQL queries

    res1 = sqlQuery(ch, "CREATE TEMP ...")

    res2 = sqlQuery(ch, "INSERT INTO ...")

    ...

    # Close session

    Close(ch)

    So my guess is that Odbc.Query does not let me do that because it closes the session immediately after sending the SQL command. So I wonder if it might be good idea to add a an extra parameter to Odbc.Query that lets the user open and close a session.

    Anyway, thank you for your reply!

    Monday, April 6, 2015 5:57 PM