Upon connection attempt I’m getting “DBMS API client not set”. As far as I understand that’s a function of the “UseAPI” option, so … what am I missing?
(I’m using Sql Server authentication, latest download of Sql Server developer addition).
Surely Christian will give you a more exhaustive answer, but what I can tell you is that you have to install sqlncli11.msi.
The property Databasename should be: “SQLServer:” + server_name + “@” + database_name.
I think you can avoid the option “SQLNCLI.LIBS”.
Thanks Nedi – this local sql server is a fresh install of the developer edition which includes sqlncli11.msi as verified from the drivers tab of the ODBC administrator (which is the quickest way I know to check) (see image below).
I’ll try the DB name adjustment and removing the SQLNCLI.LIBS option.
ETA: No joy on changing to servername@dbname with or without the SQLNCLI.LIBS option. This doesn’t surprise me as the connection would have to succeed before it could be lost about the database name.
I will say that servername in the database name field is redundant, given I’ve already specified the database name as localhost (which works for connecting with the Xojo plugins – I just can’t get past bizarre problems with those, such as the ODBC driver returning only one row when the same query in SSMS returns the whole table of nearly 6K records).
The only thing that’s different now is it hangs for a few seconds before returning the error.
Same behavior with or without the two .Option settings mentioned in my original message.
We can eliminate user name / password as an issue because it works with the Xojo plugins (their issues are past the connection stage) and there would be a different error message for invalid credentials.
I determined the computer name from system info CP device name, and the instance name from SELECT @@SERVERNAME, although I assume localhost would happen to work in my situation.
I have success doing this way - not sure it helps but works great for me. This is over VPN
me.db = new SQLDatabaseMBS
ConnectionString ="SQLServer:DBServerName@DBName"
db.DatabaseName = ConnectionString
db.UserName = "username"
db.Password = "password"
db.RaiseExceptions = true // if you like to get exceptions instead of checking error property
db.Scrollable = false // disabling scrolling cursors is much faster for Microsoft SQL Server...
db.Option("UseAPI") = "ODBC"
if db.Connect then
// do our thing here
end if
Thanks, Sean. I had missed the RaiseExceptions property; now I am getting some feedback, although, I am not sure what to make of it.
I am getting the error message, rc != SQL_SUCCESS in the form of an MBS DB exception class, when I attempt to grab the ColumnCount from a rowset that should have returned a little under 6K rows but has none. Immediately before that, the SelectSQL() method call, which is in a Try block, does not trip the corresponding Catch block, sails right past it and goes on about its business. Now at least the rowset is not Nil, it is just empty. Progress, I suppose. I’m connected now (actually hooked the blasted thing up to a system DSN I knew was working rather than futzing with connection strings) – it’s just that I’m not able to get results back.
Yes, the queries are fine … you can copy and paste the query into SSMS and it works perfectly. On startup it’s just simple “pull all of these tables into rowsets” logic basically, very simple stuff mostly without so much as an ORDER BY.
Thanks for the ideas though, every little bit helps.
We run into several things.
The server in the connection string got a timeout.
So we switched to localhost by leaving the host name away.
The user account was denied by the server now, so we removed username and password to use windows authentication.
Next we got a newer plugin for the FieldCount fix in 23.1pr.
And then we removed the code, which disconnected the connection before the RowSet was used. Please leave connections open, especially long enough until you are done with RowSets.
For everyone else’s benefit, it is drilled into .NET developers to open connections as briefly as possible and then close them, and rely on the internal connection pool in ADO.NET. It appears a global app connection, kept open for the life of the app or perhaps a connection per window / tab / whatever is necessary. Also as a .NET developer I am accustomed to in-memory structures like rowsets not being dependent or even aware of DB connections. Once you have the query result, you’re done with the DB.
In .NET you also generally wrap unmanaged resources like DB connections in using {} blocks so they go out of scope and automatically close when you’re done with them. From what I understand via Christian, in Xojo connections close automatically when they go out of the scope in which they were created.
Finally – all of this existing code and architecture worked perfectly (and very fast) as-is with the Xojo Postgres plugin on the Mac, so I had no particular reason to think it was going to act differently with a different DB backend or require rewriting beyond the relatively minor differences in SQL syntax.
All that said, the main thing is I’m connected and can move on from here and get the tool up and running in the new environment.
