Wiki Home

Sql Parameters


Namespace: SoftwareEng
* ParmObj.prg 
* by CarlKarsten
* Demos 3 things that are roughly related:
* 1. Keeping Parameters in Proper Scope
* 2. Dealing with Datatypes
* 3. Dynamic Views

Set Step on

* Warn if vars arn't local
_vfp.LanguageOptions=1

varX = 1
Release varX

Local lcSql, ldStart
Local lcPid, lhX, lnRet

* Clean up from previous runs
Close Databases 
try
	Delete Database tempdbc
Catch
EndTry 

* Connect to the server
lhX = SQLStringConnect( "dsn=northwind;UID=lu;PWD=abc" )

* Make a dbc for later
Create Database tempdbc
Create Connection tempcon datasource 'northwind' userid 'lu' password 'abc'


* Run a simple query
lnRet = SqlExec( lhx, "select * from Person" )
* Check for errors
If lnRet < 0
	? "error!"
	cancel
EndIf
browse

* run the same query using a SqlExec wrapper.
ExecSql( lhx, "select * from Person" )
Browse

* run a better query (it has a parameter!)
ExecSql( lhx, "select * from Person where cLastName = 'SANCHEZ'" )
Browse

* run a more real query
lcPid = InputBox( "Enter ID:", "Hey User!", '525-04-9026' )
lcSql = [select * from Person where cPersonId = '] + lcPid + [']
ExecSql( lhx, lcSql )
Browse 

* Run a query that needs some data type conversion
* ldStart is a VFP date datatype:
ldStart = {10/11/1993}
lcSql=[select * from Person where dStartDate = ?ldStart]
SQLExec( lhx, lcSql )
Browse 

* Show the same thing with a view
Create sql View v_Person remote connection tempcon as ;
	select * from Person ;
		where dStartDate = ?ldStart
		
USE v_Person
Browse

* Now use the SqlExec wrapper
ExecSQL( lhx, lcSql )
Browse 


* do it right
DO ItRight With lhX

* fun time
Do SqlFun With lhX

* Dynamic Views
DO DynView With lhX



* Done.
Return
*************************************************************************











Procedure ItRight 

Lparameters ;
	thX 

Local ;
	lcSql, ;
	loSqlParams 

* Do this query again, only now the vars are a Local Parameter Object

loSqlParams = CreateObject( "empty" )
AddProperty( loSqlParams, "dStart", {10/11/1993} )

lcSql = [select * from Person where dStartDate = ?.dStart]
ExecSql2( thx, lcSql, loSqlParams )
Browse 

* Show the same thing with a view
Create sql View v_Person2 remote connection tempcon as ;
	select * from Person ;
		where dStartDate = ?.dStart

USE v_Person2 Nodata 
ViewRequery(loSqlParams)
Browse


*************************************************************************
Procedure DynView 

Lparameters ;
	thX 

Local ;
	loSqlParams, ;
	lcWhere, ;
	lnRet

Create sql View v_Person3 remote connection tempcon as ;
	select * from Person 
DBSetProp( "v_Person3","VIEW","SendUpdates",.t.)

loSqlParams = CreateObject( "empty" )
AddProperty( loSqlParams, "dStart", {6/12/2000} )
AddProperty( loSqlParams, "cLastName", "MARTINEZ" )

lcWhere = [dStartDate = ?.dStart]
lcWhere = lcWhere + [ and ] + [cLastName = ?.cLastName]

lcSql = DBGetProp( "v_Person3","VIEW","SQL")

lcSql = lcSql + " WHERE " + lcWhere 

USE v_Person3 Nodata 

ExecSql2( thx, lcSql, loSqlParams )

Select v_Person3
CursorSetProp("SendUpdates",.f.)
Append From Dbf('SqlResult')
lnRet = TableUpdate()
CursorSetProp("SendUpdates",.t.)

Browse

lnRet = TableUpdate()


Return

*************************************************************************
Function SqlFun

Lparameters ;
	thX 

Local ;
	lcSql, ;
	loSqlParams 

loSqlParams = CreateObject( "empty" )
AddProperty( loSqlParams, "dStart", {10/11/1993} )

Text To lcSql Noshow 
select * 
	from Person 
	where dStartDate 
		between ?GoYear( .dStart, 1 ) and ?Date(1995,1,1)
Endtext 	
ExecSql2( thx, lcSql, loSqlParams )
Browse 



Function GoYear( tdStart, tnYears )
Local ldRet
ldRet = Gomonth( tdStart, tnYears*12 )
Return ldRet

*************************************************************************
* Wrapers *
*************************************************************************
Function ViewRequery( toSqlParams )

Local ;
	ltStart, ;
	lnRet, ;
	lnLen, ;
	llError

ltStart = Datetime()

With toSqlParams 
	lnRet = Requery()
EndWith 

lnLen = Datetime() - ltStart

llError = lCkErr( lnRet=0, "requery" )

If lnLen>60 Or llError
	LogQuery( tcSqlCmd, ltStart, lnLen, llError )
Endif 

Return
*************************************************************************


















Function ExecSql( thCon, tcSqlCmd )

* Logs wary commands 

Local ;
	ldStart, ;
	lnRet, ;
	lnLen, ;
	llError

ldStart = Datetime()
lnRet = SQLExec( thCon, tcSqlCmd )
lnLen = Datetime() - ldStart
llError = lCkErr( lnRet<0, tcSqlCmd )

If lnLen>60 Or llError
	LogQuery( tcSqlCmd, ltStart, lnLen, llError )
Endif 

Return lnRet
************************************************************************













Function ExecSql2( thCon, tcSqlCmd, toParms )

