SQL Seek.. a thought / feedback please

[EzLogic]

VFP 9 SP2
SQL server 2012 (express) backend.
Very new to SQL integration (though I know little of sql)

Little background:

App written in vfp, currently uses vfp backend data.
I am in the process of re-writing the app so it will use sql back end instead.
I am using a modified sql wrapper from west-wind. Very nice class, works well.

In many of my forms or prgs, i tend to use seek() to find certain values before other codes executes.

So, I wrote this little SQLSeek() method that, for now, seems like its giving me what i want.

FUNCTION SQLSeek(tcField,tcValue,tcTable)
	LOCAL lcCursor, lnCount, lcSQL
	lcCursor = SYS(2015)
	
	IF PCOUNT()< 3
		MESSAGEBOX("Error in SQL Seek Method." + CHR(13) + ;
				   "Method requires 3 parameters.",16,"SQL Seek Error")
		RETURN .f.
	ENDIF
	
	
	IF TYPE('tcValue') = 'C'
		tcValue = [']+tcValue+[']
	ENDIF 
	
	IF TYPE('loSQL') <> "O"
		loSQL = createfactory("EzCellSQL")
	ENDIF 
	
	TEXT TO lcSQL TEXTMERGE NOSHOW 
		SELECT TOP 1 <<tcField>> as RecFound FROM <<tcTable>> where <<tcField>> = <<tcValue>> order BY <<tcField>>
	ENDTEXT 
	losql.execute(lcsql,lcCursor)
	IF !empty(losql.cErrorMsg)
		MESSAGEBOX("Error in SQL Seek Method." + CHR(13) + ;
				   "Error: " + loSQL.cErrorMsg,16,"SQL Seek Error")
		RETURN .f.
	ENDIF
	
	IF USED(lcCursor)
		SELECT (lcCursor)
		LOCATE 
		lnCount = RECCOUNT(lcCursor)
		USE IN SELECT(lcCursor)
		RETURN IIF(lnCount>0,.t.,.f.)
	ELSE
		RETURN .f.
	ENDIF 
		
ENDFUNC

My question:
How do you normally handle the equivalent of seek() in vfp, but, now in sql?
is this a bad way of "seeking"? what is the file table was really large now?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[craigber]

I wouldn't do a SEEK at all. It smells like a bad practice. One of the purposes of server-based databases is to minimize network traffic and it increases it. Just query the data for what you may need. Then test in your code for zero records.

Here's another tip. Remove all that coding checking for parameters. It's not needed. Use ASSERT instead.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

Agreed, craig, what I already said is:

More important though, you should perhaps change xbase ways to queries overall and not solve the atomic problem of emulating a SEEK, but replace xbase loops with a single query, for example.

And in regard to solving the overall need for SEEK as SQL equivalent: I mostly don't need it, I do a query to give me a result, and if that result is empty, I know a key does not exist, for example.

I never used such an equivalent to SEEK in all my developer life of (re)doing VFP Applications with sql server backend. The need for SQLSeek() is not there, I assume it comes from too atomic conversion of code 1:1, line by line.

Bye, Olaf.

 

[Mike Lewis]

Yes, we've all already made those same points, Craig. (But they're always worth repeating.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

I appreciate the great feedback.

I will consider

Ali Koumaiha
TeknoSoft Inc.
Michigan