Returning a shaped ADO.Recordset using Stored Procs

Returning a shaped ADO.Recordset using Stored Procs

Post by Gordon Staine » Fri, 02 Jun 2000 04:00:00



Hi,

I am trying to return a shaped recordset from SQL7 and I hope to use stored
procs.

I know that you cant use a stored proc to generate the shaped recordset, but
I was hoping to use something like this:

    strSQL = ""
    strSQL = strSQL & "SHAPE {usp_PointsAllocRuleGetById ?} "
    strSQL = strSQL & "AS rsPointsAllocRule "
    strSQL = strSQL & "APPEND ({usp_PointsAllocRuleProductsGetById ?} "
    strSQL = strSQL & "AS rsPointsAllocRuleProduct "
    strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

as the commandtext.

It seems to work if the I only use one stored proc asn the parent, and
dynamic sql as the rest. eg:

'    strSQL = ""
'    strSQL = strSQL & "SHAPE {EXEC usp_PointsAllocRuleGetById ?} "
'    strSQL = strSQL & "AS rsPointsAllocRule "
'    strSQL = strSQL & "APPEND ({SELECT  PointsAllocRuleId , ProductCode
FROM PointsAllocRuleProduct WHERE PARP.PointsAllocRuleId = ?} "
'    strSQL = strSQL & "AS rsPointsAllocRuleProduct "
'    strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

If anyone has any ideas on this I'd much appriate it.

Cheers

Gordon Staines

 
 
 

Returning a shaped ADO.Recordset using Stored Procs

Post by V.Kulla [393431 » Sun, 04 Jun 2000 04:00:00


I think it's not possible because Shaping Provider doesn't support that.

[ufo], 393431


Quote:> Hi,

> I am trying to return a shaped recordset from SQL7 and I hope to use
stored
> procs.

> I know that you cant use a stored proc to generate the shaped recordset,
but
> I was hoping to use something like this:

>     strSQL = ""
>     strSQL = strSQL & "SHAPE {usp_PointsAllocRuleGetById ?} "
>     strSQL = strSQL & "AS rsPointsAllocRule "
>     strSQL = strSQL & "APPEND ({usp_PointsAllocRuleProductsGetById ?} "
>     strSQL = strSQL & "AS rsPointsAllocRuleProduct "
>     strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

> as the commandtext.

> It seems to work if the I only use one stored proc asn the parent, and
> dynamic sql as the rest. eg:

> '    strSQL = ""
> '    strSQL = strSQL & "SHAPE {EXEC usp_PointsAllocRuleGetById ?} "
> '    strSQL = strSQL & "AS rsPointsAllocRule "
> '    strSQL = strSQL & "APPEND ({SELECT  PointsAllocRuleId , ProductCode
> FROM PointsAllocRuleProduct WHERE PARP.PointsAllocRuleId = ?} "
> '    strSQL = strSQL & "AS rsPointsAllocRuleProduct "
> '    strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

> If anyone has any ideas on this I'd much appriate it.

> Cheers

> Gordon Staines


 
 
 

1. Returning a shaped ADO.Recordset using Stored Procs

Hi,

I am trying to return a shaped recordset from SQL7 and I hope to use stored
procs.

I know that you cant use a stored proc to generate the shaped recordset, but
I was hoping to use something like this:

    strSQL = ""
    strSQL = strSQL & "SHAPE {usp_PointsAllocRuleGetById ?} "
    strSQL = strSQL & "AS rsPointsAllocRule "
    strSQL = strSQL & "APPEND ({usp_PointsAllocRuleProductsGetById ?} "
    strSQL = strSQL & "AS rsPointsAllocRuleProduct "
    strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

as the commandtext.

It seems to work if the I only use one stored proc asn the parent, and
dynamic sql as the rest. eg:

'    strSQL = ""
'    strSQL = strSQL & "SHAPE {EXEC usp_PointsAllocRuleGetById ?} "
'    strSQL = strSQL & "AS rsPointsAllocRule "
'    strSQL = strSQL & "APPEND ({SELECT  PointsAllocRuleId , ProductCode
FROM PointsAllocRuleProduct WHERE PARP.PointsAllocRuleId = ?} "
'    strSQL = strSQL & "AS rsPointsAllocRuleProduct "
'    strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"

If anyone has any ideas on this I'd much appriate it.

Cheers

Gordon Staines

2. Free advanced Web hosting without banner ads

3. return mulitple recordsets using stored procs?

4. Choosing Sybase, Oracle, or Informix?

5. How to retrieve recordsets from Oracle Stored Procs Using ADO

6. Referential integrity and primary key confusion

7. Using Stored Procs w/ Data Shaping

8. Problem using shape recordsets with stored procedure that uses a temp table