MBS SQL Timeout

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.