Pervasive 8.50 OleDb Stored Procedure

[altb]

Hi,
I try to call a stored procedure with oledb provider in c#.
the procedure declaration in pervasive is as follows:
CREATE PROCEDURE GET_LICENSES(IN :CHANGED_AFTER TIMESTAMP,IN :IS_ANNUAL BIT,IN :IS_BAKARAT_NEHAGIM BIT,IN :IS_TEUDAT_LIVUY BIT)

This LICENSESSelectCommand works fine in Odbc:
this.LICENSESSelectCommand.CommandText = "{CALL GET_LICENSES( ?, ?, ?, ? )}";
this.LICENSESSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.LICENSESSelectCommand.Connection = con;
this.LICENSESSelectCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter(":CHANGED_AFTER", System.Data.Odbc.OdbcType.DateTime,14));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter(":IS_ANNUAL", System.Data.Odbc.OdbcType.Bit, 2));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter(":IS_BAKARAT_NEHAGIM", System.Data.Odbc.OdbcType.Bit, 2));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter(":IS_TEUDAT_LIVUY", System.Data.Odbc.OdbcType.Bit, 2));

however, converting the code to use OleDb provider always throws some exception which I suspect is because stored procedure definition cannot accept there OleDbTypes correctly, or the parameter size should be changed.

this.LICENSESSelectCommand.CommandText = "{CALL GET_LICENSES( ?, ?, ?, ? )}";
this.LICENSESSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.LICENSESSelectCommand.Connection = con;
this.LICENSESSelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(":CHANGED_AFTER", System.Data.OleDb.OleDbType.DBTimeStamp,14));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(":IS_ANNUAL", System.Data.OleDb.OleDbType.Boolean,2));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(":IS_BAKARAT_NEHAGIM", System.Data.OleDb.OleDbType.Boolean,2));
this.LICENSESSelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(":IS_TEUDAT_LIVUY", System.Data.OleDb.OleDbType.Boolean,2));

Help, I'm loosing my mind here for days....

OleDb DtaAdapter wizard also not working in VS .NET 2003 (ODBC works not perfectly, but works).

Thanx,
Amit

 

[mirtheil]

Couple of questions:
-- What's the exact version of Pervasive are you using? (what's the version of the W3ODBCCI.DLL and/or W3ODBCEI.DLL)?
-- What's the exact exception that you're getting?
-- Have you tried the Procedure using OLEDB using VB6?
-- Have you considered the Managed Provider? You might need an update for the Managed Provider.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[altb]

W3ODBCCI.DLL version 8.50.189 dated 29/10/2003.
but ODBC works fine.
when i try oledb the exception is always "Object reference not set to an instance of an object." which i suspect is related to the parameters of the stored proc, since all code is exactely the same as in ODBC.
I can also assure you that the parameters values are correct and not null.
I did not try OLEDB with VB6, it might be an idea but it won't help me in .NET OLEDB syntax. VB6 is much more "forgiving" development env, and all is "hidden" there.

I tried Pervasive managed provider Psql .NET 8.6. It also had a bug with stored proc arguments, but Pervasive representative has sent me a version of the DLL without this bug. But I share code that updates the same scheme and tables both in AccessXP and Pervasive DB there fore I have a choice of using either ODBC or OleDb (with a change of connection string only).
I've also found that OleDbCommand parameters still causes problems on update commands even not using stored procedures. "normal" select and insert commands do work.
It's all about parameters mappings and types.