1

(12 replies, posted in General)

Hello v_pozidis


Here is a simple example but that I used on a real database of mine. It's just 3 tables of the complete scheme but the approach is the same for all.


OLD TABLES STRUCTURE

https://i.postimg.cc/sxgv9fm6/navicat-CFFDqy-SS8h.png


As you can see, the assets and the artist are linked through a join table on their respective database IDs, that is standard and probably what most of us do.
Now, over time:

  • “holes” have been produced in all three tables because of delete and add operations

  • New needs arose and some fields need to be added to both tables or anything else that could justify having to transfer data to new tables

The question is: how do I transfer all data in new tables, but without loosing the correct links since (unless copying the old IDs over) new unique key IDs will be produced on insert?


Here is the new table’s structure:

https://i.postimg.cc/44WK4t4r/navicat-tb2bl-AU6r0.png


Note that:

  • The join table is not yet linked

  • Both the artists and assets tables have a field cold “old_id”

  • The join table also included the “old_id”, that’s the reference we will be copying


The first step is to copy all assets and artists over to the new tables, leaving the new IDs up to the database engine but storing the old ones in the corresponding fields


Transferring the old assets to the new asset table is done like (it is also a good time to sort the select query on any field you want to “clean” the table):


INSERT INTO data_assets(asset_name,asset_old_id)
SELECT
asset_name,
id
FROM asset
ORDER BY 
asset.id_vendor ASC,
CAST(asset_SKU AS INTEGER) ASC

Same things applies to the artist table:


INSERT INTO dico_artists(artist_name,artist_old_id)
SELECT
artist_name,
id
FROM
Artist
ORDER BY UPPER(artist_name)

Finally, the join table is transferred, but we transfer only the old IDs


INSERT INTO link_artist_asset(asset_old_id,artist_old_id)
SELECT
id_asset,
id_artist
FROM
artist_asset

DON’T MIX THE ORDER OF THE IDs OR YOU’LL GET IN TROUBLE


It’s time now to update the “new ID” fields based on the new IDs generated while transferring the artists and the assets to their new tables. I do it in two passes:


UPDATE link_artist_asset SET artist_id = 
(
SELECT artist_id FROM dico_artists WHERE link_artist_asset.artist_old_id = dico_artists.artist_old_id
)

My second query is almost identical but it is updating 185.000 records searching for IDs in a table containing 235.000 records (when there were only 4.500 in the artist table). It was still running after 3.600 seconds. So I stopped it and built and index on the data_assets table based on asset_id and asset_old_id.


UPDATE link_artist_asset SET asset_id = 
(
SELECT asset_id FROM data_assets WHERE data_assets.asset_old_id = link_artist_asset.asset_old_id
)
> Affected rows: 184488
> Time: 2114,027s

It still ran for 35 minutes. Once done, just rebuild the foreign keys. This will be the test because of you get no error, it means that all IDs have been transferred and converted correctly.

You can also delete the asset_old_id and artist_old_id fields form the three tables if you want to gain some space or keep them for references if you need them later.


ALTER TABLE "main"."link_artist_asset" RENAME TO "_link_artist_asset_old_20220917";

CREATE TABLE "main"."link_artist_asset" (
  "link_artist_asset_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "asset_id" INTEGER,
  "artist_id" INTEGER,
  "asset_old_id" INTEGER,
  "artist_old_id" INTEGER,
  CONSTRAINT "fk_asset" FOREIGN KEY ("asset_id") REFERENCES "data_assets" ("asset_id"),
  CONSTRAINT "fk_artist" FOREIGN KEY ("artist_id") REFERENCES "dico_artists" ("artist_id")
);

INSERT INTO "main"."sqlite_sequence" (name, seq) VALUES ("link_artist_asset", '184488');

INSERT INTO "main"."link_artist_asset" ("link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id") SELECT "link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id" FROM "main"."_link_artist_asset_old_20220917";

(This is correct for my tables, you Navicat will adapt to yours)


Here is the result with no errors

https://i.postimg.cc/c4jrwSnh/navicat-dz-Mok-WT5un.png


There is not much more to it: you need to be precise and… patient.


As I said, another approach would be to transfer data form old to new table with PRAGMA KEY off and force the transfer of the current IDs to the new tables. I do not like this method and do not trust myself enough to use it knowing I will probably forget something at one point or another


