Connect to SQL Server

7 replies [Last post]
Rainchild
Joined: Oct 10 2000
Posts: 17
Connect to SQL Server
I'm trying to get a DSN-less connection to Microsoft SQL Server through zScript... but no matter what I try, I'm getting the error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I assume I could get it running via a DSN (I resorted to that in the past) but I'd rather not because it's more difficult to maintain - I'd like my connection strings saved with the rest of my settings...

Currently my script looks like:
#VAR ADOConnection %comcreate( "ADODB.Connection" )
#CALL @ADOConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=test;Password=123;Initial Catalog=RFQuery;Data Source=192.168.0.1"
But I've tried other things like:
#VAR ADOConnection %comcreate( "ADODB.Connection" )
#NOOP %comset(ADOConnection,"ConnectionString","Driver={SQL Server};User ID=test;Password=123;Initial Catalog=RFQuery;Server=myserver"
#CALL @ADOConnection.Open
And quite a few others.

Can you please provide an example for access SQL Server without a DSN?  Perhaps I need to use different COM libraries, or a native TeSSH/Delphi provider?
Zugg
Zugg's picture
Joined: Jan 1 1999
Posts: 178
You need to get away from using the ADODB.Connection object.  The error you are getting actually has nothing to do with TeSSH and is just coming from the Microsoft ADODB object, which doesn't like your DSN.  It's been a while since I did stuff with ADO DSNs, but I don't see that actual name of the database in any of your above examples.  All I remember is that DSNs are a pain.  I usually set up named OLEDB objects in the Windows Control Panel and then just connected to them via ADO.

You could try using the new built-in SQL support in TeSSH (see #SQLDB and %sql)  For example:
#SQLDB MyDB DBName mssql 192.168.0.1 port UserName Password
#SHOW %sql(MyDB,"SELECT * FROM Table")

I haven't done any direct testing for MS SQL, but see the details on the #SQLDB page for the link to the database driver DLL that you might need.  TeSSH uses the native Delphi ZeosLib library and while I've only used it for MySQL and SQLite, plenty of people use it for other databases, so it should work.

If the native mssql protocol doesn't work above, you could also try the ADO support within the SQL library.  Use the %sqldb function to create the initial database object, then set the various properties directly instead of using a DSN.  For example:
db = %sqldb("MyDB", "dbname", "ado", "server", "port", "user", "password")
#CALL %db.Open
#SHOW %sql(MyDB,"SELECT * FROM Table")
Rainchild
Joined: Oct 10 2000
Posts: 17
For those who are reading this, perhaps looking for an answer/confirmation - I had to download the ntwdblib.dll but then I could connect at least using both options.

Also, the example:
#SHOW %sql(MyDB,"SELECT * FROM Table")
Didn't actually show the table though, it showed:
<COMObject>
You actually need to call something like this:
row = %sql(MyDB,"SELECT * FROM test")
#WHILE (!@row.Eof()) {#SHOW @row.Item("Column1");#CALL @row.Next}
Rainchild
Joined: Oct 10 2000
Posts: 17
Further to this, I'm having difficulties with Linked Servers... (I know it's kind of an obscure thing).

The problem is it's needing ANSI_NULLS and ANSI_WARNINGS turned on, but the library doesn't seem to do this out of the box... according to the googles, it says you have to set them on the connection object, not the query itself... I tried:
db = %sqldb("MyDB", "dbname", "ado", "server", "port", "user", "password")
#CALL db.Execute("SET ANSI_NULLS ON")
#CALL db.Execute("SET ANSI_WARNINGS ON")
Before the query, but didn't seem to do much good.  Is there some hidden commands/ini file we can use to manipulate the connection that Zeoslib creates and maybe set the options there?
Zugg
Zugg's picture
Joined: Jan 1 1999
Posts: 178
There isn't any ini file for Zeoslib.  Everything is done via COM object properties and methods.  The "db.Execute" method would only work if those options can be set via normal SQL commands.  There is a "Properties" property of the ZeosLib zConnection object that might be able to be used for this.  It currently isn't accessible via the TeSSH COM object (it points to a Delphi TStrings object) so I would need to write an interface that allows you to add strings to the property.  The ZeosLib documentation says this "Sets a new connection properties" so it seems like what you are looking for.

I'll try to add something in the next version that would allow you to add properties to this so you can test it and see if it works.
Rainchild
Joined: Oct 10 2000
Posts: 17
What would the syntax be for setting these string properties?  Or didn't it make it into 3.34?
Zugg
Zugg's picture
Joined: Jan 1 1999
Posts: 178
It didn't make it into 3.34 yet, sorry.
Rainchild
Joined: Oct 10 2000
Posts: 17
I've worked around the situation just by moving the database to a different server, so I don't have to do the linked server thing.

It was still a bit confusing to get it all working, so maybe you could add the following tips to the documentation for Microsoft SQL...

First: download the ntwdblib.dll and install it.

-----------
To connect to the database:

MyDatabaseVariableName = %sqldb("DatabaseAliasToUseInTeSSH", "NameOfSqlDatabase", "mssql", "ServerNameOrAddress\ServerInstanceName", "", "login", "password")
#CALL @MyDatabaseVariableName.Open

where ->
* MyDatabaseVariableName = We use this to #CALL functions
* DatabaseAliasToUseInTeSSH = We use this alias when using %sql ... (though it would be nicer if we could pass @MyDatabaseVariableName  and forget about the internal alias all together because I had some trouble where you have to give a unique alias to each database connection, it seems you can't re-use the same alias)
* NameOfSqlDatabase = the name of the database on the SQL Server
* mssql = constant... driver name
* ServerNameOrAddress = the name or IP of the server you want to hit... eg 192.168.0.1
* \ServerInstanceName = optional instance name, if required... eg might be \SQLExpress
* "" = leave the port blank, the servername/instancename will get you were you need to go
* login = your login
* password = your password
* #CALL  @MyDatabaseVariableName.Open  = actually opens the connection

-----------
To disconnect from the database:

#CALL @MyDatabaseVariableName.Close
#UNVAR MyDatabaseVariableName
#SQLCLOSE "DatabaseAliasToUseInTeSSH"
 
-----------
To execute a non-query, eg INSERT/UPDATE/DELETE:

sqltext = "UPDATE foo SET bar='abcdefg' WHERE id = 2"
#CALL @MyDatabaseVariableName.Execute(@sqltext)
 
-----------
To execute a query, eg SELECT:

row = %sql("DatabaseAliasToUseInTeSSH", "SELECT * FROM foo WHERE id = 2")
 
#WHILE (!@row.Eof()) {
  #SAY Do Something With Me: @row.Item("id") @row.Item("bar")
  #CALL @row.Next
}
 
#CALL @row.Close

.......

+ Hopefully that helps any who needs to get started with SQL (and myself in a year when I've forgotten it all and have to remember again!) :)