SDK 3.00 .net provider and MS visual studio

Posted in 2008

Topics: Installation, Setup & Upgrades, Stored Procedures & SPL, Server Administration, Security, Permissions & Auditing, Networking & sqlhosts Configuration, Platform-Specific Issues

Sloth — Mon, 16 Jun 2008 18:49:50 +0100 — source: Usenet: comp.databases.informix

Database is Informix Online 9.40 on a Sun Solaris machine, connecting from a windows XP client machine with SDK 3.00.TC3 installed (.net provider). Using visual studio 2008; can successfully connect to the database and populate a dataset, but cannot flush changes in the dataset back to the base. On the IfxDataAdaptorBankTrans.Update method I am getting the error 'Unknown SQL Type - 0'. Has anyone seen this before with the Informix .net provider? I can update the row fine in dbaccess/winsql. We are in the process of moving from VB6 to VS2008 so there may well be errors in my VB code... I had to adapt it from C# examples found on IBMs website. The .net provider requires a stored procedure to be run on the server before the .net provider can be used - this has been done. Thanks in advance... Table Schema: DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.UC3 Copyright (C) Informix Software, Inc., 1984-1997 { TABLE bank_trans_net row size = 83 number of columns = 9 index size = 50 } create table bank_trans_net ( ser serial not null , source char(20) not null , client_no integer not null , trans_code char(2) not null , trans_date date not null , descr char(30), trans_val money(12,2) not null , uname char(8) not null , updated date not null , check (ser > 0 ), check (client_no > 0 ) ); create index ix_bt1n on bank_trans_net (client_no) using btree ; create unique index ix_bt5n on bank_trans_net (ser) using btree ; create index ix_bt6n on bank_trans_net (source) using btree ; create index ix_bt7n on bank_trans_net (trans_code) using btree ; alter table bank_trans_net add constraint primary key (ser) ; VB code follows: Option Strict On Imports System.Data Imports IBM.Data.Informix Public Class MainForm Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim ifxConn As New IBM.Data.Informix.IfxConnection Try ifxConn.ConnectionString="database=xxxxxx;server=xxxxxxx;host=10.10.10.10;uid=userid;pwd=password;protocol=onsoctcp;service=2769;" ifxConn.Open() Dim IfxDataAdaptorBankTrans As New IBM.Data.Informix.IfxDataAdapter Dim CmdUpdateBankTrans As New IBM.Data.Informix.IfxCommand Dim CmdSelectBankTrans As New IBM.Data.Informix.IfxCommand CmdSelectBankTrans.CommandText = "select ser, source, client_no, trans_code, trans_date, " & _ " descr, trans_val, uname, updated from bank_trans_net where client_no = 1755" IfxDataAdaptorBankTrans.SelectCommand = CmdSelectBankTrans IfxDataAdaptorBankTrans.SelectCommand.Connection = ifxConn Dim ifxCBuild As New IfxCommandBuilder(IfxDataAdaptorBankTrans) IfxDataAdaptorBankTrans.UpdateCommand = ifxCBuild.GetUpdateCommand IfxDataAdaptorBankTrans.UpdateCommand.Connection = ifxConn Dim BankdataSet As New DataSet IfxDataAdaptorBankTrans.Fill(BankdataSet, "bank_trans_net") BankdataSet.Tables.Item(0).TableName = "bank_trans_net" Dim _Row As DataRow For Each _Row In BankdataSet.Tables.Item("bank_trans_net").Rows If CType(_Row("ser"), Integer) = 2443150 Then ClientNumber.Text = _Row("descr").ToString _Row("descr") = "Changed" ClientNumber.Text = _Row("descr").ToString End If Next For Each _Row In BankdataSet.Tables.Item("bank_trans_net").Rows ListBox1.Items.Add(_Row("descr")) Next Dim newDataSet As DataSet newDataSet = BankdataSet.GetChanges() IfxDataAdaptorBankTrans.Update(newDataSet, "bank_trans_net") BankdataSet.Tables("bank_trans_net").AcceptChanges() Catch ex As Exception MsgBox("Error " & ex.Message) End Try End Sub End Class

↪ replying to Sloth

Bill Hamilton — Mon, 16 Jun 2008 13:13:02 -0500 — source: Usenet: comp.databases.informix

