>I think you need to put some of the code out. Have you considered the coverage profiler to look for the delay? If it is in SQL Server then there is almost certainly an index or other classical explanation. If it is in VFP, you can almost certainly track it using the profiler.
I am not sure what you mean by the profiler or how to use it.
>One other suggestion re performance: SQL Server2000 and later compiles and caches parameterized queries, so if you have stereotyped updates you can use parameters to make it much quicker, also SQL-injection-proof (hackers can't "inject" SQL commands into formfields or whatever and have them concatenated into SQL where they cause unexpected results). Use a VFP parameterized remote view as a good template for creating a parameterized SQL command for SQL server.
I do not use parameterized queries as far as I know.
This is only my part of the code for the dll. More code does the vfp tables PcInsertStr and pcCheckStr are passed in from the other part of the dll. They contain the origial line of data set into the dll and the Check flag, so the program knows which table or tables need to be worked with. This dll is called either from a dump program, that dumps all the data from the application into the vfp and sql tables, or a single call that happens as the user is changing the data in the main application.
FUNCTION sqltablecheck()
PARAMETER pcInsertStr, pcCheckStr
LOCAL lnReturn, lnChgFlag, lcrecstring, laHeadKey(1)
lnReturn = 0
DO CASE
CASE SUBSTR(pcInsertStr, 11, 1) = 'A'
lnChgFlag = 1
CASE SUBSTR(pcInsertStr, 11, 1) = 'C'
lnChgFlag = 2
CASE SUBSTR(pcInsertStr, 11, 1) = 'D'
lnChgFlag = 3
CASE SUBSTR(pcInsertStr, 11, 1) = 'P'
lnChgFlag = 4
OTHERWISE
lnChgFlag = 0
ENDCASE
lcrecstring = SUBSTR(pcInsertStr, 100)
DO CASE
CASE pcCheckStr = " 1"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "Student"
laHeadKey(3) = "student_id_"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 10)
laHeadKey(5) = 3
CASE pcCheckStr = " 2"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "stutran"
laHeadKey(3) = "stutran_id_"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 10)
laHeadKey(5) = 36
CASE pcCheckStr = " 3"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "stops"
laHeadKey(3) = "stops_id___"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 11)
laHeadKey(5) = 1
CASE pcCheckStr = " 4"
DIMEN laHeadKey(4)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "runs"
laHeadKey(3) = "runs_id____"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 11)
CASE pcCheckStr = " 5"
DIMEN laHeadKey(3,6)
laHeadKey(1,1) = pcCheckStr
laHeadKey(1,2) = "stptran"
laHeadKey(1,3) = "fstop_id_"
laHeadKey(1,4) = SUBSTR(pcInsertStr, 15, 14)
laHeadKey(1,5) = 1
laHeadKey(1,6) = 3
laHeadKey(2,1) = "runtran"
laHeadKey(2,2) = "fstop_id_"
laHeadKey(2,3) = SUBSTR(pcInsertStr, 15, 14)
laHeadKey(2,4) = "Build blank string"
laHeadKey(2,5) = "runs_id____"
laHeadKey(2,6) = " "
laHeadKey(3,1) = "tripid"
laHeadKey(3,2) = "fstop_id_"
laHeadKey(3,3) = SUBSTR(pcInsertStr, 15, 14)
laHeadKey(3,4) = 5
laHeadKey(3,5) = "trip_id__"
laHeadKey(3,6) = "0" + laHeadKey(2,3)
CASE pcCheckStr = " 6"
DIMEN laHeadKey(2,6), laHdKey(4)
laHeadKey(1,1) = pcCheckStr
laHeadKey(1,2) = "runtran"
laHeadKey(1,3) = "stpruncombo"
laHdKey(1) = SUBSTR(pcInsertStr, 15, 11)
laHdKey(2) = SUBSTR(pcInsertStr, 26, 14)
laHdKey(3) = SUBSTR(pcInsertStr, 40, 5)
laHdKey(4) = laHdKey(2) + laHdKey(1) + PADR(ALLTRIM(laHdKey(3)), 5)
laHeadKey(1,4) = laHdKey(4)
laHeadKey(1,5) = 0
laHeadKey(1,6) = 3
laHeadKey(2,1) = "runtran"
laHeadKey(2,2) = "fstop_id_"
laHeadKey(2,3) = laHdKey(2)
laHeadKey(2,4) = 1
laHeadKey(2,5) = "runs_id____"
laHeadKey(2,6) = " "
CASE pcCheckStr = " 7"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "routes"
laHeadKey(3) = "routes_id__"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 10)
laHeadKey(5) = 1
CASE pcCheckStr = " 8"
DIMEN laHeadKey(5), laHdKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "cmndsts"
laHeadKey(3) = "studesckeys"
laHdKey(1) = SUBSTR(pcInsertStr, 15, 10)
laHdKey(2) = SUBSTR(pcInsertStr, 25, 32)
laHdKey(3) = SUBSTR(pcInsertStr, 58, 32)
laHdKey(4) = laHdKey(1) + laHdKey(2) + laHdKey(3)
laHdKey(5) = laHdKey(1) + laHdKey(2)
laHeadKey(4) = laHdKey(4)
laHeadKey(5) = 1
IF INLIST(lnChgFlag, 1, 2)
lcrecstring = ["] + laHdKey(4) + ["] + [,] + lcrecstring
ENDIF
CASE pcCheckStr = " 9"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "schools"
laHeadKey(3) = "school_code"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 8)
laHeadKey(5) = 1
CASE pcCheckStr = " 10"
DIMEN laHeadKey(2,6), laHdKey(5)
laHdKey(1) = SUBSTR(pcInsertStr, 15, 8)
laHdKey(2) = SUBSTR(pcInsertStr, 23, 4)
laHdKey(3) = SUBSTR(pcInsertStr, 27, 4)
laHdKey(4) = SUBSTR(pcInsertStr, 31, 16)
laHeadKey(1,1) = pcCheckStr
laHeadKey(1,2) = "schtran"
laHeadKey(1,3) = "school_code"
laHeadKey(1,4) = ["] + laHdKey(1) + ["]
laHeadKey(1,5) = "grade__"
laHeadKey(1,6) = ["] + laHdKey(2) + ["]
laHeadKey(2,2) = "programs__"
laHeadKey(2,3) = ["] + laHdKey(3) + ["]
laHeadKey(2,4) = "eff_freq_"
laHeadKey(2,5) = ["] + laHdKey(4) + ["]
CASE pcCheckStr = " 27"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "request"
laHeadKey(3) = "request_id_"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 10)
laHeadKey(5) = 1
CASE pcCheckStr = " 29"
DIMEN laHeadKey(7)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "clustran"
laHeadKey(3) = "school_code"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 8)
laHeadKey(5) = "select_time"
laHeadKey(6) = SUBSTR(pcInsertStr, 24, 8)
laHeadKey(7) = 1
CASE pcCheckStr = "177"
DIMEN laHeadKey(5)
laHeadKey(1) = 1
laHeadKey(2) = "schdsts"
laHeadKey(3) = "schoolskeys"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 17)
laHeadKey(5) = 1
CASE pcCheckStr = "179"
DIMEN laHeadKey(9), laHdKey(7)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "Dirdesc"
IF lnChgFlag = 3
laHeadKey(3) = "rcoverpos"
ELSE
laHeadKey(3) = "runid, cover"
ENDIF
laHdKey(1) = SUBSTR(pcInsertStr, 15, 30)
laHdKey(2) = SUBSTR(pcInsertStr, 47, 5)
laHdKey(3) = SUBSTR(pcInsertStr, 52, 5)
laHdKey(4) = SUBSTR(pcInsertStr, 15, 11)
laHdKey(5) = laHdKey(1) + PADR(ALLTRIM(laHdKey(2)), 5)
laHdKey(6) = laHdKey(5) + PADR(ALLTRIM(laHdKey(3)), 5)
laHdKey(7) = ["] + laHdKey(4) + [",] + laHdKey(2)
IF lnChgFlag = 3
laHeadKey(4) = ["] + laHdKey(6) + ["]
ELSE
laHeadKey(4) = laHdKey(7)
ENDIF
laHeadKey(5) = "runs_id"
laHeadKey(6) = laHdKey(4)
laHeadKey(7) = "cover"
laHeadKey(8) = laHdKey(2)
laHeadKey(9) = 17
IF INLIST(lnChgFlag, 1, 2)
lcrecstring = lcrecstring + [,"] + laHdKey(6) + ["," "]
ENDIF
CASE pcCheckStr = "185"
dimen laHeadKey(2,6)
laHeadKey(1,1) = pcCheckStr
laHeadKey(1,2) = "tripid"
laHeadKey(1,3) = "trips_id_"
laHeadKey(1,4) = SUBSTR(pcInsertStr, 15, 15)
laHeadKey(1,5) = 1
laHeadKey(1,6) = 3
laHeadKey(2,1) = "tripid"
laHeadKey(2,2) = "fstop_id_"
laHeadKey(2,3) = ""
laHeadKey(2,4) = 1
laHeadKey(2,5) = "trips_id_"
laHeadKey(2,6) = "0"
CASE pcCheckStr = "187"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "bound"
laHeadKey(3) = "boundnum"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 5)
laHeadKey(5) = 1
CASE pcCheckStr = "188"
DIMEN laHeadKey(10), laHdKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "schbnd"
laHeadKey(3) = "school_code"
laHdKey(1) = SUBSTR(pcInsertStr, 15, 8)
laHdKey(2) = SUBSTR(pcInsertStr, 23, 2)
laHdKey(3) = SUBSTR(pcInsertStr, 25, 5)
laHdKey(4) = SUBSTR(pcInsertStr, 30, 5)
laHdKey(5) = laHdKey(1) + laHdKey(2) + laHdKey(3) + laHdKey(4)
laHeadKey(4) = laHdKey(1)
laHeadKey(5) = "school_elg"
laHeadKey(6) = laHdKey(2)
laHeadKey(7) = "school_seq"
laHeadKey(8) = laHdKey(3)
laHeadKey(9) = "bound_type"
laHeadKey(10) = laHdKey(4)
CASE pcCheckStr = "189"
DIMEN laHeadKey(2,6), laHdKey(6)
laHeadKey(1,1) = pcCheckStr
laHeadKey(1,2) = "rundir"
IF lnChgFlag = 3
laHeadKey(1,3) = "runid_cover"
ELSE
laHeadKey(1,3) = "covercombo"
ENDIF
laHdKey(1) = SUBSTR(pcInsertStr, 15, 11)
laHdKey(2) = SUBSTR(pcInsertStr, 26, 14)
laHdKey(3) = SUBSTR(pcInsertStr, 40, 5)
laHdKey(4) = SUBSTR(pcInsertStr, 45, 5)
laHdKey(5) = laHdKey(2) + laHdKey(1) + laHdKey(3) + laHdKey(4)
laHdKey(6) = laHdKey(1) + laHdKey(4)
IF lnChgFlag = 3
laHeadKey(1,4) = laHdKey(1)
ELSE
laHeadKey(1,4) = laHdKey(5)
ENDIF
laHeadKey(1,5) = "run_cover"
laHeadKey(1,6) = "stop_seq"
laHeadKey(2,1) = "runtran"
laHeadKey(2,2) = "stpruncombo"
laHeadKey(2,3) = laHdKey(4)
laHeadKey(2,4) = 9
laHeadKey(2,5) = "runs_id__"
laHeadKey(2,6) = "fstop_id"
CASE pcCheckStr = "199"
DIMEN laHeadKey(5)
laHeadKey(1) = pcCheckStr
laHeadKey(2) = "rundirroute"
laHeadKey(3) = "runroute"
laHeadKey(4) = SUBSTR(pcInsertStr, 15, 16)
laHeadKey(5) = 1
OTHERWISE
lnReturn = -1
ENDCASE
IF lnReturn >= 0
lnReturn = sqlupdate(lcrecstring, lnChgFlag, @laHeadKey)
endif
RETURN lnReturn
ENDFUNC
FUNCTION sqlupdate()
PARAMETERS pcInsertStr, pnChgFlg, paKeys
EXTERNAL ARRAY paKeys
LOCAL lnReturn, lcSqlStr, lnRecCnt, lcFstopid, lcDeleteStr, lcInsertStr, lcWhereStr
LOCAL lcStpRunComb
lnRecCnt = 0
lnReturn = 0
lcSqlStr = ""
lcSelectStr = ""
lcDeleteStr = ""
lcWhereStr = ""
lcStpRunComb = ""
IF INLIST(paKeys(1), " 1", " 2", " 3", " 4", " 5", " 6", " 7",;
" 8", " 9", " 27", "185", "187", "199")
IF VARTYPE(paKeys(4)) = "C"
lcWhereStr = " where " + paKeys(3) + ' = "' + paKeys(4) + '"'
ELSE
lcWhereStr = " where " + + paKeys(3) + ' = ' + ALLTRIM(STR(paKeys(4)))
ENDIF
ELSE
DO CASE
CASE paKeys(1) = " 29"
lcWhereStr = " where " + paKeys(3) + ' = "' + paKeys(4) + '"'
lcWhereStr = lcWhereStr + " and " + paKeys(5) + ' = "' + ;
paKeys(6) + '"'
CASE paKeys(1) = " 10"
lcWhereStr = " where " + paKeys(1,3) + " = " + paKeys(1,4) ;
+ " AND " + paKeys(1,5) + " = " + paKeys(1,6) + " AND " + ;
paKeys(2,2) + " = " + paKeys(2,3) + " AND " + ;
paKeys(2,4) + " = " + paKeys(1,4)
CASE paKeys(1) = "179"
IF INLIST(pnChgFlg, 1, 2)
lcWhereStr = " where " + paKeys(5) + [ = "] + paKeys(6) ;
+ [" AND ] + paKeys(7) + [ = ] + paKeys(8)
ELSE
lcWhereStr = " where " + paKeys(3) + " = " + paKeys(4)
ENDIF
CASE paKeys(1) = "188"
lcWhereStr = " where " + paKeys(3) + ' = "' + paKeys(4) ;
+ '" AND ' + paKeys(5) + " = " + paKeys(6) + " AND " + ;
paKeys(7) + " = " + paKeys(8) + " AND " + ;
paKeys(9) + ' = "' + paKeys(10) + '"'
CASE paKeys(1) = "189"
IF INLIST(pnChgFlg, 1, 2)
lcWhereStr = " where " + paKeys(1,3) + ' = "' + paKeys(1,4) + '"'
ELSE
lcWhereStr = " where " + paKeys(2,5) + [ = "] + paKeys(1,4);
+ [" AND ] + paKeys(1,5) + [ = ] + paKeys(2,3)
ENDIF
ENDCASE
ENDIF
DO CASE
CASE!INLIST(paKeys(1), '189', '179')
lcSelectStr = "Select " + paKeys(3) + " from " + paKeys(2);
+ lcWhereStr
CASE paKeys(1) = '189'
lcSelectStr = "Select " + paKeys(1,5) + ", " + paKeys(1,6) ;
+ ", " + paKeys(2,5) ;
+ ", " + paKeys(2,2) + " from " + paKeys(2) + lcWhereStr
CASE paKeys(1) = '179'
lcSelectStr = "Select " + paKeys(5) + ", " + paKeys(7) ;
+ " from " + paKeys(2) + lcWhereStr
OTHERWISE
lcRecString = "Error in Select string to SQL Server"
= nttofoxerror(lcRecString)
ENDCASE
lcDeleteStr = "delete from " + paKeys(2) + lcWhereStr
lnReturn = sqlptexecc(lcSelectStr, "cTmpCursor")
pcInsertStr = STRTRAN(pcInsertStr, "'", "-")
pcInsertStr = STRTRAN(pcInsertStr, '""', '"')
TEXT TO lcSqlStr TEXTMERGE NOSHOW
Insert into <<paKeys(2)>> values ( <<pcInsertStr>>)
ENDTEXT
IF USED('cTmpCursor')
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnRecCnt = _TALLY
ENDIF
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
IF lnRecCnt > 0
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
lnReturn = sqlptexecc(lcSqlStr)
CASE pnChgFlg = 3
lnReturn = sqlptexecc(lcDeleteStr)
CASE pnChgFlg = 4
ENDCASE
IF paKeys(1) = '189'
SELECT stpruncomb FROM cTmpCursor ;
WHERE run_id____ = paKeys[1,4] AND run_cover_ = VAL(paKeys[2,3]) ;
AND stp_seq___ = 0 ;
INTO ARRAY laTemp
IF _TALLY > 0
lcStpRunComb = laTemp[1]
ENDIF
ENDIF
USE IN 'cTmpCursor'
IF ALEN(paKeys,1) > 5 OR ALEN(paKeys,2) > 0
DO CASE
CASE paKeys(1) = " 5"
FOR lnloop = 2 TO 3
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
lcSqlStr = "Select * from " + paKeys(lnloop,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(lnloop,2) + " = '" +;
paKeys(lnloop,3) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn = 0
IF lnloop = 2
lcInsertStr = buildblankstr("cTmpCursor", paKeys(lnloop,3);
+ " ", 1, paKeys(lnloop, 3), 5)
ELSE
lcInsertStr = buildblankstr("cTmpCursor", paKeys(lnloop,6);
, 1, paKeys(lnloop,3), 3)
ENDIF
lcInsertStr = " Insert into " + paKeys(lnloop,1) + ;
" values (" + lcInsertStr + ")"
lnReturn = sqlptexecc(lcInsertStr)
ENDIF
CASE pnChgFlg = 3
lcSqlStr = "Select * from " + paKeys(lnloop,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(lnloop,2) + " = '" + ;
paKeys(lnloop,3) + "'"
lcSqlStr = lcSqlStr + " and " + paKeys(lnloop,5) + " = '" ;
+ paKeys(lnloop,6) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn > 0
lcDeleteStr = "delete from " + paKeys(lnloop,1) + " where "
lcDeleteStr = lcDeleteStr + paKeys(lnloop,5) + " = '" ;
+ paKeys(lnloop,6) + "'"
lcDeleteStr = lcDeleteStr + " and " + paKeys(lnloop,2) ;
+ " = '" + paKeys(lnloop,3) + "'"
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
OTHERWISE
ENDCASE
ENDFOR
CASE paKeys(1) = " 6"
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,2) + " = '" + paKeys(2,3) + "'"
lcSqlStr = lcSqlStr + " and " + paKeys(2,5) + " = '" +;
paKeys(2,6) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn > 0
lcDeleteStr = "delete from " + paKeys(2,1) + " where "
lcDeleteStr = lcDeleteStr + paKeys(2,5) + " = '" ;
+ paKeys(2,6) + "'"
lcDeleteStr = lcDeleteStr + " and " + paKeys(2,2) + ;
" = '" + paKeys(2,3) + "'"
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
CASE pnChgFlg = 3
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,5) + " = '" + paKeys(2,6) + "'"
lcSqlStr = lcSqlStr + " and " + paKeys(2,2) + " = '" + ;
paKeys(2,3) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn = 0
lcInsertStr = buildblankstr("cTmpCursor", paKeys(2,3) ;
+ " ", 1, paKeys(2,3), 5)
lcInsertStr = STRTRAN(lcInsertStr, '"', "'")
lcInsertStr = " Insert into " + paKeys(2,1) + " values (" ;
+ lcInsertStr + ")"
lnReturn = sqlptexecc(lcInsertStr)
ENDIF
OTHERWISE
ENDCASE
CASE paKeys(1) = " 23"
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,2) + " = '" + paKeys(2,3) + "'"
lcSqlStr = lcSqlStr + " and " + paKeys(2,5) + " = '" + ;
paKeys(2,6) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn > 0
lcDeleteStr = "delete from " + paKeys(2,1) + " where "
lcDeleteStr = lcDeleteStr + paKeys(2,5) + " = '" + ;
paKeys(2,6) + "'"
lcDeleteStr = lcDeleteStr + " and " + paKeys(2,2) + " = '" ;
+ paKeys(2,3) + "'"
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
CASE pnChgFlg = 3
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,5) + " = '" + paKeys(2,6) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn = 0
lcInsertStr = buildblankstr("cTmpCursor", paKeys(2,6), 3,;
"", 0)
lcInsertStr = STRTRAN(lcInsertStr, '"', "'")
lcInsertStr = " Insert into " + paKeys(2,1) + " values (" ;
+ lcInsertStr + ")"
lnReturn = sqlptexecc(lcInsertStr)
ENDIF
OTHERWISE
ENDCASE
CASE paKeys(1) = "179"
IF pnChgFlg = 3
lcDeleteStr = "delete from " + paKeys(2) + " where "
lcDeleteStr = lcDeleteStr + paKeys(5) + " = '" + paKeys(6) + "'"
lcDeleteStr = lcDeleteStr + " and " + paKeys(7) + " = '" ;
+ paKeys(8) + "'"
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
CASE paKeys(1) = "185"
lcSqlStr = "Select * from " + paKeys(1,2) + " Where "
lcSqlStr = lcSqlStr + paKeys(1,3) + " = '" + paKeys(1,4) + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT 'cTmpCursor'
lcFstopid = cTmpCursor.fstop_id__
USE IN 'cTmpCursor'
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,5) + " = '" + paKeys(2,6) +;
lcFstopid + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn > 0
lcDeleteStr = "delete from " + paKeys(2,1) + " where "
lcDeleteStr = lcDeleteStr + paKeys(2,5) + " = '" +;
paKeys(2,6) + lcFstopid + "'"
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
CASE pnChgFlg = 3
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,2) + " = '" + lcFstopid + "'"
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn = 0
lcInsertStr = buildblankstr("cTmpCursor", "0" + lcFstopid,;
1, lcFstopid, 3)
lcInsertStr = STRTRAN(lcInsertStr, '"', "'")
lcInsertStr = " Insert into " + paKeys(2,1) + " values (" ;
+ lcInsertStr + ")"
lnReturn = sqlptexecc(lcInsertStr)
ENDIF
OTHERWISE
ENDCASE
CASE paKeys(1) = "189"
IF INLIST(pnChgFlg, 1, 2)
lcSqlStr = "Select * from " + paKeys(1,2) + " Where "
lcSqlStr = lcSqlStr + paKeys(1,3) + " = '" + paKeys(1,4) + "'"
ELSE
lcSqlStr = "Select * from " + paKeys(1,2) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,5) + " = '" + paKeys(1,4) + "' AND "
lcSqlStr = lcSqlStr + paKeys(1,5) + " = '" + paKeys(2,3) + "'"
ENDIF
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
IF lnReturn >= 0
SELECT 'cTmpCursor'
lcFstopid = cTmpCursor.fstop_id__
lcRunid = cTmpCursor.run_id____
lcStpSeq = cTmpCursor.stp_seq___
lcStpRun = cTmpCursor.stpruncomb
USE IN 'cTmpCursor'
DO CASE
CASE INLIST(pnChgFlg, 1, 2)
IF lcStpSeq = 0
lcSqlStr = "Select * from " + paKeys(2,1) + " Where "
lcSqlStr = lcSqlStr + paKeys(2,2) + [ = '] + lcStpRun + [']
lcSqlStr = STRTRAN(lcSqlStr, '""', '"')
lnReturn = sqlptexecc(lcSqlStr, "cTmpcursor")
SELECT * FROM cTmpCursor INTO ARRAY laTemp
lnReturn = _TALLY
IF lnReturn = 0
lcInsertStr = buildblankstr("cTmpCursor", lcRunid, 4,;
lcFstopid, 5)
lcDeleteStr = SUBSTR(lcInsertStr, AT[",", lcInsertStr] + 1)
lcDeleteStr = SUBSTR(lcDeleteStr, AT[",", lcDeleteStr] + 1)
lcInsertStr = '"' + lcStpRun + '",1, ' + lcDeleteStr
lcInsertStr = STRTRAN(lcInsertStr, '"', "'")
lcInsertStr = " Insert into " + paKeys(2,1) + " values (" ;
+ lcInsertStr + ")"
lnReturn = sqlptexecc(lcInsertStr)
ENDIF
ENDIF
CASE pnChgFlg = 3
IF !EMPTY(lcStpRunComb)
lcDeleteStr = "delete from " + paKeys(2,1) + " where "
lcDeleteStr = lcDeleteStr + paKeys(2,2) + " = " + ;
lcStpRunComb
lnReturn = sqlptexecc(lcDeleteStr)
ENDIF
OTHERWISE
ENDCASE
ELSE
= nttofoxerror(lcSqlStr)
ENDIF
ENDCASE
ENDIF
RETURN lnReturn
ENDFUNC
FUNCTION sqlPTexecc
PARAMETERS pcPassStr, pcCursorName
LOCAL laError(1), lnReturn, lcErrorStr
IF varTYPE(pcCursorName) = "C"
lnReturn = sqlexec(ghConnectionHandle, pcPassStr, pcCursorName)
ELSE
lnReturn = sqlexec(ghConnectionHandle, pcPassStr)
ENDIF
IF lnReturn < 0
= AERROR(laError)
lcErrorStr = ALLTRIM(STR(laError(1)))+ " " + laError(2)
IF TYPE(laError(3)) = "C"
lcErrorStr = lcErrorStr + " " + laError(3)
ENDIF
= nttofoxerror(lcErrorStr)
= nttofoxerror(pcPassStr)
ENDIF
RETURN lnReturn
ENDFUNC
