Pervasive

Hello,

I am using PSQL 10.30.017.0000 and RealBasic Real Studio 2011r4.3. I talk to PSQL via ODBC that installed itself along with PSQL.

We are working on a Btrieve database that had character screens, and our goal is to create new modern application that uses windows.

In one of the forms we have four date fields (along with many more fields), and those date fields can have values or not. User will update the form and change the dates as he/she sees it fit.

Now, when we send empty strings (ODBC supports only strings) containing no dates, btrieve complains that "0000-00-00" is not correct date,, also we think is suggesting us to use nullable values from realbasic side. Real Basic can't have string nulled.

As we understand it, Btrieve will accept null as a value for empty date and it will make it happy, however how can we set null values from our end if the language does not support???

Right now as a dirty solution we have a big SQL statement that is executed every time, however we have removed date fields. We handle those date fields separately to determine if there is anything in them. If they are empty than I execute separate SQL statement setting that field to null.

This means to update one form, we need to execute 5 sql command... 4 for dates and one with other data. 

Any help on this matter is more then welcome.

 

 

 

 ODBC more than just string data types.  RealBasic's support of ODBC may only support strings but that's not a limitation of the ODBC driver.

0000-00-00 is considered an invalid date in ODBC.  It's not just a PSQL restriction, it's a MS ODBC restriction. 

That being said, what you do will depend on the actual field type in question.  If it's really a Date field, you have a couple of things to do.  First, the format supported by PSQL for date fields is 'yyyy-mm-dd' (including the single quotes.  If you put a valid date in that format in an INSERT or UPDATE statement, it will work from within RealBasic.  If your users leave an empty string for the date field and your table supports NULL for the date field, you can modify the value before executing the statement.  For example, I used the sample from your last post and modified it to display a Date field.  I added the following line of code before creating the UPDATE statement:

  dim sDate as string
  if TextField5.Text  = "" then sDate = "null" else sDate = "'" + TextField5.Text + "'"
  sql = "UPDATE rb1 set f2 = '" + TextField3.Text + "', f3= '" + TextField4.Text + "', f4=" + sDate + " where F1 = '" + TextField2.Text + "'"

It appears you can set a Mask on the TextField as well.  I set it to ####-##-## and it put the right value on reading and let me see the format. 

Hope this helps. 

 

 

 

Another thought -- build a short stored procedure that accepts the fields you want to insert, then parse the date fields and change them to NULL within the SP. If anything, you can at least get back to a single call.

 Guys, thank you very much for fresh ideas :)

 

Great help!!!