SADataReader.GetSchemaTable() throws exception

[Former Member]

I am trying to use the SQL Anywhere .NET 4.0 connector v12.0.1.34574, I am attempting to query out data from a legacy Sql Anywhere 7.0 system. If I just use a "SADataReader" by just poling the data out it works as normal and I get the records out fine. However if I want to get the schema of the table I am reading from, calling the following gives me a exception

SAConnectionStringBuilder sacsb = new SAConnectionStringBuilder();
sacsb.ServerName = this.diSettings.SourceServer;
sacsb.UserID = this.diSettings.SourceUsername;
sacsb.Password = this.diSettings.SourcePassword;
sacsb.DatabaseName = this.diSettings.SourceDatabase;
sacsb.AutoStart = "true";

using (var saCommand = new SACommand("Select * from TableName;", saConn))
using (var saReader = saCommand.ExecuteReader())
{
    var schemaTable = saReader.GetSchemaTable(); //Exception here
    //Snip

The error is

iAnywhere.Data.SQLAnywhere.SAException was unhandled by user code
  HResult=-2147467259
  Message=Syntax error near '['
  Source=SQL Anywhere .NET Data Provider
  ErrorCode=-2147467259
  NativeError=-131
  StackTrace:
       at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
       at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteReader()
       at iAnywhere.Data.SQLAnywhere.SADataReader.SetKeyUniqueColumns(DataTable schemaTable)
       at iAnywhere.Data.SQLAnywhere.SADataReader.GetSchemaTable()
       at DataImporterPlugins.Plugin.Run()
       at UniversalDataImporter.MainForm.<cmdimportdata_click>b__6() in E:CodeUniversalDataImporterUniversalDataImporterMainForm.cs:line 156
       at System.Threading.Tasks.Task.InnerInvoke()
       at System.Threading.Tasks.Task.Execute()
  InnerException: 

I do not have a "[" in my code so I am assuming that "GetSchemaTable()" is passing a query in that has one.

What do I need to do to get the source table's schema? I will be moving this data to a Microsoft SQL Server 2005 database and I need to programmaticly query the column information so I can create a "mirror" table in the SQL Server database.

---

Using ILSpy it appears the error is comming from the following line in "SetKeyUniqueColumns(DataTable)" (string split across multiple lines for readability)

sACommand.CommandText = string.Format("
    SELECT systable.table_name, sysindex.index_id, sysindex.[unique], syscolumn.column_name 
    FROM sys.systable 
    JOIN sys.sysindex 
    JOIN sys.sysixcol 
    JOIN sys.syscolumn 
    WHERE ( ( sysindex.[unique] = 'U' ) OR ( sysindex.[unique] = 'Y' ) ) AND ( {0} ) 
    ORDER BY systable.table_name, sysindex.index_id, sysindex.[unique]", arg);

it seems the 7.0 engine does dot like the "sysindex.[unique]" in the query, is there anything I can do other than updating the database to something newer than 7.0 (I can do that but it is a last resort)

[jeff_albion]

I am trying to use the SQL Anywhere .NET 4.0 connector v12.0.1.34574, I am attempting to query out data from a legacy Sql Anywhere 7.0 system.

The first major version of SQL Anywhere to support our native ADO.NET driver was version 9, so it's unlikely that this arrangement will smoothly work for you (even if you are able to resolve this initial issue somehow).

It would be recommended that you also look at moving to a newer database server version if you're looking to do new development in ADO.NET with our ADO.NET provider. Our latest major version "Nagano" has recently moved into its beta phase - you may want to try it out.

---

If you'd like to continue using SQL Anywhere 7.0 and develop in ADO.NET, you might be able to access the SQL Anywhere 7.0 ODBC driver through OdbcConnection - but be aware that this method may also have its limitations.