v_pozidis wrote:

Hi all, I face a problem which is the following, I have added in my sqlite database new columns and new tables which in some cases are Related. How can I add from my old database the records to the new one using script code ? When I  copy the old sqlite database it crash in the folder of the program. So my idea was to fix a small program that will add in the  old sqlite database  the new columns and tables with the relations.  Or is there another way? In any case its a good time to learn how to convert our database with new tables and columns.
Please your help.

I'm thinking you already deployed your application to your client or you are using it.

I handle problem like this by saving a version number to the database (something like `settings`.`dbVersion`). Then, I created script to handle the alter queries. You need to carefully document each changes in your database

From the script.pas, I am indicating a CONST variable e.g.

CONST DBVER = 2; // this is the current version on the development database. I always change this manually on the dev database to avoid error when developing.

Then, executing the db revision checking on the begin end. area

procedure DBREVISION();
var currVer: Integer;
begin
    currVer := := SQLExecute('SELECT COALESCE(dbVersion, 0) AS currVer FROM settings WHERE 1 LIMIT 1');
    if currVer < DBVER then
    begin
        if DBVER < 1 then
        begin
            // alteration of the version 1
        end;
        if DBVER < 2 then
        begin
            // alteration of the version 2
        end;
        SQLExecute('UPDATE `settings` SET dbVersion='+IntToStr(DBVER));
    end;
end;

begin
    DBREVISION;
end.

I hope this can give you an idea on how handle your problem

I just check it out and trace back the problem. I was just lucky to spot it. smile Years of experience w/ MVD helps too.

Try changing the table for the query setting of the grid from newentry form and set it to customers

54

(1 replies, posted in General)

Did you try checking the KeyPreview Option? This enables me the Onkeypress on a form.

55

(3 replies, posted in General)

pdtonks77 wrote:

Hi there,
I have been using My Visual Database for a couple of years now on my windows laptop and got on well with it.
but now i have brought myself a macbook and just wondering if there is a way to run this software on there?

I think the only way for you to make it work is to make it dual boot or install windows on a virtualbox

56

(4 replies, posted in General)

k.krause wrote:

Hello,

I am looking for a formula for a calculated field that determines the age of a person from the difference between the date of birth and the current date. I tried a solution from Derek but his solution does not work with Mysql. Can someone help me?

Try this one

TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())

57

(30 replies, posted in General)

opercap wrote:
brian.zaballa wrote:
opercap wrote:

Only now i saw that you are the original sender of this message. My apologies.



Can you, please, send it to me too? It will be very usefull as i have an old users/password table wich i want to import to Role system.


Big Thanks

Best Regards

My email: classa@sapo.pt.

Sure, i'll send it to you


That would be amazing. Thanks a lot.

I already sent it. You can also refer to this topic

http://myvisualdatabase.com/forum/viewtopic.php?id=7915

58

(2 replies, posted in General)

If you are using the built in user-role functionality of MVD, then I think retrieving is not possible, however you can do something like setting new password using a secret question and answer. Here's my prototype for it.


User: admin
Pass: admin

59

(30 replies, posted in General)

opercap wrote:
brian.zaballa wrote:
gonpublic2k wrote:

Hi, could you e-mail me this please??

leggin2020@gmail.com

I'd appreciate it , thanks!!!

Sent.

Only now i saw that you are the original sender of this message. My apologies.



Can you, please, send it to me too? It will be very usefull as i have an old users/password table wich i want to import to Role system.


Big Thanks

Best Regards

My email: classa@sapo.pt.

Sure, i'll send it to you

60

(3 replies, posted in General)

sparrow wrote:

Hi brian.zaballa

In one project, I came across such a situation.
The checkbox mark is a dictionary on one of the tables, swapped JOIN in the query that built the MVD.
In your test case with an error (http://myvisualdatabase.com/forum/viewtopic.php?id=7339), specify
the check-dictionary on the "department" table. JOIN changes.
But replacing with a newer version has its advantages. 


The first time I saw the action of a checkbox. )

Oh, that solves the problem. Thank you for sharing. Checking Dictionary checkbox for the department solves the error. Kudos!

I'm using the latest(3.36.0) sqlite version on my MVD projects. Looking forward for the succeeding releases. Hoping they include UPDATE query alongside JOIN. smile

61

(3 replies, posted in General)

teco049 wrote:

Hello to all.


the included sqlite3.dll from MyVisualDatabase is from 2014.


In which folder can I place a newer version of this .dll that it will be copied to all compiled projects? After every compilation I get the old version back in the project folder.


