Page 1 of 1
Opening remote data base
Posted: Tue Jan 22, 2008 12:25 am
by mjwiech
Hi,
Is there a way to open a remote (SQL Server) database without defining it first in Windows Control Panel - Data Sources?
Eg.
OpenDataBase_DSN(#DbHandle, "SQL.MYSERVER.COM", params... )
Can anybody give me a hint how to do it?
Kind regards
mjw
Posted: Tue Jan 22, 2008 12:54 am
by pdwyer
From memory the PB network lib doesn't handle DSNLess connections. I thought that they would just work as you would just pass a "connect string" with all the details but for some reason PB wants an actual DSN.
You might be able to create a file DSN programatically (they are just text) and use that, haven't tried it though. (you can create one in control panel once, open it, look at the text then try and create it programatically. It's basically a connect string in a file)
Other alternatives would be call the ODBC API yourself (can be kind of complex at first) or use a third party lib. Not sure if SQLTools has an include for PB or not, I used to use it in powerbasic.
Or (reaching a bit now) I think someone might have posted ADO COM code in the tips section, that would allow you to pass a connect string rather than use a DSN... assuming it works
Posted: Tue Jan 22, 2008 2:45 am
by mjwiech
First, thank you very much, Paul, for helping me.
pdwyer wrote:You might be able to create a file DSN programatically (they are just text) and use that, haven't tried it though. (you can create one in control panel once, open it, look at the text then try and create it programatically. It's basically a connect string in a file)
I was able to create a *.dns file in
> C:\Program Files\Common Files\ODBC\Data Sources
How can I use it in my program? Sorry for stupid question... I am an ODBC beginner.
Other alternatives would be call the ODBC API yourself (can be kind of complex at first) or use a third party lib. Not sure if SQLTools has an include for PB or not, I used to use it in powerbasic.
I found some example files on PowerBasic site. I'll see what I can make out of them.
Or (reaching a bit now) I think someone might have posted ADO COM code in the tips section, that would allow you to pass a connect string rather than use a DSN... assuming it works
I will search for it.
One of my problems is that I don't know what is the relationship between the database "handle" used in PureBasic and the small integer value returned by SQLConnect() function in odbc32.dll.
Kind regards and thanks again
mjw
Posted: Tue Jan 22, 2008 11:06 am
by pdwyer
I made some progress, the docs are out of date which kind of threw me as their example doesn't compile.
I created a DSN file that looks like this
Code: Select all
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25
DefaultDir=c:\
DBQ=c:\MyDB.mdb
And put an access DB there with the correct name. (I don't have SQL server running at home). but you can create an SQL server one and take a look or get the text off the web.
I put the file in "C:\Program Files\Common Files\ODBC\Data Sources" and when I use the OpenDatabaseRequester() I can see it and select it and run a select against it using a modification of the PB example. What I don't know how to do and someone who's used PB's internal method more is just use "OpenDatabase()" and get it to open. I don't know what to put as the database name. Trying various things like the DSN name, the DSN file name and DB name I get the same error:
Code: Select all
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Personally I can't understand why file DSNs work one way and not another, and considering the text in them being the same as a connect string I can't understand why PB can't use connect strings for DSNless connections.
I might grab that ODBC API code off the powerbasic forum and port it, as I recall I was the one who posted it there about 5 years back
Posted: Tue Jan 22, 2008 11:37 am
by Kiffi
with the PBOSL_ExDatabase - Include from mk-soft (and ts-soft) it is possible
to add (and remove) an ODBC-Connection at runtime:
http://www.purebasic.fr/german/viewtopi ... 000#182000
Greetings ... Kiffi
Posted: Tue Jan 22, 2008 1:53 pm
by pdwyer
Posted: Thu Jan 24, 2008 4:33 am
by mjwiech
Hi Paul and Kiffi,
Thanks again for your valued help.
I share Paul's opinion on PureBasic being unable to open databases without having previously defined a datasource (opening a DSN-less connection). Perhaps in the next release...
The German library PBOSL_ExDatabase, suggested by Kiffi, seems to be very interesting. And I could make some use of my rusty German
Kind regards
mjw