Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Accessing SQL 2012 New Offset/Fetch Feature from VFP9

Status
Not open for further replies.

stanlyn

Programmer
Sep 3, 2003
945
US
Hi,

I'm having issues with the new SQL Server 2012 odbc driver and VFP9sp(latest). I'm trying to use the new offset and fetch feature that sql 2012 has. So far, all versions of SQL odbc drivers are failing when using... It does get a valid handle, however the SQLexec command is what is failing with a value of 0...

lcDSNLess="driver={SQL Server Native Client 11.0};server=Dazz;uid=sa;pwd=111111;DATABASE=DRI_131"
gnConnHandle=Sqlstringconnect(m.lcDSNLess)

?gnConnHandle
lcSql = 'select top 1000 * from PartyName ' + ;
'order by party_name, page_type, party_type offset 0 rows fetch next 20 rows only'

lnSuccess = SQLExec(gnConnHandle, lcSql, 'MyCursor')

If lnSuccess > 0
Select 'MyCursor'
Browse

SQLDisconnect(gnConnHandle)
Else
Messagebox('Sql Error')
Endif

Thanks, Stanley
 
Okay, didn't thaought about the options "Remote Data" tab. But that explains it of course.

Towards next points: Internal error handling of VFP7 wasn't better, ON ERROR and all the ERROR(), LINENO(), MESSAGE() functions haven't changed in VFP8 or 9. If you saw bahavior changes, and I see no influence of any defaults, that could make them behave different. VFP8 introduced TRY...CATCH..ENDTRY handling, but that was just additional, not instead of ON ERROR. AERROR() also was there for a long time and besides ASTACKINFO will give you more info.

Have a look here: [URL unfurl="true"]http://www.stonefield.com/techpap.aspx[/url]
Among many others, there's also a white paper on error handling. It's from 1999, but it has a good error handling strategy for an ON ERROR handler in it, which also uses AERROR.

Well, and about the errors of SQL Server: The errors occur in ODBC or within SQL SERVER and thus: How should they be able to trigger an exception event in the vfp process? You know when you do SQLCONNECT, SQLEXEC etc. and you get return value indicating you should do further investigation. The same goes for many Win API function, which point to a problem by returning 0 or -1 and the error info is then retrievable by the LastError() function.

What do you do, if any function returns a variety of values you need to react to in branching into several codes to handle, not only if this is about errors? You do a function or better yet a class around this central function or at least have some code snippet template like this:

Code:
lnRetVal =SQLEXEC(gnConnhandle, lcSQL, lcResultalias)
Do Case
   Case Retval = 1
      * OK
   Case Retval > 1
      SQLMoreResults(...)
   Case Retval = 0
      * Ok, but need to call SQLEXEC again later (to be programmed somewhere else in the event chain or in a timer or or or)
   Case Retval < 0
      lnErrorCount = AERROR(laError)
      * call some error logging/display, AERROR could also be done in there
      * .Logerror
      * .Displayerror
      If file("debug.txt") && conditional, have a debug.txt in your project home folder to flag you want to debug, remove in released version
         set step on
      Endif
   Otherwise
      * There is no otherwise here. Or the runtime has gone completely corrupted or someone has invented a new kind of number
EndCase
You see in this case it would rather be something I'd not copy&paste to every place I want to use SQLExec(), so I'd create a class SQLPassthrough, also offering other SQLPT functionalilty or at least a user defined function MySQLExec(). And you can do even more, I can't start writing a whole framework for you here. Indeed the Stonefield whitepaper has a sample section that not only has an error handling sample, but a full blown very small micro application framework.

Again, the return value reference is very good in the VFP functions reference section of the help, you find the returned type and meaning of result values. Also Notes sections in help often explain important info on special return values. You can get lazy at some points and just program the normal case, but it pays to always think of all the other cases, too. To learn a function always is just the beginning, The help topic normally has a See Also section, which points to related functions, and AERROR is mentioned in many SQL PAssthrough functions. Unfortunately not in SQLExec. The error handling sample in the vfp help also doesn't mention AERROR. There are reasons to read hentzenwerke books...

OK, enough "ranting". I hope you donn'ttake this just disabusing, but informative. It's meant informative. But it would also be worth starting a new thread about error handling best practices. This is going beyond the initial question about the new sql2012 feature into very basics of programming overall.

Bye, Olaf.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top