Error dtoc() with empty date-field in MySQL

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Error dtoc() with empty date-field in MySQL
Posted: Mon Oct 29, 2012 09:51 AM

Hi,

I'm using MySQL with ADO.
I have a stange error while browsing a data-fields.
I'm using

dtoc(oRs1:Fields( 'datum' ):Value)

for browsing a date if in MySQL.
If I add an empty record to my recordset I get an error

Error description: Error BASE/1118 Argument error: DTOC
Args:
[ 1] = U

I'm not able to browse the recordset anymore until I fill a valid date.
How can I solve this? I can first fill the data with a dummy-date, but I don't find that a nice solution, because sometimes the date can be empty...
I can do a

Code (fw): Select all Collapse

IIF(!empty(oRs1:Fields( 'datum' ):Value),dtoc(oRs1:Fields( 'datum' ):Value),'  /  /  ')

in my browse, but why is the field not a date field anymore when it's empty? Browsing an empty DBF-date field don't have this error.

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Error dtoc() with empty date-field in MySQL
Posted: Mon Oct 29, 2012 12:02 PM

That's how SQL databases work. Any fields can contain a value of its data type plus NULL (NIL). You can change the field default value, if I remember correctly.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Error dtoc() with empty date-field in MySQL
Posted: Mon Oct 29, 2012 12:43 PM

Mark

With xBrowse you can use this code to mask your NIL value for an empty date .. change aCols[x] and your recordset field to fit your situation.

Hope this helps
Rick Lipkin

Code (fw): Select all Collapse

oCol := oLbx:aCols[ 5 ]
oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Error dtoc() with empty date-field in MySQL
Posted: Mon Oct 29, 2012 12:51 PM

Rick Lipkin wrote:Mark

With xBrowse you can use this code to mask your NIL value for an empty date .. change aCols[x] and your recordset field to fit your situation.

Hope this helps
Rick Lipkin
Code (fw): Select all Collapse
oCol := oLbx:aCols[ 5 ]
oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }

Rick,

Thanks for the reply.
That's what I'm using now (see my original post). I was just wondering why the type of that field is a data-type if it's empty. I can also change the default value (like Enrico suggest), but than I don't know the different that it's empty or not, because there is allways a date...

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Error dtoc() with empty date-field in MySQL
Posted: Mon Oct 29, 2012 01:06 PM

Mark

SQL uses NIL as an valid datatype like we would think of a .dbf field = " ". If you do not assign a value to a SQL field it is NIL and not just " ".

When you test for any value from SQL you should always test for Empty()... just in case, or you will wind up with an unexpected run-time failure... especially if you have inherited a database from another application\developer.

Rick Lipkin

ps .. note, some SQL databases ( ms sql server ) do not allow you to write ctod("") back to a date\time field and those that do, write NIL back when you do this :

oRs:Fields("Date"):Value := ctod("") .. actually writes NIL to the sql field

FiveTech Support Forums

Continue the discussion