PureBasic Forums - English

Posted: **Sat Mar 25, 2023 8:49 pm**

I was thinking of connecting to ms sql database using standard pb library and ODBC. But I'm confused, the documentation implies you can only have one active query going against an "open" database. Meaning if I want to have three query results available at the same time, I would have to call OpenDatabase three times? I'm used to ...

a) gettting a connection to db,
b) using that single connection to return multiple queries, one after another or simultaneously
c) close my queries when done, and
d) finally, close database connection when done.

But PB docs show:
a) OpenDatabase returns a database ID
b) use that db ID in DatabaseQuery call to execute a Select statement
c) when done with select, call FinishDatabaseQuery to "close" query

...but FinishDatabaseQuery expects a db ID. There is no "query id". DatabaseQuery doesn't return a handle to a specific query, just a true/false result.

Well, that implies I need three different db ID's if I want three queries open at the same time. So I'd have to call OpenDatabase three times. Is it really opening three separate connections, or is it smart enough behind the scenes to use the same ODBC connection for all queries?

Thanks

Posted: **Sat Mar 25, 2023 11:25 pm**

The number returned from OpenDatabase() is the handle. Use that to do the query, and then FinishQuery.

Code: Select all

EnableExplicit

Define dataBaseNumber.i
Define dataBaseName.s
Define dataBaseUser.s
Define dataBasePassword.s
Define queryStatement.s
Define result.i

dataBaseNumber = OpenDatabase(#PB_Any, dataBaseName, dataBaseUser, dataBasePassword)

queryStatement = "SELECT data1, data2, data3 FROM data_table;"
result = DatabaseQuery(dataBaseNumber, queryStatement)

If result
    While NextDatabaseRow(dataBaseNumber) ; Loop for each records
        Debug GetDatabaseString(dataBaseNumber, 0) ; Display the content of the first field, if it is a string
        Debug GetDatabaseLong(dataBaseNumber, 1) ; Display the content of the second field, making sure it is a integer
        Debug GetDatabaseFloat(dataBaseNumber, 2) ; Display the content of the third field, making sure it is a float
    Wend
    FinishDatabaseQuery(dataBaseNumber)
EndIf

CloseDatabase(dataBaseNumber)

Now, you don't need to close the database after each query, so you can use the handle again. However, if you write a multithreaded program, you will either need to open a new database handle for each thread, or use some sort of exclusive lock to keep threads from using the handle at the same time.

Posted: **Sat Mar 25, 2023 11:26 pm**

The question is: why do you need 3 queries at the same time?

Rewrite your SQL to get the results in one query.

Else you are right, you need more connections.
But then you need also threads, else they are not simultaniously.

PureBasic Forums - English

Only one query per OpenDatabase?

Only one query per OpenDatabase?

Re: Only one query per OpenDatabase?

Re: Only one query per OpenDatabase?