Wiki Home

Spt Parameters


Namespace: WIN_COM_API

Passing parameter values to a routine that doesn't need to know the names of them.

Goals

Wrap SqlExec() in a function, call it with a parameterized statement, and
have all vars/parameters be local (not private or public, no warnings from _VFP.LanguageOptions=1 )

No "you have to name your var X" rules

Parameter object is not optional - if you don't have any params, pass createobject("emppty")

In the code below, follow the dot in ?.cItemName (stuff removed)
nSql( "sel * where cItemName like ?.cItemName")
Function nSql(tcSqlCmd )
SQLExec( tcSqlCmd )

Function main()

Local ;
 loSqlParams as empty

loSqlParams = CreateObject( "empty" )
AddProperty( loSqlParams, "cItemName", Trim( thisform.txtItemName.Value ) )
lnRet = nSql( "select * from item where cItemName like ?.cItemName", loSqlParams )
return

Function nSql()
Lparameters ;
 tcSqlCmd, ;
 toSqlParams as empty

Local ;
 lnRet

With toSqlParams
  lnRet = SQLExec( Thisform.nSqlHandel, tcSqlCmd )
EndWith

Return lnRet

Here is some more examples of using nSql(). I suppose instead of passing in 4 parameters to LogIt() I could have passed in one parm object. hmm, that ties the calling code to the property names. Not sure if that is really good or not. I think it is good. Maybe next time I will code it that way.
function LogIt

lParameters ;
	tcItemName, ;
	tcCat, ;
	tcPlayer, ;
	tnPrice

Local ;
 loSqlParams as empty

loSqlParams = CreateObject( 'empty' )	
AddProperty( loSqlParams, "kItem", sqlresult.pk_Item )
AddProperty( loSqlParams, "cCat", tcCat)
AddProperty( loSqlParams, "tEff", Datetime() )

lnRet = Thisform.nSql( "select pk_Price from Price ;
where fk_item =  ?.kItem And cCat = ?.cCat", loSqlParams )

If Reccount("sqlresult") = 0
	lnRet = Thisform.nSql( ;
		"insert into Price ( fk_item, cCat, tEffective, nPrice ) ;
values ( ?.kItem, ?.cCat, ?.tEff, ?.nPrice )", loSqlParams )
Else
	lkPrice = sqlresult.pk_Price
	lnRet = Thisform.nSql( "update Price ;
Set tEffective = ?.tEff, nPrice = ?.nPrice ;
where pk_Price = ?.kPrice", loSqlParams )
		
EndIf


VP's can be any VFP expression, and are not restricted to the WHERE clause.

I have just stumbled on this, and am not really sure how useful it is, other than solving my original problem: how do i get a virtual field filled with .f. in my view against Ms Sql. A: ?.f. as lSelected. This lead me to wonder: what else can I put after the '?'? A: lots of things, but the use is pretty small. The value gets evaluated once, before the server ever sees it, so it isn't much better than storing the value to a var and using the var as a parameter. For a min I thought I had discovered a worm hole that would allow the back end to talk to the client, but that does not seem to be the case. -- ?cfk

* Give it a valid connection to anything,
* and replace cxTablesID/xTables with a valid field and table name

Clear
lhCon = SQLConnect( "pw", "pwuser", "pw" )
ckError( lhCon )

TEXT to lcSqlCmd noshow
select cxTablesID, ?.t., ?Version(), ?TypeVal( lhCon )
 from xTables
ENDTEXT

? lcSqlCmd
lnRet =  SQLExec( lhCon, lcSqlCmd)
If !ckError( lnRet )
	Browse
Endif

Function TypeVal(tuParm)
	Local ;
		lcRet
	lcRet = Vartype( tuParm ) + " " + Transform( tuParm )
	Return lcRet

Function ckError( tnError )
	llError = tnError<0
	If llError
		For lnError = 1 To Aerror(laErrors)
			? lnError, laErrors[lnError, 2 ]
		Endfor

	Endif
	Return llError

See Also: View Parameters Sql Parameters
Contributors Carl Karsten
( Topic last updated: 2003.02.22 11:48:13 AM )