Opening remote data base

[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

[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

[mjwiech]

First, thank you very much, Paul, for helping me.

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

[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

[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

[pdwyer]

Some ADO code from people who've got it to work

http://www.purebasic.fr/english/viewtop ... hlight=ado
http://www.purebasic.fr/english/viewtop ... hlight=ado

[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