Subject Re: modifying database path
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Mon, 8 Nov 2021 11:28:28 +0530
Newsgroups dbase.getting-started
Attachment(s) a_ledger.dmd

Good Morning Mustansir,


> The below two statements is well understood. but I am finding difficult to understand  where to place them.
 >
> this.session = new session()
 > //new session
 > this.session.addalias("muneem","DBASE","Path:"+this.unc+this.direct)
I usually execute the above statements at the start of main form once
the required company is selected.
Mcompany is declared as a public object (Not a clean option but it was
designed when I had started learning dbase )
So it is accessible throughout the program.

> I have mainform which has menu file associated with it. From there other forms get opened. In these forms I have database set  with path defined in the ini. I even use them while designing the forms. In the Below is the example PAYROL needs to have different path based on company selection. How this can be made possible
Paths stored in ini cannot be modified programitically as far as I know.
>
> class payForm of FORM
 >     with (this)
 >        onOpen = class::FORM_ONOPEN
 >        height = 27.3636
 >        left = -3.7143
 >        top = 0.3182
 >        width = 190.2857
 >        text = ""
 >     endwith
 >
>     this.DPAYROL = new DATABASE(this)
 >     with (this.DPAYROL)
 >        left = 86.0
 >        databaseName = "PAYROL"
 >        active = true
 >     endwith
>
>     this.QPAY = new QUERY(this)
 >     with (this.QPAY)
 >        left = 97.0
 >        database = form.dpayrol
 >        sql = "SELECT * FROM MPAYROL"
 >        active = true
 >     endwith

This needs change. You WILL HAVE to opt in for datamodule.
The main reason for that is that the user bde alias so declared does not
show in navigator and hence cannot be accessed by the form directly. And
as far as I know memory variables used at the time of designing are
evaluated by the designer and are replaced by actual values which makes
them static. And defeats the purpose.

There must be better methods but the only solution that came to my mind
was to use datamodules. I never open them in designer. Notice the date
in datamodule I have attached. It was created long after that.

I simply make a copy of the existing datamodule file rename it, change
the class names, queries etc and I have a new datamodule.

I have developed a habit of testing queries in heidisql.

You will also notice some memory variables in sql because the table name
is also variable.

Another thing for you is my sample code used mcompany.db and dmd
contains mcompany.adodb

Most of the tables are on MariadB hence AdoDatabase but for some tables
(which have still not been moved to Mariadb) I use user bde alias.

Regards
Akshat


** END HEADER -- do not remove this line
//
// Generated on 25.02.2017
//
class a_ledgerDATAMODULE of DATAMODULE

        this.ledger = new ADOQUERY(this)
   with (this.ledger)
      left = 291.0
      top = 22.0
                database = mcompany.adodb
//      sql = 'Select *,month(ddate) mmonth from d_ledger where ddate between :fr_date AND :to_date order by ddate,recno'
      sql = 'select recno , ddate , month(ddate) mmonth,ac_name , dr , cr , if(runn_bal >0, "Cr","Dr") Bal_type , abs(runn_bal) balance,narration , jour_vouch,receipt from (SELECT recno, ddate , Ac_name , dr , cr, sum(cr-dr) over (order by ddate,jour_vouch,recno rows BETWEEN unbounded preceding and current ROW) runn_bal,narration , jour_vouch,receipt FROM d_ledger) as calc_ledger where ddate between :fr_date AND :to_date '
                params["fr_date"].value=dtos(mcompany.byear)
                params["to_date"].value=dtos(mcompany.lyear)
                requestlive = false
      active = true
   endwith

   this.ledger2 = new ADOQUERY(this)
   with (this.ledger2)
      left = 291.0
      top = 22.0
                database = mcompany.adodb
      sql = 'Select * from d_ledger order by ddate,recno'
                requestlive = false
      active = true
   endwith

   this.ldate = new ADOQUERY(this)
   with (this.ldate)
      left = 291.0
      top = 22.0
                database = mcompany.adodb
      sql = 'SELECT max(ddate) m_ddate from  (select ddate from day'+mcompany.fyear +' union select ddate from jour'+mcompany.fyear +') transxx'
//      SELECT max(ddate) m_ddate from  (select ddate from day19 union select ddate from jour19) transxx
                requestlive = false
      active = true
   endwith

endclass