1 Topic by jrga (edited by jrga 2023-10-26 20:09:51)

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Topic: Dynamic Query

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

Post's attachments

Attachment icon Query.rar 1.28 mb, 84 downloads since 2023-10-26 

Roberto Alencar
  • From: Нефтеюганск
  • Registered: 2022-06-03
  • Posts: 454

Re: Dynamic Query

jrga wrote:

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

обнаружен вирус
virus detected

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

I like it.
How about working with a NULL value, only manually?

I'll think about what I can add.

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

pavlenko.vladimir.v wrote:
jrga wrote:

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

обнаружен вирус
virus detected


Isn't it a false positive? I have Avast Antivirus which did not identify viruses!

Roberto Alencar
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Dynamic Query

Hi Roberto, Hi Vladimir,
It's a neat application - kudos to you Roberto.
For information, Bit Defender and Total AV didn't find any problems with the download.
Regards,
Derek.

6 Reply by jrga (edited by jrga 2023-10-27 12:56:18)

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

derek wrote:

Hi Roberto, Hi Vladimir,
It's a neat application - kudos to you Roberto.
For information, Bit Defender and Total AV didn't find any problems with the download.
Regards,
Derek.

Thanks  Derek

Roberto Alencar
  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,537

Re: Dynamic Query

pavlenko.vladimir.v wrote:
jrga wrote:

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

обнаружен вирус
virus detected

Та же фигня - защитник винды не даёт скачать....

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

k245 wrote:
pavlenko.vladimir.v wrote:
jrga wrote:

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

обнаружен вирус
virus detected

Та же фигня - защитник винды не даёт скачать....


I will attach it again without the HelpSqlite.exe file.

Post's attachments

Attachment icon Query without HelpSqlite.rar 300.9 kb, 55 downloads since 2023-10-27 

Roberto Alencar
  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

jrga wrote:

I don't know if "dynamic query" is the best name for this project. I like programming, but my background is different. I created it to be integrated into my applications, which can be activated with Ctrl-9. The goal is to have a feature in the application itself to eventually resolve end-user issues by checking tables, executing custom SQL statements in real time, etc. If anyone wants to improve it, please share the improvements. It was made with MVD 5.6

I included a combobox for the user to select the field that should be totaled in the tablegrid footer and converted the HelpSqlite.exe file to HelpSqlite.chm.

Post's attachments

Attachment icon Query with HelpSqlite.chm.rar 554.57 kb, 58 downloads since 2023-10-28 

Roberto Alencar
  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

Added:
- Working with IS NULL , IS NOT NULL in the filter
- Function to detach a database to connect to a new one
- When using a filter, the generated request is displayed in MEMO for the opportunity to supplement the request
- The clear button clears the filter and returns the default query
- Fixed some bugs

Post's attachments

Attachment icon Query-01.zip 589.83 kb, 55 downloads since 2023-10-29 

11 Reply by jrga (edited by jrga 2023-10-29 22:15:34)

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

sparrow wrote:

Added:
- Working with IS NULL , IS NOT NULL in the filter
- Function to detach a database to connect to a new one
- When using a filter, the generated request is displayed in MEMO for the opportunity to supplement the request
- The clear button clears the filter and returns the default query
- Fixed some bugs


Sparrow, congratulations and thank you for the improvements you have implemented! I will include the formatting below for the "Field Total" field and am making changes so that the "Field Filter" and "Field Total" comboboxes are changed when the SQL statement displays results other than the default in the tablegrid, by user intervention - e.g. :

"SELECT ID_FORNECEDORES, SUM(SAIDA) AS SAIDA FROM DB2.LANCAMENTO GROUP BY ID_FORNECEDORES".

After that, I upload the new version. I tried to use the "SQLQuery" function, to avoid the standard MVD error message, in case of an error when manually constructing the SQL statement, but I couldn't...

  frm_runsql.TableGrid1.Columns[num_col_total].Footer.Caption :=
  FormatFloat( '###,###,##0.00', frm_runsql.TableGrid1.Columns[num_col_total].Footer.FormulaValue);
Roberto Alencar
  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

jrga wrote:
sparrow wrote:

