1

I'm working with a client who is using a Pervasive SQL database, and trying to understand how their database is structured. The problem is that my client isn't aware of how the database was setup, and had been relying separate vendor who created the database. Unfortunately, I'm unable to talk to that vendor - so, I have to try and figure things out "on my own".

My first step is to get a count of all entries (from all tables) that were made within the last 10 years. I do see multiple tables with a date column. However, the column type for those seem to be CHAR(6), and the value is something like ;30304, ;31216, ;50425, ...

I've never seen date stored like that, and I'm not sure what to make of it. I remember reading from somewhere that Pervasive stores dates as number of days since 01/01/0001. If that were to be the case, then ;30304 would imply December 21, 0083. So, that doesn't seem right.

I would greatly appreciate any help/advice here. Also, I've noticed certain date values are stored starting with ":" instead of ";" - what does this mean? I can't seem to find this online.

Cheeku Jee
  • 159
  • 2
  • 9
  • 1
    Thanks Ken, I've updated the title of this post. – Cheeku Jee Aug 16 '18 at 02:58
  • There are several date types in Pervasive. The "Date" is stored as a 6 byte type with the year as an integer, month and day as bytes. What is the hex value of this field rather than the string value? Do the DDFs for the table show the Date as a char(6) or does it show something else? – mirtheil Aug 16 '18 at 14:10
  • Hi Mirtheil, thanks for replying! Yes, the DDF shows the Date as char(6). How can I get the hex value of this date column? I apologize is this is a basic question - I'm unable to find any documentation around it. If it helps, I'm using Pervasive Control Center v11. – Cheeku Jee Aug 17 '18 at 00:53
  • You would something like the Function Executor to open the file, read the records and look at the bytes in the table. You'd need to know the position of the Date filed. That being said, if it is defined as CHAR(6), you might have a custom definition for date fields. What values show up in the file if you put yesterday and today and 01/01/0001? – mirtheil Aug 17 '18 at 10:23
  • I opened the Function Executor, and see the following for the first record [https://ibb.co/hpiC7e](https://ibb.co/hpiC7e). Sorry, I'm really clueless as to what to make of this. I'm not sure how to properly insert dates - PCC is expecting me to input 6 CHAR for date. I would like to be able to export this into a flat file that can be imported into a MySQL database - but, when I export this file, the date is still exported as ";30206". – Cheeku Jee Aug 17 '18 at 12:47
  • Do you know what application generated this data? Was it a Magic application? What values show up in the file if you put yesterday and today and 01/01/0001? – mirtheil Aug 17 '18 at 12:51
  • Unfortunately, I don't. I've talked to my client, and they have no idea about it - they have been relying on their vendor to completely manage the database. I tried inserting '01/01/0001' by `INSERT INTO "table" ("date") VALUES('01/01/0001')` - however, it simply shows the date record as "01/01/" – Cheeku Jee Aug 17 '18 at 16:31
  • I wouldn't expect that INSERT to work. The Date is encode but not in a standard Pervasive way. You'll need to find out, from the application vendor, how it is encoded. – mirtheil Aug 17 '18 at 16:40
  • Thank you Mirtheil! My client mentioned that the vendor has been using "Btrieve and not SQL for their queries". Unfortunately, that vendor will charge my client for any questions - so, I want to check with you to see if this helps? From what I can understand about Btrieve / Pervasive, it seems to me that they are both the same product - is my understanding correct? – Cheeku Jee Aug 17 '18 at 18:30
  • @mirtheil: I managed to get a meeting setup with the vendor, and will be able to figure out the encoding for this. Thank you so much for your effort - I appreciate all your help with this! – Cheeku Jee Aug 19 '18 at 23:51

0 Answers0