PDA

View Full Version : Sql date to Alpha and back to SQL


ABC123

Pat Bremkamp
10-09-2009, 09:19 PM
I'm having a problem with date formats.

In V10 I created a grid to do what I used to do in a dialog in V9. I have a grid where I enter some information, a date and a quantity. I save that into a dummy table, get the record Id into a session variable and then go to an Xbasic only page where I create the quantity number of records. The grid saves the date in MySQL properly as 2009-10-09000:00:00

I'm using a MySQL backend, so I find my record using a select statement, and read the results into variables:



dim vTemp as c = ""
dim McSelect as c = ""
dim CtInsert as c = ""
dim mcon as SQL::connection
dim rset as SQL::ResultSet
vTemp=<<%a%
SELECT ..., mct_created, mct_quantity,... FROM multicerts
WHERE mct_id = {session.mct_id}
%a%
McSelect=evaluate_string(vTemp)
if mcon.open("::name::egw")
if mcon.Execute(McSelect)
rset=mcon.ResultSet
...
xdat=rset.data("mct_created")
xqty=rset.data("mct_quantity")
...


Then, I insert the records in a loop into the next table:


vTemp=<<%b%
INSERT INTO certificates ( ...ct_created,ct_quantity,... )
VALUES ( ...{xdat},{xqty},...
%b%
CtInsert=evaluate_string(vTemp)
dim j as n = 0
dim i as n
for i = 1 to xqty
if mcon.Execute(CtInsert)
sleep(.5)
next


The insert, however, is trying to insert the date in Alpha format as "10-09-2009 00:00:00 am" which, of course, causes an error.

I think this "helpful" format change is coming out of the evaluate_string() function.

Any thoughts on how I can get around this problem?

Pat

fsi
10-09-2009, 10:05 PM
I suspect I'm missing something, that my response will somehow miss the point as a result of being daft this late in the week.

But, in inserting date/time into my MySQL db, I use:

mid(sql_date(now()),2,19)

= "2009-10-09 21:03:38"

If you can't use now() - needing to retain the earlier values exactly - can you determine the difference 'X' and do sql_date(now()-X,2,19)?

Pat Bremkamp
10-10-2009, 01:06 PM
Thanks to everyone who made a suggestion, both here on the board and through a PM. I tried all the suggestions, and in the end, came up with this, which works:

First, I reformatted the date with cdate()



dim vTemp as c = ""
dim McSelect as c = ""
dim CtInsert as c = ""
dim mcon as SQL::connection
dim rset as SQL::ResultSet
vTemp=<<%a%
SELECT ..., mct_created, mct_quantity,... FROM multicerts
WHERE mct_id = {session.mct_id}
%a%
McSelect=evaluate_string(vTemp)
if mcon.open("::name::egw")
if mcon.Execute(McSelect)
rset=mcon.ResultSet
...

xqty=rset.data("mct_quantity")

...
xdat=rset.data("mct_created")
cdat=cdate(xdat)



Then, I moved the mct_created field to the end of the insert statement, so it was the last item on the field list, took it out of the values list and added it back to the insert statement outside the evaluate_string() function:



vTemp=<<%b%
INSERT INTO certificates ( ...ct_created,ct_quantity,... )
VALUES ( ...{xqty},...
%b%
CtInsert=evaluate_string(vTemp)+cdat+")"
dim j as n = 0
dim i as n
for i = 1 to xqty
if mcon.Execute(CtInsert)
sleep(.5)
next

ChristJC
10-14-2009, 03:46 AM
Hi Pat

I'm trying to create an SQL datetime argument to act as a filter within a web report. If I type the date into the SQL statement, the query works fine. But if I create an argument (either date or time) and then enter a date when prompted by the query, it says the variable is not a valid date or time variable. Interestingly, when I create a char argument for the date field, the query actually works, but Alpha throws an SQL error when you try and close the datasource panel.

To cut a long story short, have you had any luck with MySQL date based arguments in the past? I have read other threads, and some mention that this is a bug and a patch was supposedly in the pipeline post the current version (Build 2095_3264)

Cheers

Jason