Only one query per OpenDatabase?

Just starting out? Need help? Post your questions and find answers here.
sc4pb
User
User
Posts: 26
Joined: Tue Mar 07, 2023 5:33 pm

Only one query per OpenDatabase?

Post by sc4pb »

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
User avatar
RichAlgeni
Addict
Addict
Posts: 914
Joined: Wed Sep 22, 2010 1:50 am
Location: Bradenton, FL

Re: Only one query per OpenDatabase?

Post by RichAlgeni »

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.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Only one query per OpenDatabase?

Post by infratec »

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.
Post Reply