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

In MSEgui the field type of ambiguous Sqlite3 columns can be defined by setting the fielddefs datatype. Maybe Zeos has a similar option?

Martin

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

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 :

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).

zeljko

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

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 ....ahhhhhhh whatta mess.

zeljko

You should use a real database instead of sqlite.
sqlite is good only for storing strings.

Michael.

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

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

zeljko wrote

When I call that view I can see 0 in sqlite3 utility, but zeos returns it as empty string ....ahhhhhhh whatta mess.

Problem is sqlite. "PRAGMA table_info('someview');" won't return column type for subselects, casts etc etc ....

zeljko

http://wiki.freepascal.org/ZMSQL

Michael.

That's true but status says: Alpha.

zeljko

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

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.

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130216/866086a4/attachment.html>

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.

I've not yet looked in 've only taken a quick look at the wiki article on my way from the U-bahn to my train yesterday.

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

See: you store the database in a real database eventually ;-)

Michael.

But I'm using the SQLite database as a database as well. And not only as a fancy configuration file :)

Regards,
Sven

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.

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

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.

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

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.

"
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

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

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