Re: how to get functions from stored procedures
- From: "Steve Hale" <shale@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Sep 2005 08:30:03 -0700
You have hit item 2 from my reply yesterday; the provider does not support
restriction on PROCEDURE_TYPE. From C++ OLEDB you can check this support by
calling IDBSchemaRowset::GetSchemas(), which will return a list of supported
schemas and a bitmask of the supported restrictions for each schema. From
ADO you should be able to do the same thing but the actual function calls
will be slightly different. Basically you just have to fetch each row
yourself from unrestricted rowset and examine the PROCEDURE_TYPE column's
value to decide whether it's one you want. In the SQL Server case you may
prefer to execute the SQL statement. Providers are not required to support
all restrictions. :-(
I know this forces you to write non-geneneric code, but it's the best
alternative. Your code needs to look something like:
if (IsSQLServer)
{
Execute SQL statement with appropriate columns specified
}
else
{
if (IsPROCEDURE_TYPERestrictionSupported)
{
Set desired restriction for PROCEDURE_TYPE
}
Set any other desired restrictions
pIDBSchemaRowset->GetRowset()
}
Consume rowset, filtering out undesired PROCEDURE_TYPES if restriction is
not supported.
Steve
"Trainee" <trniac@xxxxxxxxxxxxx> wrote in message
news:O9VDq0YxFHA.3216@xxxxxxxxxxxxxxxxxxxxxxx
> hi i want to use this OLEDBprovider for anything say oracle or sql server
> or
> access but if i specify the value of procedure_type in the restriction
> array
> for Oracle it says parameter incorrect???? and sql server this cannot be
> used so what to do????
>
>
> "Steve Hale" <shale@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:u1hWUnUxFHA.2516@xxxxxxxxxxxxxxxxxxxxxxx
>> You don't say which OLEDB provider you are using here, but in general you
>> should be able to do the following:
>>
>> - If the provider supports restrictions on column PROCEDURE_TYPE, you can
>> restrict on PROCEDURE_TYPE == 3 (
>> DB_PT_FUNCTION) or PROCEDURE_TYPE == 2 (DB_PT_PROCEDURE).
>>
>> - If the provider does not support restrictions on PROCEDURE_TYPE (OLEDB
>> Provider for SQL Server does not), then you can just obtain unrestricted
>> DBSCHEMA_PROCEDURES and only return rows where PROCEDURE_TYPE == 3 back
>> to
>> the consumer.
>>
>> - Alternatively, if using SQL Server, you can execute: select * from
>> sysobjects where xtype = 'FN'
>>
>> OR
>>
>> select * from sysobjects where xtype = 'P'
>>
>> This will also give you the functions or procedures.
>>
>> Steve
>>
>>
>> "Trainee" <trniac@xxxxxxxxxxxxx> wrote in message
>> news:%23dCieTqwFHA.2232@xxxxxxxxxxxxxxxxxxxxxxx
>> > hi
>> > i am using an oledbprovider for retrieving schema information from a
>> > database.
>> >
>> > for that i am using OleDbSchemaGuid.Procedures to get the list of
>> > procedures but i get both functions as well as stored procedures from
> that
>> > method .
>> >
>> > how do i separately get the list of functions and the list of stored
>> > procedures using OleDbSchemaGuid?????
>> >
>> >
>>
>>
>
>
.
- References:
- Re: how to get functions from stored procedures
- From: Steve Hale
- Re: how to get functions from stored procedures
- From: Trainee
- Re: how to get functions from stored procedures
- Prev by Date: Re: Running a console application from a service : 0x80040154
- Next by Date: Re: Export SQL Data into Excel
- Previous by thread: Re: how to get functions from stored procedures
- Next by thread: Re: How to get and set OleDb Connection access mode?
- Index(es):
Relevant Pages
|
