By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,747 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

calling MSDE functions via ODBC ?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Apr 17 2005, 02:32 pm, "Zlatko Matić" <zl***********@sb.t-com.hr> wrote
in news:d3**********@ss405.t-com.hr:
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)....


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
Nov 13 '05 #2

P: n/a
Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
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.


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
--
Nov 13 '05 #3

P: n/a
On Apr 18 2005, 03:52 am, Lyle Fairfield <Lo******@FFDBA.Com> wrote in
news:Xn*******************@216.221.81.119:
Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
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.


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.


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
Nov 13 '05 #4

P: n/a
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" <df*****@cloud99.net> je napisao u poruci interesnoj
grupi:Xn****************************@127.0.0.1...
On Apr 18 2005, 03:52 am, Lyle Fairfield <Lo******@FFDBA.Com> wrote in
news:Xn*******************@216.221.81.119:
Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
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.


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.


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

Nov 13 '05 #5

P: n/a
On Apr 18 2005, 10:58 am, "Zlatko Matić" <zl***********@sb.t-com.hr>
wrote in news:d4**********@ss405.t-com.hr:
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....


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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.