CursorAdapters and UDF in FoxPro 9

IvanVC

Hi,

I like to know if is possible to create a Cursoradapter (ADO) with a select CMD using a function. For example if I have a function concat(par1,par2) that returns par1+par2.

I like to use as select command something like

select concat(lastname,firstname) as name from personal

Thanks

Ivan

dni


You may use CursorAdapter Builder to test; it is easy to handle.

IvanVC

Thanks for your answer.

Using the builder interface I can create cursoradapters with functions in the SelectCMD with NATIVE cursors, but I can not do it using ADO cursors. It sound logic because ADO is using external FOXPRO programs or DLLs, I was wondering only if there is a way to intercept or extend those DLLS, but in the same way it sound more complicated.

I think that with NATIVE cursor are good enough.

Thanks

CetinBasoz

It all depends on the provider. If function is a VFP function (either built-in or UDF, SP etc) then VFPOLEDB provider would understand it. Otherwise another provider wouldn't know what that concat() is. IOW if you're using say SQL server provider then you should pass anything in MSSQL's language (T-SQL).

IvanVC

Taking this concat function just as an example. I dont have problems with ADO Cursor adapters with Oracle because this is a native function; for SQL Server I created this function and I use as dbo.concat and works fine, but with FOXPRO it does not work, even if I have this function in a program and defined with SET PROCEDURE TO MY_RUTINE. Again using native cursoradapters FOXPRO can detect this function but with ADO Foxpro apparently this is not possible because the VFPOLEDB provider doesnt recognize this function, I got a message: Microsoft OLEDB Provider for Visual FoxPro: File 'concat.prg' does not exist.

Off course is possible to avoid the functions and after that execute another select including the function because now is FOXPRO native.

I'm trying to do this to put in a more standard way to build my queries, avoiding to have different codes for different providers, until now I can do it for all the providers that I'm supporting except for Foxpro and is sad.

Thanks

Ivan

IvanVC

Problem solved.

I found that using my function within a store procedure in my foxpro database I can build ADO Cursoradapters for Foxpro databases. I think that is not possible for free tables.

If someone has a different ideas any suggestion is welcome.

CetinBasoz

You can do that with VFP too. Check supported commands/functions. Some are only available via stored procedure (but that doesn't mean that you can't do it for free tables - just use a dummy database to serve as an SP repository).

On the other hand, in my opinion, writing the same code for different providers is an overkill. For example SQL server's TSQL is far more superior than SQL supported by VFP. Why would you give away MSSQL's syntax just for writing less code.

IvanVC

Yes the idea to use a dummy database is excellent.

Please could you clarify the idea, what do you mean with "Check supported commnads/functions", where can I do that .

IV

CetinBasoz

VFP help lists all the commands/functions (non)supported vy the OLEDB provider. You can find help on MSDN online too.

http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_foxhelp9/html/30184b27-f6e5-4aa8-ac28-12188d9093f8.asp

IvanVC

All your help and support has been great.

Thank you.

IV