I hope this will be of some help for you. As I said, I had a look at your database and it's huge and... I don't read Greek smile


Cheers


Math

2

(5 replies, posted in General)

Hello and thank you all for your answer.


Derek, thanks for the offer as well. Since my last visit, I restarted from scratch his application with a clean database layout and.... guess what ?
The application compiles and runs normally.


I'm not sure what went wrong in his creation process, but it is solved now.


My major concern is that my evaluation licence will expire soon. Would you acquire a new licence to help a friend ? That's the question smile


Until next time, take care all


Cheers


Math

3

(12 replies, posted in General)

Hello v_pozidis,

I had a look at your database but had no time to work on it this week. Fortunately, the week-end is coming and I'l have more time to setup an example for you.

Cheers


Math

4

(5 replies, posted in General)

Hello all,


Thanks to a friend (or maybe because, it's a question of perspective lol) who wants to develop his own application, I'm back to using MVD.
I downloaded the latest version and my first impression was "sluggishness" : it takes a long time to compile an application with a few forms and a 20 MB SQLite database, and the application itself take as much time to launch as well.


In my souvenirs, compilation and execution were much faster (my latest paid version was something like 3.5 or around). Is it a trial version limitation or maybe my computer ? Did you notice that as well ?


Anyway, it's good to be back smile


Cheers


Math

5

(12 replies, posted in General)

Hello v_pozidis,


Sure, you can send me a project, or just the database if you wish.


Let me precise that I do not do that through MVD though, I do it outside with third party tools. I assume it's possible to do it with MVD though, but I'd build a second application just to convert the database if I were you.


Anyway, I'll have a look at your file smile


Have a good day


Math

6

(4 replies, posted in Database applications)

Hello ralphtheprog,


You can do pretty much anything you want with MVD and I've been using it for years. But my latest subscription dates back a few now because I switched to pure Delphi (personal choice). I still roam this forum because I met good people and a community always happy and prompt to answer questions, hich I still do from time to time.


If you have some knowledge of Delphi, you can go even further by adding code to your application.


Last time I tried my latest application version that was developed with MVD, the web scraping functions still worked, but not with SSL 1.3. So you'd better update (or add) those DLL's to your root folder.


Also, the HTTPQuery method works better than the HTTPGet method (or so I noted in my case).


Do not hesitate to submit your problems and question on the forum, there's always someone around here smile


Cheers


Math

7

(4 replies, posted in Script)

Hello sonixax,

I have setup a very simple database with 3 tables :

  • test_customers

    • cID

    • cName

  • test_reservations

    • rID

    • rDate

    • cID

  • test_payments

    • pID

    • pDate

    • rID

So the payment (which comes AFTER the reservation) is linked to the reservation on the reservation number (ID) and the reservation is linked to the customer on the customer number (ID).


This query

SELECT
     test_customers.cName,
     test_reservations.rDate,
     test_payments.pDate,
     julianday('now') - coalesce(julianday(test_payments.pDate),julianday(test_reservations.rDate)) AS pDelay
FROM
     test_customers
INNER JOIN test_reservations ON test_customers.cID = test_reservations.cID
LEFT JOIN test_payments ON test_reservations.rID = test_payments.rID

calculates the delay between now and payment OR now and reservation of no payment was found.
The COALESCE SQL instruction tells the SQL engine to take the first parameter by default, or the second if the first is NULL.


The LEFT JOIN to the test_payments is because INNER JOIN would exclude all missing records when LEFT JOIN includes the results even if not found.


The ouput looks like this, since there was no payment date for Lucien (but a reservation date)

https://i.postimg.cc/kXV0Xpdy/image.png


I hope this helped


Cheers


Math

8

(12 replies, posted in General)

Hello v_pozidis,


Ouch, that is a vast subject, especially if you have foreign keys all over the place...


There are two approaches to that kind of problem.


If you are using SQLite, you can "deactivate" the auto increment on IDs in your table and force insert the old records with their previous IDs - you'll need something like PRAGMA foreign_keys=off on your tables BEFORE proceeding.


But that is a real pain in the @33...


What I usually do is :

  • rebuild the global database structure empty, without FOREIGN KEY constraints

  • I insert one more field in every new tables called old_id and save the previous ID of the record I want to transfer

  • when all tables are transferred, I rebuild the pivot tables (the link tables) and UPDATE the foreign keys according to the new ID of the old ID if that makes sens
    Something like UPDATE new_link_table SET new_foreign_key_id = (SELECT new_id FROM some_table WHERE old_id = something), at least that is the general idea, it's hard to be more precise with no actual tables to work on smile

  • finally, I reintroduce the foreign key constraints


This is also a long process and preparation is the key to success.


In any case, if you have a big database, it's going to be a tedious process.


Cheers


Mathias

9

(7 replies, posted in General)

Bonjour Nikomax,


Tu peux aussi utiliser un trigger sur l'event Before ou After Update dans ta base de données SQLite.
De cette manière tu allèges ton code MVD et tu déportes une partie de la logique du code vers la base de données qui est faite pour cela smile


Amicalement


Math


PS : si besoin d'exemple, je peux te trouver ça

10

(4 replies, posted in Script)

Hello sonixax,


Assuming your dates are stored in the following format : yyyy-mm-dd you could try something like this :


SELECT JULIANDAY(end_date) - JULIANDAY(start_date) AS date_difference_in_days FROM table_name

If the date you want a difference with is the date of today, you could use :

SELECT JULIANDAY('now') - JULIANDAY(start_date) AS date_difference_in_days FROM table_name

And if you want to include the current day in the total, just add a +1 to your query

SELECT JULIANDAY('now') - JULIANDAY(start_date) + 1 AS date_difference_in_days FROM table_name

SIDE NOTE : you will get decimals to the calculation, you could round the result and get it without decimals with :

SELECT FLOOR(JULIANDAY('now') - JULIANDAY(test_date) + 1) AS days_inclusive FROM asset

FLOOR will only keep the non decimal part of your result while ROUND would behave differently if the decimal part is lower or greater that 0.5 ROUNDING up or down.


Hope it helped


Cheers


Math

11

(7 replies, posted in Talks about all)

domebil wrote:

hi Mattia, do you have an example project made with Rad Studio to connect a database?

God, I missed that question big time, sorry.

What kind of database fo you want to connect to ?
RAD Studio ships with Firedac, and I use the TFDConnection component to connect to any database, but usually SQLite.

Give a little more detail and I'llsee to help you.


Cheers


Mathias

12

(7 replies, posted in Talks about all)

Hello all,


I haven't been active lately but I still survey the forum (via RSS feeds almost daily) and I thought I'd stop by and say hello.
It's so nice to see so many known faces still around and others that are new. I was wondering how guys were doing and I see a lot of activity, that's heart warming.


As you might guess, I am now a few versions late with MVD and haven't renewed my licence for a long time.


I still use my old MVD to "teach" my daughters the general ideas and the logic behind"coding" and what a RAD is, but as a daily tool, I now use Embarcadero RAD Studio : my needs pushed me in that direction and they are all covered now. But I was blessed to stumbled upon MVD, because this tool put me in the saddle and I never stopped learning.


Not only the tool but the great community found here, that I miss, I have to admit.
Dmitry, Derek, v_posidis, Jean B to name only a few... cheers guys and see you around


Mathias

OK, let's do a little example for you :


On a brand new form put :

  • an edit box

  • a button

  • a richedit

NOTE : Richedit could be replaced with a Memo, I use it just to preserve the structure of the response you will get from the server : the memo will output just one line of text, whether the richedit will keep the nested structure of the data you receive.


Create the Onclick event of the button and put this code :


procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    ISBN : String;
    APIResponse : String;
begin
    ISBN := Form1.Edit1.Text;
    //APIResponse := HTTPGet('https://www.googleapis.com/books/v1/volumes?q=isbn:'+ISBN,True);
    Form1.RichEdit1.Text := APIResponse;
end;

Remove the comment before the second line of code, the forum keeps wanting the url tag


You will see the data in the Richedit after a short while ( a few seconds).


Notice the tree like structure of the data ? This is a json you received but I am not sure MVD is equipped to parse json file so you will have to use regular expressions or Pos and Copy functions to extract what you need.


NOTE : input the ISBN without the dashes (-), and watch out because I you "hammer" the API server (too many requests per seconds) you will get banned.
This is just a simple example to show you how it works. Other API might use more parameters and even need a personal key that you can get by registering. Some are free, others (like  cost 5$ per year).


Once you get it working, do not output the result to the richedit, just output the parts you extracted.


Hope this helps


Cheers


Mathias

14

(3 replies, posted in General)

Hello again,


I am a bit lost. What is it exactly that you want to achieve ? Reorder the sibling into a same family (as long as there is more than one) ? Change the order of the families ? Change a sibling to another family ?


In any case, you refer in your queries to a "record_count" field that I believe to be a counter. But what increments it ? I opened the database and found only null values in this field so there is no comparison possible as per your code :


record_count1 := SQLExecute('SELECT record_count FROM OrgStructure WHERE id='+IntToStr(id1));
record_count2 := SQLExecute('SELECT record_count FROM OrgStructure WHERE id='+IntToStr(id2));

if (record_count1<>'') and (record_count2<>'') then

I usually add an "order" column to my tables and adjust their content according to the user choice for the order of display. Is it what you intended to achieve ?


I any case keep me posted, I love your application it's great !!

Cheers


Mathias

15

(3 replies, posted in General)

Hello Jean,


I had a look at your project (because I like it !) and haven't found yet why your reordering function does not work, but I think I found the reason of your "out of index" error.


Line 762 of your script reads :


Form1.TableGrid1.Cell[1,i].TextColor := clRed;     // Les caractères de la colonne sont en rouge

At this place your are in a loop, iterating through your columns. But this line deals with cells and you did not iterate.

I replaced this line with :

for i := 0 to c do
     begin
          Form1.TableGrid1.Cell[1,i].TextColor := clRed;     // Les caractères de la colonne sont en rouge
      end;

And the error message is gone.

Hope this will help a little, I will dive into your other problem smile

Cheers

Mathias

Hello v_pozidis,

There is an old post of mine that might help you a little to get started.
Of course, this post from 2015 is a little outdated and full of things I would not do like that today.


In general :
1- An HTTPGet request will give you the source code of the page : this is just text
2- Using specific HTML tag you will have to identify, you will look for the info you want embedded in the web page
3- Using Pos, Copy and so on, you can extract the data you need and save that data into variables
4- You can also use RegExp for finding your data, but it's a little tricky if you are not used to it, and I advise you not to use regExp on large chunck of text, that can have a significant impact on performances.



My old post on webscraping : http://myvisualdatabase.com/forum/viewtopic.php?id=1851
A good online regular expression tester : https://rubular.com



A STEP FURTHER
Most of the big websites like Goodreads, google books and so on, use REST API to deliver data to their pages and to third party application.


Using the REST API, you can request book data and get it from the server without all the html garbage you will have to deal with if you use html scraping.
REST API are just url that you query with parameters and that send data you in return, generally under xml or json format.
This is way faster and safer than webscraping because :
- the amount of data you receive is small
- data is organised in tree
- you don't have to worry about the web page format changing and ruining all your scraping procedures.


As an example of free API to get data here is what I tried :
https://restcountries.eu/rest/v2/name/france

Here, the parameter is the country name and I got in return data about my home country. (try it and see what data is displayed on screen after you followed the url - it is way shorter than a full web page).


All API works the same, and in book API cases, you  pass author names or titles or ISBN numbers in the URL as parameters, and get your results sorted by relevance. (most of them request that you register to obtain a personnal token as and identifier, but it is free most of the time).


I strongly advise you look in this direction because you will save a lot of time and build a scraping system that will be independent from the web page format that can change without notice.


I don't know if Dmitry implement the JSON unit in MVD, but it is very easy to parse and get data from the json format with Delphi and, if not available yet in MVD, this could be a great addition.


Hope this helped a little


Cheers

Math

17

(7 replies, posted in General)

Better adding the zip file if I mention it smile

By the way, I set parentFamilyID to -1 for root families, but NULL would work as well.

And here is the same query but more readable I think

SELECT DISTINCT
    familyID AS FID,
    familyParentID AS FPID,
    familyName AS FN,
    (
    SELECT
        COUNT( butterflyID ) 
    FROM
        butterflies 
    WHERE
        butterflyFamilyID IN (
            WITH RECURSIVE IN_MEMORY_TABLE ( familyID ) AS 
                (
                    VALUES ( butterflyFamilies.familyID )
                    UNION ALL
                    SELECT butterflyFamilies.familyID FROM butterflyFamilies JOIN IN_MEMORY_TABLE ON butterflyFamilies.familyParentID = IN_MEMORY_TABLE.familyID
                )
            SELECT
            familyID AS TREE 
            FROM
            IN_MEMORY_TABLE 
        ) 
    ) AS NB_BUTTERFLIES 
FROM
    butterflyFamilies 
ORDER BY
    familyID,
    familyName

Cheers

Mathias

18

(7 replies, posted in General)

Hello dear Jean and all MVD users, and ... happy new year !!

Haaaaa, recursive queries.... "tout un monde !!!" (en français dans le texte). Sorry in advance, this answer might be a little long smile


To start with, I do not know how you set up you database. I am going to assume that you have at least two tables : one with the butterflies families and one with the butterflies themselves.

What I set up as an example looks like this :

https://i.imgur.com/anJMbhB.png

  • the butterflyFamilies table contains :

    • a unique ID

    • a family Name

    • the ID of the parent family

  • the butterflies table contains :

    • a unique ID

    • a butterfly Name

    • the ID of the family this butterfly belongs to (the lowest sub-family in fact, not the main branch)


At this stage, and based on your screen-capture, it is not difficult to count, for each family, how many butterflies are attached to it. But if these families are in fact sub-families, how can we count (or sum) how many butterflies there are in each main families ?


Again, in your screen-capture, the Nymphalidés family should count 21, which is the sum of all butterflies belonging to each sub-familes.


For this, you can not use "nodecount" because butterflies or not subnodes of each families, they are in another table.


You will have to use a RECURSIVE query.


In a simple query, you select N fields by their names in a table. In a RECURSIVE query, one (or more) of the fields you will retrieve is not a named field, but a calculated field counting ID from a query in the main query. The "magic" being that the query interrogates itself until no more results are found !! For this, we will create a virtual table stored in memory (I called it IN_MEMORY_TABLE but you can call it whatever you want, the only reserved word being WITH RECURSIVE). This works with Sqlite and is called CTE (common table expressions). You can even solve suddokus with this type of query.


Good link for explanation : https://sqlite.org/lang_with.html


God, I hope I am being clear smile With an example, things will be simpler.


NOTE : I am not a specialist in butterflies, I merely googled for names (and even invented some) just for the table to look pretty



Based on my tables model (you might have to adapt it to you scheme) the query looks like this :




SELECT DISTINCT
    familyID AS FID,
    familyParentID AS FPID,
    familyName AS FN,
    (
    SELECT
        COUNT( butterflyID ) 
    FROM
        butterflies 
    WHERE
        butterflyFamilyID IN (
            WITH RECURSIVE IN_MEMORY_TABLE ( familyID ) AS ( VALUES ( butterflyFamilies.familyID ) UNION ALL SELECT butterflyFamilies.familyID FROM butterflyFamilies JOIN IN_MEMORY_TABLE ON butterflyFamilies.familyParentID = IN_MEMORY_TABLE.familyID ) SELECT
            familyID AS TREE 
        FROM
            IN_MEMORY_TABLE 
        ) 
    ) AS NB_BUTTERFLIES 
FROM
    butterflyFamilies 
ORDER BY
    familyID,
    familyName

You see that, the beginning of the query is pretty standard, we are selecting 3 fields from the butterfyFamilies table. The fourth one, the one called NB_BUTTERFLIES, is the one counting butterflies attached to each families, and recursively counting them for each main family.

The result of this query is :


https://i.imgur.com/01VYywE.png


As you can see, the Nymphalidés family now counts 21 members, decomposed into 3 sub-families having each 7, 2 and 12 members.



i hope this helped you a little. Do not hesitate to come back to me if you table structure is different or if anythings is unclear. you will find a zip archive attached with the sqlite3 database I used and the query file if you want to test with my data.

Again happy new year.



Cheers



Mathias

19

(4 replies, posted in General)

Hello Jason, (and all other users, long time no see)


The kind of problem you will encounter very often.


What you could do is :

  • load the list of color names in a dataset

  • search the dataset for existing name


Fortunatly, this is something easily done because Delphi is awesome !!


You'll have to check BEFORE the insert is triggered.

To load the color abbreviation into a dataset, simply do something like :

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    ColorList : TDataset; //to store the abreviation list
    ColorAbrev : String;  //to get user input
begin
    //get the user input from the edit field on the form
    ColorAbrev := Form1.edit1.text;

    //load the list of existing abreviation to the ColorList dataset
    SQLQuery('SELECT color_name FROM dbcolors',ColorList);
        //try and match the database field (named color_name here), with the input variable
        if ColorList.Locate('color_Name',ColorAbrev,0) then
            begin
                //warn the user with your own message
            end else
            begin
                //execute the query to add the new record to the color table
            end;
end;

The "locate" function will return -1 if no match is found but with the if / else structure, you are good as well.


I believe the 0 flag at the end corresponds to a case insensitive search, there might be other flags like 'partial find', but you'll have to ask Dmitry.


Hope this helps.


Cheers


Mathias

20

(2 replies, posted in General)

Hello teco049,


MVD is not multithread and the INSERT statement runs in the same memory thread as the main program.


Just added

Application.ProcessMessages;

after the SQL statment and the application stays responsive.


Have a good day


Cheers


Mathias

21

(4 replies, posted in General)

Hello AD1408 and Derek,


I do not post very often these days, but still read the forum when a new question is posted, and this one is interesting.
As Derek said, there are other ways you can do that, and that is by using ini files.


A LITTLE THEORY ABOUT INI FILES


You already have ini files in your application folder :

  • settings.ini

  • tables.ini

and you are free to add some more if you want.


They are basically text files in which you can save infos, settings or whatever you want.
Because they are plain text, I would not advise saving sensible data into them (like passwords for example), but as they are usually saved into user owned folders, they can be used to set different parameters for different users for the same application.


By using commands like (not sure MVD handles that yet)

GetSpecialFolderPath(CSIDL_APPDATA, False)

or

GetSpecialFolderPath(CSIDL_PERSONAL, False)

you can save those preferences into folders like C:\Users\XXX\AppData\Roaming or C:\Users\XXX\Documents and let every user choose it's own parameters.


Anyway, back to MVD now.

There is something a little confusing about ini files in Delphi, and it as that, EVEN IF THE FILE EXISTS ON THE DISK, you have to create it. Yes, said like this, is sounds a little dumb, but you'll understand.


The ini file might already exist somewhere, but in order for MVD to use it, it must be instantiated in memory, that's why it is created as a kind of data container in memory. It can then be accessed for reading, or saving updated data or new data.


The structure of an ini file is composed of :

  • sections, that are enclosed into [ and ], and are like "chapters" if you want, a convenient way to order data by family

  • keys, that actually contain the data you want to read or save


Here is an example (that should not be used, it is just for demonstration) :

[DATABASE]
login=root
password=mypassword

[SERVER]
address=192.168.0.155
port=80

As you can see, there are two sections in this file, because it is clearer for organizing data, but it is optionnal.
keys are associated to their values by the = sign.


Getting the database password in this example is just a matter of reading the password key in the DATABASE section. And it is done very simply by the following command :

IFile.Readstring('DATABASE','password','');

Here is the detail of the instruction :

  • IFile is the name I chose to give to the instance of Tinifile

  • the first part of the parenthesis is the name of the section where to find the info

  • the second part of the parenthesis is the name of the key we want to read

  • the last part of the parenthesis is a default value


Writing data to the ini file is as easy, and is done like this :

IFile.WriteString('DATABASE','password','myNewPassword');

Here is the detail of the instruction :

  • IFile is the name I chose to give to the instance of Tinifile

  • the first part of the parenthesis is the name of the section where you want to writeo

  • the second part of the parenthesis is the name of the key for which you want to change the value

  • the last part of the parenthesis is a value you want to save


NOTE : if the key already exists, it will be updated by overwriting the previous value, and if it does not exist, it will be added to the corresponding section.


Now, to come back to you example, changing labels and retaining those captions after each restart, here is how you could do it :

  • on application start (well onshow form in MVD), check if the ini file exists

  • if the file exists, create it's "copy" in memory

  • read the key value in the corresponding section

  • apply the change to the label

  • Free the memory you located to the ini file


//-01-LOADING THE INIFILE ON SHOW
procedure Form1_OnShow (Sender: TObject; Action: string);
var
    IFile : Tinifile;
    LabelCaption : String;
begin
    //IF THE INI FILE EXISTS, THE SOFTWARE WILL READ IT TO LOAD AN ALTERNATE CAPTION FOR LABEL1
    //IF NOT, THE DEFAULT CAPTION WILL BE DISPLAYED
    If FileExists(ExtractFilePath(Application.ExeName)+'labels.ini') then
        begin
            IFile := TIniFile.Create(ExtractFilePath(Application.ExeName)+'labels.ini');
                try
                    LabelCaption := IFile.Readstring('LABELS', 'label1','');
                    Form1.Label1.Caption := LabelCaption;
                finally
                    IFile.Free;
                end;
        end;
end;

Saving a new label is done nearly in the same way, with a little difference : if the ini file does not exist, it will be automatically created, and if it already exists, it will be updated, so no need to check it's existence.


//-02-WRITING NEW DATA TO THE INIFILE
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    IFile : Tinifile;
    NewLabelCaption : String;
begin
    NewlabelCaption := Trim(Form1.Edit1.Text);
        if Length(NewlabelCaption) > 0 then
           begin
               IFile := TIniFile.Create(ExtractFilePath(Application.ExeName)+'labels.ini');
                   try
                        IFile.WriteString('LABELS', 'label1',NewlabelCaption);
                        Form1.Label1.Caption := NewlabelCaption;
                    finally
                        IFile.Free;
                    end;
           end
        else if Length(NewlabelCaption) = 0 then
            begin
                ShowMessage('New text is empty, current caption will not be changed');
            end;
end;

I attach the example to my answer (MVD 5.5 trial version, I have not renewed my licence yet).
When this example application starts, there is not ini file in the folder, but as soon as you type in a new caption, the ini file is created and the data saved.
When you restart the application, the default caption is changed to the saved value.
This example is very simple, but you can use as many sections and keys you want.


One last thing : ini files must be readable, so they can not be stored under "program files" folders.


Have a good day both of you and talk to you soon


Cheers



Mathias

Hello again smile


Regarding SQLite, it is a question that it is generally single access that makes it incompatible with multi-threading.
There is an option in the sqlite "engine" where you could specify whether the LockingMode parameter is either "Exclusive" or "Normal", but it is not a very good idea to launch a second query (or transaction) when the previous access has not completed it's task.


As per MySQL (or other database engines), they can support multiple connections and operations at once, BUT, it has to be in different "sessions". A single user has to wait for a task to complete before he can trigger another one, but different users can trigger multiple tasks together, the database engine will handle the priorities on itself.


Finally, multi-threading is a complicated subject in Delphi (and other languages as well). Took me weeks to understand what it was about and months to have it working in my applications (notice that I did not say "to do it correctly" because I am far from there yet).


I do not know which connection and query components MVD uses (Firedac, Mydac...). Some are thread safe, some are not. Furthermore, a general rule of thumb is that every thread should create it's own connection for it's needs, and I am not sure you could do that in MVD : the connection is opened once you launch the application, and I am not sure you can close it if you need.


If Dmitry implements multi-threading in MVD, that would be awesome, but I suppose he has other priorities (and that is very much understandable), and such a feature would be time consuming and probably only useful for very little users.


Sorry my friend, I don't see it coming anytime soon in MVD.


Take care


Math

Hello prahousefamily,

Multi-threading is what you are looking for. Unfortunately, I do not think that MVD supports this feature (but I stand to be corrected).


To achieve what you describe, you need to be able to launch processes in separate memory spaces (threads), so that they run alongside each other without colliding, and without blocking the main thread which usually runs the graphical user interface.


Ever noticed how a long looping process can block the software ? That's why we use "application.processmessage" in the loop to keep a little control over the GUI. But when you click somewhere during a loop (drag the form for example), this halts the process until you let go.


This is what multi threading is for : running processes without freezing the UI.


Now, in your example, if you want to speed-up queries, (which can not run multi-threaded with Sqlite anyway), try to use SQL Transactions, this will speed-up things significantly.

transaction Start
SQL1
SQL2
...
SQL3
transaction commit

I can't give you the exact code because I am at the office, but you should find easily on the forum.


Cheers


Mathias

24

(5 replies, posted in General)

Hello Cujos and Derek,


I had a look at Derek's submission and, as always, it's spot on and without a single line of code !! Nice job


Everyone on this forum knows that I'm a big fan of complicated solutions and lines of code are like poetry to me.
The code bellow was a saturday morning coffee challenge, and might interest you if you want to know what happens in MVD but, again, Derek's solution is THE solution.

Oh, by the way old friends, I don't post often but read the forum every day smile


What the Tablergrid does not show but understands from what Derek as set up is :


SELECT DISTINCT
     "document"."record_count" as "document.record_count",
     "document"."docdate" as "document.docdate",
     "doctype"."doctype" as "doctype.doctype",
     "document".id,
     "document"."id_doctype" as "document.id_doctype"
FROM
     "document"
LEFT OUTER JOIN "doctype" ON "document"."id_doctype"="doctype".id
ORDER BY
     "document"."record_count" ASC


What does the "hidden search" button do when you select an item in the combobox ? It's filtering the SQL query that gets the complete list of document from the database (the query above).


To filter this, when you change your selection in the combobox, MVD injects a line in the above query :

When the combobox says "Invoice", the filtering line looks like :

WHERE document.id_doctype = 1

and is located just before the "ORDER BY" line. (1 is the id of the Invoice doc type in the database).


When the combobox says "Receipt", the filtering line looks like :

WHERE document.id_doctype = 2

And when the combobox is empty, there are many possibilities.
The simplest one is just to remove the "WHERE" line of course,

but this is equivalent to :

WHERE document.id_doctype = 1 OR document.id_doctype = 2 
--the filter has to be repeated, document.id_doctype = 1 OR 2 does not work

and also equivalent to

WHERE document.id_doctype IN (1,2)

And finally, if you disconnect the "hidden search" button from combobox by removing the incremental search parameter in the combobox, you could replace all this, easy and efficient, by this, heavy and unpractical code smile

procedure Form1_ComboBox1_OnCloseUp (Sender: TObject); //fires each time the combobox is closed
begin
    if Form1.ComboBox1.ItemIndex = 0 then //if first item of combobox is selected
        begin
            Form1.TableGrid1.dbFilter := '1 = 1';
            //Form1.TableGrid1.dbFilter := 'id_doctype = 1 OR id_doctype = 2';  //EQUIVALENT
            //Form1.TableGrid1.dbFilter := 'id_doctype IN (1,2)'; //EQUIVALENT
            Form1.TableGrid1.dbUpdate; //this executes the filtering
        end
    else if Form1.ComboBox1.ItemIndex = 1 then //else if second item of the combobox is selected
        begin
            Form1.TableGrid1.dbFilter := 'id_doctype = 1';
            Form1.TableGrid1.dbUpdate;
        end
    else if Form1.ComboBox1.ItemIndex = 2 then //else if third item of the combobox is selected
        begin
            Form1.TableGrid1.dbFilter := 'id_doctype = 2';
            Form1.TableGrid1.dbUpdate;
        end;
end;

I've made you a little package, just if you are curious smile


Again, as I already said, Derek's solution is perfect and simple.
Mine is complicated and won't even work anymore if you had another document type to you collection. It will have to be adapted in order to work, where Derek's will still work.

This was just for "academical interest"... well, at least it interests me smile


Cheers to all and wish you a good week-end


Math

25

(1 replies, posted in General)

Hello prahousefamily,

You have to iterate through all the form component and checking their type before setting font styles, colors and so on


You can do that like so :


procedure Form1_OnShow (Sender: TObject; Action: string);
var
    i : Integer;
begin
    for i := 0 to  Form1.ComponentCount - 1 do
        begin
            if Form1.Components[i] is TdbEdit then TdbEdit(Form1.Components[i]).Font.Style := fsBold;
            if Form1.Components[i] is TdbMemo then TdbMemo(Form1.Components[i]).Font.Style := fsBold;
            if Form1.Components[i] is TLabel then Tlabel(Form1.Components[i]).Font.Style := fsBold;

            //and so on....
        end;
end;

This example was given changing a font style to bold, but this works for color, size etc...


For a complete list of components and how to iterate through them, have a look here on the forum :

http://myvisualdatabase.com/forum/viewtopic.php?id=3053
the lasts posts are very interesting


Hope this helps


Cheers


Mathias