Added:
- Working with IS NULL , IS NOT NULL in the filter
- Function to detach a database to connect to a new one
- When using a filter, the generated request is displayed in MEMO for the opportunity to supplement the request
- The clear button clears the filter and returns the default query
- Fixed some bugs


Sparrow, congratulations and thank you for the improvements you have implemented! I will include the formatting below for the "Field Total" field and am making changes so that the "Field Filter" and "Field Total" comboboxes are changed when the SQL statement displays results other than the default in the tablegrid, by user intervention - e.g. :

"SELECT ID_FORNECEDORES, SUM(SAIDA) AS SAIDA FROM DB2.LANCAMENTO GROUP BY ID_FORNECEDORES".

After that, I upload the new version. I tried to use the "SQLQuery" function, to avoid the standard MVD error message, in case of an error when manually constructing the SQL statement, but I couldn't...

  frm_runsql.TableGrid1.Columns[num_col_total].Footer.Caption :=
  FormatFloat( '###,###,##0.00', frm_runsql.TableGrid1.Columns[num_col_total].Footer.FormulaValue);

Finished!
* includes formatting in the total field "Field Total";
* Combobox "Field Filter" will continue to have the original field structure of the selected table;
* Combobox "Field Total" will only contain the fields of the SQL statement, contained in "SQL commands"

This way, the "Field Total" field can be used without errors in queries - examples below - for the same table:
- select * from table
- select id_product, value_test from table
- select id_product, sum(value_test) as total from table group by id_products

Post's attachments

Attachment icon Query-02.rar 557.12 kb, 47 downloads since 2023-10-30 

Roberto Alencar

13 Reply by sparrow (edited by sparrow 2023-10-30 17:02:03)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

I tried to use the "SQLQuery" function, to avoid the standard MVD error message, in case of 
an error when manually constructing the SQL statement, but I couldn't...

what do you want to see on the screen when an error is intercepted, instead of the standard one?

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

sparrow wrote:
I tried to use the "SQLQuery" function, to avoid the standard MVD error message, in case of 
an error when manually constructing the SQL statement, but I couldn't...

what do you want to see on the screen when an error is intercepted, instead of the standard one?


Hi Pardal, thanks for asking.

The message that I think should be displayed, when an error is made in the SQL statement, contained in "SQL commands", would be "The SQL statement cannot be executed due to an error in syntax, operator or names related to the table be consulted." Or something equivalent. In addition to indicating the need to review and correct the problem, the message can be translated into the user's language. This subject is new to me and, unless there is any misunderstanding, it is just an idea.

Roberto Alencar

15 Reply by sparrow (edited by sparrow 2023-10-30 17:56:46)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

In principle, it is possible to intercept. Maybe not exactly pretty. But here you have to get smart.
By default, you can display a standard error message for SQL. Seems to be the most informative.
For example:
- no such column dd
- no such table dd
- error near FROM
...

I added the ability to work with DELETE, UPDATE, INSERT everything that is not SELECT, and I also want to try one thing)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

Added/fixed:

- Working with queries that do not start with SELECT (DELETE, UPDATE ...)
- Added "IN" to operators
- Delete by right mouse button in the table
- Execution of full text in a memo or selected text in a memo (for example, selecting one request from several)
- Dealing with system error
- Snippet
- Changed the request format when outputting to memo
- Changed the query construction code for the filter with display in memo
- Hidden system table
- Bugs fixed, new ones added wink

Post's attachments

Attachment icon Query-02-1.zip 596.44 kb, 73 downloads since 2023-10-31 

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: Dynamic Query

Hi Pardal, you sophisticated the project! Congratulations. However, as you said: "- Bugs fixed, new ones added". I found the new ones! I don't have that much knowledge, but I will study the new code snippets that were inserted.

Roberto Alencar

18 Reply by sparrow (edited by sparrow 2023-11-01 17:50:04)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Dynamic Query

Hi Roberto.
Are all new items appropriate?
I inserted comments in the script in new fragments for clarification.
If there are mistakes, tell me. I am ready to help correct them and indicate in the comments what has been corrected.
I also found some inaccuracies in the work.
I can't find the answer to the question of how to display NULL in a table.