Page 1 of 1
Using h2 date/time functions in AS extensions
Posted: 27 Mar 2009, 20:49
by mariana
I'm trying to use the H2 database, and when I try to add a record that contains dates or timestamps to a table I get errors. This is the code I'm using:
Code: Select all
sql = "INSERT INTO SESIONES (NAME,FECHA, STARTTIME,STOPTIME,USOLINK,VIVA) VALUES ("
sql += "'" + _server.escapeQuotes(nombre) + "', "
sql += "'" + _server.escapeQuotes(current_date()) + "', "
sql += "'" + _server.escapeQuotes(current_timestamp()) + "', "
sql += "'" + _server.escapeQuotes(null) + "', "
sql += "'" + _server.escapeQuotes(null) + "', "
sql += "'" + _server.escapeQuotes(ver) + "')"
var success = dbase.executeCommand(sql).... etc, etc
and I get errors in the extension, such as:
error in extension [xxx.as]:ReferenceError "current_timestamp()" is not defined
Can somebody please post a couple of examples of how to build sql statements involving functions, especially date/time functions?
Thanks!
Mariana
Posted: 31 Mar 2009, 05:26
by Lapo
what is current_timestamp()
is it a function in your code?
Posted: 31 Mar 2009, 05:34
by mariana
It is the syntax of the H2 database for the current timestamp, see
http://www.h2database.com/html/function ... ttimestamp
Mariana
Posted: 31 Mar 2009, 05:59
by Lapo
Not that way. In that way it is a function call in the Actionscript code, hence the error.
Posted: 31 Mar 2009, 06:07
by mariana
Lapo:
can you PLEASE send me an example extension code with an INSERT or an UPDATE statement using any one of the date/time functions?
none of the examples in the documentation (sections 8.4 , 8.19) show how to use these functions, and I am stuck for several days trying to use the H2 database with sfs because of these functions.
Thank you,
Mariana
Posted: 31 Mar 2009, 11:04
by mariana
Lapo:
I changed all the date fields to varchar, just to be able to continue working until you explain how to use the date functions in h2.
but now I have an error in the extension that I cannot find. This is the code:
Code: Select all
function iniciarSesion(nombre){
trace("iniciar sesion")
var ver = 1
sql = "SELECT * FROM SESIONES WHERE NAME ='"+_server.escapeQuotes(nombre)+"'"
sql += " AND VIVA='" +_server.escapeQuotes(ver)+ "'"
trace ("SQL+ "+sql)
queryRes = dbase.executeQuery(sql)
if(queryRes != null && queryRes.size()>0) {
trace("size>0")
var indice = (queryRes.size() - 1)
var tempRow = queryRes.get(indice);
var fecha = tempRow.getItem("FECHA");
var hora = tempRow.getItem("STARTTIME");
trace("indice "+ indice + " fecha "+fecha);
ya = ("you are still logged in since "+hora);
}
else
{
trace("208 ===================");
//===================================
var fechaStr:String = "29-04-2009";
var tStamp:String = "1234567890";
trace("211 Not size >0 " + fechaStr +" "+tStamp);
sql = "INSERT INTO SESIONES (NAME,FECHA STARTTIME,STOPTIME,USOLINK,VIVA) VALUES ("
sql += "'" + _server.escapeQuotes(nombre) + "', "
sql += "'" + _server.escapeQuotes(fechaStr) + "', "
sql += "'" + _server.escapeQuotes(tStamp) + "', "
sql += "'" + _server.escapeQuotes(null) + "', "
sql += "'" + _server.escapeQuotes(null) + "', "
sql += "'" + _server.escapeQuotes(ver) + "')"
var success = dbase.executeCommand(sql);
if (success)
{
trace("224 success Sesion"+ nombre)
} else
{
trace("227 failure Sesion"+ nombre)
error = "database error Sesion"
}//===============================
}
}
when I save the extension, the server says
Error in extension [ mandalogin.as ]: missing ; before statement (mandalogin.as#1693) Internal:210 -- Line number: 209 in file mandalogin.as
and does not start the extension.
when I comment out the part between the two //=====================
the error message disappears and the extension starts, but of course, I cannot create the "SESIONES" record
Can you see what ; I am missing?
Thank you
missing ; before statement [Fix]
Posted: 05 May 2009, 21:37
by Drew
when I save the extension, the server says
Error in extension [ mandalogin.as ]: missing ; before statement (mandalogin.as#1693) Internal:210 -- Line number: 209 in file mandalogin.as
and does not start the extension.
when I comment out the part between the two //=====================
the error message disappears and the extension starts, but of course, I cannot create the "SESIONES" record
Can you see what ; I am missing?
Thank you
I just had this same problem. By comparing your code to my own code, I was able to spot one similarity. We are not missing any ; in our code.
The two variables, fechaStr and tStamp have :String defining them as String type variables. It is causing that error. The server does not like variable types, so no :Object or :Number, etc. Use var name = value instead.
Code: Select all
//===================================
var fechaStr = "29-04-2009";
var tStamp = "1234567890";
Sorry I can't help with anything else. I am just learning.
~Drew
Posted: 06 May 2009, 04:58
by mariana
Hey, thanks, Drew, what happens is that server side extensions are AS1 or AS2, and the "var xxxx:dataType" declaration is AS3...
I had already found that out, and in order to continue working, all the dates in my extensions are now strings. But it is a SHAME that we cannot directly save date/time functions and make full use of their functionality, the same is true for booleans, and I haven't tested many others.
I am pretty certain that is the H2 and SFS people get their heads together and produce a couple of examples, it would be a big help to all of us dumb users out there.....I am quite angry at SFS for this.
Mariana
Posted: 06 May 2009, 09:20
by BigFIsh
Hey Mariana,
I'm sure there's a solution to your problem.
First of all, any h2 functions are executed from h2 side, thus current_timestamp() should be a string. (same for mysql)
i.e.
sql += "'" + _server.escapeQuotes(current_timestamp()) + "', "
should be:
sql += "current_timestamp(), "
or you can use actionscript's date function to get the "now", and convert that to string.
var date = new Date(); (this creates a date object for current's UTM date based on your computer's time (and your GTM). You'll need to google search online for associated date/time from Date().
Hope this answers your question, if not - please explain further.
Posted: 06 May 2009, 09:34
by mariana
BigFish:
current_timestamp() is what I started out with, please see my post of Fri Mar 27, 2009 8:49 pm (the first in this thread), but I got an error.... so I did what you suggest, which is turning every date to a string prior to storing it in the db.
But I'd much like to be able to avoid this step, as well as having to turn back the strings to dates when I read them. I do have a number of dates in my db....
anyway, thanks for answering and, again, I'd LOVE to see a couple of examples of use of date/time in the docs (say, in sec 8.4?).
Mariana