calling MSDE functions via ODBC ?

Hello.

How can I call some functions on MSDE when working in .mdb ? Especially
in-line functions which are similar to stored procedures.
How can I use MSDE in-line functions as recordsource for .mdb forms ?
Can I call in-line functions using ADO ? I tried, but it seems that only
stored procedures are allowed (adCmdStoredProc)....

Thanks.

re: calling MSDE functions via ODBC ?

On Apr 17 2005, 02:32 pm, "Zlatko Matiæ" <zlatko.matic1@sb.t-com.hr> wrote
in news:d3ua3r$i4o$1@ss405.t-com.hr:
[color=blue]
> How can I call some functions on MSDE when working in .mdb ? Especially
> in-line functions which are similar to stored procedures.
> How can I use MSDE in-line functions as recordsource for .mdb forms ?
> Can I call in-line functions using ADO ? I tried, but it seems that only
> stored procedures are allowed (adCmdStoredProc)....[/color]

That is correct - you will need to create a wrapper stored procedure for
each function. The only kind of functions that can be called directly
from ADO (with adCmdStoredProc parameter) are scalar UDFs, that do not
return a result set.

You can bind the ADO recordset that you obtain by calling a stored
procedure to a form by using its Recordset property - there are examples on
MS support site. It may take some extra effort to make that recordset
updatable. Post back if you run into any problems with this and need more
information.

By the way, this has nothing to do with ODBC as you indicated in the
subject - you have to use ADO.

--
remove a 9 to reply by email

re: calling MSDE functions via ODBC ?

Dimitri Furman <dfurman@cloud99.net> wrote in
news:Xns963BE46E82A8Edfurmancloud99@127.0.0.1:
[color=blue]
> That is correct - you will need to create a wrapper stored procedure for
> each function. The only kind of functions that can be called directly
> from ADO (with adCmdStoredProc parameter) are scalar UDFs, that do not
> return a result set.[/color]

Could you expand on that point?

---------

Dim r As ADODB.Recordset
Dim m As ADODB.Command
Set m = New ADODB.Command
With m
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdUnknown
.CommandText = "dbo.[2004IncomeTax]()"
Set r = .Execute()
End With
With r
If .BOF Then
Debug.Print "None"
Else
Debug.Print r.GetString(adClipString, , , vbNewLine, "null")
End If
End With

-------

creates a recordset of the records of the table returned by the UDF
2004IncomeTax. I would probably just use Connection.Execute("dbo.
[2004IncomeTax]())" however.

I'm not sure what you are implying with "(with adCmdStoredProc parameter)"
as I've not tried this, simply passing any parameters by enclosing them in
the parentheses after the function name. Surely this would be the common
way to pass parameters to a function.
Of course, TTBOMK function owner's name and the parentheses (regardless of
whether or not there are parameters) are required.

--
Lyle
--

re: calling MSDE functions via ODBC ?

On Apr 18 2005, 03:52 am, Lyle Fairfield <LookItUp@FFDBA.Com> wrote in
news:Xns963C27B56BF4CFFDBA@216.221.81.119:
[color=blue]
> Dimitri Furman <dfurman@cloud99.net> wrote in
> news:Xns963BE46E82A8Edfurmancloud99@127.0.0.1:
>[color=green]
>> That is correct - you will need to create a wrapper stored procedure
>> for each function. The only kind of functions that can be called
>> directly from ADO (with adCmdStoredProc parameter) are scalar UDFs,
>> that do not return a result set.[/color]
>
> Could you expand on that point?
>
> ---------
>
> Dim r As ADODB.Recordset
> Dim m As ADODB.Command
> Set m = New ADODB.Command
> With m
> .ActiveConnection = CurrentProject.Connection
> .CommandType = adCmdUnknown
> .CommandText = "dbo.[2004IncomeTax]()"
> Set r = .Execute()
> End With
> With r
> If .BOF Then
> Debug.Print "None"
> Else
> Debug.Print r.GetString(adClipString, , , vbNewLine, "null")
> End If
> End With
>
> -------
>
> creates a recordset of the records of the table returned by the UDF
> 2004IncomeTax. I would probably just use Connection.Execute("dbo.
> [2004IncomeTax]())" however.
>
> I'm not sure what you are implying with "(with adCmdStoredProc
> parameter)" as I've not tried this, simply passing any parameters by
> enclosing them in the parentheses after the function name. Surely this
> would be the common way to pass parameters to a function.
> Of course, TTBOMK function owner's name and the parentheses
> (regardless of whether or not there are parameters) are required.[/color]

Yes, this does work, thanks for the correction.

I'm not sure I would do it this way though: if you look at a Profiler trace
for this, you'll see that because you use adCmdUnknown, OLEDB has to try it
two times before it gets it right - first it tries

exec dbo.[2004IncomeTax](),

which fails because of incorrect syntax, then it tries

exec dbo.[2004IncomeTax] default,

