Topic: SQL query working outside but no in MVD

Using the below query I am trying to populate the data in dropdown which list the list of tables in sqllite db.

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

But this is working if run i sqlite manager. But in MVD its show as syntax error.

How to get this query running in MVD ?

Re: SQL query working outside but no in MVD

SELECT name FROM sqlite_master WHERE type=table  ORDER BY name 

3 (edited by thezimguy 2020-08-14 13:21:41)

Re: SQL query working outside but no in MVD

rbknathan wrote:

Using the below query I am trying to populate the data in dropdown which list the list of tables in sqllite db.

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

But this is working if run i sqlite manager. But in MVD its show as syntax error.

How to get this query running in MVD ?

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.
The version that came with MVD do not support functions like ROW_NUMBER(), RANK() etc.

https://www.sqlite.org/download.html
Download the 32 bit version for windows

@thezimguy

Re: 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.

Re: SQL query working outside but no in MVD

Hi,
Does doing it this way (see attached) give you what you need?
Derek.

Post's attachments

Attachment icon sqlitemaster.zip 335.33 kb, 66 downloads since 2020-08-15 

Re: SQL query working outside but no in MVD

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 :-(

7 (edited by brian.zaballa 2020-08-16 12:07:36)

Re: SQL query working outside but no in MVD

Code's working fine with me after downloading the 32bit sqlite 3.

You must replace sqlite3.dll on the mvd folder and not on your project folder. Compiling your application will automatically replaced by the dll from mvd folder.

Post's attachments

Attachment icon px1.png 25.23 kb, 7 downloads since 2020-08-16 

brian

8 (edited by ehwagner 2020-08-17 01:56:49)

Re: SQL query working outside but no in MVD

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

Post's attachments

Attachment icon sqlitemaster_Revised.zip 336.4 kb, 49 downloads since 2020-08-17 

Re: SQL query working outside but no in MVD

First of all my Thanks to derek, thezimguy, sibprogsistem, brian.zaballa, ehwagner for you suggestions.

@brian.zaballa - Updating the latest sqlite3.dll in the MVD installtion directory worked like a charm. Now, its allowing me to use all the latest sqllite in-built fucntions. Initially I replaced sqlite3.dll in the project folder and tried. That's why it was not working.

@ehwagner - I was trying to use the ROWNUM() function as an id attribute for dropdown. Your approach by having a intermediate table is also good.  In the project you have attached why the drop down field is showing blank after choosing an item ? How to hold the selection ?

10 (edited by brian.zaballa 2020-08-17 04:57:43)

Re: SQL query working outside but no in MVD

rbknathan wrote:

First of all my Thanks to derek, thezimguy, sibprogsistem, brian.zaballa, ehwagner for you suggestions.

@brian.zaballa - Updating the latest sqlite3.dll in the MVD installtion directory worked like a charm. Now, its allowing me to use all the latest sqllite in-built fucntions. Initially I replaced sqlite3.dll in the project folder and tried. That's why it was not working.

@ehwagner - I was trying to use the ROWNUM() function as an id attribute for dropdown. Your approach by having a intermediate table is also good.  In the project you have attached why the drop down field is showing blank after choosing an item ? How to hold the selection ?

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');
brian

Re: SQL query working outside but no in MVD

Thanks a lot brian.zaballa. I understand the solution.

Thank you all.