DBP and MySQL - date format

[dpayer]

I see that this issue has come up a few times in various ways for people.

I found out that although MySQL stores dates in the YYYY-MM-DD format, you cannot give a record with the date in that format to the database using NB DBP. The app will generate an error saying 'your entry' is not a valid date.

If you add the date using M/D/YYYY format, the database will accept it and store it in the YYYY-MM-DD format. My test uses the MySQL ODBC connection downloaded from Oracle.

Defining the variable as a date in Neobook does not solve the issue. It appears that when you attempt to input ANY number into an entry field once the variable is defined as a date, you get an error after the first character example: "1" is not a valid date value

Copy the code below into a new 320x200 publication to test this error.

Code: Select all

{NeoBook 5 Objects}
NeoBookVer=5.80
ObjectType=9
Name=TextEntry1
X=53
Y=36
W=232
H=38
Anchor=0
VarName=[TextEntry1]
Align=1
EditLen=0
LineColor=0
LineWidth=1
LineStyle=0
FillColor=16777215
FillPattern=0
Font=Arial
FontSize=9
FontStyle=0
FontCharset=1
TextColor=0
TabOrder=2
ObjectType=3
Name=PushButton2
X=288
Y=42
W=27
H=28
Anchor=0
Text=X
Align=2
ImageStyle=0
XPTheme=Yes
ObjAction=setvar "[textentry1]" ""
LineColor=0
LineWidth=1
LineStyle=0
FillColor=16777215
FillPattern=0
Font=Arial
FontSize=9
FontStyle=0
FontCharset=1
TextColor=0
TabOrder=4
ObjectType=8
Name=Text1
X=10
Y=92
W=308
H=74
Anchor=0
Font=Arial
FontSize=9
FontStyle=0
FontCharset=1
TextColor=0
Text={\rtf1\ansi\deff0\deftab254{\fonttbl{\f0\fnil\fcharset1 Arial;}}{\pard{\ql\li0\fi0\ri0\sb0\sl\sa0 \plain\f0\fs18\cf0 1. type in a date in the field above in any formt. \par\ql\li0\fi0\ri0\sb0\sl\sa0 \plain\f0\fs18\cf0 2. Click X to remove entry\par\ql\li0\fi0\ri0\sb0\sl\sa0 \plain\f0\fs18\cf0 3. \plain\f0\fs18\cf0\uldb click here \plain\f0\fs18\cf0\v DefineVar "[TextEntry1]" "Date" "" "Global" ""\plain\f0\fs18\cf0 to define the variable used in the text entry\par\ql\li0\fi0\ri0\sb0\sl\sa0 \plain\f0\fs18\cf0 4. type any key into the field.}}}
HMargin=3
VMargin=2
LineColor=0
LineWidth=1
LineStyle=0
FillColor=16777215
FillPattern=0
TabOrder=5


[Gaev]

David Payer:

I found out that although MySQL stores dates in the YYYY-MM-DD format, you cannot give a record with the date in that format to the database using NB DBP. The app will generate an error saying 'your entry' is not a valid date.

According to this page https://dev.mysql.com/doc/refman/5.1/en ... erals.html ... mySQL will accept date values ...

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

I don't have access to a mySQL database ... but could it be that NeoBook performed math (division) on the value before passing it to your database ?

This code ...

Code: Select all

SetVar "[dpDate1]" "2005-09-23"
SetVar "[dpDate2]" "!2005-09-23"
AlertBox "dpDate" "[dpDate1] and [dpDate2]"

... displays 1973 and 2005-09-23

Defining the variable as a date in Neobook does not solve the issue. It appears that when you attempt to input ANY number into an entry field once the variable is defined as a date, you get an error after the first character example: "1" is not a valid date value

That is most likely because you have defined the variable associated with the TextEntry object ... NeoBook is probably trying to create a date variable upon each change in value ... try using a non-defined [TextEntry1] ... and when required ...

Code: Select all

DefineVar "[myDate]" "Date" "m/d/yyyy" "Global" "1/1/1980"
SetVar "[myDateVariable]" "[TextEntry1]"

[dpayer]

True Gaev, MySQL accepts dates in that format. My issue is that I think the plugin or the ODBC connector may be the issue. I imported a database with the numbers (in text format yyyy-mm-dd) into a DATE field in MySQL with no problem.

RE: using a text object with a defined variable. My thought was that the possible answer is that dates are actually a binary firnat and nothing we enter by keyboard will fit but something we have as text in a variable could be converted to the appropriate format.

I wanted to make sure that was the case. Lets see if D@NB comments.

[stu]

True Gaev, MySQL accepts dates in that format. My issue is that I think the plugin or the ODBC connector may be the issue. I imported a database with the numbers (in text format yyyy-mm-dd) into a DATE field in MySQL with no problem.

RE: using a text object with a defined variable. My thought was that the possible answer is that dates are actually a binary firnat and nothing we enter by keyboard will fit but something we have as text in a variable could be converted to the appropriate format.

I wanted to make sure that was the case. Lets see if D@NB comments.

