Does anyone know how to set a connection timeout using the MBS SQL plugin?
I’m connecting to MS SQL and if I enter the wrong (anything), it hangs forever.
This setting is per database client type, so which one?
MS SQL Server or Express.
How do you connect?
In the connection string you may put a “&Connection Timeout=30” as additional parameter as far as I find.
e.g. See
Will try. Thanks.
Well this does not seem to work.
The referenced URL shows using connection timeout and connect timeout (either should work).
I also found reference to SQL_ATTR_CONNECTION_TIMEOUT, which is in the MBS demo code, and also doesn’t work.
I’m also seeing references that if not defined, 30 seconds will be used, however the connection hangs indefinitely.
Has anyone else found a way to do this?
I should also say that this is a connection made from a Mac.
Does that work?
db.Option(“SQL_ATTR_CONNECTION_TIMEOUT”) = “30”
as example for SQLDatabaseMBS or SQLConnectionMBS classes?
That is the one option you can use with ODBC internally.
Using SQLDatabaseMBS
I’ve tried the above option and it doesn’t work. Is it working for you?
I have no MS SQL Server here at hand to test.
So before we look further, what do you use to connect?
Is it ODBC?
that’s perfect… if you have no MS SQL then you will replicate the situation that I’m showing to never timeout. If you have no server it should stop trying to connect after 30 seconds right?
I’m using the connection settings directly from your examples.
Details?
on macOS or linux with freetds?
on Windows with native driver? ODBC? OleDB?
maybe you need SQL_ATTR_LOGIN_TIMEOUT?
db.Option(“SQL_ATTR_LOGIN_TIMEOUT”) = “30”
db.Option(“SQL_ATTR_CONNECTION_TIMEOUT”) = “30”
db.Option(“SQL_ATTR_QUERY_TIMEOUT”) = “30”
db.Option(“DBPROP_INIT_TIMEOUT”) = “30”
db.Option(“DBPROP_SSCE_DEFAULT_LOCK_TIMEOUT”) = “30”
db.Option(“DBPROP_COMMANDTIMEOUT”) = “30”
you could set them all.
Mac.
Private Function ConnectToMSSQLServer(user as string, pass as string, databasename as string, host as string, portnumber as integer) As SQLDatabaseMBS
// connect to Microsoft SQL Database
Dim con As New SQLDatabaseMBS
Dim cs As String
Dim Port As String
If portnumber<=0 Then
port = "1433"
Else
port = Str(portnumber)
End If
#If TargetWin32 Then
cs = host +","+port+ "@"+DatabaseName
con.UserName = User
con.Password = pass
con.DatabaseName = "SQLServer:"+cs
#ElseIf TargetMacOS Then
// download library on MBS website:
// http://monkeybreadsoftware.de/xojo/download/plugin/Libs/
// connect via iODBC pointing to libtdsodbc (FreeTDS)
Dim libtdsodbc As Folderitem = app.ExecutableFile.parent.parent.child("Frameworks").child("libtdsodbc.0.dylib")
cs = "DRIVER=" + libtdsodbc.NativePath + _
";Server="+host+";UId="+User+";PWD="+Pass+";Database="+DatabaseName+";TDS_VERSION=7.2;Port="+Port '+_
'";Connect Timeout=10"
con.Option("UseAPI") = "ODBC"
con.Option("ODBC.LIBS") = "/usr/lib/libiodbc.2.dylib"
con.DatabaseName = "ODBC:"+cs
#ElseIf TargetLinux
// connect via iODBC pointing to libtdsodbc (FreeTDS)
cs = "DRIVER={libtdsodbc.so};Server="+host+";UId="+User+";PWD="+Pass+";Database="+DatabaseName+";TDS_VERSION=7.2;Port="+Port
con.DatabaseName = "ODBC:"+cs
con.Option("UseAPI") = "ODBC"
#EndIf
// DB Library settings
con.Option("DBPROP_INIT_TIMEOUT") = "10"
con.Option("DBPROP_COMMANDTIMEOUT") = "10"
// ODBC settings
con.Option("SQL_ATTR_QUERY_TIMEOUT") = "10"
con.Option("SQL_ATTR_CONNECTION_TIMEOUT") = "10"
If con.Connect Then
con.Scrollable = True
// if you like to get exceptions instead of checking error property
con.RaiseExceptions = True
LastOpenedDataTypeBlob = True
con.AutoCommit=SQLDatabaseMBS.kAutoCommitOff
Return con
End If
lastConnectionError=con.ErrorMessage
Return Nil
End Function
you added SQL_ATTR_LOGIN_TIMEOUT there?
sorry no. I put that back in the wrong place.
I had it before the connect.
no that was right. I put it before the connection attempt.
I also have tried with and without the connect timeout in the string above.
I tried it here. Stops after 10 seconds.
Dim con As New SQLDatabaseMBS
Dim libtdsodbc As Folderitem = FindFile("libtdsodbc.dylib")
cs = "DRIVER=" + libtdsodbc.NativePath + _
";Server="+Server+";UId="+User+";PWD="+Pass+";Database="+DatabaseName+";TDS_VERSION=7.2;Port="+Port
con.Option("SQL_ATTR_LOGIN_TIMEOUT") = "10"
'con.Option("SQL_ATTR_CONNECTION_TIMEOUT") = "10"
'con.Option("SQL_ATTR_QUERY_TIMEOUT") = "10"
con.Option("UseAPI") = "ODBC"
con.DatabaseName = "ODBC:"+cs
Dim m1 As Double = System.Microseconds
Call con.Connect
Dim m2 As Double = System.Microseconds
Dim md As Double = m2 -m1
Dim e As String = con.errorMessage
System.DebugLog e
Break
runs and comes back after 10 seconds with an error.
Thank you for testing. That isn’t working for me either.
Time to reinstall plugins and TDS libraries.