VFP Functions in CursorAdapters 1

[stanlyn]

Hi,

The function val() at the end of this statement is failing when used as the SelectCmd in a cursoradapter. How are these things handled, as I do this sort of stuff quite frequently.

select county_pagekey, Page_Type, BOOK_NUM, Page_Prefix, Page_Num,
SubPage_Num, Page_Date, IMAGE_PATH, ocr_text1, ocr_text2, identity_id
from Page
where left(Page.county_pagekey,5)=?gcCounty
and Page.page_type=?gcPageType
and Page.book_num=?gcBookNum
and val(Page.page_num)>=?gnStartPage
order by val(Page.page_num)

Thanks,
Stanley

 

[Olaf Doschke]

You are working in the remote backend and need to use the remote backends sql dialect. T-SQL for MSSQL, and that has no Val() function, it has CONVERT().

Run MS SQL Management Studio (SSMS) and press F1, search T-SQL.

Bye, Olaf.

 

[stanlyn]

Well Olaf, I thought I was working on a local vfp style cursor where all the VFP stuff works and the cursoradapter would be doing all the translations to the SQL back end.

So does this mean that all the sql statements must be pure T-Sql statements as VFP's sql statements isn't compatible? I ask because I've had that statement work against native tables into a cursor before without issue.

Thanks, Stanley

 

[Olaf Doschke]

No, you misunderstand. You can use anything foxpro on the cursor, but for creating the cursor you use query the remote backend, and there is no automatic translation of foxpro internal or even user defined functions towards any remote sql dialect, that's not a job of ODBC.

You can't recycle your SQLs. Unless you have written them in Ansi SQL, the common part of sql any good database should support up to a certain level.

What you can recylce is all code working on results, eg selects you do on cursors can use vfp functions, as you are in the VFP world, you can reuse databse access code using Tableupdate(), Requery() GetFldState(), Oldval(), Curval() and many other functions working on aliases not usable on remote tables.

But there has to be a "first contact" to the remote database, and that is in the foreign sql dialect, no ODBC driver works different, even the other way around, if an access or a .net programmer uses VFP ODBC he has to issue VFP sql dialect, and he can use VFP functions, then, but not .net functions or access functions, how should one ODBC diver encapsulate all the different programming languages? It's just forwarding the query to the remote database engine.

You can also not use vfp functions in remote views, same reasoning. The compatibility is on the cursor side of the adapter, it does not make SQL Server a VFP Engine.

But the good news is you can use all the features of T-SQL including recursive queries, MERGE, INTERSECT as new ways to join tables, etc, etc., you ain more than you lose.

Bye, Olaf.

 

[Olaf Doschke]

There are some efforts to enable reusing VFP queries with T-SQL, eg look here:

http://fox.wikis.com/wc.dll?Wiki~VFPSQL-TSQL-Mapping

http://www.easysql4fox.vallmind.com/help/

http://it.toolbox.com/wiki/index.php/User-Defined_string_Functions_Transact-SQL_MS_SQL_Server

Using native T-SQL functions is gaining better performance, in case of Val() and many other functions transforming one to another data type, eg also Str(), DTOC(), CTOD(), Transform() etc, CONVERT() is one central T-SQL function you can use instead, the help shows a nice matrix of what types can be converted to what other types.

In regard of user defined functions: Forget about it, you can't translate VFP to T-SQL fully, you'll have to rewrite such things in T-SQL stored procs.

Bye, Olaf.

 

[stanlyn]

Olaf, your explanations have been so helpful...

>> and there is no automatic translation of foxpro internal or even user defined functions towards any remote sql dialect, that's not a job of ODBC.

Then, what is the schema doing, as I understand that to be a partial sql->vfp and vfp->sql translator as it passes data back and forth, automatically invoking something similiar to cast.


>> You can't recycle your SQLs

Please explain this, the retrieved cursors or the statements?


I'm slowly getting there and thanks for your patience and explainations, as you have cleared up a lot of confusion. There is a lot more that I'm still confused with, but one at a time...

Thanks,
Stanley

 

[Olaf Doschke]

The schema is just the schema, the list and type of fields, no more, no less.

There is an automatic mapping, but it does not always match what you need, because there can be mismatch depending on precision or length of fields. Eg SQL Server allows char(1000) fields, while VFP's limit is 254, so there is an option to translate all char fields longer thn 254 into memo, having a schema you have more control an that, eg you can specify you want a numeric field in an int or a varchar(max) (truncated) in a C(10).

But the schema in no way is including any functions or expressions or the whole query. Is it? See how that CursorSchema property is filled by the CA builder.

----

With SQLs in "You can't recycle your SQLs" I mean the statements. Only as far, as they are compatible to T-SQL.

You can for example rather query raw data and then process this in scondary queries from cursor into cursor using all the expressions, functions and even user defined functions you like and are used to.

eg you now do SELECT DTOC(datefield) FROM your.dbf, in the future you SELECT datefield FROM sqlservertable into an alias and then can SELECT DTOC(datefield) afterwards or set controlsource = (DTOC(alias.datefield)).

Just an academic, theoretical example, as you can also bind to a datefield and a textbox will still display that date as human readable without DTOC().

Bye, Olaf.

 

[Olaf Doschke]

Just one sample of a bug, where a cursorschema really helps: SQL Server Native Client ODBC driver automatically translates varchar(max) with C(0) fields.

It's astonishing VFP allows such fields, but aside of that you get no data into foxpro this way, of course. You can override that error with a schema usiung memo (M) instead, but you can't do magic with a schema, eg retrieve CTOD(charfield), no expressions allowed, just fieldnames and type, a table schema.

A schema can for example also help you to get unicode fields into vfp varbinary() fields, nvarchar() for example will otherwise be translated into (ANSI) varchar() field, and any ANSI codepage just is a small subset of unicode. Varbinary is not readable in browse, you can't display it in a normal vfp textbox, but you can forward it 1:1 to unicode activex controls, eg display it embedded in html in a webbrowser control.

Bye, Olaf.