grewegreg
IS-IT--Management
- Apr 1, 2007
- 23
Hi All,
I was watching the Bengals lose to the Browns today and was browsing some old threads at the same time. I read this one (thread184-1207200), in which Craig Boyd posted some very cool code. After running Craig's code a few times, it occurred to me that rarely does my code ever have a transaction with just one statement, so I tinkered with it and the code below is what I came up with. There are several IF and CASE Statements to slow the processing down, but to me it seems realistic.
You have an option to use native FoxPro tables or any ODBC connection on your machine. If you run this code to MS SQL Server over a Broadband connection, it runs surprisingly well. I have not finished testing all the ODBC Code and the related errors, but I think the code is pretty solid.
Though I am no longer seeing the 50 million + transactions that I saw when running Craig's original code, it still simulates that you can do what was originally questioned, either via a Citrix connection or through a Broadband connection.
Greg Grewe
Diamond Consulting, LLC
I was watching the Bengals lose to the Browns today and was browsing some old threads at the same time. I read this one (thread184-1207200), in which Craig Boyd posted some very cool code. After running Craig's code a few times, it occurred to me that rarely does my code ever have a transaction with just one statement, so I tinkered with it and the code below is what I came up with. There are several IF and CASE Statements to slow the processing down, but to me it seems realistic.
You have an option to use native FoxPro tables or any ODBC connection on your machine. If you run this code to MS SQL Server over a Broadband connection, it runs surprisingly well. I have not finished testing all the ODBC Code and the related errors, but I think the code is pretty solid.
Though I am no longer seeing the 50 million + transactions that I saw when running Craig's original code, it still simulates that you can do what was originally questioned, either via a Citrix connection or through a Broadband connection.
Code:
PUBLIC ofrmtransactionthroughput
ofrmtransactionthroughput=NEWOBJECT("frmtransactionthroughput")
ofrmtransactionthroughput.Show
RETURN
**************************************************
*-- Form: frmtransactionthroughput (c:\data\foxpro\bin\test_transaction_throughput.scx)
*-- ParentClass: form
*-- BaseClass: form
*-- Time Stamp: 09/16/07 08:28:11 PM
*
DEFINE CLASS frmtransactionthroughput AS form
Height = 456
Width = 385
ShowWindow = 2
DoCreate = .T.
AutoCenter = .T.
Caption = "Test Transaction Throughput..."
rn_sqlconnect = .F.
Name = "frmTransactionThroughput"
DIMENSION ra_drvrs[1,2]
ADD OBJECT cmdbegin AS commandbutton WITH ;
Top = 400, ;
Left = 71, ;
Height = 34, ;
Width = 75, ;
FontSize = 12, ;
Caption = "\<Begin", ;
Default = .T., ;
TabIndex = 10, ;
ForeColor = RGB(0,128,0), ;
Name = "cmdBegin"
ADD OBJECT cmdcancel AS commandbutton WITH ;
Top = 400, ;
Left = 239, ;
Height = 34, ;
Width = 75, ;
FontSize = 12, ;
Cancel = .T., ;
Caption = "Cancel", ;
TabIndex = 11, ;
ForeColor = RGB(255,0,0), ;
Name = "cmdCancel"
ADD OBJECT label1 AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Process for how many seconds?", ;
Height = 21, ;
Left = 30, ;
Top = 23, ;
Width = 227, ;
TabIndex = 12, ;
ForeColor = RGB(0,0,255), ;
Name = "Label1"
ADD OBJECT txtseconds AS textbox WITH ;
FontSize = 12, ;
Alignment = 3, ;
Value = 10, ;
Height = 23, ;
InputMask = "999", ;
Left = 307, ;
MaxLength = 3, ;
TabIndex = 1, ;
Top = 22, ;
Width = 48, ;
Name = "txtSeconds"
ADD OBJECT label2 AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Use how many Indexes?", ;
Height = 21, ;
Left = 88, ;
Top = 58, ;
Width = 169, ;
TabIndex = 13, ;
ForeColor = RGB(0,0,255), ;
Name = "Label2"
ADD OBJECT txtindexes AS textbox WITH ;
FontSize = 12, ;
Alignment = 3, ;
Value = 0, ;
Height = 23, ;
InputMask = "9", ;
Left = 307, ;
MaxLength = 1, ;
TabIndex = 2, ;
Top = 57, ;
Width = 48, ;
Name = "txtIndexes"
ADD OBJECT chkprogress AS checkbox WITH ;
Top = 93, ;
Left = 30, ;
Height = 21, ;
Width = 148, ;
FontSize = 12, ;
AutoSize = .T., ;
Alignment = 0, ;
BackStyle = 0, ;
Caption = "Display Progress?", ;
Value = .F., ;
TabIndex = 3, ;
ForeColor = RGB(0,0,255), ;
Name = "chkProgress"
ADD OBJECT chkbuffering AS checkbox WITH ;
Top = 93, ;
Left = 222, ;
Height = 21, ;
Width = 133, ;
FontSize = 12, ;
AutoSize = .T., ;
Alignment = 0, ;
BackStyle = 0, ;
Caption = "Table Buffering?", ;
Value = .F., ;
TabIndex = 4, ;
ForeColor = RGB(0,0,255), ;
Name = "chkBuffering"
ADD OBJECT cbodatasources AS combobox WITH ;
RowSourceType = 5, ;
RowSource = "THISFORM.ra_Drvrs", ;
Height = 24, ;
Left = 163, ;
TabIndex = 7, ;
Top = 154, ;
Visible = .F., ;
Width = 192, ;
BoundTo = .T., ;
Name = "cboDataSources"
ADD OBJECT opgdatasource AS optiongroup WITH ;
AutoSize = .T., ;
ButtonCount = 2, ;
BackStyle = 0, ;
Value = 1, ;
Height = 46, ;
Left = 36, ;
Top = 132, ;
Width = 107, ;
TabIndex = 5, ;
Name = "opgDataSource", ;
Option1.BackStyle = 0, ;
Option1.Caption = "\<FoxPro Tables", ;
Option1.Value = 1, ;
Option1.Height = 17, ;
Option1.Left = 5, ;
Option1.Top = 5, ;
Option1.Width = 97, ;
Option1.AutoSize = .T., ;
Option1.Name = "Option1", ;
Option2.BackStyle = 0, ;
Option2.Caption = "ODBC Tables", ;
Option2.Height = 17, ;
Option2.Left = 5, ;
Option2.Top = 24, ;
Option2.Width = 94, ;
Option2.AutoSize = .T., ;
Option2.Name = "Option2"
ADD OBJECT cntprogress AS container WITH ;
Top = 192, ;
Left = 12, ;
Width = 360, ;
Height = 72, ;
Visible = .F., ;
TabIndex = 8, ;
Name = "cntProgress"
ADD OBJECT frmtransactionthroughput.cntprogress.lblseconds AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Seconds processed so far:", ;
Height = 21, ;
Left = 14, ;
Top = 13, ;
Width = 189, ;
ForeColor = RGB(0,0,255), ;
Name = "lblSeconds"
ADD OBJECT frmtransactionthroughput.cntprogress.lblsecondsprocessed AS label WITH ;
FontSize = 12, ;
Alignment = 1, ;
BackStyle = 0, ;
Caption = "0", ;
Height = 21, ;
Left = 225, ;
Top = 13, ;
Width = 120, ;
ForeColor = RGB(0,0,255), ;
Name = "lblSecondsProcessed"
ADD OBJECT frmtransactionthroughput.cntprogress.lbltransactions AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Transactions processed so far:", ;
Height = 21, ;
Left = 14, ;
Top = 38, ;
Width = 216, ;
ForeColor = RGB(0,0,255), ;
Name = "lblTransactions"
ADD OBJECT frmtransactionthroughput.cntprogress.lbltransactionsprocessed AS label WITH ;
FontSize = 12, ;
Alignment = 1, ;
BackStyle = 0, ;
Caption = "0", ;
Height = 21, ;
Left = 225, ;
Top = 38, ;
Width = 120, ;
ForeColor = RGB(0,0,255), ;
Name = "lblTransactionsProcessed"
ADD OBJECT cntresults AS container WITH ;
Top = 276, ;
Left = 12, ;
Width = 360, ;
Height = 96, ;
TabIndex = 9, ;
Name = "cntResults"
ADD OBJECT frmtransactionthroughput.cntresults.lblseconds AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Total Seconds Executed:", ;
Height = 21, ;
Left = 12, ;
Top = 13, ;
Width = 175, ;
ForeColor = RGB(0,0,255), ;
Name = "lblSeconds"
ADD OBJECT frmtransactionthroughput.cntresults.lblsecondsprocessed AS label WITH ;
FontSize = 12, ;
Alignment = 1, ;
BackStyle = 0, ;
Caption = "0", ;
Height = 21, ;
Left = 225, ;
Top = 13, ;
Width = 120, ;
ForeColor = RGB(0,0,255), ;
Name = "lblSecondsProcessed"
ADD OBJECT frmtransactionthroughput.cntresults.lbltransactions AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Total Transactions processed:", ;
Height = 21, ;
Left = 12, ;
Top = 38, ;
Width = 211, ;
ForeColor = RGB(0,0,255), ;
Name = "lblTransactions"
ADD OBJECT frmtransactionthroughput.cntresults.lbltransactionsprocessed AS label WITH ;
FontSize = 12, ;
Alignment = 1, ;
BackStyle = 0, ;
Caption = "0", ;
Height = 21, ;
Left = 225, ;
Top = 38, ;
Width = 120, ;
ForeColor = RGB(0,0,255), ;
Name = "lblTransactionsProcessed"
ADD OBJECT frmtransactionthroughput.cntresults.label1 AS label WITH ;
AutoSize = .T., ;
FontSize = 12, ;
BackStyle = 0, ;
Caption = "Estimated, if 30 minutes:", ;
Height = 21, ;
Left = 12, ;
Top = 63, ;
Width = 173, ;
ForeColor = RGB(0,0,255), ;
Name = "Label1"
ADD OBJECT frmtransactionthroughput.cntresults.lbl30transactions AS label WITH ;
FontSize = 12, ;
Alignment = 1, ;
BackStyle = 0, ;
Caption = "0", ;
Height = 21, ;
Left = 225, ;
Top = 63, ;
Width = 120, ;
ForeColor = RGB(0,0,255), ;
Name = "lbl30Transactions"
ADD OBJECT chksqlinsert AS checkbox WITH ;
Top = 132, ;
Left = 222, ;
Height = 21, ;
Width = 133, ;
FontSize = 12, ;
AutoSize = .T., ;
Alignment = 0, ;
BackStyle = 0, ;
Caption = "Use SQL Insert?", ;
Value = .F., ;
TabIndex = 6, ;
ForeColor = RGB(0,0,255), ;
Name = "chkSQLInsert"
PROCEDURE m_loadodbcfuncs
LOCAL henv,fDirection,szDriverDesc,cbDriverDescMax
LOCAL pcbDriverDesc,szDriverAttributes,cbDrvrAttrMax,pcbDrvrAttr
LOCAL szDSN,cbDSNMax,pcbDSN,szDescription,cbDescriptionMax,pcbDescription
LOCAL lcODBCDLLFile AS Character
STORE "ODBC32.DLL" TO lcODBCDLLFile
DECLARE Short SQLDrivers IN (lcODBCDLLFile) ;
Integer henv, Integer fDirection, ;
String @ szDriverDesc, Integer cbDriverDescMax, Integer pcbDriverDesc, ;
String @ szDriverAttributes, Integer cbDrvrAttrMax, Integer pcbDrvrAttr
DECLARE Short SQLDataSources IN (lcODBCDLLFile) ;
Integer henv, Integer fDirection, ;
String @ szDSN, Integer cbDSNMax, Integer @ pcbDSN, ;
String @ szDescription, Integer cbDescriptionMax,Integer pcbDescription
RETURN 0
ENDPROC
PROCEDURE m_getodbcdrvrs
PARAMETER lDataSources
LOCAL nODBCEnv,nRetVal,dsn,dsndesc,mdsn,mdesc
lDataSources = IIF(TYPE("m.lDataSources")="L",m.lDataSources,.F.)
* Load API functions
nRetVal = THISFORM.m_LoadODBCFuncs()
IF m.nRetVal <> 0
RETURN m.nRetVal
ENDIF
* Get ODBC environment handle
nODBCEnv=VAL(SYS(3053))
* -- Possible error messages
* 527 "cannot load odbc library"
* 528 "odbc entry point missing"
* 182 "not enough memory"
IF INLIST(nODBCEnv,527,528,182)
* Failed
RETURN -113 && failed to get ODBC environment
ENDIF
DIMENSION THISFORM.ra_Drvrs[1,IIF(m.lDataSources,2,1)]
THISFORM.ra_Drvrs[1] = ""
DO WHILE .T.
dsn=space(100)
dsndesc=space(100)
mdsn=0
mdesc=0
* Return drivers or data sources
IF m.lDataSources
nRetVal = SQLDataSources(m.nODBCEnv,1,@dsn,100,@mdsn,@dsndesc,255,@mdesc)
ELSE
nRetVal = SQLDrivers(m.nODBCEnv,1,@dsn,100,@mdsn,@dsndesc,100,@mdesc)
ENDIF
DO CASE
CASE m.nRetVal = 100
nRetVal = 0
EXIT
CASE m.nRetVal <> 0 AND m.nRetVal # 1
EXIT
OTHERWISE
IF !EMPTY(THISFORM.ra_Drvrs[1])
IF m.lDataSources
DIMENSION THISFORM.ra_Drvrs[ALEN(THISFORM.ra_Drvrs,1)+1,2]
ELSE
DIMENSION THISFORM.ra_Drvrs[ALEN(THISFORM.ra_Drvrs,1)+1,1]
ENDIF
ENDIF
dsn = ALLTRIM(m.dsn)
THISFORM.ra_Drvrs[ALEN(THISFORM.ra_Drvrs,1),1] = LEFT(m.dsn,LEN(m.dsn)-1)
IF m.lDataSources
dsndesc = ALLTRIM(m.dsndesc)
THISFORM.ra_Drvrs[ALEN(THISFORM.ra_Drvrs,1),2] = LEFT(m.dsndesc,LEN(m.dsndesc)-1)
ENDIF
ENDCASE
ENDDO
RETURN nRetVal
ENDPROC
PROCEDURE m_enumodbcdrvrs
LPARAMETER aDrvrOpts,cODBCDriver
LOCAL cSourceKey
#DEFINE HKEY_LOCAL_MACHINE -2147483646 && BITSET(0,31)+2
cSourceKey = "Software\ODBC\ODBCINST.INI\" + cDataSource
RETURN THIS.EnumOptions(@aDrvrOpts,m.cSourceKey,HKEY_LOCAL_MACHINE,.F.)
ENDPROC
PROCEDURE m_enumodbcdata
LPARAMETER aDrvrOpts,cDataSource
LOCAL cSourceKey
#DEFINE HKEY_CURRENT_USER -2147483647 && BITSET(0,31)+1
cSourceKey = "Software\ODBC\ODBC.INI\" + cDataSource
RETURN THIS.EnumOptions(@aDrvrOpts,m.cSourceKey,HKEY_CURRENT_USER,.F.)
ENDPROC
PROCEDURE m_enumoptions
LPARAMETER aRegOpts,cOptPath,nUserKey,lEnumKeys
LOCAL iPos,cOptKey,cOption,nErrNum
iPos = 0
cOption = ""
nErrNum = ERROR_SUCCESS
IF PARAMETERS()<4 OR TYPE("m.lEnumKeys") # "L"
lEnumKeys = .F.
ENDIF
* Open key
m.nErrNum = THIS.OpenKey(m.cOptPath,m.nUserKey)
IF m.nErrNum # ERROR_SUCCESS
RETURN m.nErrNum
ENDIF
* Enumerate through keys
IF m.lEnumKeys
* Enumerate and get key names
nErrNum = THIS.EnumKeys(@aRegOpts)
ELSE
* Enumerate and get all key values
nErrNum = THIS.EnumKeyValues(@aRegOpts)
ENDIF
* Close key
THIS.CloseKey() &&close key
RETURN m.nErrNum
ENDPROC
PROCEDURE m_sql_error
LPARAMETERS pcErrorMessage
LOCAL ARRAY laError[1,7]
=AERROR(laError)
IF ISNULL(laError[1,4])
STORE laError[1,2] TO laError[1,3]
ENDIF &&& ISNULL(laError)
*
=MESSAGEBOX(pcErrorMessage + ', for the "' + ALLTRIM(THISFORM.cboDataSources.Value) + ;
'" Datasource, please try again!' + CHR(13) + CHR(13) + ;
'Error Message: ' + laError[1,3], 16, "ODBC Error...")
*
THISFORM.m_SQL_Drop()
ENDPROC
PROCEDURE m_fox_tables
LPARAMETERS pnStart
LOCAL lnTotal AS Number, lsSQLStr AS String, lcMultiLocks AS Character
STORE 0 TO lnTotal
STORE SET("Multilocks") TO lcMultilocks
IF THISFORM.chkBuffering.Value
SET MULTILOCKS ON
=CURSORSETPROP("Buffering", 5, "tblTest")
ENDIF &&& THISFORM.chkBuffering.Value
*
DO WHILE (SECONDS() - pnStart) < THISFORM.txtSeconds.Value &&& Run it for X seconds
BEGIN TRANSACTION
IF THISFORM.chkSQLInsert.Value
STORE "INSERT INTO tblTest (Field1, Field2, Field3, Field4, Field5, Field6, Field7, " + ;
"Field8, Field9) VALUES ('" + SYS(2015) + "', '" + SYS(2015) + "', '" + ;
SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "', '" + ;
SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "')" TO lsSQLStr
ELSE
APPEND BLANK
STORE "REPLACE Field1 WITH '" + SYS(2015) + "', Field2 WITH '" + SYS(2015) + ;
"', Field3 WITH '" + SYS(2015) + "', Field4 WITH '" + SYS(2015) + ;
"', Field5 WITH '" + SYS(2015) + "', Field6 WITH '" + SYS(2015) + ;
"', Field7 WITH '" + SYS(2015) + "', Field8 WITH '" + SYS(2015) + ;
"', Field9 WITH '" + SYS(2015) + "' IN tblTest" TO lsSQLStr
ENDIF &&& THISFORM.chkSQLInsert.Value
*
&lsSQLStr
END TRANSACTION
*
lnTotal = lnTotal + 1
IF THISFORM.chkProgress.Value
STORE TRANSFORM(SECONDS() - pnStart,"999,999,999,999") TO THISFORM.cntProgress.lblSecondsProcessed.Caption
STORE TRANSFORM(lnTotal,"999,999,999,999") TO THISFORM.cntProgress.lblTransactionsProcessed.Caption
ENDIF &&& THISFORM.chkProgress.Value
ENDDO &&& WHILE (SECONDS() - pnStart) < lnSeconds
*
SET MULTILOCKS &lcMultiLocks
RETURN lnTotal
ENDPROC
PROCEDURE m_sql_tables
LPARAMETERS pnStart
LOCAL lnTotal AS Number, lsSQLStr AS String
STORE 0 TO lnTotal
DO WHILE (SECONDS() - pnStart) < THISFORM.txtSeconds.Value &&& Run it for X seconds
STORE "INSERT INTO tblTest (Field1, Field2, Field3, Field4, Field5, Field6, Field7, " + ;
"Field8, Field9) VALUES ('" + SYS(2015) + "', '" + SYS(2015) + "', '" + ;
SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "', '" + ;
SYS(2015) + "', '" + SYS(2015) + "', '" + SYS(2015) + "')" TO lsSQLStr
*
IF SQLSETPROP(THISFORM.rn_SqlConnect, "Transactions", 2) < 0 &&& Manual Transactions
THISFORM.m_SQL_Error('I was unable to Create Manual Transactions')
EXIT
ELSE
IF SQLEXEC(THISFORM.rn_SqlConnect, lsSQLStr) >= 0
=SQLCOMMIT(THISFORM.rn_SqlConnect)
ELSE
=SQLROLLBACK(THISFORM.rn_SqlConnect)
THISFORM.m_SQL_Error('I was unable to Commit Transaction # ' + TRANSFORM(lnTotal))
EXIT
ENDIF &&& SQLEXEC(THISFORM.rn_SqlConnect, lsSQLStr) < 0
ENDIF &&& SQLSETPROP(THISFORM.rn_SqlConnect, "Transactions", 2) > 0
*
lnTotal = lnTotal + 1
IF THISFORM.chkProgress.Value
STORE TRANSFORM(SECONDS() - pnStart,"999,999,999,999") TO THISFORM.cntProgress.lblSecondsProcessed.Caption
STORE TRANSFORM(lnTotal,"999,999,999,999") TO THISFORM.cntProgress.lblTransactionsProcessed.Caption
ENDIF &&& THISFORM.chkProgress.Value
ENDDO &&& WHILE (SECONDS() - pnStart) < lnSeconds
*
RETURN lnTotal
ENDPROC
PROCEDURE m_sql_drop
IF SQLEXEC(THISFORM.rn_SQLConnect, "DROP TABLE tblTest") >= 0
=SQLDISCONNECT(THISFORM.rn_SqlConnect)
ENDIF &&& SQLEXEC(THISFORM.rn_SqlConnect,"DELETE TABLE MyTest") >= 0
ENDPROC
PROCEDURE Init
*
* Written by: Gregory J. Grewe
* Diamond Consulting, LLC
* 9/16/2007
* &&& The day the Bengals lost 51-45 to the Browns...
*
LOCAL lcTemp AS Character, lnStart AS Number, lnEnd AS Number, lnTotal AS Number, ;
lnSeconds AS Number, lnDisplay AS Boolean, lnBuffering AS Boolean, lnIndexes AS Number, ;
I AS Number, lcIndexField AS Character, lnStart AS Number
PUBLIC pcCurDir AS Character, pcStatusBar AS Character, pcStatus AS Character, ;
pcTalk AS Character, pcSafety AS Character
STORE CURDIR() TO pcCurDir
STORE SET("Status Bar") TO pcStatusBar
STORE SET("Status") TO pcStatus
STORE SET("Talk") TO pcTalk
STORE SET("Safety") TO pcSafety
SET STATUS BAR OFF
SET STATUS OFF
SET TALK OFF
SET SAFETY OFF
THISFORM.m_GetODBCdrvrs(.T.)
=ASORT(THISFORM.ra_Drvrs,1,-1,0,1)
STORE 1 TO THISFORM.cboDataSources.ListIndex
ENDPROC
PROCEDURE QueryUnload
LOCAL llDoDefault AS Boolean
LOCAL ARRAY laError[1,7]
CLOSE DATABASES ALL
IF THISFORM.opgDataSource.Value = 1
ERASE MyTest.*
ERASE tblTest.*
STORE .T. TO llDoDefault
ELSE
STORE .T. TO llDoDefault
ENDIF &&& THISFORM.opgDataSource.Value = 1
*
IF llDoDefault
SET SAFETY &pcSafety
SET TALK &pcTalk
SET STATUS &pcStatus
SET STATUS BAR &pcStatusBar
SET DEFAULT TO &pcCurDir
RELEASE pcCurDir, pcStatusBar, pcStatus, pcTalk, pcSafety
DODEFAULT()
THISFORM.Release()
ELSE
NODEFAULT
ENDIF &&& llDoDefault
*
ENDPROC
PROCEDURE m_odbc_setup
ENDPROC
PROCEDURE cmdbegin.Click
LOCAL lsSQLStr AS String, lnStart AS Number, lnEnd AS Number, lnTotal AS Number, ;
llContinue AS Boolean
LOCAL ARRAY laError[1,7]
\
STORE 0 TO lnStart, lnEnd, lnTotal
STORE "0" TO THISFORM.cntresults.lbl30Transactions.Caption, ;
THISFORM.cntResults.lblSecondsProcessed.Caption, THISFORM.cntResults.lblTransactionsProcessed.Caption
STORE .F. TO THISFORM.cmdBegin.Enabled, THISFORM.cmdCancel.Enabled, THISFORM.Closable
IF THISFORM.opgDataSource.Value = 1 &&& FoxPro tables
STORE 0 TO THISFORM.rn_SqlConnect
ELSE &&& ODBC Tables
STORE SQLCONNECT(THISFORM.cboDataSources.Value) TO THISFORM.rn_SqlConnect
ENDIF &&& THISFORM.opgDataSource.Value = 1
*
IF THISFORM.rn_SqlConnect < 0
THISFORM.m_SQL_Error('I was unable to connect')
ELSE
CLOSE DATABASES all
SET DEFAULT TO SYS(2023) &&& temporary directory
*
STORE "CREATE TABLE tblTest (Field1 Char(20), Field2 Char(20), " + ;
"Field3 Char(20), Field4 Char(20), Field5 Char(20), Field6 Char(20)," + ;
"Field7 Char(20), Field8 Char(20), Field9 Char(20))" TO lsSQLStr
IF THISFORM.opgDataSource.Value = 1 &&& FoxPro tables
CREATE DATABASE MyTest &&& deleted later
SET DATABASE TO MyTest
&lsSQLStr
STORE .T. TO llContinue
ELSE &&& ODBC Tables
IF SQLEXEC(THISFORM.rn_SqlConnect, lsSQLStr) < 0
THISFORM.m_SQL_Error('I was unable to Create a Temporary Table')
STORE .F. TO llContinue
ELSE
STORE .T. TO llContinue
ENDIF &&& SQLEXEC(THISFORM.rn_SqlConnect,lsSQLStr) >= 0
ENDIF &&& THISFORM.opgDataSource.Value = 1
*
DO WHILE llContinue
IF THISFORM.opgDataSource.Value = 2 &&& ODBC Tables
IF SQLEXEC(THISFORM.rn_SqlConnect, "SELECT * FROM tblTest", "tblTest") < 0
THISFORM.m_SQL_Error('I was unable to Retrieve any Data from the Temporary Table')
EXIT
ENDIF &&& SQLEXEC(THISFORM.rn_SqlConnect, "SELECT * FROM tblTest", "tblTest") < 0
ENDIF &&& THISFORM.opgDataSource.Value = 2
*
FOR I = 1 TO THISFORM.txtIndexes.Value
IF THISFORM.opgDataSource.Value = 1 .OR. THISFORM.chkBuffering.Value
&&& FoxPro tables .OR. Table Buffering
*
SELECT("tblTest")
STORE "Field" + ALLTRIM(STR(I)) TO lcIndexField
INDEX ON &lcIndexField TAG &lcIndexField
ELSE
STORE "CREATE INDEX IX_Field" + ALLTRIM(STR(I)) + ;
" ON tblTest (Field" + ALLTRIM(STR(I)) + ")" TO lsSQLStr
IF SQLEXEC(THISFORM.rn_SqlConnect,lsSQLStr) < 0
THISFORM.m_SQL_Error('I was unable to Indexes on the Temporary Table')
EXIT
ENDIF &&& SQLEXEC(THISFORM.rn_SqlConnect,lsSQLStr) >= 0
ENDIF &&& THISFORM.opgDataSource.Value = 1
ENDFOR &&& I = 1 TO lnIndexes
*
IF THISFORM.chkBuffering.Value
SET MULTILOCKS ON
=CURSORSETPROP("Buffering", 5, "tblTest")
ENDIF &&& THISFORM.chkBuffering
*
IF !THISFORM.chkProgress.Value
WAIT WINDOW "Processing transactions, please be patient..." NOWAIT
ENDIF &&& THISFORM.chkProgress.Value
*
STORE SECONDS() TO lnStart
IF THISFORM.opgDataSource.Value = 1
&&& FoxPro tables .OR. Table Buffering
STORE THISFORM.m_Fox_Tables(lnStart) TO lnTotal
ELSE &&& ODBC Tables
&&& ODBC tables
STORE THISFORM.m_SQL_Tables(lnStart) TO lnTotal
ENDIF &&& THISFORM.opgDataSource.Value = 1
*
lnEnd = SECONDS() - lnStart
STORE TRANSFORM(lnEnd,"999,999,999,999") TO THISFORM.cntResults.lblSecondsProcessed.Caption
STORE TRANSFORM(lnTotal,"999,999,999,999") TO THISFORM.cntResults.lblTransactionsProcessed.Caption
STORE TRANSFORM((lnTotal/lnEnd) * 1800,"999,999,999,999") TO THISFORM.cntResults.lbl30Transactions.Caption
EXIT
ENDDO &&& DO WHILE llContinue
*
CLOSE DATABASES all
WAIT CLEAR
*
IF THISFORM.opgDataSource.Value = 2 .AND. VAL(THISFORM.cntResults.lblTransactionsProcessed.Caption) <> 0
THISFORM.m_SQL_Drop()
ENDIF &&& THISFORM.opgDataSource.Value = 2 .AND. VAL(THISFORM.cntResults.lblTransactionsProcessed.Caption) <> 0
ENDIF &&& lnSQLConnect < 0
*
STORE .T. TO THISFORM.cmdBegin.Enabled, THISFORM.cmdCancel.Enabled, THISFORM.Closable
ENDPROC
PROCEDURE cmdcancel.Click
IF TXNLEVEL() > 0
ROLLBACK
ENDIF &&& TXNLEVEL() > 0
*
THISFORM.QueryUnload()
ENDPROC
PROCEDURE txtindexes.Valid
LOCAL lnRetVal
IF BETWEEN(THIS.Value, 0, 9)
STORE 1 TO lnRetVal
ELSE
=MESSAGEBOX("You must supply a value between zero and nine, inclusive!", ;
16, "Bad value...")
STORE 0 TO lnRetVal
ENDIF &&& BETWEEN(THIS.Value, 0, 9)
*
ENDPROC
PROCEDURE chkprogress.InteractiveChange
IF THIS.Value
STORE .T. TO THISFORM.cntProgress.Visible
ELSE
STORE .F. TO THISFORM.cntProgress.Visible
ENDIF &&& THIS.Value
*
ENDPROC
PROCEDURE opgdatasource.InteractiveChange
IF THIS.Value = 1
STORE .F. TO THISFORM.cboDataSources.Visible
STORE .T. TO THISFORM.cboSQLInsert.Value
ELSE
STORE .T. TO THISFORM.cboDataSources.Visible, THISFORM.chkSQLInsert.Value
STORE .F. TO THISFORM.chkSQLInsert.Visible
ENDIF &&& THIS.Value = 1
*
ENDPROC
ENDDEFINE
*
*-- EndDefine: frmtransactionthroughput
**************************************************Greg Grewe
Diamond Consulting, LLC
