Solved

VB from Paradox Forms and reports?

Posted on 2000-04-13
5
327 Views
Last Modified: 2013-12-25
I need to convert a system built with Paradox to Sequl Server and Visual Basic.  What I want to know is:

 Is there a tool that will take Paradox forms and/or reports abd convert them to Visual Basic?

 I'm on a short time frame and there are many forms and reports.
0
Comment
Question by:computist
  • 4
5 Comments
 

Expert Comment

by:tomhines
Comment Utility
It sounds like you are basically talking about reverse engineering a paradox database. I used several tools on data-warehousing projects to reverse engineer databases. These tools, such were made by logic works and powerbuilder and read ODBC compliant databases. I couldn’t find a tool to read paradox files. Most tools seem to focus on reading the data model for the databases. I wrote something that accomplished the same goal.

I used the paradox enum methods to create a database of all of the table structures by database name. We had over 50 paradox databases. Once you have the table and field lists you can create default forms in access.

For paradox for dos version 4.5 you can write a program to parse the table and field lists from the form and report files. These files have extensions such as .f* and .r*. Then you can use the field list from the forms and reports to create the access forms and reports. We have over 80,000 paradox files to convert using this method so I am writing a program that will process the data dictionary and parse the paradox files and then generate the databases and forms in access. The program I am writing also takes out characters such as _, -, #, @, $, ., /, to make the database ODBC compliant.
0
 

Expert Comment

by:tomhines
Comment Utility
I am now working on a table of equivalents to convert the 4500 paradox queries that we have to SQL. Let me know if you need to do this.
0
 

Expert Comment

by:tomhines
Comment Utility
http://www.inthisworld.com/datacentriks/exchangev2.htm

Follow the link above for a visual. I hope you can wait for the images because there are over 50. I'll try to post the code soon.

As you can see, all you have to do is use the enum commands to create a database of all of the table structures. Use some scripts to make the table structures odbc compliant. You’ll also need some scripts to create the tables and import the data from your development or production databases. You can also convert the paradox scripts by separating the scripts up between queries and pal. You can convert the queries to sql using a table of equivalents. You can also read the table name and fields lists from the forms and reports to create default forms in access. Since we have over 80,000 files to convert it seems like a worthwhile project. If you store the whole thing in an object database you can create an object model.
0
 

Author Comment

by:computist
Comment Utility
Tom,
 I tried the link but all the pictures where 'broken'.  I have an ADSL connection that tested at over 700KB so it is not a speed problem.  I'll check back tomorrow.  I'm looking forward to seeing the code.  It sounds like you have a solution for me.

Frank
0
 

Accepted Solution

by:
tomhines earned 200 total points
Comment Utility



List tables

method pushButton(var eventInfo Event)
var
  fs FileSystem
  tblDir, tblTmp String
  tblNames Array[] String
  AppTblTC  TCursor
  p PopUpMenu
  ptv tableView
endvar

tblDir=""


formDir = QRYRUNMS.SourceDir + "\\*.db"
tblTmp = QRYRUNMS.SourceDir + "\\*.db"

if fs.findFirst(formDir) then          ; if one *.f?l is found
;      fs.enumFileList(formDir+"\\*.db", ":WORK:apptbltm.db") ; create FORMS.DB listing *.f?l files
      fs.enumFileList(formDir,":WORK:apptbltm.db") ; create FORMS.DB listing *.f?l files
;      tv.open("apptbltm.db")                  ; display FORMS.DB table
endIf

endmethod

Add tables to list

method pushButton(var eventInfo Event)
var
  cItem, cProjectId String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  thisTbl Table
endVar

cTitle=self.labeltext
cProjectId=QRYRUNMS.ProjectId

thisTbl.attach("APPTBLTM.DB")      thisTbl.compact()      message("APPTBLTM.DB")
thisTbl.unAttach()


QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

APPTBLTM.DB | Name   | Size   | Attributes | Date   | Time   |
            | _join1 | _join2 | _join3     | _join4 | _join5 |

APPTBLS.DB | ProjectID   | Name   | Size   | Attributes | Date   | Time   |
Insert     | ~cProjectId | _join1 | _join2 | _join3     | _join4 | _join5 |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

;After the last transaction table has been calculated it can be used
;to link back to the poltran.db to create reports

endmethod

List files

method pushButton(var eventInfo Event)
var
  fs FileSystem
  tblDir, cDestDir String
  tblNames Array[] String
  AppTblTC  TCursor
  p PopUpMenu
  ptv tableView
endvar

cDestDir=QRYRUNMS.DestDir
tblDir=""
AppTblTC.open(":WORK:init.db")

       formDir = QRYRUNMS.SourceDir +"\\*.*"


if fs.findFirst(formDir) then          ; if one *.f?l is found
      fs.enumFileList(formDir, ":WORK:appfiltm.db") ; create FORMS.DB listing *.f?l files
;      tv.open("apptbltm.db")                  ; display FORMS.DB table
endIf

endmethod


Add files to list

method pushButton(var eventInfo Event)
var
  cItem, cProjectId String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  TC Tcursor
  thisTC TCursor
  cFileExt,cFileName  String
  nExtPos Number
  thisTbl Table
endVar

cTitle=self.labeltext
cProjectId=QRYRUNMS.ProjectId
msgStop("Printing Report", cTitle)

tblName = "appfilt2.DB"
if isTable(tblName) then
  tc.open(tblName)
       tc.empty()
else
  msgInfo("Error", "Can’t find " + tblName + " table.")
endIf

thisTbl.attach("appfiltm.db")      thisTbl.compact()      message("appfiltm.db")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

appfiltm.db | Name   | Size   | Attributes | Date   | Time   |
                        | _join1 | _join2 | _join3     | _join4 | _join5 |

appfilt2.DB | AppId       | Name   | Size   | Attributes | Date   | Time   |
Insert      | ~cProjectId | _join1 | _join2 | _join3     | _join4 | _join5 |

EndQuery


nExtPos=1

;DDFILES.edit()
thisTC.open(":WORK:appfilt2.DB")
thisTC.Edit()

scan thisTC:

cFileName = thisTC."Name"

ignoreCaseInStringCompares(No)


  nExtPos=cFileName.search(".")

if nExtPos > 0 then
  ; start from first position, take three characters
  cFileExt = cFileName.substr((nExtPos+1), 3)  ; get the area code
  thisTC."FileExtension"=cFileExt

else
  ;do nothing
endIf
      

endScan
thisTC.endEdit()
thisTC.close()


