ADO Object information and examples.
Go to the ADO Connection String Walkthrough for details on creating an ADO ADO Connection.
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)
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
---------------------------------------
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
---------------------------------------
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 until
//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
Try
form.adoDatabase1.executeSQL(cStatement)
MsgBox("executeSQL Complete","Done")
Catch (exception e)
adoErrorInfo = form.adoDatabase1.errors[1].message+chr(13)+;
"Code: "+form.adoDatabase1.errors[1].code+chr(13)+;
"NativeCode: "+form.adoDatabase1.errors[1].Nativecode
Msgbox(e.message+" : "+adoErrorInfo,"Error wth executeSQL")
endtry
---------------------------------------
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