Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 February 2013 13:41:23 zeljko wrote: Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. sqlite3_column_type() works for CAST(0 AS INTEGER) too in a select statement, just tested with MSEgui git master and Sqlite 3.7.12.1. Is the problem caused by VIEW? Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sunday 17 of February 2013 10:05:56 Martin Schreiber wrote:
On Friday 15 February 2013 13:41:23 zeljko wrote:
Thanks for the idea, sqlite3_column_type does the job, but now I have
another error ... seem that step() doesn't know anything about some
column types, even if I call sqlite3_column_type inside step() call I
get empty column type result.eg. in one CREATE VIEW someview(blabla
fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...)
When I call that view I can see 0 in sqlite3 utility, but zeos returns it
as empty string ahhh whatta mess.
sqlite3_column_type() works for CAST(0 AS INTEGER) too in a select
statement, just tested with MSEgui git master and Sqlite 3.7.12.1. Is the
problem caused by VIEW?
Didn't test 3.7, but 3.6.23. As I already mentioned it works ok for select
,but pragma table_info(''myview') does not work for casts returns empty
type.
z.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sunday 17 February 2013 10:31:04 zeljko wrote:
sqlite3_column_type() works for CAST(0 AS INTEGER) too in a select
statement, just tested with MSEgui git master and Sqlite 3.7.12.1. Is the
problem caused by VIEW?
Didn't test 3.7, but 3.6.23. As I already mentioned it works ok for select
,but pragma table_info(''myview') does not work for casts returns
empty type.
CREATE VIEW testview AS select * ,cast(0 as integer) as nullint from table1
And then
select * from testview
in SQL property of tmsesqlquery returns ftinteger for the nullint field. Can
you post a simple complete example?
Martin
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sunday 17 of February 2013 12:00:51 Martin Schreiber wrote:
On Sunday 17 February 2013 10:31:04 zeljko wrote:
sqlite3_column_type() works for CAST(0 AS INTEGER) too in a select
statement, just tested with MSEgui git master and Sqlite 3.7.12.1. Is
the problem caused by VIEW?
Didn't test 3.7, but 3.6.23. As I already mentioned it works ok for
select ,but pragma table_info(''myview') does not work for casts
returns empty type.
CREATE VIEW testview AS select * ,cast(0 as integer) as nullint from
table1
And then
select * from testview
in SQL property of tmsesqlquery returns ftinteger for the nullint field.
Can you post a simple complete example?
You misunderstood me. I returns ftIntger too when using SELECT, but WHEN using
PRAGMA table_info('testview'); it does not return integer.
So what returns tmsesqlquery in this case:
CREATE VIEW testview AS select * ,cast(0 as integer) as nullint from table1;
PRAGMA table_info('testview');
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sunday 17 February 2013 12:17:24 zeljko wrote:
You misunderstood me. I returns ftIntger too when using SELECT, but WHEN
using PRAGMA table_info('testview'); it does not return integer.
So what returns tmsesqlquery in this case:
CREATE VIEW testview AS select * ,cast(0 as integer) as nullint from
table1;
PRAGMA table_info('testview');
You should use sqlite3_column_type() instead of PRAGMA table_info().
Martin
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sunday 17 of February 2013 12:30:19 Martin Schreiber wrote:
On Sunday 17 February 2013 12:17:24 zeljko wrote:
You misunderstood me. I returns ftIntger too when using SELECT, but WHEN
using PRAGMA table_info('testview'); it does not return integer.
So what returns tmsesqlquery in this case:
CREATE VIEW testview AS select * ,cast(0 as integer) as nullint from
table1;
PRAGMA table_info('testview');
You should use sqlite3_column_type() instead of PRAGMA table_info().
I'm using (actualy it's zeos , not me) sqlite3_colum_type for selects, but for
metadata it uses pragma and that's problem. PRAGMA is ok for tables, but it's
bad for views contains CAST AS SOMETHING since sqlite3 don't know anything
about it. I'll try to use SELECT instead of PRAGMA for metadata in zeos (in
case of views) to see if it'll work.
zeljko
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Fri, 15 Feb 2013 13:43:21 +0100 (CET), you wrote: You should use a real database instead of sqlite. sqlite is good only for storing strings. I don't know what you mean by a real database but you are wrong about the data types SQLite can hold. I seem to recall that what you say was once true (or closer to the truth), but things change. I have stored integers, reals, blobs and strings in sqlite tables. Furthermore, SQLite makes a good partner for FreePascal and Lazarus, so you should not discourage people here from using it. Peace, Tony -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sat, 16 Feb 2013, TonyMc wrote: On Fri, 15 Feb 2013 13:43:21 +0100 (CET), you wrote: You should use a real database instead of sqlite. sqlite is good only for storing strings. I don't know what you mean by a real database but you are wrong about the data types SQLite can hold. I seem to recall that what you say was once true (or closer to the truth), but things change. I have stored integers, reals, blobs and strings in sqlite tables. I never said you cannot do that. I said: it is *good* only for storing strings. Configuration variables or so. Why ? Because sqlite does not enforce the data type. You can perfectly store a string in an integer declared column: home: sqlite3 SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t (a int); sqlite insert into t (a) values (a string); sqlite select * from t; a string sqlite QED. Furthermore, SQLite makes a good partner for FreePascal and Lazarus, so you should not discourage people here from using it. I strongly disagree it is a good partner. Object Pascal is a strongly typed language: FPC database code expects an integer in a column declared as integer. So if sqlite does not ensure this, it is a bad partner for Object Pascal. A good partner is Firebird or Postgres: real databases with stable apis. I will never stop discouraging people from what I think is very bad software. And I consider sqlite very bad for the reasons outlined above. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
Am 16.02.2013 13:28 schrieb Michael Van Canneyt mich...@freepascal.org: Furthermore, SQLite makes a good partner for FreePascal and Lazarus, so you should not discourage people here from using it. I strongly disagree it is a good partner. Object Pascal is a strongly typed language: FPC database code expects an integer in a column declared as integer. So if sqlite does not ensure this, it is a bad partner for Object Pascal. A good partner is Firebird or Postgres: real databases with stable apis. I will never stop discouraging people from what I think is very bad software. And I consider sqlite very bad for the reasons outlined above. But unlike Firebird and PostgreSQL one can rather simply port SQLite to a new platform. And I also like the idea of virtual tables. So in that sense the ZMSQL you posted might be a compareable alternative if now one only adds support for virtual tables, adds some other nice features like triggers (or do they exist already?) and improves the performance :D Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sat, 16 Feb 2013, Sven Barth wrote: Am 16.02.2013 13:28 schrieb Michael Van Canneyt mich...@freepascal.org: Furthermore, SQLite makes a good partner for FreePascal and Lazarus, so you should not discourage people here from using it. I strongly disagree it is a good partner. Object Pascal is a strongly typed language: FPC database code expects an integer in a column declared as integer. So if sqlite does not ensure this, it is a bad partner for Object Pascal. A good partner is Firebird or Postgres: real databases with stable apis. I will never stop discouraging people from what I think is very bad software. And I consider sqlite very bad for the reasons outlined above. But unlike Firebird and PostgreSQL one can rather simply port SQLite to a new platform. And I also like the idea of virtual tables. So in that sense the ZMSQL you posted might be a compareable alternative if now one only adds support for virtual tables, adds some other nice features like triggers (or do they exist already?) and improves the performance :D Well, as far as I know : ZMSQL are always virtual tables. You have triggers: all TDataset events. And I never denied the ease of use of sqlite. I just don't think it is a database in the RDBMs sense of the word. It remains for me at the level of TIniFile or TXMLDocument. And looking at how it's commonly used: mostly it seems to be config settings or key/value pairs and I don't think you need SQL for those. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On 16.02.2013 13:58, Michael Van Canneyt wrote: On Sat, 16 Feb 2013, Sven Barth wrote: Am 16.02.2013 13:28 schrieb Michael Van Canneyt mich...@freepascal.org: Furthermore, SQLite makes a good partner for FreePascal and Lazarus, so you should not discourage people here from using it. I strongly disagree it is a good partner. Object Pascal is a strongly typed language: FPC database code expects an integer in a column declared as integer. So if sqlite does not ensure this, it is a bad partner for Object Pascal. A good partner is Firebird or Postgres: real databases with stable apis. I will never stop discouraging people from what I think is very bad software. And I consider sqlite very bad for the reasons outlined above. But unlike Firebird and PostgreSQL one can rather simply port SQLite to a new platform. And I also like the idea of virtual tables. So in that sense the ZMSQL you posted might be a compareable alternative if now one only adds support for virtual tables, adds some other nice features like triggers (or do they exist already?) and improves the performance :D Well, as far as I know : ZMSQL are always virtual tables. You have triggers: all TDataset events. I've not yet looked in detail at ZMSQL. I've only taken a quick look at the wiki article on my way from the U-bahn to my train yesterday. And I never denied the ease of use of sqlite. I just don't think it is a database in the RDBMs sense of the word. It remains for me at the level of TIniFile or TXMLDocument. And looking at how it's commonly used: mostly it seems to be config settings or key/value pairs and I don't think you need SQL for those. In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Sat, 16 Feb 2013, Sven Barth wrote: In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). See: you store the database in a real database eventually ;-) Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On 16.02.2013 14:33, Michael Van Canneyt wrote: On Sat, 16 Feb 2013, Sven Barth wrote: In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). See: you store the database in a real database eventually ;-) But I'm using the SQLite database as a database as well. And not only as a fancy configuration file :) Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). What you could do is: use TSQLQuery to perform a query on Oracle or MSSQL. Then store the dataset to file. Then copy this file to your WinCE client. Then you can work with it on your client, but don't use ApplyUpdates but do save your dataset to disk. When you're done, copy the dataset to another computer. Open the dataset into an TSQLQuery and run ApplyUpdates... But this only works if you do not use SQL on your WinCE client. But maybe this can be solved since ZMSQL adds that functionality to TBufDataset? (I only read the wiki-article, never actually looked at it) Joost. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On 16.02.2013 14:41, Joost van der Sluis wrote: In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). What you could do is: use TSQLQuery to perform a query on Oracle or MSSQL. Then store the dataset to file. Then copy this file to your WinCE client. Then you can work with it on your client, but don't use ApplyUpdates but do save your dataset to disk. When you're done, copy the dataset to another computer. Open the dataset into an TSQLQuery and run ApplyUpdates... We are using not only one table, but multiple ones with references in between them. Also not every data needs to be loaded each time and the exported amount of data can get huge depending on the customers existing database. Also we don't directly write the created/modified data back into the main database, but go through a special import interface that's designed for input from mobile devices and our web frontend (because depending on the type of created/modified data specific other actions need to be triggered which would not be possible if simply doing a ApplyUpdates as tables are touched that are not downloaded to the mobile client). But this only works if you do not use SQL on your WinCE client. But maybe this can be solved since ZMSQL adds that functionality to TBufDataset? (I only read the wiki-article, never actually looked at it) As I wrote: ZMSQL sounds interesting. But up until now I've also only read the wiki article. Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On 16 February 2013 13:51, Sven Barth pascaldra...@googlemail.com wrote: On 16.02.2013 14:41, Joost van der Sluis wrote: In our WinCE client for our application we use it for a miniature version of our database. As the client does not need all tables and only a specific subset of the data we generate a database on the host computer and download that to the device. When the user is done the database is copied back and the created data is integrated with the normal database (which is either a Oracle, MSSQL or DB2 one). What you could do is: use TSQLQuery to perform a query on Oracle or MSSQL. Then store the dataset to file. Then copy this file to your WinCE client. Then you can work with it on your client, but don't use ApplyUpdates but do save your dataset to disk. When you're done, copy the dataset to another computer. Open the dataset into an TSQLQuery and run ApplyUpdates... We are using not only one table, but multiple ones with references in between them. Also not every data needs to be loaded each time and the exported amount of data can get huge depending on the customers existing database. Also we don't directly write the created/modified data back into the main database, but go through a special import interface that's designed for input from mobile devices and our web frontend (because depending on the type of created/modified data specific other actions need to be triggered which would not be possible if simply doing a ApplyUpdates as tables are touched that are not downloaded to the mobile client). But this only works if you do not use SQL on your WinCE client. But maybe this can be solved since ZMSQL adds that functionality to TBufDataset? (I only read the wiki-article, never actually looked at it) As I wrote: ZMSQL sounds interesting. But up until now I've also only read the wiki article. Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus The nature of SQLite means your client library has to do a very good job of checking the table definitions and ensuring that and ensuring that the right conversions are made when reading and writing records in accord with standard TDataset expectations, and I don't think the clients do a perfect job with SQLite. It can be convenient though. -- Frank Church === http://devblog.brahmancreations.com -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 of February 2013 09:57:11 Martin Schreiber wrote: On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. zeljko -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 09:57:11 Martin Schreiber wrote: On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. You should use a real database instead of sqlite. sqlite is good only for storing strings. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 of February 2013 13:43:21 Michael Van Canneyt wrote: On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 09:57:11 Martin Schreiber wrote: On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. You should use a real database instead of sqlite. sqlite is good only for storing strings. O yes ? Is there any other database on android phones/tablets except sqlite3 ? :) ... I'm using pgsql but need local database on Android devices (those who does not connect to pgsql or similar). zeljko -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 of February 2013 13:41:23 zeljko wrote:
When I call that view I can see 0 in sqlite3 utility, but zeos returns it
as empty string ahhh whatta mess.
Problem is sqlite. PRAGMA table_info('someview'); won't return column type
for subselects, casts etc etc
zeljko
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 13:43:21 Michael Van Canneyt wrote: On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 09:57:11 Martin Schreiber wrote: On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. You should use a real database instead of sqlite. sqlite is good only for storing strings. O yes ? Is there any other database on android phones/tablets except sqlite3 ? :) ... I'm using pgsql but need local database on Android devices (those who does not connect to pgsql or similar). http://wiki.freepascal.org/ZMSQL Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Friday 15 of February 2013 14:06:34 Michael Van Canneyt wrote: On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 13:43:21 Michael Van Canneyt wrote: On Fri, 15 Feb 2013, zeljko wrote: On Friday 15 of February 2013 09:57:11 Martin Schreiber wrote: On Friday 15 February 2013 09:36:47 zeljko wrote: On Friday 15 of February 2013 00:29:01 luiz americo pereira camara wrote: I just tested with sqlite3ds and works fine. See test below. You may look at its source and see if the approach used can be adapted for zeos There's no problem with such approach, but as I already said it's not solution for me since there's hundreds of dynamic queries and I have to rewrite all of them to get it work in that way (and that queries are used by pgsql too). Just in case, I added a check of sqlite3_column_type() if there is no existing fielddef. Seems to work for sum() statements. git master 81d976c8372a6654a3a2962e12d57c53eaac0176. Thanks for the idea, sqlite3_column_type does the job, but now I have another error ... seem that step() doesn't know anything about some column types, even if I call sqlite3_column_type inside step() call I get empty column type result.eg. in one CREATE VIEW someview(blabla fields,CAST(0 AS INTEGER) AS myintfield, etc etc. ...) When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ahhh whatta mess. You should use a real database instead of sqlite. sqlite is good only for storing strings. O yes ? Is there any other database on android phones/tablets except sqlite3 ? :) ... I'm using pgsql but need local database on Android devices (those who does not connect to pgsql or similar). http://wiki.freepascal.org/ZMSQL That's true but status says: Alpha. zeljko -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] Problem with zeos and sqlite-3
Hi, When using eg. sum(somecolumn) AS sum1, sum(somecolumn2) AS sum2 sqlite returns (zeos recognizes) result columns as string not as float. I've tried CAST(sum(somecolumn) AS REAL) AS sum1 but have same problem. If I use pure data from table (eg. item INTEGER,value float) then results are ok. I know that sqlite3_column_type() is tricky about correct results, but asking if someone have solution for this. Anyone ? zeljko -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Thursday 14 February 2013 13:48:46 zeljko wrote: Hi, When using eg. sum(somecolumn) AS sum1, sum(somecolumn2) AS sum2 sqlite returns (zeos recognizes) result columns as string not as float. I've tried CAST(sum(somecolumn) AS REAL) AS sum1 but have same problem. If I use pure data from table (eg. item INTEGER,value float) then results are ok. I know that sqlite3_column_type() is tricky about correct results, but asking if someone have solution for this. In MSEgui the field type of ambiguous Sqlite3 columns can be defined by setting the fielddefs datatype. Maybe Zeos has a similar option? Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
On Thursday 14 of February 2013 16:19:05 Martin Schreiber wrote: On Thursday 14 February 2013 13:48:46 zeljko wrote: Hi, When using eg. sum(somecolumn) AS sum1, sum(somecolumn2) AS sum2 sqlite returns (zeos recognizes) result columns as string not as float. I've tried CAST(sum(somecolumn) AS REAL) AS sum1 but have same problem. If I use pure data from table (eg. item INTEGER,value float) then results are ok. I know that sqlite3_column_type() is tricky about correct results, but asking if someone have solution for this. In MSEgui the field type of ambiguous Sqlite3 columns can be defined by setting the fielddefs datatype. Maybe Zeos has a similar option? It have, but I'm dynamically creating queries so adding fielddefs manually for each query isn't nice option, anyway thanks for the info. zeljko -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Problem with zeos and sqlite-3
I just tested with sqlite3ds and works fine. See test below. You may
look at its source and see if the approach used can be adapted for
zeos
Luiz
Sqlite3Dataset1.FileName := 'data.db';
Sqlite3Dataset1.TableName := 'Test';
if not Sqlite3Dataset1.TableExists then
begin
Sqlite3Dataset1.FieldDefs.Add('AFloat', ftFloat);
Sqlite3Dataset1.CreateTable;
Sqlite3Dataset1.Open;
Sqlite3Dataset1.Append;
Sqlite3Dataset1.FieldByName('AFloat').AsFloat := 1;
Sqlite3Dataset1.Post;
Sqlite3Dataset1.Append;
Sqlite3Dataset1.FieldByName('AFloat').AsFloat := 2;
Sqlite3Dataset1.Post;
Sqlite3Dataset1.ApplyUpdates;
Sqlite3Dataset1.Close;
end;
Sqlite3Dataset1.SQL := 'Select SUM(AFloat) as SUM1 from Test';
Sqlite3Dataset1.Open;
S := Format('Type: %s Value: %f',
[Fieldtypenames[Sqlite3Dataset1.FieldByName('SUM1').DataType],
Sqlite3Dataset1.FieldByName('SUM1').AsFloat]);
// Type: Float Value: 3,00
2013/2/14 zeljko zel...@holobit.net:
Hi,
When using eg. sum(somecolumn) AS sum1, sum(somecolumn2) AS sum2 sqlite
returns (zeos recognizes) result columns as string not as float.
I've tried CAST(sum(somecolumn) AS REAL) AS sum1 but have same problem.
If I use pure data from table (eg. item INTEGER,value float) then results are
ok.
I know that sqlite3_column_type() is tricky about correct results, but asking
if someone have solution for this.
Anyone ?
zeljko
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
