Source language: Translate to:

DBP and MySQL - date format

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

DBP and MySQL - date format

Postby dpayer » Mon Aug 24, 2015 12:11 pm

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
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBP and MySQL - date format

Postby Gaev » Mon Aug 24, 2015 3:33 pm

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]"
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: DBP and MySQL - date format

Postby dpayer » Wed Aug 26, 2015 10:39 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBP and MySQL - date format

Postby stu » Wed Aug 26, 2015 11:56 am

dpayer wrote: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. =)
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: DBP and MySQL - date format

Postby dpayer » Fri Aug 28, 2015 8:20 am

stu wrote: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!
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBP and MySQL - date format

Postby Gaev » Fri Aug 28, 2015 9:28 am

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';" ""
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: DBP and MySQL - date format

Postby dpayer » Mon Aug 31, 2015 9:38 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBP and MySQL - date format

Postby stu » Mon Aug 31, 2015 9:43 am

dpayer wrote: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?
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: DBP and MySQL - date format

Postby dpayer » Mon Aug 31, 2015 9:48 am

stu wrote:[
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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: DBP and MySQL - date format

Postby Neosoft Support » Mon Aug 31, 2015 11:12 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 0 guests

cron