LinLog

I've been wrestling with Sybase again lately. Both last Thursday and today, I had to write a couple of small programs to update and reformat values in some Sybase tables at work. The programs themselves were very simple and took maybe half an hour to write. However, due to the pain inherent in connecting to Sybase, this quick update turned into a several-hour ordeal.

The database in question is running on , which is about three major versions out of date.

While I wrote the original client program in VB6 with ADO 2.6, I have now switched to C#. This is partly because VB6 sucks by comparison to C# and partly because I'm trying to make my skillset more marketable. So when I wrote those little update programs, I did it in C# with .NET 2.0. However, that didn't go so well.

Method 1 for connecting to database in question, which runs Sybase SQL Anywhere 7, was to use the System.Data.OleDb classes and just use the provider that worked with ADO 2.6. But, of course, it didn't work with ADO.NET. I even tried following the Sybase how-to on ADO.NET, but the ExecuteDataReader always resulted in a "no such interface supported" COM exception. The only difference was that I was using the ASAProv.70 provider instead of ASAProv.80, which I don't have. Presumably the older provider doesn't do ADO.NET.

The second attempt was to use the .NET 2.0 System.Data.Odbc classes. My reasoning was that, since ODBC is an industry standard supported by virtually everyone, using straight ODBC should work. And it did...sort of. I was able to connect to the database and read records without incident. The problem was with updating using OdbcCommand objects.

The code I used was pretty unremarkable, which is why I was a little surprised when it failed. It looked something like this:

OdbcConnection dbconn = new OdbcConnection("DSN=SomeDSN);
dbconn.Open();
/* Lots of unrelated code.... /*
string sql = "update MyTable set Bar = ?, Baz = ? where ID = ?;";
OdbcCommand cmd = new OdbcCommand(sql, dbconn);
cmd.Parameters.Add("Foo", OdbcType.Char, 15).Value = someVar;
cmd.Parameters.Add("Baz", OdbcType.Char, 15).Value = someOtherVar;
cmd.Parameters.Add("Foo", OdbcType.Int).Value = someIDVar;
int result = cmd.ExecuteNonQuery();

There shouldn't really be much to go wrong there. However, executing the command resulted in the following exception:

Exception System.Data.Odbc.OdbcException was thrown in debuggee:
ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]General error: Host variables may not be used within a batch

Googling this message came up with this page from Sybase website, which helpfully tells me:

You have attempted to execute a batch which contains host variable references. Host variables are not supported within a batch.

That's it. Just a restatement of the error message. I find this to be fairly typical of Sybase documentation.

At this point, I was faced with two questions:
1) What the heck is a "host variable?"
2) Why am I getting a message that they aren't allowed?

The first question was answerable with a little Googling. As explained by this page from the IBM iSeries manual, a host variable is just what the name suggests: an external variable that gets used in an SQL statement. You can use them for input and output parameters when writing more complicated SQL programs.

For the second question, Google failed me, so I can only speculate as to the answer. My guess is that ADO.NET is translating the parameters into host variables rather than inserting the values directly into the SQL. Presumably my version of the ODBC provider doesn't support host variables, hence the error. It seems to make sense, but as I said, that's only a guess.

My final solution? After spending way too much time on fruitless searching and experimentation, I finally decided to just go the quick and dirty route of string concatenation. Instead of parameters, I just used a String.Replace to escape quotes and spliced the new field values directly into the SQL. It's ugly, and I wouldn't want to use it in a "real" program, but you know what? It works.