thisTbl.attach("appfilt2.DB")      thisTbl.compact()      message("appfilt2.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

:WORK:appfilt2.DB | APPID  | Name   | Size   | Attributes | Date   | Time   |
                                    | _join1 | _join2 | _join3 | _join4     | _join5 | _join6 |

:WORK:appfilt2.DB | FileExtension |
                                    | _join8        |

Ddfiles.db | APPID  | Name   | Size   | Attributes | Date   | Time   |
Insert     | _join1 | _join2 | _join3 | _join4     | _join5 |        |

Ddfiles.db | FileType | FileExtension | SystemType | Comment |
                    |          | _join8        |            |         |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

endmethod


Enumerate table structures into a table

method pushButton(var eventInfo Event)
var
  cItem, cAlias, cCurTbl, cAppId String
  dBegDate, dEndDate Date
  dCurDateTime DateTime
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  nCntr, nCntr2, nStart, nStop, nMCntr Number
  power, nStartPos, nCurPos, nCurRec Number
  CurFldsTC, SourceTblTC, DestTblTC, CopyRecTC, AppTblTC, cCurFldTC TCursor
  aCopyRec DynArray[] AnyType
  curTbl, newcurTbl, appTbl Table
  curTC TCursor
  cSourceDir, cCurPath String
  nRecCnt, nFldCnt, nSubTotElements, nDataElements, nCurDataElement, nPerComplete Number
  aField, aTmp AnyType
  sTmp String
  underwritingUI UIObject
  appInfoTC TCursor
endVar

;assign variables
;
nCntr=1
nCntr2=1
cCurTbl=""
aField=""
aTmp=""
nRecCnt=0
nFldCnt=0
nSubTotElements=0
nDataElements=0
nCurDataElement=0
nsPerComplete=0
nStart=0
nCurRecTmp=0
cAppId=""
;
If AppTblTC.attach(APPTBLS) then
  ;do nothing
else
  errorshow()
endif
;
;If AppTblTC.open("AppTbls.db") then
  ;do nothing
;else
;  errorshow()
;endif
;
;appTbl.setFilter(ProjectID, ProjectID)
;
;Count total number of data elements
;
 AppTblTC.movetoRecord(1)
;
Scan AppTblTC:
APPTBLS.resync(AppTblTC)
cCurTbl=APPTBLSBox.APPTBLS.ThisTable
cAlias2=APPCONV.SourceDir+"\\"+cCurTbl
message("Counting Fields "+ cAlias2)
;
;count data elements in current table
;
If SourceTblTC.open(CAlias2) then
  ;do nothing

       try
            nRecCnt=SourceTblTC.nRecords()
;            nFldCnt=SourceTblTC.nFields()
            nSubTotElements=nRecCnt*nFldCnt
       onFail                                     ; handle the error
            message("Read Error...")
      sleep(1000)
;      reTry                                   ; try again
       endTry

       try
;            nRecCnt=SourceTblTC.nRecords()
            nFldCnt=SourceTblTC.nFields()
            nSubTotElements=nRecCnt*nFldCnt
       onFail                                     ; handle the error
            message("Read Error...")
      sleep(1000)
;      reTry                                   ; try again
       endTry

;  nRecCnt=SourceTblTC.nRecords()
;  nFldCnt=SourceTblTC.nFields()
;  nSubTotElements=nRecCnt*nFldCnt
else
;  errorshow()
  message("Read error...")
endif
;
;accumulate total data elements
nDataElements=nDataElements+nSubTotElements
;endscan for count
endScan

nRecTot=nDataElements
nStop=nDataElements
;
APPTBLS.movetoRecord(1)
;loop through table of application tables
;for nStart from nStart to nStop
Scan AppTblTC:
;
;read postaction events
sleep(25)
;
  cCurTbl=AppTblTC."Name"
  cAlias1=APPCONV.SourceDir+"\\"+cCurTbl
  cAlias2=APPCONV.DestDir+"\\"+cCurTbl
;
if appInfoTC.open(APPCONV.SourceDir+"\\"+"appinfo.db") then
              if appInfoTC.locate("Item", "APPID") then
                   cAppID=appInfoTC."Desc"
        else
          msgInfo("","Application ID is Not Assigned")
          return
        endif
else
  ;use exchange id
  message("Appinfo.db not found. Using exchange id")
  cAppID=QRYRUNMS.ProjectID

;  errorshow()
endif
;
    ;MsgInfo("List Fields For:", cAlias1)
    Message("Listing Fields For:"+ cAlias1)
;
    if curTbl.attach(cAlias1) then
      ;do nothing
    else
      errorshow()
    endif
;
;    if curTbl.isTable() then
      ;list current table fields
;      if curTbl.enumFieldStruct("curFlds.db") then
              ;do nothing  
;      else
;        msgStop("Error", "Can't find current table.")
;            endif

            try
              curTbl.enumFieldStruct("curFlds.db")                    ; try to set color of box5
            onFail                                     ; handle the error
              message("Read Error...")
;      reTry                                   ; try again
            endTry




;    else
;      errorshow()
;    endif

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

CURFLDS.DB | Field Name | Type   | Size   | Dec    | Key    | _Required Value |
           | _join1     | _join2 | _join3 | _join4 | _join5 | _join6          |

CURFLDS.DB | _Min Value | _Max Value | _Default Value | _Picture Value |
           | _join7     | _join8     | _join9         | _join10        |

CURFLDS.DB | _Table Lookup | _Table Lookup Type | _Invariant Field ID |
           | _join11       | _join12            | _join13             |

APPSTRCS.DB | AppID    | TableName | Field Name | Type   | Size   | Dec    | Key    |
Insert      | ~cAppId  | ~cCurTbl  | _join1     | _join2 | _join3 | _join4 | _join5 |

APPSTRCS.DB | _Required Value | _Min Value | _Max Value | _Default Value |
            | _join6          | _join7     | _join8     | _join9         |

APPSTRCS.DB | _Picture Value | _Table Lookup | _Table Lookup Type |
            | _join10        | _join11       | _join12            |

APPSTRCS.DB | _Invariant Field ID |
            | _join13             |

EndQuery

;if NOT executeQBE(QryThisRep) then errorshow() endif

      try
              executeQBE(QryThisRep)                    ; try to set color of box5
            onFail                                     ; handle the error
              message("Read Error...")
        sleep(1000)
;      reTry                                   ; try again
            endTry

  message("Mapping "+cAlias1)

  ;reset field counter for current table
  nCntr=1
  ;end scan for records in current table  

;  endScan
;reset counter for total data elements
nCntr2=1
;
;delete table of fields for current table
if curTbl.attach(cCurTbl) then
 ;do nothing
else
 errorShow()
endIf

if curTbl.isTable() then
  curTbl.delete()
  message(cCurTbl, " deleted.")
else
  message("Can't find ", cCurTbl, ".")
endIf
;
;if not AppTblTC.atLast() then
;  AppTblTC.nextRecord()
;else
;  AppTblTC.movetoRecord(1)
;endif
  ;assign current date
  ;
  dCurDateTime=datetime()
;end for scan tables for application
endScan
;endfor
;
;ThisRepRptInfo.Name="APPSTRCS"
;
if PrintBackwards="T" then
  ThisRepRptInfo.printBackwards=True
else
  ThisRepRptInfo.printBackwards=False
endif
;
if View="T" then
  RptThisRep.open(ThisRepRptInfo)
else
  RptThisRep.print(ThisRepRptInfo)
endif
;
endmethod

method pushButton(var eventInfo Event)
var
  cItem, cProjectId String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  thisTbl Table
endVar

cTitle=self.labeltext
cProjectId=QRYRUNMS.ProjectId
msgStop("Printing Report", cTitle)

QryThisRep=Query
ANSWER: :PRIV:DDEnt2.DB

Appstrcs.db | TableName |
            | Check     |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("DDEnt2.DB")      thisTbl.compact()      message("DDEnt2.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

:PRIV:DDEnt2.DB | TableName |
                      | _join1    |

DATAENT.DB | Entity_name |
Insert     | _join1      |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("APPSTRCS.DB")      thisTbl.compact()      message("APPSTRCS.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

APPSTRCS.DB | AppID  | TableName | Field Name | Type   | Size   |
                        | _join1 | _join2    | _join3     | _join4 | _join5 |

DATAFLD.DB | Entity_name | Field_name | Dept_or_app_name | Data_type | Size   |
Insert     | _join2      | _join3     | _join1           | _join4    | _join5 |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif

QryThisRep=Query
ANSWER: :PRIV:DDFilAp.DB

Datafld.db | Dept_Or_App_Name |
           | Check            |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("DDFilAp.DB")      thisTbl.compact()      message("DDFilAp.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

:PRIV:DDFilAp.DB | Dept_or_app_name |
                                | _join1           |

DDAPPLST.DB | Dept_or_app_name |
Insert     | _join1           |

EndQuery



if NOT executeQBE(QryThisRep) then errorshow() endif

QryThisRep=Query
ANSWER: :PRIV:DDApp.DB

Appstrcs.db | AppID  |
                        | Check  |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("DDApp.DB")      thisTbl.compact()      message("DDApp.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

:PRIV:DDApp.DB | AppID  |
                              | _join1 |

DATAREP.DB | ObjectID | Dept_or_app_name | Entity_name |
Insert     | _join1   | _join1           | _join1      |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("Dataent.db")      thisTbl.compact()      message("Dataent.db")
thisTbl.unAttach()

thisTbl.attach("Datafld.db")      thisTbl.compact()      message("Datafld.db")
thisTbl.unAttach()

thisTbl.attach("Datarep.db")      thisTbl.compact()      message("Datarep.db")
thisTbl.unAttach()



QryThisRep=Query
ANSWER: :PRIV:DDEntApp.DB

Dataent.db | Entity_name |
                    | Check _a    |

Datafld.db | Entity_Name | Dept_Or_App_Name |
                    | Check _a    | Check _b         |

Datarep.db | ObjectID | Dept_or_app_name |
                    | Check    | Check _b         |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif



QryThisRep=Query
ANSWER: :PRIV:DDEntAp3.DB

DDEntApp.DB | Dept_Or_App_Name | Entity_name  | ObjectID | TableClass |
                        | Check _join1     | Check _join2 | Check    | Check      |

:PRIV:DDEntApp.DB | Entity_name   | Dept_Or_App_Name |
                                    | Check _join2! | Check _join1!    |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

:PRIV:DDEntAp3.DB | Dept_Or_App_Name | Entity_name | Entity_name_1 |
Delete            | _join1           | _join2      | _join2        |

:PRIV:DDEntAp3.DB | Dept_Or_App_Name_1 |
                                    | _join1             |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

thisTbl.attach("DDEntAp3.DBb")      thisTbl.compact()      message("DDEntAp3.DB")
thisTbl.unAttach()

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

DDEntApp.DB | Dept_Or_App_Name | Entity_name |
Insert      | _join1           | _join2      |

:PRIV:DDEntAp3.DB | Entity_name_1 | Dept_Or_App_Name_1 |
                                    | _join2        | _join1             |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif


endmethod


Create ODBC compliant table names

method pushButton(var eventInfo Event)
var
  cThisTxt, cNewTxt, cTextErr String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  thisTC TCursor
  nTxtSize Number
endVar

message("Processing")

nCntr=0
nTxtSize=0
cThisTxt=""
cNewTxt=""
cTextErr=""

;example usage of tcursor and scan loop

ENTMDL2.edit()

thisTC.attach(ENTMDL2)

scan thisTC:
ENTMDL2.resync(thisTC)

cThisTxt=""
cNewTxt=""
nCntr=nCntr =1
cThisTxt = ENTMDL2.Entity_Name
nTxtSize=cThisTxt.size()

For nCntr from 1 to nTxtSize

;create odbc compliant field names
;remove -, _, " "

cTxttmp = cThisTxt.substr(nCntr, 1)  ; get the area code

if cTxttmp = "-" or cTxttmp = "_" or cTxttmp = " "
 or cTxttmp = "'" or cTxttmp = "." then

       ;do nothing
       cTextErr=cTextErr+cTxttmp
else
       cNewTxt=cNewTxt+cTxttmp
endIf

endFor
 ENTMDL2.NewEntityName=cNewTxt
 cNewTxt=""

cNewTxt=""
nCntr=1
cThisTxt = ENTMDL2.NewEntityName
nTxtSize=cThisTxt.size()


For nCntr from 1 to nTxtSize

 cTxttmp = cThisTxt.substr(nCntr, 2)

if cTxttmp = "DB" then
     nCntr=nCntr+1
       ;do nothing
else
        cNewTxt=cNewTxt+cTxttmp.substr(1, 1)
endIf

endFor
 ENTMDL2.NewEntityName=cNewTxt
 cNewTxt=""


endScan

msgInfo("Errors Found!", cTextErr)

ENTMDL2.endEdit()
ENTMDL2.moveToRecord(1)

Endmethod

Create ODBC compliant field names

method pushButton(var eventInfo Event)
var
  cThisTxt, cNewTxt, cTextErr String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  thisTC TCursor
  nTxtSize Number
endVar

message("Processing")

nCntr=0
nTxtSize=0
cThisTxt=""
cNewTxt=""
cTextErr=""

;example usage of tcursor and scan loop

ENTMDL2.edit()

thisTC.attach(ENTMDL2)

scan thisTC:
ENTMDL2.resync(thisTC)

cThisTxt=""
cNewTxt=""
nCntr=1
cThisTxt = ENTMDL2.Field_Name
nTxtSize=cThisTxt.size()

For nCntr from 1 to nTxtSize

;create odbc compliant field names
;remove -, _, " "

cTxttmp = cThisTxt.substr(nCntr, 1)  ; get the area code

if cTxttmp = "-" or cTxttmp = "_" or cTxttmp = " "
or cTxttmp = "'" or cTxttmp = "." then
       ;do nothing
       cTextErr=cTextErr+cTxttmp
else
       cNewTxt=cNewTxt+cTxttmp
endIf

endFor
 ENTMDL2.NewFieldName=cNewTxt
 ENTMDL2.FNAltLabelSize=cNewTxt.size()

 cNewTxt=""
endScan

msgInfo("Errors Found!", cTextErr)

ENTMDL2.endEdit()
ENTMDL2.moveToRecord(1)

Endmethod

Default to prior version fieldnames

method pushButton(var eventInfo Event)
var
  cItem, cAppID2 String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
endVar

cTitle=self.labeltext
cAppID=DDAPPLST.Dept_or_app_name

msgStop("Processing Report", cTitle)
;msgStop("Report", "Not Available at this time")

;To execute a query string place the query code(beginning with "Query" and
;ending with endQuery) after the = sign below.
;Then uncomment the next two lines lines by deleting the semicolon.
QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

EntMdl.DB | EnterpriseModel | Field_Name | Dept_Or_App_Name |
                   | ~cAppID         | _a         | ~cAppID          |

EntMdl.DB | NewFieldName |
                   | changeto _a  |

EndQuery


;After the last transaction table has been calculated it can be used
;to link back to the poltran.db to create reports

endmethod

Default alternate field name to prior version fieldnames

method pushButton(var eventInfo Event)
var
  cItem, cAppID2 String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
endVar

cTitle=self.labeltext
cAppID=DDAPPLST.Dept_or_app_name

msgStop("Processing Report", cTitle)
;msgStop("Report", "Not Available at this time")

;To execute a query string place the query code(beginning with "Query" and
;ending with endQuery) after the = sign below.
;Then uncomment the next two lines lines by deleting the semicolon.

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

EntMdl.DB | EnterpriseModel | NewFieldName | Dept_Or_App_Name |
                   | ~cAppID         | _a         | ~cAppID          |

EntMdl.DB | NewFieldNameAlt |
                   | changeto _a     |

EndQuery



if NOT executeQBE(QryThisRep) then errorshow() endif



;After the last transaction table has been calculated it can be used
;to link back to the poltran.db to create reports

endmethod

Default field attributes

method pushButton(var eventInfo Event)
var
  cItem String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
endVar

cTitle=self.labeltext
cAppID=PrevCurrBox.Dept_or_app_name
cAppID2=PrevCurrBox.Dept_or_app_name2

msgStop("Processing Report", cTitle)
QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Allstrcs.db | AppID  | TableName | Field Name | Type | Size | Key |
                        | _join1 | _join2    | _join3     | _a   | _b   | _c  |

EntMdl.DB | EnterpriseModel | Dept_Or_App_Name |  Entity_Name | Field_Name | Data_Type          |
                   |                 | ~cAppID _join1   | _join2      | _join3     | blank, changeto _a |

EntMdl.DB | Size               | Key                 |
                   | blank, changeto _b | blank, changeto _c  |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Appstrcs.db | AppID  | TableName | Field Name | Type | Size | Key |
                        | _join1 | _join2    | _join3     | _a   | _b   | _c  |

EntMdl.DB | EnterpriseModel | Dept_Or_App_Name |  Entity_Name | Field_Name | Data_Type          |
                   |                 | ~cAppID _join1   | _join2      | _join3     | blank, changeto _a |

EntMdl.DB | Size               | Key                 |
                   | blank, changeto _b | blank, changeto _c  |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

;After the last transaction table has been calculated it can be used
;to link back to the poltran.db to create reports

endmethod

Lookup invariant field id

method pushButton(var eventInfo Event)
var
  cItem String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
endVar

cTitle=self.labeltext
msgStop("Processing Report", cTitle)
QryThisRep=Query
ANSWER: :PRIV:EntMdl17.DB

EntMdl.DB | Entity_Name | Field_Name | Dept_Or_App_Name | _Invariant Field ID |
                   | _join2      | _join3     | _join1           | changeto _a         |

Allstrcs.db | AppID  | TableName | Field Name | _Invariant Field ID |
                        | _join1 | _join2    | _join3     | _a                  |

EndQuery


;if NOT executeQBE(QryThisRep) then errorshow() endif
QryThisRep=Query

ANSWER: :PRIV:EntMdl17.DB

EntMdl.DB | Entity_Name | Field_Name | Dept_Or_App_Name | _Invariant Field ID |
                   | _join2      | _join3     | _join1           | changeto _a         |

Appstrcs.db | AppID  | TableName | Field Name | _Invariant Field ID |
                        | _join1 | _join2    | _join3     | _a                  |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif

;After the last transaction table has been calculated it can be used
;to link back to the poltran.db to create reports

endmethod

Check for non-odbc compliant characters

method pushButton(var eventInfo Event)
var
  cThisTxt, cNewTxt, cTextErr String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  ThisRepRptInfo ReportPrintInfo
  thisTC TCursor
  nTxtSize Number
endVar

message("Processing")

msgStop("More", "More to come...")


nCntr=0
nTxtSize=0
cThisTxt=""
cNewTxt=""
cTextErr=""

;example usage of tcursor and scan loop

ENTMDL2.edit()

thisTC.attach(ENTMDL2)

scan thisTC:
ENTMDL2.resync(thisTC)

cThisTxt=""
cNewTxt=""
nCntr=nCntr =1
cThisTxt = ENTMDL2.Field_Name
nTxtSize=cThisTxt.size()

For nCntr from 1 to nTxtSize

;create odbc compliant field names
;remove -, _, " "

cTxttmp = cThisTxt.substr(nCntr, 1)  ; get the area code

if cTxttmp = "#" or cTxttmp = "$" or cTxttmp = "%" then
       ;do nothing
       cTextErr=cTextErr+cTxttmp

       if cTxttmp = "#" then
            cNewTxt=cNewTxt+"No"
       endIf
       if cTxttmp = "$" then
            cNewTxt=cNewTxt+"No"
       endIf
       if cTxttmp = "%" then
            cNewTxt=cNewTxt+"No"
       endIf

else
       cNewTxt=cNewTxt+cTxttmp
endIf


endFor
 ENTMDL2.NewFieldName=cNewTxt
nTxtSize =  NewFieldNameAlt.size()

 ENTMDL2.FNAltLabelSize=nTxtSize
 cNewTxt=""
endScan

msgInfo("Errors Found!", cTextErr)

ENTMDL2.endEdit()
ENTMDL2.moveToRecord(1)

Endmethod

Read file list

method pushButton(var eventInfo Event)
var
  cItem, tblName, cProjectID String
  QryThisRep Query
  tblVar Table
endVar
cTitle=self.labeltext
msgStop("Processing", cTitle)
;msgStop("Report", "Not Available at this time")
tblName = "Images.DB"
cProjectID=AppListBox.DDAPPLST.Dept_or_app_name
tblVar.attach(tblName)
if UseDD = "Y" then
if ListAll = "N" then

QryThisRep=Query
ANSWER: :PRIV:Ruletmp.DB

Ddfiles.db | APPID                 | Name   | FileExtension      |
                    | Check _a, ~cProjectID | Check  | Check SC |

Appconv.db | ProjectID | SourceDir |
                    | _a        | Check     |

EndQuery
if NOT executeQBE(QryThisRep) then errorshow() endif
else

QryThisRep=Query
ANSWER: :PRIV:Ruletmp.DB

Ddfiles.db | APPID    | Name   | FileExtension      |
                    | Check _a | Check  | Check SC  |

Appconv.db | ProjectID | SourceDir |
                    | _a        | Check     |

EndQuery
if NOT executeQBE(QryThisRep) then errorshow() endif
endIf

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Images.db | ImageAlias | ImagePath | ImageFileName |
Insert    | _c         | _b        | _a            |

:PRIV:Ruletmp.DB | APPID | Name | SourceDir |
                                | _c    | _a   | _b        |

EndQuery
if NOT executeQBE(QryThisRep) then errorshow() endif

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Images.db | ImageSeqNo | ImageFileName | ImageAlias |
                   | _b         | _a            | _c         |

Rules.DB | RuleSeqNo | RuleName | RuleClass | RuleFileName | Dept_Or_App_Name |
Insert      | _b        | _a       | UI        | _a           | _c               |

EndQuery
if NOT executeQBE(QryThisRep) then errorshow() endif

else

if ListAll = "N" then

QryThisRep=Query
ANSWER: :PRIV:Ruletmp.DB

Allfiles.db | APPID                 | Name   | FileExtension      |
                        | Check _a, ~cProjectID | Check  | Check  |

Appconv.db | ProjectID | SourceDir |
                    | _a        | Check     |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

else

QryThisRep=Query
ANSWER: :PRIV:Ruletmp.DB

Allfiles.db | APPID    | Name   | FileExtension      |
                        | Check _a | Check  | Check |

Appconv.db | ProjectID | SourceDir |
                    | _a        | Check     |

EndQuery

if NOT executeQBE(QryThisRep) then errorshow() endif

endIf

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Images.db | ImageSeqNo | ImageFileName | ImageAlias |
                   | _b         | _a            | _c         |

Rules.DB | RuleSeqNo | RuleName | RuleClass | RuleFileName | Dept_Or_App_Name |
Insert   | _b        | _a       | UI        | _a           | _c               |

EndQuery
 
if NOT executeQBE(QryThisRep) then errorshow() endif

endIf

endMethod


Read rules

method pushButton(var eventInfo Event)
var
  cItem, cTmp String
  QryThisRep Query
  UiObjTC, ImagesTC TCursor
  cFileExt,cFileName, cImageFile  String
  nExtPos Number
  MemoFile Memo
endVar
;example usage of tcursor and scan loop
UIObj.edit()
UIObjTC.attach(UIObj)
ImagesTC.attach(Images)
scan UIObjTC:
UIObj.resync(UIObjTC)
cTitle=self.labeltext
message("Processing", cTitle)
UIObj.resync(UIObjTC)
       ImagesTC.attach(Images)
       if ImagesTC.locate("ImageAlias",  UIOBJ.Dept_Or_App_Name,"ImageFileName",  UIOBJ.RuleFileName) then
              cImageFile=ImagesTC.ImagePath+"\\"+ImagesTC.ImageFileName
              If UIObj.SkipRule = "Y" Then
                   ;do nothing
              else
                   ;read rule
                   try
                        MemoFile.readFromFile(cImageFile)
                        UIObj.RuleScript = MemoFile
                        UIObj.SkipRule = "Y"
                   onFail                                     ; handle the error
                        message("Assignment Error")
                        UIObj.cancelEdit()
                        UIObj.Edit()
                        UIObj.RuleScript = "Read Error..."
                        UIObj.SkipRule = "Y"
                        UIObj.endEdit()
                   endTry
              endIf
            else
              ;do nothing
;              errorshow()
              message("Can't read that")
;              loop
;            endIf
       endIf
endScan
UIObj.endEdit()
endMethod


Write files to text

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cMemoStr, cMemoIndex String
  QryThisRep Query
  UIObjTC, ImagesTC TCursor
  cFileExt,cFileName, cImageFile  String
  nExtPos Number
  MemoFile, MemoFileAll Memo
  ts               TextStream
  firstLine        String
  allLines Array[] String
  fs               FileSystem

endVar
;example usage of tcursor and scan loop
cTitle=self.labeltext
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
UIObj.edit()
UIObjTC.attach(UIObj)
ImagesTC.attach(Images)
scan UIObjTC:
UIObj.resync(UIObjTC)
message("Processing", cTitle)
UIObj.resync(UIObjTC)
       ImagesTC.attach(Images)
       ; find image
            MemoFile = ";@"+UIObj.Dept_Or_App_Name+"^m"+UIObj.RuleFileName +"^p"+UIObj.RuleScript             ; if not, write to MemoFile var
            MemoFileAll = MemoFileAll + MemoFile
;            cMemoStr = cMemoStr + MemoFile
            MemoFile.writeToFile("Rules\\"+UIObj.Dept_Or_App_Name+UIObj.RuleFileName) ; write MemoFile to text file
            cMemoIndex = cMemoIndex + ";@" + UIObj.Dept_Or_App_Name + "^m" + UIObj.SeqOrder + "^m" + "^m" + UIObj.RuleFileName
endScan
UIObj.endEdit()
cApp=UIObj.Dept_Or_App_Name
MemoFileAll.writeToFile("Rules\\"+cApp)
; Create a file, open for writing and reading
  ts.create("Rules\\"+cApp+"Index")  
  ts.writeLine(cMemoIndex)
  ts.home()
  ts.readLine(allLines)
  allLines.view("Index:")
UIObj.moveToRecord(1)
endMethod


Read rule lines

method pushButton(var eventInfo Event)
var
  cItem, tblName, cProjectID, cRuleFile String
  tblVar Table
  dtHandle                  DataTransfer      ;// handle to transfer variable
  tCreate                  Table                        ;// tmp var for creating tables
  tcHandle                  TCursor                  ;// tmp var for filling spec table
  tvTable                  TableView            ;// tmp handle for displaying imported file
  QryThisRep Query  
endVar

cTitle=self.labeltext
msgStop("Processing", cTitle)
;msgStop("Report", "Not Available at this time")
tblName = "Images.DB"
cProjectID=UIOBJ.Dept_Or_App_Name
cRuleFile=UIOBJ.RuleFileName

      ;// Set notification for user
      setMouseShape(MOUSEWAIT, TRUE)

      cThisRule=UIObj.Dept_Or_App_Name+UIObj.RuleFileName

      dtHandle.setSource(":WORK:Rules\\"+cThisRule, dtASCIIFixed)
      dtHandle.setSourceCharSet(dtANSI)

      ;// Enable the key violation and problems tables
      ;//
      dtHandle.setKeyViol(TRUE)
      dtHandle.setProblems(TRUE)

      ;// Create the fixed length specification file
      ;//
      tCreate = create ":PRIV:IMPORT.DB"
            with      "Field Name"      : "A31",
                        "Type"                  : "A31",
                        "Start"                  : "S",
                        "Length"                  : "S"
      endCreate

      ;// Fill the fixed length specification file
      ;//
      tcHandle.open(tCreate)
      tcHandle.edit()
      tcHandle.insertAfterRecord()
      tcHandle."Field Name"      = "Field1"
      tcHandle."Type"                  = "A"
      tcHandle."Start"                  = 1
      tcHandle."Length"                  = 94

      ;// Close and release table handles
      ;//
      tcHandle.close()
;      tCreate.unAttach()

      ;// Load fixed length specification file
      ;//
      dtHandle.loadSourceSpec(":PRIV:IMPORT.DB")

      ;// Create destination table; this is not necessary, but it
      ;// assures that the correct user entered field SIZES are created
      ;//
      if isTable(":WORK:Rules\\ReadLines.DB") then
             delete(":WORK:Rules\\ReadLines.DB")
      endIf

      tCreate = create ":WORK:Rules\\ReadLines.DB"
                              with "Field1"      :      "A94"
             endCreate
 


      ;// Release table handle
      ;//
;      tCreate.unAttach()

      ;// We are always appending in this script since we force the
      ;// destination table to always exist
      ;//
      dtHandle.setDest(":WORK:Rules\\ReadLines.DB")
      dtHandle.setAppend(TRUE)

      ;// Perform the import
      ;//
      dtHandle.transferData()

      ;// Reset cursor for the user
      ;//
      setMouseShape(MOUSEARROW, TRUE)

      ;// Display table
      ;//
;      tvTable.open(":WORK:Rules\\ReadLines.DB")

QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Rules\ReadLines.DB | Field1 |
                                     | _join1 |

RlPrcMgr.DB | Dept_Or_App_Name | RuleName   | ScriptRows |
Insert      | ~cProjectID      | ~cRuleFile | _join1     |

EndQuery


if NOT executeQBE(QryThisRep) then errorshow() endif

RLPRCMGR.moveToRecord(1)

endMethod


Write script quieries

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cMemoStr, cMemoIndex, cThisString, cQryStr String
  dBegDate, dEndDate Date
  RLPRCMGRTC, ImagesTC TCursor
  cFileExt, cFileName, cImageFile  String
  nExtPos Number
  thisText TextStream
endVar
;example usage of tcursor and scan loop
cTitle=self.labeltext
message("Processing", cTitle)
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
lBeginQry = "N"
cQryStr = ""
;UIObj.edit()
 PDQBEBox.RLQBEMRO.edit()
RLPRCMGRTC.attach(RLPRCMGR)
;ImagesTC.attach(Images)
;scan for Query endQuery substrings
scan RLPRCMGRTC:
RLPRCMGR.resync(RLPRCMGRTC)
;assign string to variable
cThisString = UIOBJ.ProcessManagerBox.RLPRCMGR.ScriptRows
;search for Query
if cThisString.search("Query") > 0 then
 lBeginQry = "Y"
endIf
if cThisString.search("Endquery") > 0 then
 lBeginQry = "N"
endIf
If lBeginQry = "Y" then
cQryStr = cQryStr + "\010" + cThisString
;search for endQuery
endIF
if cThisString.search("Endquery") > 0 then
 cQryStr = cQryStr + "\010" + cThisString
 lBeginQry = "N"
 ;found end of query, write text file and reset
 lBeginQry = "N"
 cThisFilePath = SourceDir+"\\"+UIOBJ.RuleName.substr(1,((UIOBJ.RuleName.search("."))-1)) +UIOBJ.ProcessManagerBox.RLPRCMGR.RowSeqNo+".qbe"
 thisText.open(cThisFilePath, "nw")         ; open a new file
 thisText.writeString(cQryStr)  
 PDQBEBox.RLQBEMRO.ScriptRows=cQryStr
 PDQBEBox.RLQBEMRO.ScriptRows.nextRecord()

 msgInfo("File size:", string(cQryStr.size()))  ; displays 19
 thisText.close()
 cQryStr=""
endIf
endScan
;UIObj.endEdit()
 PDQBEBox.RLQBEMRO.endEdit()
RLPRCMGR.moveToRecord(1)
endMethod


Assign script link id's

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cMemoStr, cMemoIndex, cThisString, cQryStr String
  RLPRCMGRTC, ImagesTC TCursor
  cFileExt,cFileName, cImageFile  String
  nExtPos Number
  fileBmp Graphic
  Cust UIObject
endVar
;example usage of tcursor and scan loop
cTitle=self.labeltext
message("Processing", cTitle)
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
lBeginQry = "N"
cQryStr = ""
RLPRCMGR.edit()
RLPRCMGRTC.attach(RLPRCMGR)
;ImagesTC.attach(Images)
;scan for Query endQuery substrings
scan RLPRCMGRTC:
RLPRCMGR.resync(RLPRCMGRTC)
;assign string to variable
cThisString = UIOBJ.ProcessManagerBox.RLPRCMGR.ScriptRows
if cThisString.search("Endquery") > 0 then
; lBeginQry = "N"
UIOBJ.ProcessManagerBox.RLPRCMGR.RuleLinkID=UIOBJ.ProcessManagerBox.RLPRCMGR.RowSeqNo
endIf
endScan
RLPRCMGR.endEdit()
RLPRCMGR.moveToRecord(1)
endMethod


Replace changeto strings with checks

method pushButton(var eventInfo Event)
var
  cThisString, cQryStr, cComb, cChar String
  cThisScript String
  nPrCntr, nSize Number
  thisText TextStream
  fs FileSystem
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
cThisString = ""
cThisQry = ""
nPrCntr=0
n=0
i=1
cChar=""
cComb=""
nSize=0

;RLPDSMRO.edit()
cThisScript = UIOBJ.RuleScript

; cThisFilePath = SourceDir+"\\"+UIOBJ.RuleName.substr(1,((UIOBJ.RuleName.search("."))-1)) +UIOBJ.ProcessManagerBox.RLPRCMGR.RowSeqNo+".qbe"
; thisText.open(cThisFilePath, "r")         ; open a new file
; nSize=thisText.size()
; thisText.readChars(cQryStr)  
; msgInfo("File size:", string(cQryStr.size()))  ; displays 19
; thisText.close()

n=cThisScript.size()
for i from i to n
  cChar = cThisScript.substr(i, 1)
  cComb = cThisScript.substr(i, 8)
  if cComb = "CHANGETO" or cComb = "Changeto" or cComb = "changeto" then
       ;do nothing
       ;find double paragraphs 013 carriage, 010 line feed
;       if nPrCntr < 2 then
            cThisString = cThisString+"Check"
;       endIF
;       nPrCntr= nPrCntr+1
       i = i + 7
  else
       cThisString = cThisString+cThisScript.substr(i, 1)
;       if cChar = "\013" or cChar = "\010" then
;            ;do nothing
;       else
;            nPrCntr=1
;       endIf
  endIf
endFor
msgStop("cThisQry", cThisString)

RuleScript.Edit()
RuleScript=cThisString
RuleScript.endEdit()


endMethod

More psuedo code for changing the paradox local sql code to vba sql

Determine table names by FROM Clause, then select table names from EntMdl. Search for substring "FROM" begin position to crlf end posittion.
Create table names by FROM Clause, from EntMdl.
Determine table by FROM Clause, then select table from EntMdl.
Replace table String from Link 1
Determine dos path and table  from EntMdl.
Replace dos path and table  from EntMdl.
Determine expression having pipe operator.
Replace expression having pipe operator with plus operator and remove parenthesis.
Determine dos root path element .
Remove dos root path element.
Determine table join expression by From clause and comma separator. Lookup table name from EntMdl. Search for begin position of substring "FROM" to crlf for end position.
Replace table join expression with table from EntMdl.
Determine variable expression by "=" in SET clause with null.
Replace variable expression by "=" in SET clause with expression string.
Determine if table string is encapsulated.
Remove encapsulators from table string.
Determine encapsulators and non odbc compliant characters
Remove encapsulators and non odbc compliant characters
Determine variable expression by "~" in clause.
Replace variable expression "~" in clause.
Search for Number string.
Replace number string with ODBC compliant string.

Remove double paragraphs

method pushButton(var eventInfo Event)
var
  cThisString, cQryStr, cComb, cChar String
  cThisScript String
  nPrCntr Number
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
cThisString = ""
cThisQry = ""
nPrCntr=0
n=0
i=1
cChar=""
cComb=""

;RLPDSMRO.edit()
cThisScript = UIOBJ.RuleScript
n=cThisScript.size()
for i from i to n
  cChar = cThisScript.substr(i, 1)
  cComb = cThisScript.substr(i, 2)
  if cComb = "\013"+"\010" then
       ;find double paragraphs 013 carriage, 010 line feed
       if nPrCntr < 2 then
;            cThisString = cThisString+"\013"+"\010"
       endIF
       nPrCntr= nPrCntr+1
       i = i + 2
  else
       cThisString = cThisString+cThisScript.substr(i, 1)
       if cChar = "\013" or cChar = "\010" then
            ;do nothing
       else
            nPrCntr=1
       endIf
  endIf
endFor
msgStop("cThisQry", cThisString)

RuleScript.Edit()
RuleScript=cThisString
RuleScript.endEdit()


endMethod


Write the paradox local sql rule to text

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cMemoStr, cMemoIndex String
  dBegDate, dEndDate Date
  UiObjTC, ImagesTC  TCursor
  cFileExt, cFileName, cImageFile  String
  MemoFile, MemoFileAll Memo
  nExtPos Number
endVar
;example usage of tcursor and scan loop
cTitle=self.labeltext
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
UIObj.edit()
UIObjTC.attach(UIObj)
ImagesTC.attach(Images)
;scan UIObjTC:
;UIObj.resync(UIObjTC)
message("Processing", cTitle)
UIObj.resync(UIObjTC)
       ImagesTC.attach(Images)
       ; find image
            MemoFile = ";@"+UIObj.Dept_Or_App_Name+"^m"+UIObj.RuleFileName +"^p"+"@sql"+UIObj.PDSQLScript             ; if not, write to MemoFile var
            MemoFileAll = MemoFileAll + MemoFile
            MemoFile.writeToFile("Rules\\"+UIObj.Dept_Or_App_Name+"pds"+UIObj.RuleFileName) ; write MemoFile to text file
;endScan
UIObj.endEdit()
cApp=UIObj.Dept_Or_App_Name
MemoFileAll.writeToFile("Rules\\"+cApp+"pds")
endMethod

Read the paradox local sql lines into a table

method pushButton(var eventInfo Event)
var
  cItem, tblName, cProjectID, cRuleFile String
  QryThisRep Query
  tblVar Table
  dtHandle                  DataTransfer      
  tCreate                  Table                        
  tcHandle                  TCursor                  
  tvTable                  TableView      
endVar
cTitle=self.labeltext
msgStop("Processing", cTitle)
;msgStop("Report", "Not Available at this time")
cProjectID=UIOBJ.Dept_Or_App_Name
cRuleFile=UIOBJ.RuleFileName
      ;// Set notification for user
      setMouseShape(MOUSEWAIT, TRUE)
      cThisRule=UIObj.Dept_Or_App_Name+"pds"+UIObj.RuleFileName
      dtHandle.setSource(":WORK:Rules\\"+cThisRule, dtASCIIFixed)
      dtHandle.setSourceCharSet(dtANSI)
      ;// Enable the key violation and problems tables
      ;//
      dtHandle.setKeyViol(TRUE)
      dtHandle.setProblems(TRUE)
      ;// Create the fixed length specification file
      ;//
      tCreate = create ":PRIV:IMPORT.DB"
            with      "Field Name"      : "A31",
                        "Type"                  : "A31",
                        "Start"                  : "S",
                        "Length"                  : "S"
      endCreate
      ;// Fill the fixed length specification file
      ;//
      tcHandle.open(tCreate)
      tcHandle.edit()
      tcHandle.insertAfterRecord()
      tcHandle."Field Name"      = "Field1"
      tcHandle."Type"                  = "A"
      tcHandle."Start"                  = 1
      tcHandle."Length"                  = 255
      ;// Close and release table handles
      ;//
      tcHandle.close()
;      tCreate.unAttach()
      ;// Load fixed length specification file
      ;//
      dtHandle.loadSourceSpec(":PRIV:IMPORT.DB")
      ;// Create destination table; this is not necessary, but it
      ;// assures that the correct user entered field SIZES are created
      ;//
      if isTable(":WORK:Rules\\ReadLine.DB") then
             delete(":WORK:Rules\\ReadLine.DB")
      endIf
      tCreate = create ":WORK:Rules\\ReadLine.DB"
                              with "Field1"      :      "A255"
                         endCreate
      ;// Release table handle
      ;//
;      tCreate.unAttach()
      ;// We are always appending in this script since we force the
      ;// destination table to always exist
      ;//
      dtHandle.setDest(":WORK:Rules\\ReadLine.DB")
      dtHandle.setAppend(TRUE)
      ;// Perform the import
      ;//
      dtHandle.transferData()
      ;// Reset cursor for the user
      ;//
      setMouseShape(MOUSEARROW, TRUE)
      ;// Display table
      ;//
;      tvTable.open(":WORK:Rules\\ReadLines.DB")
QryThisRep=Query
ANSWER: :PRIV:ANSWER.DB

Rules\ReadLine.DB | Field1 |
                                     | _join1 |

RlPDSMgr.DB | Dept_Or_App_Name | RuleName   | ScriptRows |
Insert      | ~cProjectID      | ~cRuleFile | _join1     |

EndQuery
if NOT executeQBE(QryThisRep) then errorshow() endif
RLPDSMGR.moveToRecord(1)
endMethod


Read the paradox local sql rules into a table

method pushButton(var eventInfo Event)
var
  cMemoStr, cMemoIndex, cThisString, cQryStr String
  RLPDSMGRTC, RLPRCMGRTC, ImagesTC TCursor
  nExtPos Number
  thisText TextStream
  firstLine, cThisFilePath, lRuleBeginsNextRow        String
  allLines Array[] String
  fs               FileSystem
  dy DynArray[] String
  n Number

endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
lBeginQry = "N"
cQryStr = ""
lRuleBeginsNextRow = "T"
n = 1

;find script sequence ID's
RLPRCMGRTC.attach(RLPRCMGR)
;ImagesTC.attach(Images)

;scan for Query endQuery substrings
scan RLPRCMGRTC:
RLPRCMGR.resync(RLPRCMGRTC)

;assign string to variable
cThisString = UIOBJ.ProcessManagerBox.RLPRCMGR.RuleLinkID
;search for Query
;if cThisString.search("Query") > 0 then
; lBeginQry = "Y"
;endIf

if not isBlank(cThisString) then
; load array

dy[n]     = cThisString           ; load the DynArray
n=n+1
endIf

endScan
RLPRCMGR.endEdit()

RLPRCMGR.moveToRecord(1)

;dy.view()


n=1

RLPDSMGR.edit()
RLPDSMGRTC.attach(RLPDSMGR)
;ImagesTC.attach(Images)

;scan for Query endQuery substrings
scan RLPDSMGRTC:
RLPDSMGR.resync(RLPDSMGRTC)

;assign string to variable
cThisString = UIOBJ.ProcessManagerBox.RLPDSMGR.ScriptRows

if lRuleBeginsNextRow = "T" then
 UIOBJ.ProcessManagerBox.RLPDSMGR.RuleLinkID = dy[n]
 n=n+1
 lRuleBeginsNextRow = "F"
endIf

if isBlank(cThisString) then
; lBeginQry = "N"
 lRuleBeginsNextRow = "T"
endIf

if not isBlank(cThisString) then
; lBeginQry = "N"
 if cThisString.subStr(1, 1) = "@" then
  lRuleBeginsNextRow = "T"
 endIf
endIf

endScan
RLPDSMGR.endEdit()

RLPDSMGR.moveToRecord(1)

             
endMethod

Read the paradox local sql rules into the object model database

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cThisString, cQryStr String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  RLPRCMGRTC, ImagesTC, RLPDSMROTC, UIOBJTC TCursor
  cThisScript, cFoundRow, cThisQry  String
  nExtPos, i, n, nRowStart, nLastQryStart Number
  fileBmp Graphic
  Cust UIObject
  MemoFile, MemoFileAll Memo
  fs               FileSystem
  thisText TextStream
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
lBeginQry = "N"
cQryStr = ""
i=1
cThisString = ""
nLastQryStart = 1
cThisQry = ""

;UIOBJTC.Attach(UIOBJ)
RLPDSMRO.edit()
;scan UIOBJTC:
;UIOBJ.resync(UIOBJTC)

;assign string to variable
cThisScript = UIOBJ.PDSQLScript
n=cThisScript.size()
;loop and find text blocks separated by a blank line
for i from i to n
  cFoundRow = cThisScript.substr(i, 3)
  cThisString = cThisString+cThisScript.substr(i, 1)

  if cFoundRow = "\013"+"\010"+"\013" then
  cFoundRow = cThisScript.substr(i, 3)
       ;row found
       nRowStart=i
       ;assign first query block to variable
       cThisQry = cThisScript.substr(nLastQryStart, nRowStart-nLastQryStart)
       PDQBEBox.RLQBEMRO.RuleLinkID=nLastQryStart
       PDQBEBox.RLQBEMRO.ScriptRows.nextRecord()
       UIOBJ.PFSQLMROBox.RLPDSMRO.ScriptRows=cThisQry
       UIOBJ.PFSQLMROBox.RLPDSMRO.RuleLinkID=nLastQryStart
       UIOBJ.PFSQLMROBox.RLPDSMRO.ScriptRows.nextRecord()
       UIOBJ.PFSQLMROBox.RLPDIMRO.ScriptRows=cThisQry
       UIOBJ.PFSQLMROBox.RLPDIMRO.RuleLinkID=nLastQryStart
       UIOBJ.PFSQLMROBox.RLPDIMRO.ScriptRows.nextRecord()
      
       nLastQryStart =  nRowStart
;    msgStop("cThisQry", cThisQry)
  endIf
endFor
;msgStop("cThisString", cThisString)
;msgStop("cThisScript", cThisScript)

;endScan

RLPDSMRO.endEdit()
;RLPDSMRO.moveToRecord(1)
RLPDIMRO.endEdit()
;RLPDIMRO.moveToRecord(1)
;UIOBJ.moveToRecord(1)
PDQBEBox.RLQBEMRO.ScriptRows.moveToRecord(1)

endMethod


Assign object link id's

method pushButton(var eventInfo Event)
var
  cItem, cTmp, cApp, cThisString, cQryStr String
  dBegDate, dEndDate Date
  RptThisRep Report
  QryThisRep Query
  RLPRCMGRTC, ImagesTC, RLPDSMROTC, UIOBJTC TCursor
  cThisScript, cFoundRow, cThisQry  String
  nExtPos, i, n, nRowStart, nLastQryStart Number
  fileBmp Graphic
  Cust UIObject
  MemoFile, MemoFileAll Memo
  fs               FileSystem
  thisText TextStream
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
MemoFile = ""
MemoFileAll = ""
cMemoStr = ""
cMemoIndex = ""
lBeginQry = "N"
cQryStr = ""
i=1
cThisString = ""
nLastQryStart = 1
cThisQry = ""

;UIOBJTC.Attach(UIOBJ)
RLPDSMRO.edit()
;scan UIOBJTC:
;UIOBJ.resync(UIOBJTC)

;assign string to variable
cThisScript = UIOBJ.PDSQLScript
n=cThisScript.size()
;loop and find text blocks separated by a blank line
for i from i to n
  cFoundRow = cThisScript.substr(i, 3)
  cThisString = cThisString+cThisScript.substr(i, 1)

  if cFoundRow = "\013"+"\010"+"\013" then
  cFoundRow = cThisScript.substr(i, 3)
       ;row found
       nRowStart=i
       ;assign first query block to variable
       cThisQry = cThisScript.substr(nLastQryStart, nRowStart-nLastQryStart)
;       PFSQLMROBox.RLPDSMRO.ScriptRows=cThisQry
       PDQBEBox.RLQBEMRO.RuleLinkID=nLastQryStart
       PDQBEBox.RLQBEMRO.ScriptRows.nextRecord()
       UIOBJ.PFSQLMROBox.RLPDSMRO.RuleLinkID=nLastQryStart
       UIOBJ.PFSQLMROBox.RLPDSMRO.ScriptRows.nextRecord()
;       PFSQLMROBox.RLPDIMRO.ScriptRows=cThisQry
       UIOBJ.PFSQLMROBox.RLPDIMRO.RuleLinkID=nLastQryStart
       UIOBJ.PFSQLMROBox.RLPDIMRO.ScriptRows.nextRecord()
       UIOBJ.PFSQLMROBox.RLMODMRO.RuleLinkID=nLastQryStart
       UIOBJ.PFSQLMROBox.RLMODMRO.ScriptRows.nextRecord()
       nLastQryStart =  nRowStart
;    msgStop("cThisQry", cThisQry)
  endIf
endFor
;msgStop("cThisString", cThisString)
;msgStop("cThisScript", cThisScript)

;endScan

RLPDSMRO.endEdit()
RLPDSMRO.moveToRecord(1)
RLPDIMRO.endEdit()
RLPDIMRO.moveToRecord(1)
;UIOBJ.moveToRecord(1)
PDQBEBox.RLQBEMRO.ScriptRows.moveToRecord(1)
UIOBJ.PFSQLMROBox.RLPDSMRO.ScriptRows.moveToRecord(1)
UIOBJ.PFSQLMROBox.RLPDIMRO.ScriptRows.moveToRecord(1)
UIOBJ.PFSQLMROBox.RLMODMRO.ScriptRows.moveToRecord(1)

endMethod


Add rule close characters

method pushButton(var eventInfo Event)
var
  cThisScript, cFoundRow, cThisQry  String
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
cThisString = ""
RLPDSMRO.edit()
cThisScript = UIOBJ.PDSQLScript
UIOBJ.PDSQLScript = cThisScript + "\013"+"\010" + "\013"+"\010"
RLPDIMRO.endEdit()
endMethod


Append from clipboard

method pushButton(var eventInfo Event)
var
  cThisScript, cFoundRow, cThisQry, vrString  String
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
cThisString = ""
RLPDSMRO.edit()
cThisScript = UIOBJ.PDSQLScript
vrString.readFromClipboard()
UIOBJ.PDSQLScript = cThisScript + vrString + "\013"+"\010" + "\013"+"\010"
RLPDIMRO.endEdit()
endMethod


Append place holder

method pushButton(var eventInfo Event)
var
  cThisScript, cFoundRow, cThisQry, vrString  String
endVar
;example usage of tcursor and scan loop

cTitle=self.labeltext
message("Processing", cTitle)
cThisString = ""
RLPDSMRO.edit()
cThisScript = UIOBJ.PDSQLScript
UIOBJ.PDSQLScript = cThisScript  + "||||" + "\013" +"\010" + "\013" +"\010"
RLPDIMRO.endEdit()
endMethod


Break rules apart into properties

method pushButton(var eventInfo Event)
var
  ar Array[] String ; Must be resizable
  s, c String
  n Number
  thisTC, parsesTC TCursor
endvar


thisTC.attach(RULES)
parsesTC.open("Parses.db")

parsesTC.edit()

scan thisTC:
RULES.resync(thisTC)

cTitle=self.labeltext
message("Processing", cTitle)

cAppID    = thisTC."Dept_Or_App_Name"
cFileName = thisTC."RuleName"

s = Rules.RuleScript
i = 1
s.breakApart(ar) ; breaks on spaces by default
;ar.view()
n = ar.size()
 
for i from i to n
  c=ar[i]
  message(c)
        parsesTC.insertAfterRecord()
        parsesTC."AppID" = cAppID
        parsesTC."FileName" = cFileName
        parsesTC."String" = c

endFor

endScan
Rules.moveToRecord(1)
Parses.moveToRecord(1)
EndMethod

Link data dictionary to properties table to list
Tables and field names by file type such as form, report, script by department, application, filename.

Concatenate strings to create odbc compliant tables in object pal or visual basic code

Concatenate strings to create strings to create default forms reports in visual basic code.





0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now