An IT Newsletter has written that the newer versions of SQLITE have a speed improvement. More speed is a good thing.

You can put it under the installation folder of your MVD. In my case it is under "C:\Program Files (x86)\My Visual DataBase"


just make sure to have a backup of the current sqlite, you might run into an error when accessing 2 level of parent of a child in a report using the new version of sqlite. I hope you dont have this kind of record in your project/application. This error was not been fixed.

Here's my post with regards to the problem
http://myvisualdatabase.com/forum/viewtopic.php?id=7339

62

(13 replies, posted in General)

Can you elaborate your calculation?

63

(13 replies, posted in General)

Here's a workaround I've made in my project that act like numberbox. It doesn't have button thou.

Check your grid Settings, make sure you are not loading any data from it if you are using search button to load your data, vice versa. It's either you are missing a filed on Grid Settings or in the Search Button. Check it out and go back with OnChange of the grid.

Other possible cause, if you are sure your grid has no problem in field count, then check other grid, on your appliication, sometimes, copy-paste of grid will cause you error such as this when you didn't remove/change that OnChange event of the copied grid.

65

(5 replies, posted in FAQ)

rkpatro wrote:

I have purchased 4.x and 5.x .the license for both is also with me but but these old ver are not available .How can i download these pl help.

Here. I have the 5.5 version.
https://www.dropbox.com/s/nlita5g2w75w7 … 5.exe?dl=1

TNxNumberColumn will change the format displayed on the grid, you can put it in the OnChange Event of the grid.


To give you an idea, I've created and using a procedure(not with date thou but the idea is the same) in my projects that Format display of a field(a calculated one) into a money format

{
    Format Column in a Grid
    Called in onChange Event of a Grid

  FormatMoneyGrid(main.grdOR, 1);
}
procedure FormatMoneyGrid(TblGrid: TdbStringGridEx; column: integer; formatFooter:Boolean=True);
begin
    TNxNumberColumn(TblGrid.columns[column]).FormatMask := '#,##0.00';
    TblGrid.columns[column].Alignment := taRightJustify;
    if formatFooter then
        TblGrid.Columns[column].Footer.Caption := FormatFloat('#,###,##0.00', TblGrid.Columns[column].Footer.FormulaValue);
end;

Then I call it on Onchange event

procedure frmObligationToPay_grdOBWB_OnChange (Sender: TObject);
begin
    FormatMoneyGrid(frmObligationToPay.grdOBWB, 4, True);
end;

I'm using it when MVD didn't automatically formatting it on the grid. You might want to use it in the future when you are having trouble displaying calculated currency on the grid.:)

67

(6 replies, posted in Script)

daamoucheacil wrote:
brian.zaballa wrote:

Stock monitoring can be done in MVD. There are many way to achieve it.

You can elaborate more on what you are working on so that someone might help you.

I want to create a form to add orders and a form that contains a table that contains the quantity of goods in the store, the purchase price and the selling price. When adding an order to the customer, the quantity of the goods in stock, the purchase price and the selling price, and when saving the order, the quantity purchased by the customer automatically decreases from the store.  I apologize for the poor expression because I am not good at English

Again, there are many ways to do it.


For the purchase price and selling price, in my experience, saving this details will only give you estimated markup(when the price differ on your next stock or purchase transaction of the goods/item and the store has remaining stock of it). You don't want your application to check the quantity of each stock transaction you made and tag each sales/pullout of the item to where stock transaction you get it from.


If you are trying to compute exact income of the store, it is better to just have a cashflow module that will handle it.
Saving all expenses and sales/collections.


Then,
Income = Total(Sales/Collections) - Total(Expenses, this includes the purchase of the goods/stock basically).


Just charge your client on the additional module(cashflow). 3:)

68

(2 replies, posted in Script)

I'm not sure to what you are trying to do here but try changing

Form1.Button3.Click;

to

Form1.Edit4.Clear;
if Form1.ComboBox2.dbItemID > 0 then
    Form1.Edit4.Text := SQLExecute('SELECT Prixachat FROM PORTEGRIS WHERE id='+IntToStr(Form1.ComboBox2.dbItemID));

69

(2 replies, posted in General)

Wayne wrote:

Hi All,

I have a question about Windows 11, will our programs still work on windows 11?

Regards
Wayne

Using windows 11 now(dev-insider) and all is well.

Sometimes, when working with bunch of record/row in my database, complex calculated field make my application slow, even hang sometimes especially for the users with a low-spec computers. But then I use it(calculated) in some cases.


