Subject Re: Connection string in ADODatabase
From Gaetano <gaetanodd@hotmail.com>
Date Thu, 22 Oct 2020 06:40:35 +1000
Newsgroups dbase.getting-started


Dear Mustansir,

You are welcome. There is a section in Ken's tutorial that explains how
to do this. The base datamodule can also be used to stored functions
that you may need frequently in your application, e.g. to create
calculated fields (although not on ADO rowsets since they are mostly
read-only).

The coding is very short for your BASE.CDM:

* END HEADER -- do not remove this line
class BaseCDATAMODULE of DATAMODULE custom


    this.ADODB1 = new ADODATABASE(this)
    with (this.ADODB1)
       left = 19.0
       top = 135.0
       connectionstring = "your connection string"
       active = true
    endwith


//making this function available to add calculated fields

function calcField(cName,nLength)
        local oField
        oField = new field()
        ofield.fieldname := cName
        oField.length := nLength
        this.rowset.fields.add(oField)
        return

================================

Then you can create a SAMPLE.DMD file that inherits from this BASE.CDM
file and adds the ADOquery:

================================

** END HEADER -- do not remove this line
//
// Generated on 17/09/2020
//
class CUSTOMERDATAMODULE of BASECDATAMODULE from "Base.cdm"
    with (this.SO2ADO1)
       left = 26.0
       top = 43.0
       width = 115.0
       height = 112.0
    endwith

    this.ADOCUSTOMER1 = new ADOQUERY(this)
    with (this.ADOCUSTOMER1)
       left = 162.0
       top = 60.0
       width = 115.0
       height = 146.0
       database = form.adodb1
       connected = true
       SQL  = "<any SQL statement including params>"
       active = true
    endwith

        this.rowset = this.ADOCUSTOMER1.rowset
                
endclass

=================


finally, you can use the above either in a form/report by dropping it on
the designer surface, just be aware that the connection to the server
will time-out, so in a form, you may need to reactivate the connection
before using the ADOQUERY objects because you cannot control when the
user will click the button, example:

//reestablish database connection
form.adodatabase1.active=null
form.adodatabase1.active=true

You can even use the DMD in a simple PRG routine:

SET PROCEDURE TO SAMPLE.DMD ADDITIVE
oDMD = new CUSTOMERDATAMODULE()

With the above 2 lines, now you have a rowset and a database object that
you can use in a PRG routine:

oDMD.adodb1.executeSQL("<any database-level SQL statement e.g. to create
tables>")

or

oDMD.rowset.first()
oDMD.rowset.count()

...etc.

Cheers,
Gaetano.

On 20/10/2020 22:48, MUSTANSIR GHOR wrote:
> Dear Gaetano
 >
> Thank you.
 >
> Your solution is perfect
 >
> So far I have not used datamodules dmd or cmd. But I will work on it
 >
> Thank you once again
 >
> Mustansir
 >
>
> Gaetano Wrote:
 >
>> On 19/10/2020 20:31, Mustansir Ghor wrote:
 >>> Dear all
 >>>
 >>> Can we put connection string of ADODatabase in an INI file
 >>>
 >>> Regards
 >>> Mustansir
 >>>
 >>
 >>
 >> Hi Mustansir,
 >>
 >> You can (see example hereunder), but it is not a very secure thing to do
 >> because the password is stored as plain text in an INI file. Anyone with
 >> access to the folder can get to see it.
 >>
 >> My preferred way is to create a custom datamodule (.CDM) that only has
 >> the database connection details. Then every query/report/form that needs
 >> the database connection just requires a simple datamodule to add the
 >> query details (possibly with params to change selection at runtime).
 >>
 >> Once compiled, the password becomes invisible in the EXE file.
 >>
 >> Should you want to change the connectionstring later, just update the
 >> CDM file with the new database connection string, compile it and drop
 >> the CDO file in the same folder as the EXE file. The change will be
 >> effective instantly to all users from that moment onward and it will
 >> have priority over the CDO object inside the EXE file for program execution.
 >>
 >> Cheers,
 >> Gaetano.
 >>
 >> [Connections]
 >> 1=ADOinINI
 >> [ADOinINI]
 >> Provider=MSDASQL.1
 >> Password=<password>
 >> Persist Security Info=True
 >> User ID=<user name>
 >> Extended Properties="Driver=MySQL ODBC 8.0 ANSI Driver
 >> SERVER=127.0.0.1
 >> UID=<user id>
 >> DATABASE=<database name>
 >> PORT=3307"
 >>
 >