Parameters count 2

[WeirdScience]

All,

I hope somebody can help me.

I've created a procedure in VFP8 which needs (really needs) 90 parameters.

In this procedure I call a user-defined SQL Server sp which inserts a record in a SQL-table.

Is there any way to go around the 26 parameters max for VFP procedures?

--------------------------
Foxpro.. Why-oh-why?

 

[Mike Lewis]

Yes, there are several ways of achieving this.

One option is to pass a single parameter, this being an object with 90 custom properties. Create a custom class; add properties that match your SQL Server field names. In your calling code, populate the properties with the required field values. Then pass the entire object as the parameter.

Another option: Pass an array with 90 rows.

Yet another: Create a cursor with 90 fields. Select the cursor in the calling program. No need to pass any parameters.

Going further, if the purpose of the procedure is to pass values from a VFP table to a SQL Server table, you could write a generic procedure which takes any existing VFP table or cursor (whose structure matches that of the SQL Server table), and use the field names and values to programmatically construct a SQL command, which is then sent to the server.

No doubt other folk here will suggest more possibilities.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[WeirdScience]

Hi Mike,

Thx for the quick response.

It are single values so no VFP table to SQL. So the last option won't work.

I'm rewriting the Procedure to accept Public variables which I release after the Procedure.

It is kinda the same as the array/cursor, but a different approach.

Any other possibilities are appreciated anyway!

Rgds,
Guido

--------------------------
Foxpro.. Why-oh-why?

 

[cbasoz]

The simple solution is to pass a single parameter what is known as "parameter object". You simply create an 'empty' object and add properties to it. ie:

local loParameter
loParameter = createobject('Empty')
addproperty(loParameter, 'ID', myGUID())
addproperty(loParameter, 'CharValue', 'Char')
addproperty(loParameter, 'StartDate', Datetime())
addproperty(loParameter, 'EndDate', Datetime()+86400*10)

MyProcedure(loParameter)

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Receiving procedure would use the paramneters either by directly name or could loop using amembers(). ie:

Procedure MyProcedure
lparameter toParameters
text to lcSQL noshow
execute MySP 
 ?toParameters.id,
 ?toParameters.CharValue,
 ?toParameters.StartDate,
 ?toParameters.EndDate
endtext
*...
SQLExec(lnHandle, m.lcSQL)

PS: VFP8 like VFP9 have CursorAdapter support. CA have support for using SPs for insert/update/delete/selectCmd.

Cetin Basoz
MS Foxpro MVP, MCP

 

[WeirdScience]

I've now used:
ADOcmd = CREATEOBJECT("ADODB.Command")
ADOParam = CREATEOBJECT("ADODB.Parameter")

This works like a charm. And the advantage is the we use almost the same code in an other VB6 app.

I'm going to remember your solution, but for the moment it works with my Public variables, which is not the neatest solution.

--------------------------
Foxpro.. Why-oh-why?

 

[cbasoz]

ADODB
That is actually what comes down to CursorAdapter. You are using it against SQL server and that is OK. If you ever need to use against VFP tables be warned that VFP has a bug that it cannot process the time part of a datetime parameter.





Cetin Basoz
MS Foxpro MVP, MCP

 

[WeirdScience]

We've already split datetime into 2 fields.. Date field and Time field, but we are starting a migration which implements that all vfp tables will be migrated into SQL tables.
The only disadvantage remaining is the VFP code.

--------------------------
Foxpro.. Why-oh-why?

 

[Mike Lewis]

Guido,

Using a parameter object is the best way to go. It would certainly be my first choice in this situation.

Using public variables would be my last choice, for various reasons. In general, it's best to avoid using public variables. It's also good to avoid a "global" solution to a local problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business