Trigger is a piece of code inside your database. you have to code it manually,
I'm putting some of the business logic inside the database using trigger., think of it as an auto-query triggered before/after insert, update and delete. with values NEW.[field] and OLD.[filed] which you can use to update/insert/delete other record from other table of the database.


You can search for "trigger in sqlite" for that.


Just be careful for updating fields on a table using MVD will delete the trigger or sometimes prevent updating it. My way of doing trigger is to have a file and save all triggers there. Here's a sample of my trigger to give you an idea

DROP TRIGGER IF EXISTS "main"."debit_post";
CREATE TRIGGER "main"."debit_post"
AFTER UPDATE OF "dbt_is_posted"
ON "debit"
WHEN ((OLD.dbt_is_posted=0) AND (NEW.dbt_is_posted=1))
BEGIN        
    DELETE FROM commitment_payment WHERE id_commitment in (SELECT DISTINCT(id_commitment) FROM debit_commitment WHERE id_debit=NEW.id);

    INSERT INTO commitment_payment(
        id_commitment, id_account, id_fms_budget, amt, dtime, id_debit    
    )
    SELECT 
        debit_commitment.id_commitment,
        fms_budget.id_account,
        fms_budget.id,
        CASE WHEN (debit_commitment.fms IS NOT NULL ) THEN 
            SUM(debit_commitment.dbtc_amt)
        ELSE 
            debit.dbt_amt
        END
        as amt,
        debit.dbt_dtime AS dtime,
        debit.id as dvid
    FROM debit_commitment
    LEFT JOIN debit ON debit.id=debit_commitment.id_debit
    LEFT JOIN fms_budget ON fms_budget.id = 
        CASE
            WHEN (debit_commitment.id_fms_budget IS NULL ) THEN debit_commitment.fms
            ELSE debit_commitment.id_fms_budget
        END
    WHERE id_debit=NEW.id
    GROUP BY CASE
        WHEN (debit_commitment.id_fms_budget IS NULL ) THEN debit_commitment.fms
        ELSE debit_commitment.id_fms_budget
    END, fms_budget.id_account;
    UPDATE bdcm SET dbt_is_posted = 1 WHERE id_debit = NEW.id;
END;

Then before updating field using MVD, I am deleting all triggers created then re-run it(copy-paste). I'm using Database manager(Navicat) when creating and updating triggers. Save so much time. There are free software too like DBeaver, DB Browser, etc. I think some of then can create trigger too with just a few clicks. But knowing how to construct it manually is a great advantage.


You can put aside trigger for now and try to study it first.

71

(6 replies, posted in Script)

Stock monitoring can be done in MVD. There are many way to achieve it.

You can elaborate more on what you are working on so that someone might help you.

I rarely use calculated fields on the applications I am working on. I rather use trigger or save-it-in-database. This might be a workaround on what you are looking for.


I also updated the filter date, since entering a record that will fall in the last day of the month will not be displayed on the search.
I just added 0.99999 (less than a millisecond/second for a day)


Try changing this line too

frmDetail.a_ampm.Text := FormatDateTime('AM/PM',frmDetail.dtpDate.DateTime);

to

frmDetail.a_ampm.Text := FormatDateTime('hh:nn AM/PM',frmDetail.dtpDate.DateTime);

It might be the one you are looking for. I just go with what your calculated fields do.


PS: I didn't change the report. just the saving part. You might want to just update it.

73

(11 replies, posted in General)

I found other fix to your problem, without using the function I gave, you can have extra sql query to get what you want

SQLExecute('PRAGMA case_sensitive_like=OFF');
If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
End;
SQLExecute('PRAGMA case_sensitive_like=ON'); // make sure to turn it on

74

(11 replies, posted in General)

if you are using this code

If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
      Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
      End;

Then, you might want to change it to

procedure Save_Button_OnCLick();
var
   _chk: String;
begin
   _chk := SQLExecute('SELECT COALESCE(Col1, '''') FROM MyTable WHERE Col1 LIKE "' + Form2.Edit_Col1.Text + '" LIMIT 1');
   if CheckExactString(Form2.Edit_Col1.Text, _chk) then
   begin
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
   end;
end;

75

(11 replies, posted in General)

procedures => do algorithm and/or process
function => will do algorithm and/or process then return a value

That function returns a boolean value so can use it on a condition like

if CheckExactString('Flag', 'Flag') then
begin
  // do something
end;

or


// If not the same
if (Not CheckExactString('Flag', 'flag')) then
begin
  // do something
end;