ConnectToMicrosoftAccess
From Pickwiki
Jump to navigationJump to search
To use this you will need to setup a datasource on the client under Control Panel. The name of datasource should be what you use in the SQLConnect statement for the access database.
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
*-----------------------------------------------------------------------
* Preparing the SQL processes
*-----------------------------------------------------------------------
SQL.OK = TRUE ; * error flag for sql processes
SQL.STATUS = [[ClearDiagnostics]]()
* setup the ODBC connection to the TEST.mdb file
SQL.STATUS = [[SQLAllocConnect]](@HENV,TEST.MDB.CONNECT)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not establish a connection to the Access workspace: "
MESSAGE := "Failed to allocate a CONNECTION environment"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
IF SQL.OK THEN
GOSUB MDB.CONNECT
END ; * endif sql.ok (TEST.MDB.CONNECT)
* Setup the ODBC connection to the LOCAL UV account
SQL.STATUS = [[SQLAllocConnect]](@HENV,TEST.UV.CONNECT)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not establish a connection to the Universe Database environment: "
MESSAGE := "Failed to allocate a CONNECTION environment"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
IF SQL.OK THEN
SQL.STATUS = SQLConnect(TEST.UV.CONNECT,"localuv",'','')
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not establish a connection to the Universe Database environment: "
MESSAGE := "Failed to connect to data source"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
END ; * endif sql.ok (TEST.UV.CONNECT)
* setup customer table transfer sql statement environments
SQL.STATUS = [[SQLAllocStmt]](TEST.UV.CONNECT,CUSTOMER.SEL.STMT)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not access the CUSTOMER file in Universe: "
MESSAGE := "Failed to allocate a STATEMENT environment (select)"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,CUSTOMER.INS.STMT)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not access the CUSTOMER file in Universe: "
MESSAGE := "Failed to allocate a STATEMENT environment (insert)"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
* SQL statement objects to access TEST.MDB parts Allocations table
SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,TEST.PARTS)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not access the TEST parts allocations table in TEST.mdb: "
MESSAGE := "Failed to allocate a STATEMENT environment"
GOSUB ERROR.PROCESS
END
* SQL statement objects to access TEST.MDB Order Allocations table
SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,TEST.ORDERS)
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not access the TEST Orders Allocations table in TEST.mdb: "
MESSAGE := "Failed to allocate a STATEMENT environment"
GOSUB ERROR.PROCESS
END
IF NOT(SQL.OK) THEN
CLOSE TESTF
STOP
END ; * endif not(sql.ok) ...
*-------------------------------------------------------------------
* End of SQL Preparation
*-------------------------------------------------------------------
LOOP
SQL.STATUS = [[ClearDiagnostics]]()
SQL.OK = TRUE
GOSUB RESET.SQL.STATEMENTS
GOSUB PREPARE.SQL.INSERT.COMMANDS
IF NOT(SQL.OK) THEN EXIT
SQL.STATUS = [[ClearDiagnostics]]()
' code removed
UNTIL RET.VALUE<1> = "CANCEL" DO
TEST.ID = PART.NO:"*":LOCATION
* get the inventory balance and part description
INV = RAISE(TRANS('INV',PART.NO,-1,'X'))
LOCATE(LOCATION,INV,2;LOC.VMC) THEN
INV.BAL = OCONV(INV<3,LOC.VMC>,'MR4')
END ELSE
INV.BAL = 0
END ; * endlocat(location,inv ....
PART.DESC = TRANS('PARTS',PART.NO,1,'X')
* calculate header totals
ALLOC = OCONV(SUM(TEST<14>),'MR4')
ON.ORDER = OCONV(SUM(TEST<4>), 'MR4')
BACKORDER = ON.ORDER - ALLOC
NUM.ORDER = 0
NUM.CUST = 0
* select the customer data & add to TEST.mdb database
SQL.STATUS = SQLExecute(CUSTOMER.SEL.STMT)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not execute the SQL Selection of TEST Customer data! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
SQL.OK = FALSE
GOSUB ERROR.PROCESS
EXIT
END ; * endif sql.status # sql.success ....
LOOP
SQL.STATUS = SQLFetch(CUSTOMER.SEL.STMT)
UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not insert record into the TEST.mdb DATABASE, Customer table! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
SQL.OK = FALSE
GOSUB ERROR.PROCESS
EXIT
END ; * endif sql.status # sql.success ....
NUM.CUST += 1
REPEAT
IF NOT(SQL.OK) THEN EXIT
* update the TEST order allocation table (line item detail of allocations)
LINE.CNT = DCOUNT(TEST<1>,@VM)
SO.LIST = ''
FOR IDX = 1 TO LINE.CNT
ALLOC.LINE = TEST<1,IDX>
SO.NO = TEST<2,IDX>["*",1,1]
SO.LINO = TEST<2,IDX>["*",2,1]
CUST.NO = TEST<6,IDX>
SCHED.DATE = OCONV(TEST<3,IDX>,'D4\')
SCHED.QTY = OCONV(TEST<4,IDX>,'MR4')
ALLOC.QTY = OCONV(TEST<14,IDX>,'MR4')
PRICE = OCONV(TEST<5,IDX>,'MR4')
LOCATE(SO.NO,SO.LIST;DUMMY) ELSE SO.LIST<-1> = SO.NO
SQL.STATUS = SQLExecute(TEST.ORDERS)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not insert orders allocation data into the TEST.MDB file! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
EXIT
END ; * endif sql.status # sql.success ....
NEXT IDX
IF NOT(SQL.OK) THEN EXIT
* update TEST part allocation table data
NUM.ORDER = DCOUNT(SO.LIST,@AM)
* load the Parts allocation table in TEST.mdb
SQL.STATUS = SQLExecute(TEST.PARTS)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not insert parts allocation data into the TEST.MDB file! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
SQL.OK = FALSE
GOSUB ERROR.PROCESS
EXIT
END ; * endif sql.status # sql.success ....
* reset just the TEST.ORDERS statement environment
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)
NAP 500
* ====> CALL EXTERNAL CLIENT PROGRAM
IF STATUS = 1 THEN
SLEEP 3
END ELSE
MESSAGE = "Could not start [[TestClient]] program on workstation"
MESSAGE<2> = MB.ERROR
EXIT
END ; * endif status = 1 (pix.win.run call)
* reopen the test data source connection
* retrieve updated test allocations from the mdb file
GOSUB PREPARE.SQL.RESULTS.COMMANDS
IF SQL.OK THEN
SQL.STATUS = SQLExecute(TEST.ORDERS)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not execute the SQL Selection against TEST.mdb "
MESSAGE := "[[OrderAllocations]] table data! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
SQL.OK = FALSE
GOSUB ERROR.PROCESS
END ; * endif sql.status # sql.success ....
IF SQL.OK THEN
LOOP
SQL.STATUS = SQLFetch(TEST.ORDERS)
UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
LOCATE(ALLOC.LINE,TEST,1;TEST.IDX) THEN
IF (SO.NO:"*":SO.LINO) = TEST<2,TEST.IDX> THEN
TEST<14,TEST.IDX> = ICONV(ALLOC.QTY,'MR4')
END ELSE
END ; * endif (so.no:"*":so.lino) = test<2,test.idx> ....
END ; * endlocate(alloc.line,....
REPEAT
TEST<17> = SUM(TEST<4>) - SUM(TEST<14>)
END ; * endif sql.ok (select of [[OrderAllocations]])
END ; * endif sql.ok (prepare sql results)
GOSUB CLEANUP.MDB
IF SINGLE.UPDATE THEN EXIT ; * break out of loop if single update only
REPEAT
GOSUB CLEANUP.MDB ; * insure that we have cleaned up our workspace
*
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.DROP)
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.DROP)
SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.DROP)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.DROP)
SQL.STATUS = SQLDisconnect(TEST.UV.CONNECT)
SQL.STATUS = [[SQLFreeConnect]](TEST.UV.CONNECT)
SQL.STATUS = SQLDisconnect(TEST.MDB.CONNECT)
SQL.STATUS = [[SQLFreeConnect]](TEST.MDB.CONNECT)
*
STOP
*
* ===================================================================
* <Routines>:
* ===================================================================
PREPARE.SQL.INSERT.COMMANDS:* Load sql statement environments appropriate
* to loading TEST.MDB
* Prepare UV customer file selection command for retreiving customer data to
* be loaded into the Customer table in TEST.MDB
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.SEL.STMT,1,SQL.B.BASIC,SQL.CHAR,25,0,TEST.ID)
SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,1,SQL.B.DEFAULT, CUSTNO)
SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,2,SQL.B.DEFAULT, NAME)
SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,3,SQL.B.DEFAULT, CITY)
SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,4,SQL.B.DEFAULT, STATE)
SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,5,SQL.B.DEFAULT, ZIP)
CUST.SEL.CMD = "SELECT DISTINCT [[CUST_NO]],NAME,CITY,STATE,ZIP FROM CUSTOMER "
CUST.SEL.CMD := "WHERE [[CUST_NO]] IN (SELECT [[CUST_NO]] FROM [[TEST_TEST_L0]] WHERE @ID = ?)"
SQL.STATUS = SQLPrepare(CUSTOMER.SEL.STMT,CUST.SEL.CMD)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL select command for the TEST customer data! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
* Prepare the SQL insert command to load the customer data into the Customer table
* of the TEST.MDB file. Note the binding of parameters to variable names.
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,2,SQL.B.BASIC,SQL.CHAR,10,0,CUSTNO)
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,3,SQL.B.BASIC,SQL.CHAR,30,0,NAME)
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,4,SQL.B.BASIC,SQL.CHAR,30,0,CITY)
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,5,SQL.B.BASIC,SQL.CHAR,2,0,STATE)
SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,6,SQL.B.BASIC,SQL.CHAR,10,0,ZIP)
CUST.INS.CMD = "INSERT INTO Customer ([[Port_No]],[[Customer_No]],Name,City,State,[[ZipCode]]) "
CUST.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?)"
SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,CUST.INS.CMD)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Insert command for the customer table! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
* Prepare the SQL insert command to load the [[PartsAllocation]] Table of the
* TEST.MDB file. Note the variable bindings.
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,4,SQL.B.BASIC,SQL.CHAR,30,0,PART.DESC)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,5,SQL.B.BASIC,SQL.REAL,12,4,INV.BAL)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,6,SQL.B.BASIC,SQL.REAL,12,4,ALLOC)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,7,SQL.B.BASIC,SQL.REAL,12,4,ON.ORDER)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,8,SQL.B.BASIC,SQL.REAL,12,4,BACKORDER)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,9,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.ORDER)
SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,10,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.CUST)
PART.INS.CMD = "INSERT INTO [[PartsAllocation]] ([[Port_No]],[[Part_No]],[[Inventory_Location]],"
PART.INS.CMD := "[[Part_Description]],[[Inventory_Balance]], Allocated, [[On_Order]], "
PART.INS.CMD := "Backorder,[[Order_Count]],[[Customer_Count]]) "
PART.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
SQL.STATUS = SQLPrepare(TEST.PARTS,PART.INS.CMD)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Insert command for the [[PartsAllocations]] table! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
* Prepare the SQL insert command to load the [[OrderAllocationS]] Table of the
* TEST.MDB file. Note the variable bindings.
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,2,SQL.B.BASIC,SQL.INTEGER,6,0,ALLOC.LINE)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,3,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,4,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,5,SQL.B.BASIC,SQL.CHAR,6,0,SO.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,6,SQL.B.BASIC,SQL.SMALLINT,5,0,SO.LINO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,7,SQL.B.BASIC,SQL.CHAR,10,0,CUST.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,8,SQL.B.BASIC,SQL.DATE,10,0,SCHED.DATE)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,9,SQL.B.BASIC,SQL.REAL,10,4,SCHED.QTY)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,10,SQL.B.BASIC,SQL.REAL,10,4,ALLOC.QTY)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,11,SQL.B.BASIC,SQL.REAL,10,4,PRICE)
ORDER.INS.CMD = "INSERT INTO [[OrderAllocations]] ([[Port_No]],[[Alloc_Line]],[[Part_No]],"
ORDER.INS.CMD := "[[Inventory_Location]],[[Sales_Order]], [[Sales_Order_Line]], [[Customer_No]],"
ORDER.INS.CMD := "[[Schedule_Date]],[[Schedule_Quantity]],[[Allocated_Quantity]],[[Unit_Price]]) "
ORDER.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)"
SQL.STATUS = SQLPrepare(TEST.ORDERS,ORDER.INS.CMD)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Insert command for the [[OrdersAllocations]] table! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
RETURN
* ===================================================================
PREPARE.SQL.RESULTS.COMMANDS:* Load sql statement envrionments appropriate
* to retrieving the altered data from TEST.MDB
* reset just the TEST.ORDERS statement environment
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)
* build SQL select statement to retrieve Order allocation lines to load any
* allocation adjustments into the UV TEST file
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,1,SQL.B.DEFAULT, ALLOC.LINE)
SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,2,SQL.B.DEFAULT, SO.NO)
SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,3,SQL.B.DEFAULT, SO.LINO)
SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,4,SQL.B.DEFAULT, ALLOC.QTY)
READ.ALLOC.CMD = "SELECT [[Alloc_Line]],[[Sales_Order]],[[Sales_Order_Line]],[[Allocated_Quantity]] FROM [[OrderAllocations]] "
READ.ALLOC.CMD := "WHERE [[Port_No]] = ? and [[Part_No]] = ? and [[Inventory_Location]] = ?"
SQL.STATUS = SQLPrepare(TEST.ORDERS,READ.ALLOC.CMD)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL select statment to retrieve test order allocations! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
RETURN
* ===================================================================
RESET.SQL.STATEMENTS:* reset and clear the statement environments
* reset the customer selection statement environment
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.RESET.PARAMS)
* reset the customer table insert statement environment
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.RESET.PARAMS)
* reset the parts allocation table environment
SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.RESET.PARAMS)
* reset the order allocation table environment
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)
RETURN
* ===================================================================
CLEANUP.MDB:* commands to cleanup the TEST.mdb file for this users data
* clean up order allcation data
SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
SQL.STATUS = SQLPrepare(TEST.ORDERS,"DELETE FROM [[OrderAllocations]] NOWAIT where [[Port_No]] = ?")
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Delete command for the [[OrderAllocations]] table, table not cleared! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ELSE
SQL.STATUS = SQLExecute(TEST.ORDERS)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not clear the [[OrderAllocations]] table for the current user! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
END ; * endif sql.status # sql.success ....
* clean up parts allcation data
SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.CLOSE)
SQL.STATUS = SQLPrepare(TEST.PARTS,"DELETE FROM [[PartsAllocation]] NOWAIT where [[Port_No]] = ?")
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Delete command for the [[PartsAllocation]] table, table not cleared! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ELSE
SQL.STATUS = SQLExecute(TEST.PARTS)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not clear the [[PartsAllocation]] table for the current user! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END ; * endif sql.status # sql.success ....
END ; * endif sql.status # sql.success ....
* clean up customer data
SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.CLOSE)
SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,"DELETE FROM Customer NOWAIT where [[Port_No]] = ?")
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not create the SQL Delete command for the customer table, table not cleared! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
END ELSE
SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
IF SQL.STATUS # SQL.SUCCESS THEN
SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
MESSAGE = "Could not clear the customer table for the current user! "
MESSAGE := "SQL.STATE=":SQL.STATE:": DB ERROR=":DB.ERROR:": MESSAGE=":SQL.MSG
GOSUB ERROR.PROCESS
END ; * endif sql.status # sql.success ....
END ; * endif sql.status # sql.success ....
RETURN
* ===================================================================
MDB.CONNECT:* routine to open a connection to the TEST data source
SQL.STATUS = SQLConnect(TEST.MDB.CONNECT,"TEST",'','')
IF SQL.STATUS <> SQL.SUCCESS THEN
MESSAGE = "Could not (re)establish a connection to the Access workspace: "
MESSAGE := "Failed to connect to data source"
GOSUB ERROR.PROCESS
SQL.OK = FALSE
END
RETURN
* ===================================================================
ERROR.PROCESS:* error reporting loop
RETURN
*
* ===================================================================
*
*
* <End>:
This was originally posted on u2-users, and the author provided a cleaned-up version for the Wiki.
http://www.mail-archive.com/u2-users%40listserver.u2ug.org/msg04593.html
