** END HEADER -- do not remove this line
//
// Generated on 07/23/2015
//
parameter bModal
local f
f = new GetAdoConnectionsForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class GetAdoConnectionsForm of FORM
with (this)
onOpen = class::FORM_ONOPEN
height = 31.6818
left = 12.1429
top = 2.1818
width = 143.2857
text = ""
endwith
this.ADODATABASE1 = new ADODATABASE(this)
with (this.ADODATABASE1)
left = 99.0
top = 4.0
endwith
this.LINE1 = new LINE(this)
with (this.LINE1)
left = 118.7143
right = 1.5714
top = 23.0909
bottom = 23.0909
width = 1
endwith
this.RECTANGLE1 = new RECTANGLE(this)
with (this.RECTANGLE1)
left = 1.5714
top = 24.0909
width = 140.0
height = 5.5
text = ""
borderStyle = 2 // Lowered
endwith
this.COMBOBOX1 = new COMBOBOX(this)
with (this.COMBOBOX1)
onChange = class::COMBOBOX1_ONCHANGE
onOpen = class::COMBOBOX1_ONOPEN
height = 1.0
left = 2.1429
top = 4.0
width = 28.2857
style = 1 // DropDown
endwith
this.EDITOR1 = new EDITOR(this)
with (this.EDITOR1)
height = 2.5909
left = 2.0
top = 6.3182
width = 92.7143
value = ""
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
height = 11.7273
left = 2.2857
top = 10.8182
width = 140.0
endwith
this.PBGETDATA = new PUSHBUTTON(this)
with (this.PBGETDATA)
onClick = class::PBGETDATA_ONCLICK
height = 1.0909
left = 2.5714
top = 9.5455
width = 27.0
text = "Update Grid with Table Data"
endwith
this.COMBOBOX2 = new COMBOBOX(this)
with (this.COMBOBOX2)
height = 1.0
left = 31.2857
top = 4.0
width = 31.2857
style = 1 // DropDown
endwith
this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 0.8182
left = 2.5714
top = 3.1818
width = 24.7143
text = "ADO Connection Alias:"
endwith
this.TEXT2 = new TEXT(this)
with (this.TEXT2)
height = 0.7727
left = 31.5714
top = 3.1818
width = 24.7143
text = "Table List:"
endwith
this.TEXT3 = new TEXT(this)
with (this.TEXT3)
height = 2.5455
left = 0.0
top = 0.0
width = 143.0
alignHorizontal = 1 // Center
l0 = "This program will first get a list of ADOconnections you have set up through 'Properties | Desktop Properties | Connection Aliases'
Then it will populate the Table List with the tables in that database.
If you click on 'Update Grid with Tabl"
l0 += "e Data' it will use an ADOTable to connect to the data and populate the grid with that table.
"
text = l0
borderStyle = 2 // Lowered
endwith
this.EXECUTESQL = new PUSHBUTTON(this)
with (this.EXECUTESQL)
onClick = class::EXECUTESQL_ONCLICK
height = 1.0909
left = 5.5714
top = 24.5909
width = 15.2857
text = "ExecuteSQL"
endwith
this.SQLSTATEMENT = new EDITOR(this)
with (this.SQLSTATEMENT)
onOpen = class::SQLSTATEMENT_ONOPEN
height = 4.5
left = 25.5714
top = 24.5909
width = 113.8571
value = ""
endwith
this.TABBOX1 = new TABBOX(this)
with (this.TABBOX1)
onSelChange = class::TABBOX1_ONSELCHANGE
id = 112
height = 1.0
left = 0.0
top = 30.5909
width = 143.0
dataSource = 'ARRAY {"List of Tables","Table Detail"}'
endwith
this.EDITOR2 = new EDITOR(this)
with (this.EDITOR2)
height = 26.0
left = 4.0
top = 2.5
width = 111.0
value = ""
fontName = "Courier"
pageno = 2
endwith
this.INFOFORTABLE = new TEXT(this)
with (this.INFOFORTABLE)
height = 1.0
left = 5.0
top = 0.5
width = 90.0
text = "Text4"
pageno = 2
endwith
this.TEXT4 = new TEXT(this)
with (this.TEXT4)
height = 1.5
left = 2.5714
top = 26.0909
width = 22.0
text = "Execute a SQL Statement on the current Database!"
endwith
this.TEXT5 = new TEXT(this)
with (this.TEXT5)
height = 0.8636
left = 2.5714
top = 5.4091
width = 24.7143
visible = false
text = "Connection Statement is:"
endwith
this.COMBOBOX3 = new COMBOBOX(this)
with (this.COMBOBOX3)
height = 1.0
left = 64.2857
top = 4.0
width = 31.2857
style = 1 // DropDown
endwith
this.TEXT6 = new TEXT(this)
with (this.TEXT6)
height = 0.7727
left = 64.5714
top = 3.1818
width = 24.7143
text = "Procedure List:"
endwith
this.EDITOR3 = new EDITOR(this)
with (this.EDITOR3)
height = 4.9545
left = 96.4286
top = 4.0455
width = 45.4286
value = ""
endwith
this.TEXT8 = new TEXT(this)
with (this.TEXT8)
onLeftMouseUp = class::TEXT8_ONLEFTMOUSEUP
height = 1.0
left = 33.5714
top = 9.2727
width = 108.2857
alignHorizontal = 2 // Right
text = "List of ADO Data types: http://msdn.microsoft.com/en-us/library/windows/desktop/ms675318(v=vs.85).aspx"
endwith
this.PBPARAMETERS = new PUSHBUTTON(this)
with (this.PBPARAMETERS)
onClick = class::PBPARAMETERS_ONCLICK
height = 1.0909
left = 97.0
top = 2.8182
width = 25.0
text = "Get Procedure Parameters: "
endwith
function sqlstatement_onOpen
this.value = "CREATE TABLE ( , ....) "+chr(13)+"MODIFY TABLE ... "+chr(13)+"UPDATE TABLE ... "+chr(13)+"CREATE PROCEDURE...."
return
**
*******************************************************************************
function COMBOBOX1_onChange
*******************************************************************************
local ADOR_Tables, ADOR_PROCEDURE
nItem = form.aADOList.scan(this.value)
cConnectionString = form.aAdoConnections[nItem,2]
form.editor1.value := cConnectionString
//get ADORowset and populate it withe the schema info on Tables
form.adoDatabase1.active = false
form.adoDatabase1 = new ADODatabase()
form.adoDatabase1.connectionString = cConnectionString
form.adoDatabase1.databaseName = form.combobox1.value
try
form.adoDatabase1.active = true
lCanContinue = true
catch (exception e)
MsgBox("Cannot continue to connect"+chr(13)+e.message+chr(13)+"LineNo: "+e.lineNo)
lCanContinue = false
endtry
//if Database was activated
if lCanContinue
//Attempt to get getSchema for tables
try
ADOR_Tables = form.adoDatabase1.getSchema("TABLES") //returns adoRowset with list of 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()
form.aListOfTables = new Array()
ADOR_Tables.first()
for x = 1 to ADOR_Tables.count()
//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"
cTable = ADOR_Tables.fields["TABLE_NAME"].value
form.aListOfTables.add(cTable)
endif
ADOR_Tables.next()
endfor
if form.aListOfTables.size > 0
form.ComboBox2.dataSource = "ARRAY form.aListOfTables"
form.ComboBox2.value := form.aListOfTables[1] //set initial value of combobox to the first table
else
Form.ComboBox2.dataSource = null
form.ComboBox2.value := ""
endif
else
Form.ComboBox2.dataSource = null
form.ComboBox2.value := ""
endif //Able to getSchema
//Attempt to get getSchema for procedures
try
ADOR_Procedures = form.adoDatabase1.getSchema("PROCEDURES") //returns adoRowset with list of Procedures
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 "Procedures" list
if lCanContinue
//Get list of actual Procedures
ADOR_Procedures.first()
form.aListOfProcedures = new Array()
ADOR_Procedures.first()
for x = 1 to ADOR_Procedures.count()
//if item in list is a procedure that is not from sys
? ADOR_Procedures.fields["PROCEDURE_SCHEMA"].value, ADOR_Procedures.fields["PROCEDURE_NAME"].value
if lower(ADOR_Procedures.fields["PROCEDURE_SCHEMA"].value) <> "sys"
cProcedure = iif(";" $ ADOR_Procedures.fields["PROCEDURE_NAME"].value, LEFT(ADOR_Procedures.fields["PROCEDURE_NAME"].value,AT(";",ADOR_Procedures.fields["PROCEDURE_NAME"].value)-1),ADOR_Procedures.fields["PROCEDURE_NAME"].value)
cSchema = ADOR_Procedures.fields["PROCEDURE_SCHEMA"].value
cCat = ADOR_Procedures.fields["PROCEDURE_CATALOG"].value
form.aListOfProcedures.add(cProcedure)
endif
ADOR_Procedures.next()
endfor
if form.aListOfProcedures.size>0
form.ComboBox3.dataSource = "ARRAY form.aListOfProcedures"
form.ComboBox3.value := form.aListOfProcedures[1] //set initial value of combobox to the first procedure
else
form.ComboBox3.dataSource = null
form.ComboBox3.value := ""
endif
else
Form.ComboBox3.dataSource = null
form.ComboBox3.value := ""
endif //Able to getSchema for procedure
//reset list of Procedures Parameters
form.editor3.value := ""
endif //Database was activated
return
**
*******************************************************************************
function COMBOBOX1_onOpen
*******************************************************************************
if form.ConnectionsFound
class::COMBOBOX1_onChange()
endif
return
**
*******************************************************************************
function getParamType(nType)
*******************************************************************************
cType = "UNKNOWN"
do CASE
CASE nType = 1
cType = "INPUT"
CASE nType = 2
cType = "INPUTOUTPUT"
CASE nType = 3
cType = "OUTPUT"
CASE nType = 4
cType = "RETURNVALUE"
ENDCASE
return cType
*******************************************************************************
*******************************************************************************
function EXECUTESQL_onClick
*******************************************************************************
cStatement = form.sqlStatement.value
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
return
*******************************************************************************
function PBGETDATA_onClick
*******************************************************************************
form.adoTable1 = new adoTable()
form.adoTable1.database = form.adoDatabase1
form.adoTable1.tableName = form.Combobox2.value
form.adoTable1.active = true
form.grid1.dataLink = form.adoTable1.rowset
return
**
*******************************************************************************
function PBPARAMETERS_onClick
*******************************************************************************
cProcedure = form.Combobox3.value
//MsgBox(cProcedure)
try
adoRParameters = form.adoDatabase1.getSchema("PROCEDURE_PARAMETERS")
lCanContinue = true
catch (exception e)
MsgBox(e.message,"Unable to get parameter list")
lCanContinue = false
endtry
if lCanContinue
aParams = new Array(1,7)
n = 1
do
if lower(adoRParameters.fields["PROCEDURE_SCHEMA"].value) <> "sys"
//? adoRParameters.fields["PROCEDURE_NAME"].value
if lower(left(adoRParameters.fields["PROCEDURE_NAME"].value,AT(";",adoRParameters.fields["PROCEDURE_NAME"].value)-1)) == lower(cProcedure)
iif(n<>1,aParams.grow(1),"")
aParams[n,1] = adoRParameters.fields["PARAMETER_NAME"].value
aParams[n,2] = adoRParameters.fields["PARAMETER_TYPE"].value
aParams[n,3] = adoRParameters.fields["PARAMETER_HASDEFAULT"].value
aParams[n,4] = adoRParameters.fields["PARAMETER_DEFAULT"].value
aParams[n,5] = adoRParameters.fields["DATA_TYPE"].value
aParams[n,6] = adoRParameters.fields["DESCRIPTION"].value
aParams[n,7] = cProcedure
n++
endif
endif
until NOT adoRParameters.next()
if n>1
n-- //there is n-1 rows in the aParams array
cParameters = "Procedure: "+aParams[n,7]+chr(13)
for x = 1 to n
//if aParams[x,2] == 1 or aParams[x,2] == 2 //DBPARAMTYPE_INPUT or DBPARAMTYPE_INPUTOUTPUT
//?x
?"PARAMETER NAME: "+aParams[x,1]
?"TYPE: "+aParams[x,2]," (DBPARAMTYPE_INPUT(1), DBPARAMTYPE_INPUTOUTPUT(2), DBPARAMTYPE_OUTPUT(3), DBPARAMTYPE_RETURNVALUE(4))"
?"HASDEFAULT: "+aParams[x,3]
?"DEFAULT: "+aParams[x,4]
?"DATA_TYPE: "+aParams[x,5]," Go here for list of OLE DB Data Types : http://msdn.microsoft.com/en-us/library/windows/desktop/ms675318(v=vs.85).aspx"
?"DESCRIPTION: "+aParams[x,6]
?
? aParams[x,3],type("aParams[x,3]")
?
cParameters += chr(13)+"PARAMETER NAME: "+ aParams[x,1]+ chr(13) + "TYPE: "+ aParams[x,2] +" - "+ form.getParamType( aParams[x,2]) + chr(13) + "DEFAULT: "+aParams[x,4]+chr(13)+"DATA TYPE: "+aParams[x,5]+chr(13)+chr(10)
form.editor3.value = cParameters
//endif
endfor
endif
endif //Able to get Procedure list
return
*******************************************************************************
function TABBOX1_onSelChange
*******************************************************************************
if this.curSel = 2
//get table specific details.
form.adoTable1 = new adoTable()
form.adoTable1.database = form.adoDatabase1
form.adoTable1.tableName = form.Combobox2.value
form.adoTable1.active = true
if type("form.adoTable1") == "O"
form.pageno = this.cursel
rTable = form.adoTable1.rowset
rF = rTable.fields
Form.aListOfFields = new Array(1,3)
form.cFieldDataText = "FieldName SchemaDataType Length"+chr(13)
form.cFieldDataText += "--------- --------------- ------"+chr(13)
for x = 1 to rF.size //iterate through list of fields
//add field info to array
form.aListOfFields[x,1] = rF[x].fieldName
form.aListOfFields[x,2] = rF[x].Type
form.aListOfFields[x,3] = rF[x].Length
form.cFieldDataText = form.cFieldDataText +;
rF[x].fieldName+replicate(" ",42-LEN(rF[x].fieldName))+;
rF[x].Type+replicate(" ",32-LEN(rF[x].type))+;
rF[x].Length+chr(13)
form.aListOfFields.grow(1)
endfor
form.aListOfFields.resize(rF.size,3)
form.Editor2.value = form.cFieldDataText
form.infofortable.text = "Field information for "+upper(form.adoDatabase1.databaseName)+" : "+form.adoTable1.tableName
//Show info
else //form.adoTable1 is not an object yet
MsgBox("Sorry, cannot get table data. Table object was not able to be activated.","Error")
this.curSel = 1
endif
else //curSel <> 2
form.pageNo = this.cursel
endif
return
*******************************************************************************
function TEXT8_onLeftMouseUp(flags, col, row)
*******************************************************************************
return
*******************************************************************************
function form_onOpen
*******************************************************************************
this.ConnectionsFound = false
set procedure to MyAdoConnectionFunctions.prg
this.aAdoConnections = getlistofConnections()
//this.aAdoConnections is a two dimentional array
// col1 - has list of ADOConnections
// col2 - has the detail of that connection
//we just want the list for the combobox
if type ("this.aAdoConnections") = "A"
form.ConnectionsFound = true
this.aADOList = new Array()
if this.aADOConnections.size>1
for x = 1 to this.aADOConnections.size/2
this.aADOList.add(this.aADOConnections[x,1])
endfor
this.combobox1.dataSource = "array form.aAdoList"
endif
endif
if NOT form.ConnectionsFound
msgBox("no ADO Connections have been detected"+;
chr(13)+"Check your Properties | Desktop Properties dialog for 'Connection Aliases'"+;
chr(13)+" "+;
chr(13)+"See the help under ADO | Connection String Walkthrough for more information")
endif
return
endclass