I am going to start posting under this topic errors I encounter while
developing with the .NET provider. Feel free to comment on any of these
errors if you've encountered them before or if you have any advice to
offer.

I am using a 8.6 server running on a Windows 2000 Server and developing
on a Windows XP workstation in Visual Studio .NET 2003 with .NET
framework 1.1.

================================================== ==========

PROBLEM #1
==========
provider throwing a number of exceptions when using ExecuteNonQuery on
different commands.

I created two PsqlCommands using the forms designer. One is mapped to a
stored procedure and the other to a text statement. Both have
parameters (which I had to define in code since the designer, using the
collections editor, did not seem to generate any code for them). The
text command is executed a number of times in a loop and is associated
to a transaction. The stored procudure command is then called to update
another table.

When I run the commands, I first set their parameters through the
parameters collection and then execute them using ExecuteNonQuery. The
following behavior occurs:

When the commands are run for the first time, they all succeed and the
DB tables are updated.

The second time through, the text command running in a transaction does
not generate errors but PsqlTransaction.Commit() does not write the
data to the database. The second command is executed independently of a
transaction and throws a PsqlException with the message "Error
retrieving error information". Stack trace:
at Pervasive.Data.SqlClient.Lna.LnaObject.CheckError( LnaResponse
res)
at Pervasive.Data.SqlClient.Lna.LnaStatement.Execute( ParamInfoSet
pis)
at Pervasive.Data.SqlClient.PsqlCommandImpl.execute(B oolean
parseOnly, CommandBehavior behavior, Boolean needReader)
at Pervasive.Data.SqlClient.PsqlCommandImpl.doExecute NonQuery()
at DDInt.Common.CommonCommandImpl.ExecuteNonQuery()
at Pervasive.Data.SqlClient.PsqlCommand.ExecuteNonQue ry()
at KeywordCleaner.MainForm.btnFind_Click(Object sender, EventArgs e)

Here are the Psql trace entries:

First time succeeds:
--------------------
00000001 15:53:41.116 T1 I00032 Command::ExecuteNonQuery ENTER

CommandText: {call KeywordCleanup_MakeList(?)}
CommandType: StoredProcedure
CommandTimeout: 30
Par #1: , Input, Default, Default, System.String,
"{E5C4D9DD-B79E-40CB-A014-021172B7A84C}"

00000001 15:53:41.304 T1 I00032 Command::ExecuteNonQuery EXIT
Return: 1

Second time fails:
------------------
00000002 15:53:42.507 T1 I00032 Command::ExecuteNonQuery ENTER

CommandText: {call KeywordCleanup_MakeList(?)}
CommandType: StoredProcedure
CommandTimeout: 30
Par #1: , Input, Default, Default, System.String,
"{1108005C-0F8B-4787-B227-AA7ABA81CCB9}"

Error or Warning Received from Pervasive Server
Number = 1
Message = Error retrieving error information
Error Position = 0

After handling the exception, any further execution of pre-existing
commands related to the same connection cause an
NullReferenceException.

I solved the problems by creating and disposing a new command object
each time I need to run it, thus making it impossible to use the
designer.

I'd suggest that you report these problems to Pervasive otherwise they will
likely never get fixed. If you have a support contract, call them and get
the ball rolling. If not, you can submit bugs for free at
http://www.pervasive.com/support/submit_defect.asp and they will get looked
into. If you have a test case that you can send that will make it a lot
more likely that your bug will be looked at.

I have found numerous bugs in Pervasive.SQL over the years and Pervasive is
good about fixing them when I report them.

Regards,
- Greg

"Fardreamer" <doron.assayas (AT) gmail (DOT) com> wrote

I forgot to mention that everything I post here is first submitted as a
defect to Pervasive. So far, the response to my first submission was
"insufficient data". Very disappointing. The following error, however,
is much more serious and has stopped development dead in it's tracks.
Let's hope they respond.

After upgrading to 8.7 last night, the following behavior appeared when
using the Psql .NET provider (both with the first release and with the
March FTF to which I upgraded hoping it would solve the problem):

Stored procedures that return a result set generate the exception
"Invalid cursor position; no keyset defined" when the SELECT statement
in the SP has an ORDER BY clause with a non-indexed column. In my tests
adding an index with the column as the first segment (and first segment
ONLY) solved the problem, however in my actual stored procedures (that
ORDER BY expressions rather than fixed columns) I could not solve the
problem.

Get test files here
http://www.phototakeusa.com/Pervasiv...on_OrderBy.zip

As you can see in the test files, the error is generated after the
SELECT statement has successfully terminated - the PRINT message is
generated regardless of whether the exception is raised or not.

Please note that this behavior occurs only with the .NET provider.
Calling the stored procedure through the Sql Data Manager does not
generate an error.

In addition please note that this behavior was not present in 8.6 and
appeared when I ran the first query after updating to 8.7.

I have included SQL scripts to recreate my test, ASP.NET code to run
the proc using the provider, the stack trace generated when the
exception is thrown, and a trace file generated with PsqlTrace.

Please help me resolve this issue as it is completely blocking
development progress.

Here's another bug, but I'm not going to submit this one just yet
because I don't have time to test different scenarios for them.

Using PsqlDataAdapter in conjunction with stored procedures for every
command (InsertCommand, UpdateCommand, etc.), I encountered the
following error: when a column in a DataTable is mapped to a
LongVarChar parameter in the InsertCommand, two bugs occur:

1) When NULL (DBNull.Value) is put in the field, an
InvalidCastException without any additional information is thrown in
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) when PsqlDataAdapter.Update is called.
This does not happen when the field is set to a string value instead of
DBNull. The .NET provider reference states that LongVarChar is mapped
to byte[], but when I tried using AsciiEncoding,GetBytes to convert a
string to a byte array, I got the same InvalidCastException when
calling Update.

2) When an empty string (String.Empty, "") is set to the LongVarChar
field, the adapter does not throw an exception, but the Pervasive
services on the server hangand stop responding. I have to manually shut
down and restart both the transactional and relational services.

I solved this issue by converting any DBNulls in a LongVarChar field to
a single space string (" ") and having the stored procedure insert NULL
when a single space is received.

As with all bugs I submit here, these do not occur when using the
control center's query window to directly call the stored procedures.