SQL query working outside but no in MVD

I tried with 32-bit DLL (x86) for SQLite version 3.33.0 from sqlite download page.

But no luck. Still the same error.

Derek, in your example you haven't added the rownum function.

SELECT ROW_NUMBER() OVER (ORDER BY name) as id,  name FROM sqlite_master WHERE type="table"

I also tried with below query to get the list of fields in a table. But that too not working in MVD.

SQLQuery('select name from pragma_table_info("sqlite_master")', dataSet);

I think MVD is not allowing paranthesis in the sql query.

Is there any other way to get this Working ? I badly need this :-(

rbknathan,
Just curious. What is wrong with Derek's solution? Why do you need row number?

I am attaching another way, but I really do not understand the necessity of having a number in the combobox. I added onto Derek's example (Thank you Derek).

You're welcome. @thezimguy already said it tho "Just update the default sqlite3.dll that came with MVD to the current version and it will work. I tried to attach the current sqlite3.dll but upload was refused.". I just elaborated it with an image. I just think you overlooked it because it happened to me when i was trying sqlcipher to work. Cheers!

As for @ehwagner's code. You can replace the code in line 17 with

form1.ComboBox2.dbSQLExecute('Select id, (id||" - "||TableName) as val from MyTables');

Anyway, I think you don't need the first combobox approach. You can also use this to minimize the code.

SqlExecute('Delete From MyTables');
SqlExecute('Insert Into MyTables (id,TableName) select  ROW_NUMBER() OVER (ORDER BY name) as id, sm.name from sqlite_master sm where sm.name <> "sqlite_sequence" AND sm.name <> "MyTables" order by sm.name');
form1.ComboBox2.dbSQLExecute('Select id, (id||" - "||TableName) as val from MyTables');