locked
How to reuse SQL connection RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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