Many people have emailed to ask for the attachment since it got filtered. I am inserting it as text below my signature.
Rich Taylor | Senior Programmer/Analyst| VERTIS 250 W. Pratt Street | Baltimore, MD 21201 P 410.361.8688 | F 410.528.0319 [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. "The more they complicate the plumbing the easier it is to stop up the drain" - Montgomery Scott NCC-1701 **********************\/\/\/\/\/\//\/\/\/\/\/\/\/******************** $INCLUDE UNIVERSE.INCLUDE ODBC.H * PROGRAM = 'SOSHIP.UPDATE' ERROR.NO = 0 *----------------------------------------------------------------------- * Preparing the SQL processes *----------------------------------------------------------------------- SQL.OK = TRUE ; * error flag for sql processes SQL.STATUS = ClearDiagnostics() * setup the ODBC connection to the SOSHIP.mdb file SQL.STATUS = SQLAllocConnect(@HENV,SOSHIP.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 (SOSHIP.MDB.CONNECT) * Setup the ODBC connection to the LOCAL UV account SQL.STATUS = SQLAllocConnect(@HENV,SOSHIP.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(SOSHIP.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 (SOSHIP.UV.CONNECT) * setup customer table transfer sql statement environments SQL.STATUS = SQLAllocStmt(SOSHIP.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(SOSHIP.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 SOSHIP.MDB parts Allocations table SQL.STATUS = SQLAllocStmt(SOSHIP.MDB.CONNECT,SOSHIP.PARTS) IF SQL.STATUS <> SQL.SUCCESS THEN MESSAGE = "Could not access the SOSHIP parts allocations table in SOSHIP.mdb: " MESSAGE := "Failed to allocate a STATEMENT environment" GOSUB ERROR.PROCESS END * SQL statement objects to access SOSHIP.MDB Order Allocations table SQL.STATUS = SQLAllocStmt(SOSHIP.MDB.CONNECT,SOSHIP.ORDERS) IF SQL.STATUS <> SQL.SUCCESS THEN MESSAGE = "Could not access the SOSHIP Orders Allocations table in SOSHIP.mdb: " MESSAGE := "Failed to allocate a STATEMENT environment" GOSUB ERROR.PROCESS END IF NOT(SQL.OK) THEN CLOSE SOSHIPF 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 SOSHIP.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(SOSHIP<14>),'MR4') ON.ORDER = OCONV(SUM(SOSHIP<4>), 'MR4') BACKORDER = ON.ORDER - ALLOC NUM.ORDER = 0 NUM.CUST = 0 * select the customer data & add to soship.mdb database SQL.STATUS = SQLExecute(CUSTOMER.SEL.STMT) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL. MSG) MESSAGE = "Could not execute the SQL Selection of SOSHIP 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,SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL .MSG) MESSAGE = "Could not insert record into the SOSHIP.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 soship order allocation table (line item detail of allocations) LINE.CNT = DCOUNT(SOSHIP<1>,@VM) SO.LIST = '' FOR IDX = 1 TO LINE.CNT ALLOC.LINE = SOSHIP<1,IDX> SO.NO = SOSHIP<2,IDX>["*",1,1] SO.LINO = SOSHIP<2,IDX>["*",2,1] CUST.NO = SOSHIP<6,IDX> SCHED.DATE = OCONV(SOSHIP<3,IDX>,'D4\') SCHED.QTY = OCONV(SOSHIP<4,IDX>,'MR4') ALLOC.QTY = OCONV(SOSHIP<14,IDX>,'MR4') PRICE = OCONV(SOSHIP<5,IDX>,'MR4') LOCATE(SO.NO,SO.LIST;DUMMY) ELSE SO.LIST<-1> = SO.NO SQL.STATUS = SQLExecute(SOSHIP.ORDERS) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG ) MESSAGE = "Could not insert orders allocation data into the SOSHIP.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 soship part allocation table data NUM.ORDER = DCOUNT(SO.LIST,@AM) * load the Parts allocation table in soship.mdb SQL.STATUS = SQLExecute(SOSHIP.PARTS) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.PARTS,SQL.STATE,DB.ERROR,SQL.MSG) MESSAGE = "Could not insert parts allocation data into the SOSHIP.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 .... * Call structure for SoshipClient: soshipclient.exe <path> <port=999> * where path is the full network path to the SOSHIP.MDB file * (path is currently not used, but the position must be filled} * and 999 is the port number of the user. * reset just the SOSHIP.ORDERS statement environment SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS) NAP 500 * ====> CALL EXTERNAL CLIENT PROGRAM IF STATUS = 1 THEN SLEEP 3 END ELSE MESSAGE = "Could not start SoshipClient program on workstation" MESSAGE<2> = MB.ERROR EXIT END ; * endif status = 1 (pix.win.run call) * reopen the soship data source connection * retrieve updated soship allocations from the mdb file GOSUB PREPARE.SQL.RESULTS.COMMANDS IF SQL.OK THEN SQL.STATUS = SQLExecute(SOSHIP.ORDERS) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG ) MESSAGE = "Could not execute the SQL Selection against SOSHIP.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(SOSHIP.ORDERS) UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO LOCATE(ALLOC.LINE,SOSHIP,1;SOSHIP.IDX) THEN IF (SO.NO:"*":SO.LINO) = SOSHIP<2,SOSHIP.IDX> THEN SOSHIP<14,SOSHIP.IDX> = ICONV(ALLOC.QTY,'MR4') END ELSE END ; * endif (so.no:"*":so.lino) = soship<2,soship.idx> .... END ; * endlocate(alloc.line,.... REPEAT SOSHIP<17> = SUM(SOSHIP<4>) - SUM(SOSHIP<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(SOSHIP.PARTS,SQL.DROP) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.DROP) SQL.STATUS = SQLDisconnect(SOSHIP.UV.CONNECT) SQL.STATUS = SQLFreeConnect(SOSHIP.UV.CONNECT) SQL.STATUS = SQLDisconnect(SOSHIP.MDB.CONNECT) SQL.STATUS = SQLFreeConnect(SOSHIP.MDB.CONNECT) * STOP * * =================================================================== * <Routines>: * =================================================================== PREPARE.SQL.INSERT.COMMANDS:* Load sql statement environments appropriate * to loading SOSHIP.MDB * Prepare UV customer file selection command for retreiving customer data to * be loaded into the Customer table in SOSHIP.MDB SQL.STATUS = SQLBindParameter(CUSTOMER.SEL.STMT,1,SQL.B.BASIC,SQL.CHAR,25,0,SOSHIP.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 SOSHIP_SOSHIP_L0 WHERE @ID = ?)" SQL.STATUS = SQLPrepare(CUSTOMER.SEL.STMT,CUST.SEL.CMD) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL. MSG) MESSAGE = "Could not create the SQL select command for the SOSHIP 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 SOSHIP.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,SOSHIP.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 * SOSHIP.MDB file. Note the variable bindings. SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,4,SQL.B.BASIC,SQL.CHAR,30,0,PART.DESC) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,5,SQL.B.BASIC,SQL.REAL,12,4,INV.BAL) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,6,SQL.B.BASIC,SQL.REAL,12,4,ALLOC) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,7,SQL.B.BASIC,SQL.REAL,12,4,ON.ORDER) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,8,SQL.B.BASIC,SQL.REAL,12,4,BACKORDER) SQL.STATUS = SQLBindParameter(SOSHIP.PARTS,9,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.ORDER) SQL.STATUS = SQLBindParameter(SOSHIP.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(SOSHIP.PARTS,PART.INS.CMD) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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 * SOSHIP.MDB file. Note the variable bindings. SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,2,SQL.B.BASIC,SQL.INTEGER,6,0,ALLOC.LINE) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,3,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,4,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,5,SQL.B.BASIC,SQL.CHAR,6,0,SO.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,6,SQL.B.BASIC,SQL.SMALLINT,5,0,SO.LINO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,7,SQL.B.BASIC,SQL.CHAR,10,0,CUST.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,8,SQL.B.BASIC,SQL.DATE,10,0,SCHED.DATE) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,9,SQL.B.BASIC,SQL.REAL,10,4,SCHED.QTY) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,10,SQL.B.BASIC,SQL.REAL,10,4,ALLOC.QTY) SQL.STATUS = SQLBindParameter(SOSHIP.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(SOSHIP.ORDERS,ORDER.INS.CMD) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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 SOSHIP.MDB * reset just the SOSHIP.ORDERS statement environment SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS) * build SQL select statement to retrieve Order allocation lines to load any * allocation adjustments into the UV SOSHIP file SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO) SQL.STATUS = SQLBindParameter(SOSHIP.ORDERS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION) SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,1,SQL.B.DEFAULT, ALLOC.LINE) SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,2,SQL.B.DEFAULT, SO.NO) SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,3,SQL.B.DEFAULT, SO.LINO) SQL.STATUS = SQLBindCol(SOSHIP.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(SOSHIP.ORDERS,READ.ALLOC.CMD) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG ) MESSAGE = "Could not create the SQL select statment to retrieve soship 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(SOSHIP.PARTS,SQL.CLOSE) SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.UNBIND) SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.RESET.PARAMS) * reset the order allocation table environment SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND) SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS) RETURN * =================================================================== CLEANUP.MDB:* commands to cleanup the SOSHIP.mdb file for this users data * clean up order allcation data SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE) SQL.STATUS = SQLPrepare(SOSHIP.ORDERS,"DELETE FROM OrderAllocations NOWAIT where Port_No = ?") IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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(SOSHIP.ORDERS) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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(SOSHIP.PARTS,SQL.CLOSE) SQL.STATUS = SQLPrepare(SOSHIP.PARTS,"DELETE FROM PartsAllocation NOWAIT where Port_No = ?") IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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(SOSHIP.PARTS) IF SQL.STATUS # SQL.SUCCESS THEN SQL.ERR.STATUS = SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.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,SOSHIP.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,SOSHIP.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 SOSHIP data source SQL.STATUS = SQLConnect(SOSHIP.MDB.CONNECT,"SOSHIP",'','') 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>: T ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
