Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Error when accessing a date field with 0 value in Pervasive SQL

    Good Day!

    This is my first time to join this forum. I'm looking forward to interact with all the IT brainies out there.

    I'm currently running a Visual Basic program wherein i need to access a Pervasive SQL database. But an error occurs everytime i hit a date field with a value of 0. I am not that familiar with Pervasive SQL. 0 value in date fields is supposed to be NULL. How can i store the date field 0 value in a variable inside my visual basic codes?

    Thanks in advance for any help.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    How are you accessing the database? If you're using ODBC, how were your DDFs created? I've seen some cases where DDFs don't define "NULL" properly and a 00/00/0000 date is returned and treated as Invalid (since it is invalid in terms of ODBC).
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    I'm accessing Pervasive SQL through ODBC connection.

    My objective is to import the data from Pervasive SQL to MS SQL.

    I have tried different options to achieve my objective:
    1.) Import data feature of MS SQL
    2.) Local Packages feature of MS SQL
    3.) Visual Basic

    No. 1 & 2 options has an error in importing because 0 value in datefields of MS SQL is not accepted.

    In Visual Basic, it can't even read (no importing done yet) the data with 0 value.
    The error is:
    "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

    The table design for the date field is nullable.
    If i can only modify the PSQL table, i would just put null on these 0 value.
    I only have control in our MS SQL tables.

    I noticed that in PErvasive SQL, 0 is not really accepted for the date fields. How come there are 0 values in the table? (I know i need to ask the owner of these table but they are not very cooperative )

    Thanks for taking the time to anwer my questions.

    God Bless.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    First, if this is data created with a third party application, you need to call them. They may already have updated DDFs that resolve this.
    Second, as I stated, I have seen this behavior where the "Null" behavior isn't set properly within the DDFs. Btrieve/Pervasive has two very different definitions of nulls. There are Legacy Nulls and True Nulls. Your app is probably using "Legacy" nulls but the DDFs are probably defined using "True" nulls.
    From an ODBC perspective, 0 isn't allowed in a date. From a Btrieve perspective, anythings accepted because Btrieve doesn't store field metadate and doesn't know what the application is putting in.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Oct 2005
    Posts
    119
    Since, I cant modify the DDF. My last option would be to import the data from PSQL into a temporary table in MSQL wherein the data type of the "DueDate" is set to character. This is just to avoid the error in importing 0 value in date fields. Then from the temporary table, Ill just convert the "DueDate" value into its real date equivalent before transporting the data into the actual transaction table.

    Hows that? Do you think this is a good option? Now, I have another question. What is the correct syntax (in MS SQL) when I want to use IF or IIF condition inside my MSQL UPDATE statement?

    Example: UPDATE Table
    SET DueDate_Date=
    IIF(DueDate_Char=0,NULL,CAST (DueDate_Char AS DATETIME))

    Thank you so much.
    God Bless.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •