OLEDB Driver
Hi,
Is there anybody using succesfully Informix OLEDB provider?
Until now we were using an old Intersolv ODBC dirver (ver 3.11). But this
driver works very bad in connection with MDAC 2.7 (which is obligatory since
Windows XP)- eg: it simpy cuts all zeros when returning data from decimal
(16,2) column and recordsets CursorLocation = adUseServer (23000 is returned
as 23). So we decided to try latest IBM Informix Client 2.81_2 (aka IBM
Informix 3.82) and switch to OLEDB. But:
First we had to give up with executing "begin work" - instead we have to use
methods of 'connection' object like BeginTrans. Ok, it's done (but it is a
pain that we cannot control tranactions started within a stored procedure).
And the worst thing - it looks as a big bug. It happens while retunnig
client-side cursor which is a product of a an outer join.The driver returns
an error if in second row any outer column is null (NativeError: 1010
Source: Microsoft Cursor Engine, Description: Non-nullable column cannot be
updated to Null). The problem disapears when we change cursor location to
server-side. But then we cannot use recordcount property (it allways
returns -1 for server-side cursors).
The questions:
Is there any reliable method to connect to informix database from MS Windows
using MDAC 2.7?
Which cursors you are using (server or client side) and how to get
RecorCount if server-side?
If anybody wants to reproduce the error - here is the SQL and a few lines of
VB code:
Tables:
CREATE TABLE a (a1 INT NOT NULL);
CREATE TABLE b (b1 INT NOT NULL);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO b VALUES (1);
Vb code
Sub test()
Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset
With con
.ConnectionString = "Provider=Ifxoledbc;Password=xxxx;User
ID=xxxx;Data Source=xxxx
.Open
End With
With rec
rec.ActiveConnection = con
rec.CursorLocation = adUseClient
rec.Open "SELECT * FROM a,OUTER b WHERE a.a1=b.b1"
End With
'NativeError: 1010
'Source: Microsoft Cursor Engine
'Description: Non-nullable column cannot be updated to Null.
End Sub
(Sorry for my bad english)
Regards
Piotrek