mORMot Open Source

Gene Lamb

Member

Registered: 2011-11-23

Posts: 4

Two questions Busy_Timeout and column types

Please pardon my ignorance. I would like to use your software to access an existing SQLite database. I'm not using the ORM features as this would require a total rewrite of my code. I just want to use your framework to replace Zeos.

If I use the TSQLDBConnectionProperties to connect to the db, I can easily access data with an ISQLDBRows field but can't see any way to set the Busy_Timeout.

If I connect with the TSQLDataBase, I can set the Busy_Timeout but can't see any easy way to get rows of data.

I have existing programs (both an application and a service accessing the same database) that I'm plugging this into (replacing Zeos) but am not sure of the best way to do this. What can you suggest? Is there a way to use the TSQLDBConnectionProperties and set the busy_timeout? Or is there an easy way to get row data using just the TSQLDataBase?

I have another question. When getting ISQLDBRows data from a query, there doesn't seem to be any way to determine the datatypes of each column. The ColumnType function just makes a guess returning an ftNull for an empty field and ftUTF8 for a datetime field. I can use the GetFields or GetFieldDefinitions to get the datatypes from a table but this won't work for a query.

Thanks to anyone for their help.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Two questions Busy_Timeout and column types

Thanks for your interest.

I think that it is better to use our SynDBSQlite3 related classes instead of directly use SynSQLite3 classes.
It will allow you to change the database engine in the future - just like with Zeos.

You can set any property of the TSQLDataBase instance (including Busy_Timeout) embedded in a TSQLDBSQLite3Connection, but using its DB property.

About TSQLDBSQLite3Statement.ColumnType(), this method will indeed retrieve the exact column type, as returned by SQLite3.
Since SQLite3 has no standard handling of a date time field - it will return ftUTF8 for a TEXT column in this case.
This is a limitation of SQlite3 itself.
But you can get an ISO-8601 encoded date and time by using the TSQLDBSQLite3Statement.ColumnDateTime() method.

Gene Lamb

Member

Registered: 2011-11-23

Posts: 4

Re: Two questions Busy_Timeout and column types

Thank you for your response. For your answer to my second question, I've tried and when using Execute to get an ISQLRows result, ColumnType() does not return the proper column type. As already mentioned, for DateTime fields it returns ftUTF8 and if there is a null in the field it just returns ftNull. I am trying to write a generic routine to grab the results of a query and put them into a memory table (kbmmemtable, which I can then hook directly to screen fields). I need to know the proper column types to do this (even if DateTime, TimeStamp or field value is Null). To use the ColumnDateTime() or other column type methods, I need to know beforehand what the column type is. Is there another way to get query results that will return the proper native column type information?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Two questions Busy_Timeout and column types

You have already some code to guess the column type used in TSQLTable of SQLite3Commons.pas.

You can use this kind of code to guess the column result from a result set.

If you retrieve your results using a TSQLTableJSON (as e.g. SynDBExplorer does), you have the corresponding method at hand.