What happen when you attack it directly with ExecuteNonQuery to do the update? ______________________ EMAILING FOR THE GREATER GOOD > From: zen110758@remove.zen.co.uk> Subject: SDK 3.00 .net provider and MS visual studio> Date: Mon, 16 Jun 2008 18:49:50 +0100> To: informix-list@iiug.org> > Database is Informix Online 9.40 on a Sun Solaris machine, connecting from a > windows XP client machine with SDK 3.00.TC3 installed (.net provider).> > Using visual studio 2008; can successfully connect to the database and > populate a dataset, but cannot flush changes in the dataset back to the > base. On the IfxDataAdaptorBankTrans.Update method I am getting the error > 'Unknown SQL Type - 0'. Has anyone seen this before with the Informix .net > provider? I can update the row fine in dbaccess/winsql.> > We are in the process of moving from VB6 to VS2008 so there may well be > errors in my VB code... I had to adapt it from C# examples found on IBMs > website.> > The .net provider requires a stored procedure to be run on the server before > the .net provider can be used - this has been done.> > Thanks in advance...> > > > Table Schema:> > > > DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.UC3> Copyright (C) Informix Software, Inc., 1984-1997> { TABLE bank_trans_net row size = 83 number of columns = 9 index size> = 50 }> create table bank_trans_net> (> ser serial not null ,> source char(20) not null ,> client_no integer not null ,> trans_code char(2) not null ,> trans_date date not null ,> descr char(30),> trans_val money(12,2) not null ,> uname char(8) not null ,> updated date not null ,> > check (ser > 0 ),> > check (client_no > 0 )> );> > create index ix_bt1n on bank_trans_net (client_no)> using btree ;> create unique index ix_bt5n on bank_trans_net> (ser) using btree ;> create index ix_bt6n on bank_trans_net (source)> using btree ;> create index ix_bt7n on bank_trans_net (trans_code)> using btree ;> alter table bank_trans_net add constraint primary> key (ser) ;> > > VB code follows:> > > Option Strict On> Imports System.Data> Imports IBM.Data.Informix> > Public Class MainForm> > Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles Button1.Click> Dim ifxConn As New IBM.Data.Informix.IfxConnection> > > Try> ifxConn.ConnectionString="database=xxxxxx;server=xxxxxxx;host=10.10.10.10;uid=userid;pwd=password;protocol=onsoctcp;service=2769;"> > ifxConn.Open()> > Dim IfxDataAdaptorBankTrans As New > IBM.Data.Informix.IfxDataAdapter> Dim CmdUpdateBankTrans As New IBM.Data.Informix.IfxCommand> Dim CmdSelectBankTrans As New IBM.Data.Informix.IfxCommand> > > CmdSelectBankTrans.CommandText = "select ser, source, client_no, > trans_code, trans_date, " & _> " descr, trans_val, uname, updated from > bank_trans_net where client_no = 1755"> IfxDataAdaptorBankTrans.SelectCommand = CmdSelectBankTrans> IfxDataAdaptorBankTrans.SelectCommand.Connection = ifxConn> > Dim ifxCBuild As New IfxCommandBuilder(IfxDataAdaptorBankTrans)> IfxDataAdaptorBankTrans.UpdateCommand = > ifxCBuild.GetUpdateCommand> IfxDataAdaptorBankTrans.UpdateCommand.Connection = ifxConn> > Dim BankdataSet As New DataSet> IfxDataAdaptorBankTrans.Fill(BankdataSet, "bank_trans_net")> BankdataSet.Tables.Item(0).TableName = "bank_trans_net"> > Dim _Row As DataRow> > For Each _Row In BankdataSet.Tables.Item("bank_trans_net").Rows> If CType(_Row("ser"), Integer) = 2443150 Then> ClientNumber.Text = _Row("descr").ToString> _Row("descr") = "Changed"> > ClientNumber.Text = _Row("descr").ToString> End If> Next> > > For Each _Row In BankdataSet.Tables.Item("bank_trans_net").Rows> ListBox1.Items.Add(_Row("descr"))> Next> > Dim newDataSet As DataSet> newDataSet = BankdataSet.GetChanges()> > IfxDataAdaptorBankTrans.Update(newDataSet, "bank_trans_net")> BankdataSet.Tables("bank_trans_net").AcceptChanges()> > > Catch ex As Exception> MsgBox("Error " & ex.Message)> > End Try> End Sub> End Class > > _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Bill Hamilton

Sloth — Wed, 18 Jun 2008 18:56:47 +0100 — source: Usenet: comp.databases.informix

"Bill Hamilton" <garage_dba@hotmail.com> wrote in message news:mailman.1245.1213639994.20610.informix-list@iiug.org... What happen when you attack it directly with ExecuteNonQuery to do the update? It works fine if I do that... see below; Dim IfxDataAdaptorBankTrans As New IBM.Data.Informix.IfxDataAdapter Dim CmdUpdateBankTrans As New IBM.Data.Informix.IfxCommand Dim CmdSelectBankTrans As New IBM.Data.Informix.IfxCommand CmdUpdateBankTrans.CommandText = "update bank_trans_net set trans_date = ?, descr= ? " & _ " where ser = ?" CmdUpdateBankTrans.Parameters.Add("trans_date", IfxType.Date) CmdUpdateBankTrans.Parameters.Add("descr", IfxType.Char) CmdUpdateBankTrans.Parameters.Add("ser", IfxType.Integer) CmdUpdateBankTrans.Connection = ifxConn CmdUpdateBankTrans.Parameters("trans_date").Value = "07/06/2006" CmdUpdateBankTrans.Parameters("descr").Value = "Description" CmdUpdateBankTrans.Parameters("ser").Value = 2443150 IfxDataAdaptorBankTrans.UpdateCommand = CmdUpdateBankTrans IfxDataAdaptorBankTrans.UpdateCommand.ExecuteNonQuery()

Glossary

dbaccess