OLE – Remote Database

Whilst the SQLite capability of this access is powerful, it is by its very nature “local”. So I became interested to see if I could use OLE to access a “remote” database.

Since I did not have access to any remote databases I decide to install a full database service application, specifically I downloaded and installed the MAMP package for Windows, backed-up my www.ripe.com web site and loaded the back-up site to the MAMP installation on my PC.

This gave me a database  that I could access as if it were remote, except it was at 127.0.0.0 i.e. hosted locally.

Here is a script that I used to:

• connect to the database
• run a query on a remote table
• create a local (SQLite) copy of the remote table

 

newscript
dbclose
dbopen ("memory")
dim dbCON = createobject ("ADODB.Connection")
dim dbRS = CreateObject("ADODB.Recordset")
dim myItem
dbCON.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;UID=root;PWD=root;DATABASE=wordpress;OPTION=3"
dbCON.Open ()
dbRS .open ( "select * from wp_options ", dbCON )

dim index = 0
freestring = " create table myTable ( "
FOR EACH myItem in dbRS.fields
IF ( index ) then
freestring = freestring & " , "
END If
index = 1
freestring = freestring & sqlIdentifier(myItem.name) & " text"
NEXT myItem
freestring = freestring & " ) "
sql = freestring
DO while ! dbrs.EOF
index = 0
freestring = "INSERT into myTable values ( "
FOR EACH myItem in dbRS.fields
IF ( index ) then
freestring = freestring & " , "
END If
index = index+ 1

freestring = freestring & sqlString ( (myItem.value) )
NEXT myItem
freestring = freestring & " ) "

sql = freestring
dbRS.movenext
LOOP
dbRS.close