Re: [flexcoders] Re: sqlite insert issue
thanks. I recalled last night that insert constraints are not enforced, the schema i took was taken directly from a postgre dump and all this work will be thrown out as soon as i create my db directly in java. but yeah, i will alter the schema. thanks for all the help from both of you. On Tue, Nov 4, 2008 at 8:22 AM, jason_williams_mm [EMAIL PROTECTED]wrote: --- In flexcoders@yahoogroups.com flexcoders%40yahoogroups.com, Johannes Nel [EMAIL PROTECTED] The confusion here is due to the affinities that you have selected. When the AIR embedded db cannot find the affinity specified it defaults to NUMERIC. There is no affinity called timestamp (or bigint for that matter) and you are left with a NUMERIC affinity. In this case the string value of 2008-08... is being inserted into a field in which a NUMERIC value is expected. The embedded db in the AIR runtime makes an attempt to convert that string into the desired value, however, a conversion isn't possible, and the result is the error you see. Why does this work in SQLite? In the SQLite db affinities are not enforced, so when the attempt to convert fails the string value is inserted as is. If you change the affinity specified here to DATE you will get what you expect, and additionally, when you select the value back out you will get an AS3 Date object. jw wrote: Hi All I have a sql statement which runs sans any issues in my sqlite viewer, but when executed from within air it gives me the error SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.' this is the table schema CREATE TABLE category ( id bigint NOT NULL, version integer NOT NULL, name character varying(255), ordering integer NOT NULL, external boolean NOT NULL, createddate timestamp without time zone, modifieddate timestamp without time zone, createdby_id bigint, modifiedby_id bigint ); and here is the sql statement insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,'2008-08-19 16:46:54.26',NULL,1109); any ideas? i find it really weird that this works on a SQLite level, but not from AS. johan -- j:pn \\no comment -- j:pn \\no comment
[flexcoders] Re: sqlite insert issue
From memory, a *nix timestamp is a Real so it is having trouble converting '2008-08-19 16:46:54.26' to a number. Have you tried converting it before adding it to the database? --- In flexcoders@yahoogroups.com, Johannes Nel [EMAIL PROTECTED] wrote: Hi All I have a sql statement which runs sans any issues in my sqlite viewer, but when executed from within air it gives me the error SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.' this is the table schema CREATE TABLE category ( id bigint NOT NULL, version integer NOT NULL, name character varying(255), ordering integer NOT NULL, external boolean NOT NULL, createddate timestamp without time zone, modifieddate timestamp without time zone, createdby_id bigint, modifiedby_id bigint ); and here is the sql statement insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,'2008-08-19 16:46:54.26',NULL,1109); any ideas? i find it really weird that this works on a SQLite level, but not from AS. johan -- j:pn \\no comment
Re: [flexcoders] Re: sqlite insert issue
i did try and pass it as a non string, which gave an error as well.as i said, weirdly it works when i use a sqlite tool, only from AS it fails. On Mon, Nov 3, 2008 at 7:54 PM, valdhor [EMAIL PROTECTED] wrote: From memory, a *nix timestamp is a Real so it is having trouble converting '2008-08-19 16:46:54.26' to a number. Have you tried converting it before adding it to the database? --- In flexcoders@yahoogroups.com flexcoders%40yahoogroups.com, Johannes Nel [EMAIL PROTECTED] wrote: Hi All I have a sql statement which runs sans any issues in my sqlite viewer, but when executed from within air it gives me the error SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.' this is the table schema CREATE TABLE category ( id bigint NOT NULL, version integer NOT NULL, name character varying(255), ordering integer NOT NULL, external boolean NOT NULL, createddate timestamp without time zone, modifieddate timestamp without time zone, createdby_id bigint, modifiedby_id bigint ); and here is the sql statement insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,'2008-08-19 16:46:54.26',NULL,1109); any ideas? i find it really weird that this works on a SQLite level, but not from AS. johan -- j:pn \\no comment -- j:pn \\no comment
[flexcoders] Re: sqlite insert issue
Nope - not as a non string. Make it a number! Try this... insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,1234567,NULL,1109); Does it work? If so, you will need to convert the string to a timestamp (ie. The number of seconds since the unix epoch on January 1st, 1970). --- In flexcoders@yahoogroups.com, Johannes Nel [EMAIL PROTECTED] wrote: i did try and pass it as a non string, which gave an error as well.as i said, weirdly it works when i use a sqlite tool, only from AS it fails. On Mon, Nov 3, 2008 at 7:54 PM, valdhor [EMAIL PROTECTED] wrote: From memory, a *nix timestamp is a Real so it is having trouble converting '2008-08-19 16:46:54.26' to a number. Have you tried converting it before adding it to the database? --- In flexcoders@yahoogroups.com flexcoders%40yahoogroups.com, Johannes Nel johannes.nel@ wrote: Hi All I have a sql statement which runs sans any issues in my sqlite viewer, but when executed from within air it gives me the error SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.' this is the table schema CREATE TABLE category ( id bigint NOT NULL, version integer NOT NULL, name character varying(255), ordering integer NOT NULL, external boolean NOT NULL, createddate timestamp without time zone, modifieddate timestamp without time zone, createdby_id bigint, modifiedby_id bigint ); and here is the sql statement insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,'2008-08-19 16:46:54.26',NULL,1109); any ideas? i find it really weird that this works on a SQLite level, but not from AS. johan -- j:pn \\no comment -- j:pn \\no comment
[flexcoders] Re: sqlite insert issue
--- In flexcoders@yahoogroups.com, Johannes Nel [EMAIL PROTECTED] The confusion here is due to the affinities that you have selected. When the AIR embedded db cannot find the affinity specified it defaults to NUMERIC. There is no affinity called timestamp (or bigint for that matter) and you are left with a NUMERIC affinity. In this case the string value of 2008-08... is being inserted into a field in which a NUMERIC value is expected. The embedded db in the AIR runtime makes an attempt to convert that string into the desired value, however, a conversion isn't possible, and the result is the error you see. Why does this work in SQLite? In the SQLite db affinities are not enforced, so when the attempt to convert fails the string value is inserted as is. If you change the affinity specified here to DATE you will get what you expect, and additionally, when you select the value back out you will get an AS3 Date object. jw wrote: Hi All I have a sql statement which runs sans any issues in my sqlite viewer, but when executed from within air it gives me the error SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.' this is the table schema CREATE TABLE category ( id bigint NOT NULL, version integer NOT NULL, name character varying(255), ordering integer NOT NULL, external boolean NOT NULL, createddate timestamp without time zone, modifieddate timestamp without time zone, createdby_id bigint, modifiedby_id bigint ); and here is the sql statement insert into category(id, version, name, ordering, external, createddate, modifieddate, createdby_id, modifiedby_id) values (477,14,'Leads',0,'false',NULL,'2008-08-19 16:46:54.26',NULL,1109); any ideas? i find it really weird that this works on a SQLite level, but not from AS. johan -- j:pn \\no comment
