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
Only one query per OpenDatabase?
- RichAlgeni
- Addict
- Posts: 914
- Joined: Wed Sep 22, 2010 1:50 am
- Location: Bradenton, FL
Re: Only one query per OpenDatabase?
The number returned from OpenDatabase() is the handle. Use that to do the query, and then FinishQuery.
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.
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)
Re: Only one query per OpenDatabase?
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.
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.