System.Data.Odbc bug for connect IRISDB through odbc for unixodbc

[1009058470]

Description

When i try to using System.Data.Odbc to connect the irisdb thought odbc to do some select sql , it give a error for that. and when i using isql to do the same sql , it return right result

Reproduction Steps

1 to install the IRISDB(https://docs.intersystems.com/iris20222/csp/docbook/DocBook.UI.Page.cls) 2 write this code

        public DataSet exeSqlForDataSet(string queryString)
        {
            try
            {
                  string MysqlString = "DSN=iris;UID=_system;pwd=sys";//"DSN=kingbase;" +
                                      //      "UID=Admin;" +
                                        //    "pwd=Passw0rd";

                  OdbcConnection conn; = new OdbcConnection(MysqlString); //数据库连接 
                //CLogger._log.Debug(QueryString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                queryString = queryString.Replace('\r', ' ').Replace('\n', ' ');

                var cmd = new OdbcCommand();
                cmd.Connection = this.conn;
                if (inTransaction)
                    cmd.Transaction = trans;
                DataSet ds = new DataSet();
                var ad = new OdbcDataAdapter();
                cmd.CommandText = queryString;
                //CLogger._log.Debug(QueryString);
                ad.SelectCommand = cmd;
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e2)
            {
                throw e2;
            }
        }

3 when you exec this fun for a sql , it will throw the Exception for that something is not valid

Expected behavior

return the result for that sql

Actual behavior

throw exception System.FormatException: Input string was not in a correct format.

Regression?

No response

Known Workarounds

No response

Configuration

the dotnet is dotnet5 system.data.odbc version is 5

Other information

No response

[dotnet-issue-labeler[bot]]

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

[ghost]

Tagging subscribers to this area: @roji, @ajcvickers See info in area-owners.md if you want to be subscribed.

Issue Details

---

### Description When i try to using System.Data.Odbc to connect the irisdb thought odbc to do some select sql , it give a error for that. and when i using isql to do the same sql , it return right result ### Reproduction Steps 1 to install the IRISDB(https://docs.intersystems.com/iris20222/csp/docbook/DocBook.UI.Page.cls) 2 write this code ``` public DataSet exeSqlForDataSet(string queryString) { try { string MysqlString = "DSN=iris;UID=_system;pwd=sys";//"DSN=kingbase;" + // "UID=Admin;" + // "pwd=Passw0rd"; OdbcConnection conn; = new OdbcConnection(MysqlString); //数据库连接 //CLogger._log.Debug(QueryString); if (conn.State != ConnectionState.Open) { conn.Open(); } queryString = queryString.Replace('\r', ' ').Replace('\n', ' '); var cmd = new OdbcCommand(); cmd.Connection = this.conn; if (inTransaction) cmd.Transaction = trans; DataSet ds = new DataSet(); var ad = new OdbcDataAdapter(); cmd.CommandText = queryString; //CLogger._log.Debug(QueryString); ad.SelectCommand = cmd; ad.Fill(ds); return ds; } catch (Exception e2) { throw e2; } } ``` 3 when you exec this fun for a sql , it will throw the Exception for that something is not valid ### Expected behavior return the result for that sql ### Actual behavior throw exception System.FormatException: Input string was not in a correct format. ### Regression? _No response_ ### Known Workarounds _No response_ ### Configuration the dotnet is dotnet5 system.data.odbc version is 5 ### Other information _No response_

Author:	1009058470
Assignees:	-
Labels:	`untriaged`, `area-System.Data.Odbc`
Milestone:	-

[danmoseley]

@1009058470 Can you please try .NET Framework and confirm you see the same behavior, just to exclude a regression here?

[1009058470]

@danmoseley it run well on windows with the same using dotnet5 and i try net framework also on windows , it also run well but i do not how to install net framework on linux this issus is on ubuntu 1804 using unixOdbc

[danmoseley]

Ah, my bad, I missed its Unix.

[roji]

@1009058470 can you please include the full exception, including its stack trace?

[1009058470]

@roji @danmoseley i only find this error in this db , the other db that using this nuget return the true data, do you need to download that db to make some test ?

the unixOdbc do the same sql is here

$ isql iris
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select id from image
+---------------------+
| ID                  |
+---------------------+
| 1                   |
| 2                   |
+---------------------+
SQLRowCount returns 2
2 rows fetched
SQL>

The full log is here

System.FormatException: Input string was not in a correct format.
   at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)
   at System.Number.ParseInt64(ReadOnlySpan`1 value, NumberStyles styles, NumberFormatInfo info)
   at System.Int64.Parse(String s, IFormatProvider provider)
   at System.Data.Odbc.OdbcDataReader.internalGetInt64(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at _1.OdbcDB.exeSqlForDataSet(String queryString) in E:\w\1\1\ODBCDB.cs:line 232
Unhandled exception. System.FormatException: Input string was not in a correct format.
   at _1.OdbcDB.exeSqlForDataSet(String queryString) in E:\w\1\1\ODBCDB.cs:line 238
   at _1.Program.Main(String[] args) in E:\w\1\1\Program.cs:line 14

and the full code is here

using System;

namespace _1
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            #region iris odbc
            OdbcDB odbcDB = new OdbcDB();
            odbcDB.Open();
            odbcDB.exeSqlForDataSet("select id from image");
            #endregion

            Console.ReadLine();

        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Odbc;

namespace _1

{
    public class OdbcDB
    {
        #region private
        public string MysqlString = "DSN=iris;UID=_system;pwd=sys";//"DSN=kingbase;" +
                                                                   //      "UID=Admin;" +
                                                                   //    "pwd=Passw0rd";

        private OdbcConnection conn; //数据库连接 

        private OdbcTransaction trans; //事务处理类 
        private bool inTransaction = false; //指示当前是否正处于事务中 

        #endregion

        #region Interface.IPhysicsDb
        public IDbConnection Connection => this.conn;//new OdbcConnection(MysqlString);

        public string strConnectionDB { get => this.MysqlString; set { } }

        public void Close()
        {
            if (conn.State.ToString().ToUpper() == "OPEN")
                this.conn.Close();
        }

        public DataSet exeSqlForDataSet(string queryString)
        {
            try
            {
                //Console.WriteLine(QueryString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                queryString = queryString.Replace('\r', ' ').Replace('\n', ' ');

                var cmd = new OdbcCommand();
                cmd.Connection = this.conn;
                DataSet ds = new DataSet();
                var ad = new OdbcDataAdapter();
                cmd.CommandText = queryString;
                ad.SelectCommand = cmd;
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e2)
            {
                Console.WriteLine(e2);
                throw e2;
            }
        }

        public bool Open()
        {
            if (conn == null)
            {
                if (MysqlString != null)
                    this.conn = new OdbcConnection(MysqlString);
                else
                    return false;
            }

            if (conn.State.ToString().ToUpper() != "OPEN")
            {
                try
                {
                    this.conn.Open();
                }
                catch (Exception e)
                {
                    throw e;

                }
                return true;
            }

            return true;
        }
        #endregion
    }
}

[roji]

i only find this error in this db , the other db that using this nuget return the true data, do you need to download that db to make some test ?

Yes - we need to be able to fully reproduce the exception in order to investigate further. The link you provided above seems to be a driver installation help page, and I'm still not clear whether you're able to successfully connect to a different database (with the same driver!), or successfully connect to the problematic database with a different driver (i.e. from another language).

BTW I noticed on the driver download page that there's an ADO.NET driver - this could be a better option than ODBC. You may want to give that a try.

[1009058470]

Yes, it has a ado.net driver and that can use But i want to using the odbc to connect that why has that error, and that ado.net driver that for this db has many version and it is difficult to support that using that

[1009058470]

i put that db and key and that odbc dirver here can you have a look ? @roji

IRISHealth-2020.1.0.215.0-win_x64.exe ：
https://www.aliyundrive.com/s/w1RiAbWeWVQ

key：
https://www.aliyundrive.com/s/czzHAQYn5pK

odbc on ubuntu ：
https://github.com/intersystems-community/iris-driver-distribution

Thanks a lot

[1009058470]

How can i debug into the system.data.odbc this thing seem that it has some error when parse a long value

[danmoseley]

@1009058470 if you’re using the Visual Studio debugger you should just be able to debug into it. Make sure that Just My Code is off, and that you have enabled the Microsoft symbol server, and source server is enabled too.

[1009058470]

@danmoseley i may be find why that happen it seems that the System.Data.Odbc only support unicode odbc driver, it do not support 8-bit odbc driver and IRIS database unixodbc driver only support 8-bit odbc driver and that make this happened. Is there any config that i can make the System.Data.odbc to support 8-bit odbc driver? Should i need to open a new issus and close this?

 the ODBC log, when the .NET application uses GetInt64() to get the data, the ODBC log shows:

--> SQLGetData: (23:01:03:952) hstmt = 0x55a41c627e00 col = 1 fCType = -8 cbValueMax = 4092

ID = ø‘€‰

When the .NET application uses GetInt32() to get the data, the ODBC log shows correctly:

--> SQLGetData: (03:34:24:206) hstmt = 0x563eb1398b20 col = 1 fCType = -16 cbValueMax = 4092

ID = 1

Thanks a lot ?

[danmoseley]

@roji or someone in his team will have to answer that as it's not my area.

[1009058470]

@roji Is the System.Data.Obdc can only use unixODBC on ubuntu? I config a DSN which is a iodbc driver and when using System.Data.Obdc will throw these error.

Unhandled exception. System.Data.Odbc.OdbcException (0x80131937): ERROR [0] [unixODBC][
  at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at _1.OdbcDB.Open() in E:\w\1\1\ODBCDB.cs:line 256

If there any config that i can use the iodbc for that?

My /etc/odbcinst.ini

[InterSystems iODBC]
UsageCount=1
Driver=/home/fcy/irisOdbc/2020lnxubuntux64/bin/libirisodbciw35.so
Setup=/home/fcy/irisOdbc/2020lnxubuntux64/bin/libirisodbciw35.so
SQLLevel=1
FileUsage=0
DriverODBCVer=02.10
ConnectFunctions=YYN
APILevel=1
DEBUG=1
CPTimeout=<not pooled>

My /etc/odbc.ini is here:

[ekg]
Driver=InterSystems iODBC
Description=IRIS ODBC driver
Host=192.168.102.153
Namespace=dhc-ekg-dev-test
UID=_system
Password=SYS
Port=1972
Protocol=TCP
Query Timeout=1
Static Cursors=0
Trace=on
TraceFile=/etc/odbc/log/iodbctrace.log

the iodbctest "DSN=ekg" show ok like this

$ iodbctest "DSN=ekg"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326
Driver: 2020.1.2.517.0 (libirisodbciw35.so)

SQL>

And my code is also this one only change the DSN

[dimenus]

I've also run into this issue using Caché (which is Intersystems legacy product). I tracked it down to it being an issue with the null check.

https://github.com/dotnet/runtime/blob/283a2debf5270711e791f845b688efa2a98c8e77/src/libraries/System.Data.Odbc/src/System/Data/Odbc/OdbcDataReader.cs#L1785

class Program
{
    static void Main(string[] args)
    {
        using var db_conn = new OdbcConnection("DSN=Samples");
        db_conn.Open();
        using var cmd = db_conn.CreateCommand();
        cmd.CommandText = "select FavoriteColors from sample.person";
        using var reader = cmd.ExecuteReader();
        while (reader.Read()) {
            var value_isnull = reader.IsDBNull(0);
            Console.WriteLine($"{value_isnull}:{reader.GetString(0)}");
        }
    }
}

This check always fails. cbActual returns 0xFFFFFFFF from libodbc and that does not equal IntPtr(-1) in .NET land.

[1009058470]

thanks @dimenus i had find the iris dev and they say that them odbc not support unixodbc, it is only support iodbc in unicode