ADO Object information and examples.
Go to the ADO Connection String Walkthrough for details on creating an ADO ADO Connection.
Working with ADO objects in a Web App with Apache
When you click and drag an ADO based table from the Navigator to a Form or Report designer an adoQuery is automatically created. It's requestLive property is set to true internally this ensures that the data for the query is automatically editable. You can use the adoQuery.rowset.autoEdit property to make the ADO rowset non-editable by default, just as you would in a BDE Query object.
parameters Property (How to use named parameters)
parameters Property (How to use numbered parameters)
Using MySQL data with Non-Null Zero dates
What are the differences between adoTable and adoQuery
objects?
The biggest difference is the fact that an adoTable does NOT go through
relational engine. This means a couple of things.
- In some cases the adoTable rendering is faster than adoQuery because
there is no sql satement that has to be interpreted.
- The adoTable does not have all of the properties of the adoQuery object
such as: CommandTimeout, ExecuteOptions, MasterSource, MaxRows,
and Parameters
In order to use the adoTable object you must use the Component Palette under the 'ADO Access' tab.
Click and drag the adoTable object to the designer.
Then set the databaseName property to the ADO
Connection Alias, or enter the connection string directly into the connectionString property of the adoTable.
Then enter the table name in the tableName property.
Then set active to true.
At this point you can use the adoTable.rowset just as you would an adoQuery.
When using a Stored Procedure object it is important to note that the syntax of the stored procedure is proprietary to the database; Unlike the .sql property of the adoQuery object (which is interpreted through dBL (dBASE Language)).
You can use an existing Stored Procedure that was already created on the database server or you can use the adoDatabase.executeSQL method to create a new Stored Procedure.
(See adoDatabase object: section below for an example)
In order to use the adoStoredProc object you would use the Component Palette under the 'ADO Access' tab.
Click and drag the adoStoredProc object to the designer.
Set the databaseName property to the ADO Connection Alias or enter the connection string directly into the connectionString property.
Enter the procedureName of the procedure you will be using.
(IMPORTANT NOTE: If your procedure was not created with default values in the parameters you will not be able to set active to true at this point. Instead you can use the form's onDesignOpen event to set the parameter value and therefore be able to view the data and use the Field Palette to grab fields from the procedure to add them to you form.)
MySQL example (with onDesignOpen)
The adoDatabase is not necessary for use with adoQuery,
adoTable or adoStoredProc objects like the BDE Query objects (in most
cases - unless all the tables in use are in the current directory).
However, there are some useful functions/methods of the adoDatabase object
that will help you get more out of ADO.
For example, getting Schema information on tables and databases and being
able to execute a non select.. SQL statement to create, modify etc.. most
database server objects (tables, databases etc...).
Using getSchema to get a list of tables
Using executeSQL to create a new stored procedure
Using errors to get the Database Server Error information.
When deploying an application that uses ADO objects to connect to other databases, the most common question is how to make the Connection Alias available to a third party who does not have dBASE PLUS 8 installed.
There are two ways to do this.
1 - use the connectionString
property of the ADO Data Object to set up the connection. This takes out
the need for the databaseName property all together.
The connectionString property can be set in the
inspector at design time or in the source editor.
each element of the connectionString is separated
by a semi-colon. For example, here I am connecting to a PostGres database:
q = new adoQuery()
q.connectionString = "Driver=postgreSQL ODBC Driver(ANSI);Server=127.0.0.1;Port=5432;Database=postgres;Uid=postgres;Pwd=test"
2 - using the .ini file of the .exe to set up the Connection Alias for the databaseName
Connecting to the same postgres database shown above. You would do the following ...
q = new adoQuery()
q.databaseName = "PostGresData"
Then in the .ini file for the .exe you would add the following ...
[Connections]
1=PostGresData
[PostGresData]
Driver=postgreSQL ODBC Driver(ANSI)Server=127.0.0.1
Port=5432
Database=postgres
Uid=postgres
Pwd=test
IMPORTANT NOTE: You will also find any Connection Aliases you create in the dBASE PLUS IDE in your plus.ini file.
SAMPLES
---------------------------------------
Using Named
Parameters:
** END HEADER -- do not remove this line
//
// Generated on 11/21/2013
//
parameter bModal
local f
f = new ADO_ParamsForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class ADO_ParamsForm of FORM
with (this)
height = 16.0
left = 18.2857
top = 9.9091
width = 126.0
text = ""
endwith
this.COMPANIES1 = new ADOQUERY(this)
with (this.COMPANIES1)
beforeConnect = class::COMPANIES1_BEFORECONNECT
left = 15.0
top = 4.0
databaseName = "SQLTEST"
sql = "SELECT * FROM KathyTest.dbo.Companies where TYPE >= :TYPE1 and TYPE <= :TYPE2 ORDER BY NAME "
active = true
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.companies1.rowset
height = 13.0
left = 1.0
top = 2.0
width = 116.0
endwith
this.rowset = this.companies1.rowset
function COMPANIES1_beforeConnect
this.parameters["TYPE1"] = "A"
this.parameters["TYPE2"] = "Z"
return
endclass
OR in the above example you could use the params[''].value property...
function COMPANIES1_beforeConnect
this.params["TYPE1"].value = "A"
this.params["TYPE2"].value = "Z"
return
---------------------------------------
Using
Numbered Parameters:
You can also used numbered parameters if the named parameters are not working for you. Though in most cases the named parameters will work.
Using the same sample for 'Using Named Parameters' above, this sample shows the .sql change that has to be made to use numbered parameters ...
sql = "SELECT * FROM KathyTest.dbo.Companies where TYPE >= ? OR TYPE <= ? ORDER BY NAME "
//parameters are used in order of their initilization...
function COMPANIES1_beforeConnect
this.parameters["TYPE1"] = "A"
this.parameters["TYPE2"] = "Z"
return
OR you can use the params... value properties...
function COMPANIES1_beforeConnect
this.params["TYPE1"].value = "A"
this.params["TYPE2"].value = "Z"
return
For UPDATES:
Q.sql = "Update Numbers set PAYNO = ?, INVNO = ?, PURCHNO = ? where PERIOD = ?"
Q.parameters["pm_payNo"] = "Z1"
Q.parameters["pm_TranNo"] = "z2"
Q.parameters["pm_purchNo"] = "z3"
Q.parameters["pm_ID"] = 10
OR you can use the params... value properties...
Q.sql = "Update Numbers set PAYNO = ?, INVNO = ?, PURCHNO = ? where PERIOD = ?" Q.params["pm_payNo"].value = "b1"
Q.params["pm_TranNo"].value = "b2"
Q.params["pm_purchNo"].value = "b3"
Q.params["pm_ID"].value = 10
For INSERTS:
CLIENTI1 = new ADOQUERY()
CLIENTI1.connectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Firebird DSN"
CLIENTI1.sql = "insert into Customer (Customer, Contact_First , Contact_Last) "
CLIENTI1.sql += " values (:Customer, :Contact_First, :Contact_Last )"
CLIENTI1.Parameters["Customer"] = "Aloha Int."
CLIENTI1.Parameters["Contact_First"] = "Craig"
CLIENTI1.Parameters["Contact_Last"] = "Halsten"
CLIENTI1.Active = true
CLIENTI1 = new ADOQUERY()
CLIENTI1.connectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Firebird DSN"
CLIENTI1.sql = "insert into Customer (Customer, Contact_First , Contact_Last) "
CLIENTI1.sql += " values (:Customer, :Contact_First, :Contact_Last )"
CLIENTI1.Params["Customer"].value = "Brinkman LLC"
CLIENTI1.Params["Contact_First"].value = "Bob"
CLIENTI1.Params["Contact_Last"].value = "Broden"
CLIENTI1.Active = true
---------------------------------------
MySQL
adoStoredProc:
This example does a couple of things.
It sets up the Stored Procedure during the Form's onDesignOpen
event so the Field Palette can be used to get fields and add them to the
designer. It also allows a connection the grid object in order to see
data laid out at design time.
If your Stored Procedure allows for and uses default values for parameters then you can get the adoStoredProc.active to true while in the Designer without having to use the onDesignOpen event.
IMPORTANT NOTE: if you use onDesignOpen to make the procedure active during design time, you will need to manually remove the "active = true" line from the Procedure's constructor code. The code streaming engine will set it in the constructor code automatically when you save the form. If you leave it in during runtime you will get an error.
It also includes code to change the parameter value during runtime using a Pushbutton object.
** END HEADER -- do not remove this line
//
// Generated on 11/21/2013
//
parameter bModal
local f
f = new ADO_StoredProcMySQLForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class ADO_StoredProcMySQLForm of FORM
with (this)
onOpen = class::FORM_ONOPEN
onDesignOpen = class::FORM_ONDESIGNOPEN
height = 16.0
left = 48.1429
top = 0.0
width = 96.8571
text = ""
endwith
this.ADOSTOREDPROC1 = new ADOSTOREDPROC(this)
with (this.ADOSTOREDPROC1)
left = 16.0
top = 2.0
databaseName = "MYSQLTEST_CA"
procedureName = "CustomersByState"
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.adostoredproc1.rowset
height = 12.6364
left = 2.0
top = 2.6364
width = 91.7143
endwith
this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
height = 1.0
left = 22.5714
top = 0.6818
width = 4.7143
value = "NY"
maxLength = 2
endwith
this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 1.0
left = 3.1429
top = 0.7273
width = 18.0
text = "Filter List by STATE: "
endwith
this.PUSHBUTTON1 = new PUSHBUTTON(this)
with (this.PUSHBUTTON1)
onClick = class::PUSHBUTTON1_ONCLICK
height = 1.0909
left = 28.5714
top = 0.5909
width = 5.0
text = "Go"
endwith
function FORM_ONOPEN
this.grid1.dataLink = null
this.adoStoredProc1.active = false
this.adoStoredProc1.parameters["ST"].value = this.entryfield1.value
this.adoStoredProc1.active = true
this.grid1.dataLink = this.adoStoredProc1.rowset
return
function PUSHBUTTON1_onClick
form.grid1.dataLink = null
form.adoStoredProc1.active = false
form.adoStoredProc1.parameters["ST"].value = form.entryfield1.value
form.adoStoredProc1.active = true
form.grid1.dataLink = form.adoStoredProc1.rowset
return
function form_onDesignOpen(bFromPalette)
this.adoStoredProc1.parameters["ST"].value = this.entryfield1.value
this.adoStoredProc1.active = true
this.grid1.dataLink = this.adoStoredProc1.rowset
return
endclass
---------------------------------------
Using
getSchema to get
a list of tables
//Go here for a list of Schema Names and Columns: Methods_getSchema
//set up ADOdatabase object
adoDatabase1 = new adoDatabase()
adoDatabase1.connectionString = "Provider=SQLOLEDB.1;Password=Test123;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=11.111.11.111,1433"
adoDatabase1.active = true
//Attempt to get getSchema for tables
try
//returns adoRowset with list of tables
ADOR_Tables = adoDatabase1.getSchema("TABLES")
lCanContinue = true
catch (exception e)
MsgBox("Cannot getSchema"+chr(13)+e.message+chr(13)+"LineNo: "+e.lineNo)
lCanContinue = false
endtry
//if Able to getSchema - try to get actual "TABLES" list
if lCanContinue
//Get list of actual tables
ADOR_Tables.first()
aListOfTables = new Array()
ADOR_Tables.first()
DO
//if item in list is an Actual Table - and not a 'SYSTEM VIEW' or Some other type
if ADOR_Tables.fields["TABLE_TYPE"].value
== "TABLE"
?ADOR_Tables.fields["TABLE_NAME"].value
endif
ADOR_Tables.next()
Until NOT ADOR_Tables.next()
endif //Able to getSchema
---------------------------------------
Using
executeSQL to create a Stored Proceduer and the adoDatabase Errors aray
to get the server error info
This example uses the adoDatabase.executeSQL() method to create a Stored
Procedure on a MS SQL database. It also shows how to use the adoDatabase.errors
array to get additional error information from the server.
adoDatabase1 = new adoDatabase()
adoDatabase1.connectionString = "Provider=SQLOLEDB.1;Password=Test123;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=11.111.11.111,1433"
adoDatabase1.active = true
cStatement = "CREATE PROCEDURE [dbo].[ElementsByMassDefaultTest] @MassGreater INT = 0," cStatement +=" @MassLessThan INT = 100"
cStatement +=" AS"
cStatement +=" SELECT *"
cStatement +=" FROM dbo.DD_PERIODIC_TABLE_OF_ELEMENTS"
cStatement +=" WHERE MASS > @MassGreater AND MASS < @MassLessthan order by MASS"
Try
adoDatabase1.executeSQL(cStatement)
MsgBox("executeSQL Complete","Done")
Catch (exception e)
adoErrorInfo = adoDatabase1.errors[1].message+chr(13)+;
"Code: "+adoDatabase1.errors[1].code+chr(13)+;
"NativeCode: "+adoDatabase1.errors[1].Nativecode
Msgbox(e.message+" : "+adoErrorInfo,"Error wth executeSQL")
endtry
---------------------------------------
This will allow the count() to process correctly as well as get the data..
---------------------------------------
Working with ADO objects in a Web App using Apache
This issue will occur on any 64 bit version of windows beginning with Vista.
The problem is due to the fact that Windows uses the environment variable 'CommonProgramFiles(x86)' to find the ADO DLL's at runtime but Apache does not allow parenthesis in the names of any environment variables that it passes or creates for web apps that it executes.
It converts the parenthesis into underscores, for example: CommonProgramFiles_x86_.
The simplest solution for this is to have each web app that requires ADO
access to create its own environment variable before it attempts to create
any ADO objects.
Adding the following code near the beginning of these web apps will create the needed environment variable.
(be sure to adjust the specified path to the one matching the machine
on which Apache is executing):
extern CLOGICAL SetEnvironmentVariable(CSTRING, CSTRING) kernel32 from "SetEnvironmentVariableA"
bResult = SetEnvironmentVariable("CommonProgramFiles(x86)", "C:/Program Files (x86)/Common Files")
- - - - - - -
Here is a function: initEnv(), that will only call SetEnvironmentVariable() when necessary - from within a web app. This can be added to the web class (as a method) if desired and called before any ADO objects are created:
function initEnv()
local cEnvStr, cEnvVar, cEnvVarNoParens
if OSVersion() >= 6 // Windows Vista is version 6.0
if IsWow64() // test if running on 64 bit version of Windows
if type("SetEnvironmentVariable") # "FP"
extern CLOGICAL SetEnvironmentVariable(CSTRING, CSTRING) kernel32 from "SetEnvironmentVariableA"
endif
cEnvVar = "CommonProgramFiles(x86)"
cEnvVarNoParens = "CommonProgramFiles_x86_"
// check if correct env.var already exists and has a value
cEnvStr = getenv(cEnvVar)
if empty(cEnvStr)
// EnvVar with parens not found - so must create it
// check if modified env.var (without parens) already exists and has a value
cEnvStr = getenv(cEnvVarNoParens)
if empty(cEnvStr)
// cEnvVarNoParens not found - use default value for it
cEnvStr = "C:/Program Files (x86)/Common Files"
else
// otherwise cEnvVarNoParens exists and has a value
endif
// Create and set value of env.var (with parens)
bResult = SetEnvironmentVariable(cEnvVar, cEnvStr)
endif
endif
endif
return
function OSVersion
local cVersion
cVersion = OS()
return val(ltrim(substr(cversion,rat(" ",cVersion))))
function IsWow64
local lIsWow64
lIsWow64 = false
if type("GetCurrentProcess") # "FP"
extern CHANDLE GetCurrentProcess( CVOID ) kernel32
endif
if type("IsWow64Process") # "FP"
extern CLOGICAL IsWow64Process(CHANDLE, CPTR CLOGICAL) kernel32
endif
if type("IsWow64Process") = "FP"
// Make sure extern worked for retrieving this function pointer
// Because older versions of windows do not contain this function
// From MSDN, minimum supported operating systems are:
// Client OS's: Win XP SP2, Vista
// Server OS's: Windows Server 2003 with SP1, Windows Server 2008
IsWow64Process(GetCurrentProcess(), lIsWow64)
endif
return lIsWow64
- - - - - - - -
In order for the above initEnv() to function correctly the following setting should be in the httpd.conf file (in any place - I put mine in the beginning) for Apache:
PassEnv CommonProgramFiles(x86)
This instructs Apache to pass the existing environment variable (CommonProgramFiles(x86)
to any web apps as part of their default environment.
Apache will pass this env.variable, but renamed to: CommonProgramfiles_x86_
but it will have the needed path assigned to it which initEnv() will
use.
Also when connecting to an ADO database it is best to use the .connectionString property when connecting to an external database in a web app.
for example ...
this.adoQuery1 = new ADOQUERY(this)
with (this.adoQuery1)
connectionString = "<connectionString>"
sql = "SELECT * FROM <tablename>"
active = true
requestLive = false
endwith
- - - - - - - -