1

I am trying to read some data from a Pervasive database (through a C# Console app I wrote) and whenever I reach a row that contains information on a particular date column, the program throws an exception.

I managed to identify the problematic records:

DtTransDate        DtSystemTime           DtnLotteryDrawDate
7/15/2013          3:01:32 AM             9/1/8226

As you can see, the DtnLotteryDrawDate is a weird value but I should be able to read it as it is a valid date. I am using the ADO.NET Provider for Pervasive (which by the way, indicates that there's a bug on the Pervasive driver itself):

at Pervasive.Data.SqlClient.Lna.e.ak(u )
at Pervasive.Data.SqlClient.Lna.w.a(Int16 , Int32 , ad , f )
at Pervasive.Data.SqlClient.Lna.e.aa(Int32 , Boolean )
at Pervasive.Data.SqlClient.PsqlDataReader.e()
at Pervasive.Data.SqlClient.PsqlDataReader.Read()

So I tried the ODBC Driver and got the same problem but a tiny better message: 

at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at System.DateTime..ctor(Int32 year, Int32 month, Int32 day, Int32 hour, Int32 minute, Int32 second, Int32 millisecond)
   at Pervasive.Data.SqlClient.Lna.u.r()
   at Pervasive.Data.SqlClient.Lna.aq.a(u )
   at Pervasive.Data.SqlClient.Lna.e.ak(u )
   at Pervasive.Data.SqlClient.Lna.w.a(Int16 , Int32 , ad , f )
   at Pervasive.Data.SqlClient.Lna.e.aa(Int32 , Boolean )
   at Pervasive.Data.SqlClient.PsqlDataReader.e()
   at Pervasive.Data.SqlClient.PsqlDataReader.Read()

So I tried converting the Date to a string to see if that would get rid of the issue but that didn't work. Basically, I wrote the following SQL statement:

 SELECT DtTransDate ,
        TmSystemTime ,
        CONVERT(DtnLotteryDrawDate, SQL_CHAR) as DtnLotteryDrawDate
        FROM    TICKHISH
        WHERE   ( DtTransDate >= { d '2013-07-15' }

The above SQL Statement would throw the following exception:

error in row and the stack trace I pasted above.

If I DO NOT try to convert the field to string, then the Exception is more helpful:

Year, Month, and Day parameters describe an un-representable DateTime.

So essentially, the ADO.NET driver is trying to construct a Date passing in incorrect values for either the month or the day. Probably is passing the 8226 as either the day or the month field.

I tried running the same SQL statement on the Pervasive Control Panel (PCC) and interestingly, the PCC program doesn't crash if I do not attempt to convert to string the date field but if I try any kind of conversion or calling a function like year(DtnLotteryDrawDate) immediately crashes as well.

Any ideas on how to work around this issue?

TINY UPDATE

I was able to set up a Linked Server using SSMS and connect to the Pervasive database through the linked server. When I attempt to run this simple query:

SELECT  * FROM OPENQUERY(linked_server, 'SELECT * FROM MyTable where DtTransDate  = ''2013-07-15'' ')

I get this error:

OLE DB provider "MSDASQL" for linked server "linked_server" returned message "[Pervasive][ODBC Client Interface]Data truncated column 186.".
OLE DB provider "MSDASQL" for linked server "linked_server" returned message "[Pervasive][ODBC Client Interface]Invalid date, time or timestamp value.".
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "linked_server".
Icarus
  • 63,293
  • 14
  • 100
  • 115
  • What's the exact exception? What version of PSQL? What version of the ADO.NET provider are you using? – mirtheil Sep 14 '13 at 22:01
  • @mirtheil I tried the ADO.NET 4, the 3.5 and 3.2 versions. This is Pervasive v11. The exception message is: `Year, Month, and Day parameters describe an un-representable DateTime.` if I don't try to convert the field to string. If I try `CONVERT(DtnLotteryDrawDate , SQL_CHAR) as DtnLotteryDrawDate` then I get a wonderful: "error in row" :-/ – Icarus Sep 14 '13 at 22:08
  • What happens if you open the file in the Pervasive Function Executor and look at the row and data for this field? You'll be looking at the actual hex data in the record since Btrieve doesn't store field information. Are you using v11.30 Update 7? Try changing the 'TimeType' connection string option to DateTime as documented at http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=adonet/objintrf.08.3.html. – mirtheil Sep 14 '13 at 22:20
  • I changed the connection string as you suggested, both using `TimeType=DateTime;` and `TimeType=TimeSpan;` No luck. I am sorry for the silly question but I don't know where to look for the Pervasive Function Executor. I am doing a consulting job to migrate some of this data to MS SQL Server and I hit this issue on 2 of the tables. Doing it through SSIS also has the same problem. – Icarus Sep 14 '13 at 22:32
  • Apparently the server is V11.2. I can't update that. It's out of my control. In fact, not even my client, I think, can update it. This is part of a product called CRAFTSMAN. – Icarus Sep 14 '13 at 22:34
  • Function Executor is a client tool. It's filename is WBEXEC32.EXE. What data type is DtnLotteryDrawDate as defined in the PCC? You might try CAST rather than CONVERT. I don't know if it'll help but it's worth a shot at this point. – mirtheil Sep 15 '13 at 02:52
  • @mirtheil `"DtnLotteryDrawDate" DATE`. I tried casting as binary to see if I could retrieve the actual binary value stored in the field and make sense out of it but it craps out as well. I also tried casting to other data types and got the same error. Will look into the Function Executor tool and let you know. Thanks for all your help so far! I appreciate it. – Icarus Sep 15 '13 at 14:02

1 Answers1

1

There seems to be something wrong with the actual data. I was able to insert, through the PCC, and view through both PCC and the ADO.NET provider a data value of 9/1/8226. If I changed the bytes of the date value through Function Executor to make the date appear invalid, I was able to get an "error in row" type message. There used to be a couple of programs that could fix invalid dates but I think one was pulled. The other is FixDates from Goldstar Software, but it is not free.

mirtheil
  • 8,952
  • 1
  • 30
  • 29