Re: [flexcoders] AIR SQL - Datatype issues
On Mon, Apr 7, 2008 at 4:22 PM, Kevin Aebig [EMAIL PROTECTED] wrote: Hey all, I've looked at the documentation about 400 times now and can't seem to find myself an example of using a column of datatype Date within AIR SQL. I know that it's been customized to use Actionscript / JavaScript Date objects, but I can't get it to work properly for the life of me. Any examples or pointers? The database engine used in Air is SQLite. I am not that experienced with SQLite, but I suppose that there should be a DATE and TIME datatypes. I have seen people use Text and Real for storing date values. So, just do a search on the web for sqlite. All the best, Fidel.
RE: [flexcoders] AIR SQL - Datatype issues
Hi Fidel, Actually, the version of SQLite that AIR uses has been customized to implement dates as julian dates and stores them as REAL. My issue arises in that I'm getting the dates originally from a MySQL DateTime column and still want to be able to use that form of formatting to query the local database with date ranges and ordering. It would be nice if there were better examples of using the AIR SQL engine. !k _ From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Fidel Viegas Sent: Monday, April 07, 2008 9:52 AM To: flexcoders@yahoogroups.com Subject: Re: [flexcoders] AIR SQL - Datatype issues On Mon, Apr 7, 2008 at 4:22 PM, Kevin Aebig [EMAIL PROTECTED] mailto:kevin%40keslabs.com com wrote: Hey all, I've looked at the documentation about 400 times now and can't seem to find myself an example of using a column of datatype Date within AIR SQL. I know that it's been customized to use Actionscript / JavaScript Date objects, but I can't get it to work properly for the life of me. Any examples or pointers? The database engine used in Air is SQLite. I am not that experienced with SQLite, but I suppose that there should be a DATE and TIME datatypes. I have seen people use Text and Real for storing date values. So, just do a search on the web for sqlite. All the best, Fidel.. http://geo.yahoo.com/serv?s=97359714/grpId=12286167/grpspId=1705007207/msgI d=109106/stime=1207584342/nc1=4507179/nc2=3848644/nc3=5170406
Re: [flexcoders] AIR SQL - Datatype issues
Hey Kevin,
Heres an example of some sql text on a SQLStatement that I use in an AIR
app I'm building. I use a dateformatter for the actual date output i.e
'2008-04-06' and if I need the time component, I just build up and
replace the end of the text with my vars, its not pretty but it works
fine. The thing to watch out for is that AS regards 12am as 24 rather
than 00. Hope that helps..
Cheers
Vaan
select * from Foo where bar_id = 2 and datetime(date) =
datetime('2008-04-06 00:00:00');
Kevin Aebig wrote:
Hey all,
I’ve looked at the documentation about 400 times now and can’t seem to
find myself an example of using a column of datatype Date within AIR
SQL. I know that it’s been customized to use Actionscript / JavaScript
Date objects, but I can’t get it to work properly for the life of me.
Any examples or pointers?
Cheers,
!k
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives: http://www.mail-archive.com/flexcoders%40yahoogroups.comYahoo!
Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/flexcoders/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group/flexcoders/join
(Yahoo! ID required)
* To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
* To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
RE: [flexcoders] AIR SQL - Datatype issues
Hi Vaan,
Thanks... I just found references to using a specific date function which
I'm going to try out.
convert string to datetime object:
strftime('%Y-%m-%d %H:%M:%S','timeStringShouldGoHere','localtime')
Cheers,
!k
-Original Message-
From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Vaan S Lanko
Sent: Monday, April 07, 2008 10:39 AM
To: flexcoders@yahoogroups.com
Subject: Re: [flexcoders] AIR SQL - Datatype issues
Hey Kevin,
Heres an example of some sql text on a SQLStatement that I use in an AIR
app I'm building. I use a dateformatter for the actual date output i.e
'2008-04-06' and if I need the time component, I just build up and
replace the end of the text with my vars, its not pretty but it works
fine. The thing to watch out for is that AS regards 12am as 24 rather
than 00. Hope that helps..
Cheers
Vaan
select * from Foo where bar_id = 2 and datetime(date) =
datetime('2008-04-06 00:00:00');
Kevin Aebig wrote:
Hey all,
I've looked at the documentation about 400 times now and can't seem to
find myself an example of using a column of datatype Date within AIR
SQL. I know that it's been customized to use Actionscript / JavaScript
Date objects, but I can't get it to work properly for the life of me.
Any examples or pointers?
Cheers,
!k
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives:
http://www.mail-archive.com/flexcoders%40yahoogroups.comYahoo! Groups Links
Re: [flexcoders] AIR SQL - Datatype issues
CREATE TABLE tbl_test ( testID INTEGER PRIMARY KEY AUTOINCREMENT , field1 DATE )
All of the following work:
INSERT INTO tbl_test (field1) VALUES ('now')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07 20:57:44')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07')
INSERT INTO tbl_test (field1) VALUES (CURRENT_TIMESTAMP)
More info on this page:
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html
Look for: Date and time formatting functions and Time formats (about 3/4
down the page).
regards,
Muzak
- Original Message -
From: Kevin Aebig [EMAIL PROTECTED]
To: flexcoders@yahoogroups.com
Sent: Monday, April 07, 2008 5:22 PM
Subject: [flexcoders] AIR SQL - Datatype issues
Hey all,
I've looked at the documentation about 400 times now and can't seem to find
myself an example of using a column of datatype Date within AIR SQL. I know
that it's been customized to use Actionscript / JavaScript Date objects, but
I can't get it to work properly for the life of me. Any examples or
pointers?
Cheers,
!k
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives: http://www.mail-archive.com/flexcoders%40yahoogroups.comYahoo!
Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/flexcoders/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group/flexcoders/join
(Yahoo! ID required)
* To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
* To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
RE: [flexcoders] AIR SQL - Datatype issues
Hey Muzak,
I've actually tried a few of those from references at SQLite websites but to
no avail. I think it has something to do with the way the DB is handling
data typing as I've heard that SQLite will actually allow unique datatypes
to each cell instead of column.
Thanks a lot for the examples... hopefully something clicks.
Cheers,
!k
-Original Message-
From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Muzak
Sent: Monday, April 07, 2008 1:12 PM
To: flexcoders@yahoogroups.com
Subject: Re: [flexcoders] AIR SQL - Datatype issues
CREATE TABLE tbl_test ( testID INTEGER PRIMARY KEY AUTOINCREMENT , field1
DATE )
All of the following work:
INSERT INTO tbl_test (field1) VALUES ('now')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07 20:57:44')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07')
INSERT INTO tbl_test (field1) VALUES (CURRENT_TIMESTAMP)
More info on this page:
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html
Look for: Date and time formatting functions and Time formats (about 3/4
down the page).
regards,
Muzak
- Original Message -
From: Kevin Aebig [EMAIL PROTECTED]
To: flexcoders@yahoogroups.com
Sent: Monday, April 07, 2008 5:22 PM
Subject: [flexcoders] AIR SQL - Datatype issues
Hey all,
I've looked at the documentation about 400 times now and can't seem to
find
myself an example of using a column of datatype Date within AIR SQL. I
know
that it's been customized to use Actionscript / JavaScript Date objects,
but
I can't get it to work properly for the life of me. Any examples or
pointers?
Cheers,
!k
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives:
http://www.mail-archive.com/flexcoders%40yahoogroups.comYahoo! Groups Links
Re: [flexcoders] AIR SQL - Datatype issues
I'm working on an (AIR) SQLite Admin tool, and the examples are what I use (in
the app) so they do work.
The DB's used however are created with the app itself, so maybe that has
something to do with it.
If you're interested in trying the app (still a work in progress) shoot me a
mail:
regards,
Muzak
- Original Message -
From: Kevin Aebig [EMAIL PROTECTED]
To: flexcoders@yahoogroups.com
Sent: Monday, April 07, 2008 9:02 PM
Subject: RE: [flexcoders] AIR SQL - Datatype issues
Hey Muzak,
I've actually tried a few of those from references at SQLite websites but to
no avail. I think it has something to do with the way the DB is handling
data typing as I've heard that SQLite will actually allow unique datatypes
to each cell instead of column.
Thanks a lot for the examples... hopefully something clicks.
Cheers,
!k
-Original Message-
From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Muzak
Sent: Monday, April 07, 2008 1:12 PM
To: flexcoders@yahoogroups.com
Subject: Re: [flexcoders] AIR SQL - Datatype issues
CREATE TABLE tbl_test ( testID INTEGER PRIMARY KEY AUTOINCREMENT , field1
DATE )
All of the following work:
INSERT INTO tbl_test (field1) VALUES ('now')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07 20:57:44')
INSERT INTO tbl_test (field1) VALUES ('2008-04-07')
INSERT INTO tbl_test (field1) VALUES (CURRENT_TIMESTAMP)
More info on this page:
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html
Look for: Date and time formatting functions and Time formats (about 3/4
down the page).
regards,
Muzak
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives: http://www.mail-archive.com/flexcoders%40yahoogroups.comYahoo!
Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/flexcoders/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group/flexcoders/join
(Yahoo! ID required)
* To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
* To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
RE: [flexcoders] AIR SQL - Datatype issues
Try this:
private var dbConn:SQLConnection;
private var dbFile:File;
private function init():void {
dbFile =
File.applicationStorageDirectory.resolvePath(contact.db);
dbConn = new SQLConnection();
dbConn.addEventListener(SQLEvent.OPEN, createContactTable);
dbConn.addEventListener(SQLErrorEvent.ERROR, sqlError);
dbConn.open(dbFile);
// create the table
private function createContactTable():void {
var dbStatement:SQLStatement = new SQLStatement();
dbStatement.sqlConnection = dbConn;
var dbQuery:String = CREATE TABLE IF NOT EXISTS contact ( +
contID INTEGER PRIMARY KEY
AUTOINCREMENT, +
firstName TEXT, +
lastName TEXT, +
DOB DATETIME +
);;
dbStatement.text = dbQuery;
dbStatement.addEventListener(SQLEvent.RESULT, createdContact);
dbStatement.addEventListener(SQLErrorEvent.ERROR, sqlError);
dbStatement.execute();
}
// put a contact in the table
private function createdContact(event:SQLEvent):void {
var dbStatement:SQLStatement = new SQLStatement();
dbStatement.sqlConnection = dbConn;
var dbQuery:String = INSERT INTO contact (firstName, lastName, dob) +
VALUES (:fn, :ln, :dob);;
dbStatement.text = dbQuery;
dbStatement.parameters[:fn] = John;
dbStatement.parameters[:ln] = Doe;
// set dob to 11/28/1973 -- remember, the month field is 0-based
dbStatement.parameters[:dob] = new Date(1973, 10, 28);
dbStatement.addEventListener(SQLEvent.RESULT, insertComplete);
dbStatement.addEventListener(SQLErrorEvent.ERROR, sqlError);
dbStatement.execute();
}
That's it. SQLite has a datetime object just like a normal sql server. The
above code is incomplete -- is doesn't have the error handler or the
insertComplete handler.
HTH,
~randy
_
From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Kevin Aebig
Sent: Monday, April 07, 2008 11:43 AM
To: flexcoders@yahoogroups.com
Subject: RE: [flexcoders] AIR SQL - Datatype issues
Hi Fidel,
Actually, the version of SQLite that AIR uses has been customized to
implement dates as julian dates and stores them as REAL. My issue arises in
that I’m getting the dates originally from a MySQL DateTime column and still
want to be able to use that form of formatting to query the local database
with date ranges and ordering…
It would be nice if there were better examples of using the AIR SQL engine…
!k
_
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Fidel Viegas
Sent: Monday, April 07, 2008 9:52 AM
To: [EMAIL PROTECTED]
Subject: Re: [flexcoders] AIR SQL - Datatype issues
On Mon, Apr 7, 2008 at 4:22 PM, Kevin Aebig HYPERLINK
mailto:kevin%40keslabs.com[EMAIL PROTECTED] wrote:
Hey all,
I've looked at the documentation about 400 times now and can't seem to
find
myself an example of using a column of datatype Date within AIR SQL. I
know
that it's been customized to use Actionscript / JavaScript Date objects,
but
I can't get it to work properly for the life of me. Any examples or
pointers?
The database engine used in Air is SQLite. I am not that experienced
with SQLite, but I suppose that there should be a DATE and TIME
datatypes. I have seen people use Text and Real for storing date
values. So, just do a search on the web for sqlite.
All the best,
Fidel..
HYPERLINK
http://geo.yahoo.com/serv?s=97359714/grpId=12286167/grpspId=1705007207/msgI
d=109106/stime=1207584342/nc1=4507179/nc2=3848644/nc3=5170406
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.8/1362 - Release Date: 4/6/2008
11:12 AM
