Sybase, FreeTDS driver / SRV_DYNAMIC

[STiAT]

Hi,

I know this was mainly written for MSSQL (seems like it at least).

I've the issue that when I try to execute a statement towards a Sybase database, I get the following Error: [FreeTDS][SQL Server]No SRV_DYNAMIC handler installed.

I've tried to find more sources, but it seems as if either sybase or this specific version of sybase does not support dynamic requests (so ? / prepare). I didn't use them in my statements, still I get this error. What I could find in FreeTDS is, that it works with non dynamic requests (see DBD::Sybase)..

What SAP/Sybase docs says to that:

SRV_DYNAMIC A client has sent a dynamic SQL request. SRV_PROC* The default handler sends the message “No SRV_DYNAMIChandler installed” to the client. Open Server returns DONEERROR to the client.

Is there a way to work around this, or in other words: Not to send a dynamic request to the server?

[alexbrainman]

@STiAT I have never used Sybase. And I know very little about FreeTDS. Can you use FreeTDS with Sybase? How do you configure FreeTDS to connect to Sybase? Can you access your database from different programming language, like C or Python or whatever? What OS do you use?

Alex

[STiAT]

Hi,

To be honest, I didn't try in other languages but Perl, but in Perl using DBD::Sybase works as long as I don't use dynamic requests, so Prepare with "?" placeholders, which is very well documented on FreeTDS as not being supported. I'll try in Python / C later today.

Example of connecting to a Sybase (not Sybase Anywhere, Sybase, because Anywhere would be protocol version 4.2, Sybase is 5.0):

db, err := sql.Open("odbc", "server=myserver,4100;driver=freetds;uid=ggtest;pwd:censored;TDS_Version=5.0")
if err != nil {
    log.Println(err)
}
err = db.Ping()
if err != nil {
    log.Println(err)
}

[alexbrainman]

DBD::Sybase works as long as I don't use dynamic requests

What is "dynamic requests"? I have never heard that expression.

db, err := sql.Open("odbc", "server=myserver,4100;driver=freetds;uid=ggtest;pwd:censored;TDS_Version=5.0") if err != nil { log.Println(err) } err = db.Ping() if err != nil { log.Println(err) }

So which log.Println prints the "[FreeTDS][SQL Server]No SRV_DYNAMIC handler installed" error?

Alex

[STiAT]

Hi,

It's the log.Println after the db.Ping(), which actually is the first time odbc establishes a real connection to the database. The SRV_DYNAMIC error comes from the Sybase DB.

Dynamic requests are requests using the prepare / bind method instead of just passing a full SQL statement to the database. So using db.Query without preparing, I'd not have expected the request to be dynamic - it seems it is. Maybe there is something in the driver here that does something before executing my actual query. That's at least what I could find out by FreeTDS documentation and mailing lists, that it's connected with parameter binding.

Example-query:

rows, err := db.Query("SELECT Node FROM custom.test")
if err != nil {
    log.Println(err)
} else {
    log.Println(rows)
}

Documentation about dynamic or what is seen as dynamic by Sybase can be found in DBD::Sybase: http://search.cpan.org/~mewp/DBD-Sybase-1.16/Sybase.pm#Using_?_Placeholders_&_bind_parameters_to_$sth->execute

[alexbrainman]

Dynamic requests are requests using the prepare / bind method instead of just passing a full SQL statement to the database. So using db.Query without preparing, I'd not have expected the request to be dynamic - it seems it is. Maybe there is something in the driver here that does something before executing my actual query.

Well, the driver uses SQLPrepare followed by SQLExecute ODBC calls for all queries and statements. So that is, probably, your problem. If you want to not call SQLPrepare, you have to use SQLExecDirect ODBC call instead.

There is even TODO in odbcstmt.go to make use of SQLExecDirect. So you would have to implement golang.org/pkg/database/sql/driver/Execer (or ExecerContext) that calls SQLExecDirect.

Or you could just change driver to see if SQLExecDirect actually works.

Alex

[STiAT]

Yep, that is very likely to be my issue here.

I'm getting confused by the layers here. First of all, "changing the driver" you mean changing the odbc go implementation or using another driver than FreeTDS?

To my knowledge, there is one more driver for Sybase not being FreeTDS, which would be the SAP Sybase ODBC driver, which is itself another beast.

About implementing SQLExecDirect ... I've never done that before, but I'll give it a closer look (after the weekend). I'll need to get a better knowledge of your code to actually change anything.

[alexbrainman]

"changing the driver" you mean changing the odbc go implementation or using another driver than FreeTDS?

Sorry for confusing you. I was suggesting you try changing my code. You could see which function get called when your SQL is executed. I suspect it is Stmt.Query - you could add some fmt.Printf calls in there to confirm. If it os Stmt.Query, then replace calls to s.c.PrepareODBCStmt and s.os.Exec with call to api.SQLExecDirect (you would have to add api.SQLExecDirect function too).

About implementing SQLExecDirect ... I've never done that before, but I'll give it a closer look (after the weekend). I'll need to get a better knowledge of your code to actually change anything.

Have a go. If you get stuck, just ask. If nothing else, I will do it myself - I was going to implement this myself anyway.

Alex

[STiAT]

I've scoured through your code by now a bit more, and there's one section which I can't ignore: odbcstmt.go, 100-104 (ye, the comment). Maybe there could be decided if we'd even need to bind params or not - and ye, the implementation of providing Execer / ExecerContext will very likely be necessary to that.

Thanks for all the hints & looking into this (it's likely to be a very special case), but as I said I'll be busy with other stuff that weekend and will get back to that on Monday.

[alexbrainman]

I've scoured through your code by now a bit more, and there's one section which I can't ignore: odbcstmt.go, 100-104 (ye, the comment). Maybe there could be decided if we'd even need to bind params or not - and ye, the implementation of providing Execer / ExecerContext will very likely be necessary to that.

I would not worry about this comment. What is happening here is that if SQL has some parameters, current code bind parameter values every time it executes the statement. But it can be more efficient - it can bind parameters once before running any SQLExecute, and then update parameters value every time before we run SQLExecute.

Your problem is to make SQLExecDirect work to see if it fixes your issue. So you could make it work for simplest query possible - the ones that don't have any parameters. So parameter binding code won't even run in your scenario.

Alex

[STiAT]

Oh, forgot to answer. That worked out for me, thanks!

[alexbrainman]

That worked out for me

Sounds good. I am happy that you are happy. :-)

Alex

[STiAT]

Thanks for your support Alex, was very valuable to me. And thanks for the implementation in first place - good to have solutions at hand for doing PoCs.