[Solved] SQLite - UncachedGetColumns

[naumov13]

Hello again!

While working with the attached DB (sqlite feature), I discovered a new problem.

When a query is made to a table from the attached DB, the following call chain is executed

Code: Select all

 1. TZSQLiteDatabaseMetadata.UncachedGetColumns
 2. FillResult
 3. IsAutoIncrement

Function IsAutoIncrement executes the following query

Code: Select all

with GetStatement.ExecuteQuery('select sql from sqlite_master where name = '''+TableName+'''') do 

However, since the work is done through connection of main database, and the table is actually from another database, this query returns an empty dataset. As a consequence, the CreateSQL variable remains an empty string. And then we get an error on the line

Code: Select all

 Assert (CreateSQL <> '')

At the same time, if I compile ZEOS without the Assertions option, then there is no error, and the IsAutoIncrement function simply returns False (although this is not), but my code continues to work fine.

So my first question is: can i use this trick (disabling Assertions) or it is strongly not reccomended?

This is one half of the problem.

The other part of the problem is that the call

Code: Select all

Result.UpdateBoolean (TableColColumnReadonlyIndex, [Boolean]);

is executed twice, and the second time (line 1440) the second parameter is always = False.

I cant to talk about it is correct or not without knowing all details.
But something tells me that one of the calls is clearly superfluous.

[marsupilami]

Hello naumov,

the right way of handling this would be to make the sqlite driver work correctly with attached databases. Disabling the assertions is just more or less a quick and dirty way to ignore the problem.

I just had a quick read of https://www.sqlitetutorial.net/sqlite-attach-database/. To me that sounds like the sqlite way of doing schemas. Did you see during your debugging if the schema (database) name is correctly identified and provided in the call to GetColumns / UncachedGetColumns? In that case the SchemaPattern parameter of the GetColumns / UncachedGetColumns call should not be empty but hold the name of the attached database?

If this parameter is not empty, it should be quite easy to modify the query for retrieving the metadata?

Best regards,

Jan

[naumov13]

Hello.

Parameter SchemaPattern is empty, but parameter Catalog hold the alias of the attached database.
So I modified query by this way

Code: Select all

GetStatement.ExecuteQuery('select sql from '''+Catalog+'''.sqlite_master where name = '''+TableName+'''') 

and it is work correctly!


What about second half question?
Is there any meaning to call at line 1440?

Code: Select all

Result.UpdateBoolean(TableColColumnReadonlyIndex, False)

Need to create a ticket?

[EgonHugeist]

Hi naumov,

yes please open a new ticket. And please provide an example. Than it's quickly fixed.

[naumov13]

I opened ticket
https://sourceforge.net/p/zeoslib/tickets/503/

demo project which demonstrating problem is in attacment

[naumov13]

Solved