[SOLVED] Database Desktop connecting via ODBC to SQL Server, how?

[mdlueck]

Greetings,

I jumped directly into the interface between Laz and SQL Server. I had heard that the only bridge is to use the ODBC Driver which comes with Windows / SQL Server Management Studio. Referring to the Laz Complete Guide, I learned of a "Database Desktop" application which comes with Laz and looks quite helpful, so thought to get that connected to SQL Server first.

On my machine (WinXP), I have an existing System ODBC connection to the database using the MS SQL Server driver. I can view tables in the DB via Access 2007 and using the wizard to link to an ODBC data source. That UI does not re-ask the questions which are in the ODBC connection.

So trying the Laz "Database Desktop" application, I get the impression that it is not going to utilize that System ODBC connection I have created in the control panel ODBC applet as it asks the DB server hostname, etc... so I oblige it and fill in all of the details. I try connecting and received the error:

Project Lazarus Data Desktop raised exception class 'EODBCException' with message:
Could not connect with connection string "DSN=... correct details here ...". ODBC error details: SQL_ERROR; Record 1: SqlState: IM002; NativeError: 0; Message: [Microsoft][ODBC ###(gdb unparsed remainder:...)###

And the connection string appears to be correct.

Suggestions?

[mdlueck]

I got into the Source Editor window, conneditor tab, procedure TConnectionEditor.Connect and the ConnectionString contains variables: Host,Database,User,Password,Charset.

So that seems to confirm that it is NOT going to use the system ODBC connection I have set up in Windows control panel.

Back to the Laz book for now...

[mdlueck]

I was able to finally successfully connect via filling in Database/User/Password and leaving Host empty, where Database is the name of the ODBC connection name.

Database Desktop THEN successfully looked up the connection details per Windows Control Panel.

fffeeewwww....

[IndianaJones]

So can you mark as [SOLVED]
Thanks.

[BigChimp]

I was able to finally successfully connect via filling in Database/User/Password and leaving Host empty, where Database is the name of the ODBC connection name.

Database Desktop THEN successfully looked up the connection details per Windows Control Panel.

Glad you succeeded.
I don't think Database Desktop uses the connection details of any connection saved in the ODBC Control Panel. Aren't the names of your SQL Server database (e.g. master) and your ODBC connection name the same? If they're not, that would be very interesting...

To test, try connecting to the master database which should always be there...
(I've got a small application with connection to SQL Server myself; I specify these strings as the params of the ODBCConnection object:
Database=<databasename note doesn't have to have an ODBC entry>
Server=.\SQLEXPRESS
Trusted_Connection=Yes
(leave the DatabaseName, Username, Password and HostName properties of the ODBConnection object empty)

It connects to a database on the SQLExpress instance on the local host and uses trusted authentication.

As you found out, you therefore need to specify host, database, user, password yourself.

I don't suppose there's an SQL Server example tutorial on the wiki? If not, and when I have time, I'll try to create one.

[mdlueck]

So can you mark as [SOLVED]

I tried adding that to the subject line of my "solved" post. I will try again. Else there must be another method to update the subject line in this forum system.

[mdlueck]

Aren't the names of your SQL Server database (e.g. master) and your ODBC connection name the same? If they're not, that would be very interesting...

The ODBC DNS I have added "-dev" to so that I may have my three (Dev/QA/Prod) ODBC connections at the same time on my developer machine. The actual database name via Microsoft SQL Server Management Console is without the suffix "-dev".

To test, try connecting to the master database which should always be there...

But there is no SQL Server on my workstation, so I need "something" to connect to the remote DB server. SQL Server does not appear to be like DB2 that you catalog remote databases on the local workstation node. It sounds as if the only something which Lazarus is able to use is the ODBC driver.

Perhaps not SQL Server specific, but just a brief how-to explaining connecting to an ODBC DNS cataloged on the local workstation... "no need to specify host/id/pw, just database = DNS and that is enough."

Thanks!

[BigChimp]

Have you seen the wiki; pages of interest may include:
http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL
http://wiki.lazarus.freepascal.org/ODBCConn

The ODBC DNS I have added "-dev" to so that I may have my three (Dev/QA/Prod) ODBC connections at the same time on my developer machine. The actual database name via Microsoft SQL Server Management Console is without the suffix "-dev".

Then it seems you can connect either via an ODBC control panel entry or via specifying parameters - i.e. the ODBC connection string - in your program. Cool. It should be documented somewhere, hopefully in the wiki.

[mdlueck]

Then it seems you can connect either via an ODBC control panel entry or via specifying parameters - i.e. the ODBC connection string - in your program.

???? The more verbose settings (remote host / ID / pw) are defined in the ODBC control panel applet. The Laz program merely points to the correct ODBC DSN name.

Your reply seems to suggest I could make the ODBC connection without having the ODBC control panel settings in place. Is that what you are suggesting?

How would the Laz program know which ODBC driver to use if it was not defined in ODBC control panel? In my mind, the requirements are Laz + ODBC control panel.

[BigChimp]

You're right:
1. I have found a way to connect to to an MSSQL server using only Lazarus/SQLDB, with no relevant DSN entry in the ODBC control panel.
2. You have found a way to connect to an MSSQL server using Lazarus/SQLDB and pointing it to a DSN entry in the ODBC control panel.

So apparently you can do either 1 or 2 

As for the driver in 1 - sorry I forgot to mention it, but I have set the Driver property of the ODBCConnection component to "SQL Server"

Using ODBC connections without an entry in the ODBC control panel can be done and is quite common practice. Try googling for "DSN-less connection".
You just need to specify all required data in the ODBC connection string - including, as you pointed out correctly - the driver.

[mdlueck]

You're right:
1. I have found a way to connect to to an MSSQL server using only Lazarus/SQLDB, with no relevant DSN entry in the ODBC control panel.

Oh, THAT is what you meant. Ma ei tea... I had no idea such was possible. I will give that a try as well. Thanks!