I have the ODBC driver provided by xojo and I have the driver provided by Microsoft for the ODBC connection, but what I can’t find is a connection string to get connected to SQL Server.
Does anyone have a sample that I can reference?
Maybe check over the ODBC examples on this site, given the version you’re using - SQL Server connection strings
Thank you Scott… that will get me started.
I’ve installed the ODBC driver that Microsoft provides for SQL Server, and I found the following connection string, but it’s crashing xojo. Here is my code (with some key parts hidden).
var odbcDB As New ODBCDatabase
odbcDB.DataSource = “Driver={ODBC Driver 18 for SQL Server}”
odbcDB.DatabaseName = “ProwareOE”
odbcDB.Host = “id_address_for_server”
odbcDB.UserName = “my_username”
odbcDB.Password = “my_password”
try
odbcDB.connect
catch error As DatabaseException
Messagebox(“Error!”)
end
Immediately crashes XOJO on the odbdDB.Connect command.
Maybe try putting all your connection info in the DataSource?
Var db As New ODBCDatabase
db.DataSource = "Driver={ODBC Driver 18 for SQL Server};Server=user\SQLEXPRESS;Database=TestDB;Trusted_Connection=Yes;"
Try
db.Connect()
Catch error As DatabaseException
MessageBox("Error!")
End Try
You mean like this? Still get a crash on the connect.
odbcDB.DataSource = “Driver={ODBC Driver 18 for SQL Server};Server=ip_address_of_server;Database=ProwareOE;UID=my_username;Pwd=my_password”
This is a working code example from one of my apps. With the “ODBC Driver 18” I couldn’t get a working connection either. That’s why I stuck with “ODBC Driver 17”.
#If TargetMacOS then
const DATA_SOURCE as string = "Driver={Actual SQL Server};Server=192.168.1.92;Database=MyDatabase;UID=sa;Pwd=MyPassword"
#Else
const DATA_SOURCE as string = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.1.92;Database=MyDatabase;UID=sa;Pwd=MyPassword;" //Replace <database>, <uid> and <password>
#EndIf
//Set up the database object
self.mDb = new ODBCDatabase
self.mDB.DataSource = DATA_SOURCE
self.mDB.Timeout = 5 // not necessary
self.mDB.SetConnectionAttribute(ODBCConstant.SQL_LOGIN_TIMEOUT, 5) // not necessary
System.DebugLog(DATA_SOURCE)
//Attempt to connect
try //Trap database exceptions
self.mDB.Connect() // Connect to the database engine
catch error as DatabaseException //Catch any DatabaseExceptions
System.DebugLog("Error: " + error.Message) // Display the exception message
exit sub // and bail
end try //End the database connection trap
System.DebugLog("Database connected") //OK so far
I’ve never tried Microsoft driver on macOs.
We’ve used the Actual driver previously on Mac. MS is supposed to be providing a standard one for Mac also these days, but we’ve lost our need to use it.
Michael
Do you think I need to uninstall the version 18 driver before installing and trying the version 17 driver?
No. Both drivers are installed in my Windows
I really need this to work on a Mac. I tried the code you provided for Windows, but it does not work for Version 17 or 18 of the MS ODBC driver.
I can’t get access with the Microsoft drivers on my Mac either.
I have had the ActualTech driver there for a long time. Since switching to ODBC, the only problem I have had on the Mac is that reading and writing blob fields on the MS SQL server does not work (Issue #76243).
On Windows: Do you have adapted the Database, the Server, the UID and the Password in the DATA_SOURCE constant?
This is a sample out of a running App.
I have tested the ODBC drivers on my Mac and under Windows again:
Windows: With “ODBC Driver 17 for SQL Server” I get the connection. With “ODBC Driver 18 for SQL Server” I get the error number 1: “Dialog error”.
macOS: With “ODBC Driver 17 for SQL Server” the app crashes without an error message. With “ODBC Driver 18 for SQL Server” I also get error number 1, but without the text: “Dialog error”.
On the Mac I use iODBC Database Administrator (64 bit) to Create the System DSN (here for Actual SQL Server) - I currently have two MS SQL databases.
For example, the connection to the database does not work if the user from the DATA_SOURCE string is already logged on to the database in Windows with SQL Server Management Studio.
To test the connection, you can also log on to the MS SQL Server without specifying a database:
#If TargetMacOS then
const DATA_SOURCE as string = "Driver={Actual SQL Server};Server=192.168.1.1;Database=;UID=sa;Pwd=MyPassword" //Replace <Server>,<UID> and <Password>
'const DATA_SOURCE as string = "Driver={ODBC Driver 18 for SQL Server};Server=192.168.1.1;Database=;UID=sa;Pwd=MyPassword;"
#Else
const DATA_SOURCE as string = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.1.1;Database=;UID=sa;Pwd=MyPassword;" //Replace <Server>,<UID> and <Password>
#EndIf
I do this if the app still has to create the database.
What happens if you create a DSN within the ODBC administrator app and attempt to connect to that DSN. At least this would tell you if there is something that you have to configure and perhaps what it is.
(1) If I try to install in macOS the “ODBC Driver 17 for SQL Server” (“opt/homebrew/Cellar/msodbcsql17/17.10.6.1/lib/libmsodbcsql.17.dylib”) I get a “general installer error”.
(2) If I try to install in macOS the system DSN for my database with the “ODBC Driver 18 for SQL Server” (“/opt/homebrew/Cellar/msodbcsql18/18.4.1.1/lib/libmsodbcsql.18.dylib”) I get the message:
An error occurred when trying to add the DSN : Request failed
(3) The connection to MS SQL Server with driver version 17, without specifying a database, immediately leads to a dump.
(4) The connection to MS SQL Server with driver version 18, without specifying a database, leads to error number “1” - I think that is a “Dialog Error”.
(5) If I add a System DSN with “Actual SQL Server” I get a connection:
But the connection with Actual SQL Server has the error that no BLOB can be read or written.
What architecture are you targeting? According to this page:
Apple Silicon required 17.7 or 18 to work. You seem good for both of those.
What installs the stock ODBC system, into which the other drivers connect? Is that up to date. Being unable to add a DSN would suggest that something is odd about that. You could try a UserDSN as a test of permissions.
Once you have a DSN the administrator application allows you to test the connection. This is independent of Xojo so should allow you to test the basics vs any Xojo issues.
Reading it again, it kinda suggests that your ODBC administrator app isn’t compatible with the drivers. Perhaps a version issue or an architecture one (Admin is x64 and drivers are Arm?)
I’m on a trip. But I will take a look at it in the course of next week.
Let’s see if there is an upgrade for iODBC administrator. I will then also reinstall the 17 driver from MS.
Thank you for your support!
No problem. I remember similar issues when there was a 32bit/64bit transition.