none
How to reuse SQL connection RRS feed

  • Question

  • Hi,

    Is it possible to write SQL query in separate line?

    e.g. this M code: Sql.Database("localhost", "Northwind", [Query= select * from Customers])

    can I write like below?

    connection = Sql.Database("localhost", "Northwind"),

    result = connection {[Query= select * from Customers])

    My next question is if PQ can reuse the same connection for multiple query. In above case, I can write 2nd query by using the same connection again and my join them later.

    Thanks, Di

    Thursday, September 1, 2016 12:47 PM

Answers

All replies

  • Yes, you can definitely do this.

    In fact, there's a command in the UI to help you extract steps into a separate query. Simply right-click on a query step and choose "Extract previous".

    Ehren


    Friday, September 2, 2016 9:40 PM
    Owner
  • Hi Ehren,

    Can you provide an example how to write as 2 statements?

    In fact, you can't use Extract previous if you add 1st step as

    connection = Sql.Database("localhost", "Northwind")

    My finding is it's possible to add 2nd step only if query a table/view. Then i can write as

    result = Connection{[Schema="dbo",Item="Customers"]}[Data]

     

    Saturday, September 3, 2016 3:48 AM
  • Hi Di,

    Just to make sure I understand, are you wanting to keep the server/database static, but be able to change the SQL query dynamically?

    If that's the case, you can do this by creating a custom function. Something like:

    Connection = (query as text) => Sql.Database("localhost", "Northwind", [Query = query]),

    Result1 = Connection("select * from Customers")

    Result2 = Connection("select * from Orders")

    Hope that helps.

    Ehren

    Tuesday, September 6, 2016 3:33 PM
    Owner
  • Hi Ehren,

    This helps but not really solve my question. By using function, it seems SQL will establish connection every time call. I try to reuse one established connection. Just imagine typical coding in other language like below, which will only establish one connection.

    conn = new connection ( ("localhost", "Northwind")

    q1 = conn.(query1)

    q2 = conn.(query2) ....

    conn.close()

    thanks,

    Saturday, September 17, 2016 2:04 AM
  • Ah, I see. No, there's currently no way to share a SQL connection across multiple queries.

    Ehren

    Monday, September 19, 2016 5:55 PM
    Owner