Subject Re: Http: Response into my local software
From Mervyn Bick <invalid@invalid.invalid>
Date Sun, 17 Sep 2017 12:08:30 +0200
Newsgroups dbase.getting-started

On 2017-09-17 9:54 AM, Michael wrote:
> Hi Guys,
>
> I have been looking for an easy way to send and retrieve data from a hosted server mysql table and process locally. I have been studying running an Mysql database but I see too many steps and parameters to make it functional for my software.
>
> Recently I discovered the OLEautoclient(ADODB.stream) used by google to retrieve images through dbase.
>
>        // Google API Call -- don't change this address:
>        cURL = "https://chart.googleapis.com/chart"  // POST do not use ?
>        // max size is 520?
>        cPar = "chs="+nSize+"x"+nSize+"&cht=qr&chl=" + cStr  // image format 520x520
>        objHttp = new OleAutoClient("Msxml2.XMLHTTP")
>        objHttp.open("POST",cURL,false)
>        objHttp.setRequestHeader("Content-Type", "application/x-www- form-urlencoded")
>        objHttp.send(cPar)
>        if not empty(objHttp.responseText)
>            objStream = new OleAutoClient("ADODB.Stream")
>            objStream.Open()
>            objStream.Type = 1 // adTypeBinary
>            objStream.Write(objHttp.responseBody)
>            objStream.SaveToFile(cImg,2) //adSaveCreateOverWrite = 2
>            objStream.Close()
>        else
>         *? "Login Failed! " + datetime()+cImg
>        endif
>
> I already have a database driven website and scripts in php for a portal I have set up for my clients. I just want to update and recieve some data when I run my local software.
>
> I can pass parameters up to the server, so have no problems to run php pages that can create and insert records. The problem I have is how can I retrieve data from an executed script/webpage back down that I can read from my software. I can read the physical page but not the executed result.
>
> Does anyone know how I can receive the results?
>
> The ADODB.Stream is great for receiving graphical binary data but what commands and or does anyone have any source to show how I can receive the results of my executed sql query on the web.
>
> Please help!

It depends on which version of dBASE you are using but since dBASE Plus
8  access to SQL servers via ADO using ODBC has been possible.
Initially the ADO components were "buggy" but they have improved such
that in dBASE 11.3.1 they are very useable.

It goes without saying that you should only experiment on copies of your
production tables until you are sure everything is working properly.

You will need to download and install the ODBC driver for MySql.  If you
are using a 64-bit version of Windows you apparently need both the
64-bit and 32bit ODBC drivers installed.

If you are using a dBASE version earlier than dBASE Plus 8 you can
access MySQL via the BDE but in that case you will need an earlier
version (3.51 instead of 5.3) of the ODBC driver


I use Firebird as a SQL server and this allows me to set up a read-only
connection for fetching data and a writeable connection for inserting or
updating data.


A snippet from one of my ADO forms is

   this.ADODATABASE2 = new ADODATABASE(this)
    with (this.ADODATABASE2)
       left = 40.0
       connectionString = "DRIVER=Firebird/InterBase(r)
driver;DBNAME=d:\firebird_databases\dBASE_Examples.fdb;DIALECT=3"
       loginString = "mervyn/******"
       active = true
    endwith

    this.ADODATABASE1 = new ADODATABASE(this)
    with (this.ADODATABASE1)
       left = 2.0
       connectionString = "DRIVER=Firebird/InterBase(r)
driver;DBNAME=d:\firebird_databases\dBASE_Examples.fdb;DIALECT=3READONLY
=true"
       loginString = "mervyn/******"
       active = true
    endwith

    this.ADOQUERY1 = new ADOQUERY(this)
    with (this.ADOQUERY1)
       left = 25.0
       database = form.adodatabase1
       connected = true
       sql = "select  firstname||', '||lastname as comp_name,iif(salary
= 0,'n','x ') as xx from mbemployees m order by upper(lastname) desc"
       maxRows = 4 //inserted by dBASE but apparently ignored
       requestLive = false
       active = true
    endwith



MySQL doesn't provide for read-only and writeable connections for the
same user so you will only need one ADOdatabase object.

The connection string for MySQL is

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

As this contains the username and password you won't need to populate
the loginString for the ADOdatabase object.

To examine data you can use a grid with it's dataLink property set to
the ADOquery's rowset.

To Insert or Update build the Insert or Update statement into a variable
and use the ADOdatabase object's executeSQL() method to implement it.


cmd = "insert into tablename (fieldlist) values (valuelist)"
form.ADOdatabase1.executeSQL(cmd)


cmd = "update tablename set field1 = 34,field2 = 'whatever' where
primary_key_field = 12"
form.ADOdatabase1.executeSQL(cmd)

Mervyn