I also had similar issues working with dates but its in the past now since I discovered the best way (from nbdbpro) is to work with the default generated variables. eg: [MyDBConn.tblSomething.registrar_dob] instead of pushing the values when saving. Converting dates its no fun so now I just workaround using these variables when inserting data in which case I go about parsing the forward slash and setting [a_date] to ![3]-[2]-[1] then it goes in like [#39][a_date][#39] on an INSERT statement.

I tried the same on MySQL and I really had no problem working with dates at all...

Of course there's always the possibility I didnt understood what dpayer is trying to accomplish in which case I apologize. =)

[dpayer]

Of course there's always the possibility I didnt understood what dpayer is trying to accomplish in which case I apologize. =)

No, you got it!

[Gaev]

David Payer:

I found out that although MySQL stores dates in the YYYY-MM-DD format, you cannot give a record with the date in that format to the database using NB DBP. The app will generate an error saying 'your entry' is not a valid date.

1) Can you post the exact NeoBook commands you are attempting to use (when attempting to set/update date fields) ? ... please include values in any variables used in those commands ... e.g. ...

Code: Select all

SetVar "[myDB.myTable.DOB]" "!1915-08-26"
... or ...
SetVar "[thisDOB]" "!1915-08-26"
SetVar "[myDB.myTable.DOB]" "![thisDOB]"


2) Have you tried using something like ...

Code: Select all

dbpExecSQL "myDB" "UPDATE myTable SET DOB = '1915-08-26' WHERE  NAME = 'Methuselah';" ""
... or ...
SetVar "[thisDOB]" "!1915-08-26"
dbpExecSQL "myDB" "UPDATE myTable SET DOB = [thisDOB] WHERE  NAME = 'Methuselah';" ""


[dpayer]

Gaev, here is what works:

Code: Select all

dbpOpenTable "CDMADB" "modems" ""
dbpAddRecord "CDMADB" "modems"
SetVar "[cdmadb.modems.MDN]" "[ThisMDN]"
SetVar "[cdmadb.modems.ESN]" "[ESNEntry]"
SetVar "[cdmadb.modems.DateActive]" "![MonthNum]/[DayNum]/[Year]"
AlertBox "testdbp error" "last data error: [dbplast]"
SetVar "[cdmadb.modems.Location]" "[CDMADB.altesntable.Location]"
dbpSaveEdits "CDMADB" "modems"

MySQL takes the date information and stores it as YYYY-MM-DD in the table

Here are some samples of what doesn't work:

Code: Select all

DefineVar "[thisdayformated]" "String" "" "Global" "[year]-[monthnum]-[daynum]

This gives a "This is not a valid date format" error. Defining as a date in DBP doesn't work with any format. The column *is* defined as a date by MySQL.

Code: Select all

SetVar "[cdmadb.modems.DateActive]" "![[Year]-[MonthNum]-[DayNum]"


This does not generate an error from the plugin but it does not put an entry in the field of the table, instead it inserts a null

So, I have learned an idiosyncracy. My sense is that it is in the manner the plugin deals with dates.

[stu]

Gaev, here is what works:

Code: Select all

dbpOpenTable "CDMADB" "modems" ""
dbpAddRecord "CDMADB" "modems"
SetVar "[cdmadb.modems.MDN]" "[ThisMDN]"
SetVar "[cdmadb.modems.ESN]" "[ESNEntry]"
SetVar "[cdmadb.modems.DateActive]" "![MonthNum]/[DayNum]/[Year]"
AlertBox "testdbp error" "last data error: [dbplast]"
SetVar "[cdmadb.modems.Location]" "[CDMADB.altesntable.Location]"
dbpSaveEdits "CDMADB" "modems"

MySQL takes the date information and stores it as YYYY-MM-DD in the table

Here are some samples of what doesn't work:

Code: Select all

DefineVar "[thisdayformated]" "String" "" "Global" "[year]-[monthnum]-[daynum]

This gives a "This is not a valid date format" error. Defining as a date in DBP doesn't work with any format. The column *is* defined as a date by MySQL.

Code: Select all

SetVar "[cdmadb.modems.DateActive]" "![[Year]-[MonthNum]-[DayNum]"


This does not generate an error from the plugin but it does not put an entry in the field of the table, instead it inserts a null

So, I have learned an idiosyncracy. My sense is that it is in the manner the plugin deals with dates.

Yes, your right in the sense if you try to insert a date (or update) after defining the var to whatever it wont let me... but I dont get it, why would you define it before sending it to the table?

[dpayer]

[
Yes, your right in the sense if you try to insert a date (or update) after defining the var to whatever it wont let me... but I dont get it, why would you define it before sending it to the table?

I was testing to see if the MySQL was expecting the data in some unique date/binary format that defining the var creates. I could see from my example above that when a variable is defined as a date in NB, you cannot use a text entry field to create the data in the variable. Thus, a defined date variable contains non text info.

[Neosoft Support]

You shouldn't need to do anything fancy to set a date in mySQL. For example, this works:

SetVar "[MySQL.table1.aDate]" "!8/31/15"

mySQL will store the date internally as YYYY/MM/DD. However, in NeoBook you should always enter dates in your locale's short date format (as set in the Windows Control Panel).

When using SetVar the exclamation point "!" is required, otherwise NeoBook will interpret the date as a mathematical formula.