which also fails because it's not a stored procedure, and only then it
tries

select * from dbo.[2004IncomeTax](),

which does work.

As far as passing parameters to a function, I'm sure you can just create a
string for CommandText. I'd prefer using the Parameters collection to
define them formally, which I believe you cannot do with adCmdUnknown (so
there is also no way to get the return value of the function in this case).

What I meant when I mention scalar UDFs is that you can actually call a UDF
as if it were a stored procedure by specifying adCmdStoredProc for
CommandType, and thus use the Parameters collection, but only if the
function is a scalar one, not a table-valued one.

--
remove a 9 to reply by email

re: calling MSDE functions via ODBC ?

thank you, guys...

Now, if I link tables to PostgreSQL via ODBC, and want to execute some
parameterized query directly on the PostgreSQL server, how can I accomplish
that?
Postgre doesn't have stored procedure, but has functions that can perform
everything that stored procedures can...
I know how to code those functions, but I don't know how to pass parameters
from client (Access) and how to use them as Recordset for forms and
reports....
Any idea?



"Dimitri Furman" <dfurman@cloud99.net> je napisao u poruci interesnoj
grupi:Xns963C5399B8109dfurmancloud99@127.0.0.1...[color=blue]
> On Apr 18 2005, 03:52 am, Lyle Fairfield <LookItUp@FFDBA.Com> wrote in
> news:Xns963C27B56BF4CFFDBA@216.221.81.119:
>[color=green]
>> Dimitri Furman <dfurman@cloud99.net> wrote in
>> news:Xns963BE46E82A8Edfurmancloud99@127.0.0.1:
>>[color=darkred]
>>> That is correct - you will need to create a wrapper stored procedure
>>> for each function. The only kind of functions that can be called
>>> directly from ADO (with adCmdStoredProc parameter) are scalar UDFs,
>>> that do not return a result set.[/color]
>>
>> Could you expand on that point?
>>
>> ---------
>>
>> Dim r As ADODB.Recordset
>> Dim m As ADODB.Command
>> Set m = New ADODB.Command
>> With m
>> .ActiveConnection = CurrentProject.Connection
>> .CommandType = adCmdUnknown
>> .CommandText = "dbo.[2004IncomeTax]()"
>> Set r = .Execute()
>> End With
>> With r
>> If .BOF Then
>> Debug.Print "None"
>> Else
>> Debug.Print r.GetString(adClipString, , , vbNewLine, "null")
>> End If
>> End With
>>
>> -------
>>
>> creates a recordset of the records of the table returned by the UDF
>> 2004IncomeTax. I would probably just use Connection.Execute("dbo.
>> [2004IncomeTax]())" however.
>>
>> I'm not sure what you are implying with "(with adCmdStoredProc
>> parameter)" as I've not tried this, simply passing any parameters by
>> enclosing them in the parentheses after the function name. Surely this
>> would be the common way to pass parameters to a function.
>> Of course, TTBOMK function owner's name and the parentheses
>> (regardless of whether or not there are parameters) are required.[/color]
>
> Yes, this does work, thanks for the correction.
>
> I'm not sure I would do it this way though: if you look at a Profiler
> trace
> for this, you'll see that because you use adCmdUnknown, OLEDB has to try
> it
> two times before it gets it right - first it tries
>
> exec dbo.[2004IncomeTax](),
>
> which fails because of incorrect syntax, then it tries
>
> exec dbo.[2004IncomeTax] default,
>
> which also fails because it's not a stored procedure, and only then it
> tries
>
> select * from dbo.[2004IncomeTax](),
>
> which does work.
>
> As far as passing parameters to a function, I'm sure you can just create a
> string for CommandText. I'd prefer using the Parameters collection to
> define them formally, which I believe you cannot do with adCmdUnknown (so
> there is also no way to get the return value of the function in this
> case).
>
> What I meant when I mention scalar UDFs is that you can actually call a
> UDF
> as if it were a stored procedure by specifying adCmdStoredProc for
> CommandType, and thus use the Parameters collection, but only if the
> function is a scalar one, not a table-valued one.
>
> --
> remove a 9 to reply by email[/color]

re: calling MSDE functions via ODBC ?

On Apr 18 2005, 10:58 am, "Zlatko Matiæ" <zlatko.matic1@sb.t-com.hr>
wrote in news:d40hv3$fml$1@ss405.t-com.hr:
[color=blue]
> Now, if I link tables to PostgreSQL via ODBC, and want to execute some
> parameterized query directly on the PostgreSQL server, how can I
> accomplish that?
> Postgre doesn't have stored procedure, but has functions that can
> perform everything that stored procedures can...
> I know how to code those functions, but I don't know how to pass
> parameters from client (Access) and how to use them as Recordset for
> forms and reports....[/color]

You will probably want to use pass-through queries for this. I never used
PostgreSQL, so cannot give you any specific information. You may want to
search Google groups for more details.

--
remove a 9 to reply by email