Local ;
	ltStart, ;
	lnRet, ;
	lnLen, ;
	llError


ltStart = Datetime()

* This is the magic: WITH
With toParms
	lnRet = SQLExec( thCon, tcSqlCmd )
Endwith

lnLen = Datetime() - ltStart

llError = lCkErr(lnRet<0)

If lnLen>60 Or llError
	LogQuery( tcSqlCmd, ltStart, lnLen, llError )
Endif 

Return lnRet
*************************************************************************










Function LogQuery( tcSqlCmd, ttStart, tnLen, tlError )

Insert Into QueryLog ;
	( cSqlCmd, tStart, nLen, lError ) ;
Values ;
	( tcSqlCmd, ttStart, tnLen, tlError )

Return



* create command to make the log
Create Table QueryLog ( ;
	cSqlCmd m, ; 
	tStart t, ;
	nLen i, ;
	lError l) 

*************************************************************************
Function lckErr( tlErr, tcDsc )
If tlErr
	If !Empty( tcDsc )
		? tlErr, tcDsc
	Endif
	xErrHlr()
Endif
Return tlErr
Endfunc
*************************************************************************
Function xErrHlr( tnLin, tcLin )

#Define CRLF Chr(13)+Chr(10)
#Define CR_LF Chr(13) + Chr(10)
External array gaErrors

Local ;
	lnErr, lnErr, ;
	llOleErr, llOdbcErr, llTrigErr, ;
	lcErrMsg

Local ;
	array laErrs[1]

If !Empty( tcLin )
	? "Line " + Transform( tnLin ) + ": " + Transform( tcLin )
Endif

For lnErr = 1 To Aerror( laErrs )

	llOleErr = Between( laErrs[lnErr, 1], 1426, 1429 )
	llOdbcErr = laErrs[lnErr, 1] = 1526
	llTrigErr = laErrs[lnErr, 1] = 1539

	lcErrMsg = ;
		'1. Error: ' + Trans( laErrs[lnErr, 1] ) + CRLF ;
		+ '2. Message: "' + laErrs[lnErr, 2] + '"' + CRLF ;
		+ '3. ' + Iif( llOleErr Or llOdbcErr, "OLE/ODBC: ", "EMP: " ) + Transform( laErrs[lnErr, 3] ) + CRLF ;
		+ '4. ' + Iif( llOleErr, "App: ", ;
		iif( llOdbcErr, "SQL State: ", ;
		"Work Area (?): " ) + Transform( laErrs[lnErr, 4] ) ) + CRLF ;
		+ '5. ' + Iif( llOleErr, "OLE Help file: " + Trans( laErrs[lnErr, 5] ), ;
		+ Iif( llOdbcErr, "ODBC Error: " + Transform( laErrs[lnErr, 5] ), ;
		+ Iif( llTrigErr, "Trigger: " + Substr( "InsertUpdateDelete", laErrs[lnErr, 5]*6-5, 6 ), ;
		+ "na: " + Transform( laErrs[lnErr, 5] ) ) ) ) + CRLF ;
		+ '6. ' + Iif( llOleErr, "OLE Help Context Id : ", ;
		+ Iif( llOdbcErr, "ODBC Connection Handle: ", ;
		+ "na: " ) ) + Transform( laErrs[lnErr, 6] ) + CRLF ;
		+ '7. ' + Iif( llOleErr, "OLE exception number: ", ;
		+ "na: " ) + Transform( laErrs[lnErr, 6] )

	Do Case
	Case laErrs(1) = 1539
		lcErrMsg = lcErrMsg ;
			+ "Trigger error #: " ;
			+ Transform( laErrs(5) ) + ', ' ;
			+ Substr( "InsertUpdateDelete", laErrs(5)*6-5, 6 ) ;
			+ CR_LF + CR_LF

		If Vartype( gaErrors[1] ) = 'N'
			For lnErrorRow = 1 To Alen( gaErrors, 1 )
				lcErrMsg = lcErrMsg +;
					"1. Error number: " + Trans( gaErrors[lnErrorRow,1] ) + CR_LF + ;
					"2. Error text: " + Trans( gaErrors[lnErrorRow,2] ) + CR_LF + ;
					"3. Source: " + gaErrors[lnErrorRow,3] + CR_LF + ;
					"4. Call stack: " + gaErrors[lnErrorRow,4] + CR_LF + ;
					"5. Parent table name: " + gaErrors[lnErrorRow,5] + CR_LF + ;
					"6. Parent record number: " + Trans( gaErrors[lnErrorRow,6] ) + CR_LF + ;
					"7. Original value of Primary Key of the Parent: " + Trans( gaErrors[lnErrorRow,7] ) + CR_LF + ;
					"8. Parent Primary Key (expression): " + gaErrors[lnErrorRow,8] + CR_LF + ;
					"9. Child Table Name: " + gaErrors[lnErrorRow,9] + CR_LF + ;
					"10. Child Record Number: " + Trans( gaErrors[lnErrorRow,10] ) + CR_LF + ;
					"11. Original value of Foreign Key in the Child: " + Trans( gaErrors[lnErrorRow,11] ) + CR_LF + ;
					"12. Child Foreign Key (expression): " + gaErrors[lnErrorRow,12 ] + CR_LF + CR_LF
			Endfor
		Endif
	Endcase

	? lcErrMsg
	_Cliptext = _Cliptext + CR_LF + lcErrMsg
	_Cliptext =	laErrs[1,2]
Endfor
Suspend

Return

Endfunc



This code should use the northwind database, not my own. If someone feels inclined to make it so, remember that ldStart should colide with the start time of the query, or something similare. It can error too, but I find the empty result set to be 'better'. -- cfk