Function call within SQL statement

Function call within SQL statement

Post by Cameron Eckma » Sun, 09 Aug 1998 04:00:00



I asked this before but unfortunately I didn't get back to the newsgroup
soon enough.  I am using VB5 and storing data in a DAO 3.5 database (Access
97).

I would like to do the following query in code:

  UPDATE PlotRecords SET PlotRecords.Accounting =
FirstNumberSeries(PlotRecords.PlotName);

The problem I have is that FirstNumberSeries is a public function defined in
the VB5 code and I get the message:
"Undefined function 'FirstNumberSeries' in expression."

I have tried to put the FirstNumberSeries in a code module in the mdb file
with no luck either.  I would prefer to avoid creating a recordset and
walking through each record (or would that be faster?).  Could someone
please give some me guidance.  Thanks.

Cameron A. Eckman

 
 
 

Function call within SQL statement

Post by Coat » Sun, 09 Aug 1998 04:00:00


Sorry, you cant user user defined functions embeded in SQL Statment in VB
.. (only in Access ...)

--
Alberto Borbolla
Tecnologia en Sistemas
MEXICO

Replace "X" to send mail



> I asked this before but unfortunately I didn't get back to the newsgroup
> soon enough.  I am using VB5 and storing data in a DAO 3.5 database
(Access
> 97).

> I would like to do the following query in code:

>   UPDATE PlotRecords SET PlotRecords.Accounting =
> FirstNumberSeries(PlotRecords.PlotName);

> The problem I have is that FirstNumberSeries is a public function defined
in
> the VB5 code and I get the message:
> "Undefined function 'FirstNumberSeries' in expression."

> I have tried to put the FirstNumberSeries in a code module in the mdb
file
> with no luck either.  I would prefer to avoid creating a recordset and
> walking through each record (or would that be faster?).  Could someone
> please give some me guidance.  Thanks.

> Cameron A. Eckman



 
 
 

Function call within SQL statement

Post by R. Ayott » Tue, 11 Aug 1998 04:00:00


Cameron Eckman wrote ...

>I asked this before but unfortunately I didn't get back to the newsgroup
>soon enough.  I am using VB5 and storing data in a DAO 3.5 database (Access
>97).

>I would like to do the following query in code:

>  UPDATE PlotRecords SET PlotRecords.Accounting =
>FirstNumberSeries(PlotRecords.PlotName);

>The problem I have is that FirstNumberSeries is a public function defined
in
>the VB5 code and I get the message:
>"Undefined function 'FirstNumberSeries' in expression."

>I have tried to put the FirstNumberSeries in a code module in the mdb file
>with no luck either.  I would prefer to avoid creating a recordset and
>walking through each record (or would that be faster?).  Could someone
>please give some me guidance.  Thanks.

>Cameron A. Eckman


You can't have a function embedded in your sql statement.
It has to do with the fact that the Jet Engine evaluates
SQL statements and your user-defined function is not
an SQL statement.

However, since all Jet is asking for is an SQL statement
(a string) why not do the following:

    ...
    dim db as database
    dim strSQL as string

    set db = DBEngine.Workspaces(0).OpenDatabase("Your database", etc...)

    ' Build the SQL statement using concatenation ...

    strSQL = "UPDATE PlotRecords SET PlotRecords.Accounting = '" & _
             FirstNumberSeries(PlotRecords.PlotName) & "';"

    db.Execute strSQL

    ...

This is assuming that your function FirstNumberSeries() returns a string
value.

The public function will ve evaluated by VB and you will be sending
an SQL statement to JET.

Now, let's assume your function returns the following:  "whatever"
your SQL statement (strSQL) would then be:

    UPDATE PlotRecords SET PlotRecords.Accounting = 'whatever';

Hope this helps !

Robert Ayotte

Montreal

 
 
 

Function call within SQL statement

Post by Cameron Eckma » Tue, 11 Aug 1998 04:00:00


Thanks, I know I could do what you said.  However, the function needs to be
applied to each record as it finds the first series of numbers in a string;
mostly useful for searching for project numbers in varying directory paths;
better than Val because it goes until it does find a number.  For
"P:\Projects\012345\myplot.plt", returns "012345".

Code in case anyone would like it:

Public Function FirstNumberSeries(ByVal InString As String) As String
Dim blnFoundNumber As Boolean
Dim lngStringLength As Long, lChar As Long
Dim strTempValue As String, strChar As String

   strTempValue = ""
   lngStringLength = Len(InString)

   For lChar = 1 To lngStringLength
      strChar = Mid(InString, lChar, 1)

      If (Asc(strChar) >= 48) And (Asc(strChar) <= 57) Then
         blnFoundNumber = True
         strTempValue = strTempValue & strChar
      ElseIf (blnFoundNumber) Then
         If (strChar = " ") Or (strChar = "_") Or (strChar = "-") Then
            strTempValue = strTempValue & strChar
         Else
            Exit For
         End If
      End If
   Next lChar

   FirstNumberSeries = strTempValue
End Function


>>FirstNumberSeries(PlotRecords.PlotName);
>    strSQL = "UPDATE PlotRecords SET PlotRecords.Accounting = '" & _
>             FirstNumberSeries(PlotRecords.PlotName) & "';"

 
 
 

Function call within SQL statement

Post by Jaime Die » Thu, 13 Aug 1998 04:00:00


I havent tryed this but I think you can include the FirstNumberSeries
function in the MDB database and your query should work fine. Try it out.

Hope it works.
Jaime



> I asked this before but unfortunately I didn't get back to the newsgroup
> soon enough.  I am using VB5 and storing data in a DAO 3.5 database
(Access
> 97).

> I would like to do the following query in code:

>   UPDATE PlotRecords SET PlotRecords.Accounting =
> FirstNumberSeries(PlotRecords.PlotName);

> The problem I have is that FirstNumberSeries is a public function defined
in
> the VB5 code and I get the message:
> "Undefined function 'FirstNumberSeries' in expression."

> I have tried to put the FirstNumberSeries in a code module in the mdb
file
> with no luck either.  I would prefer to avoid creating a recordset and
> walking through each record (or would that be faster?).  Could someone
> please give some me guidance.  Thanks.

> Cameron A. Eckman


 
 
 

Function call within SQL statement

Post by Jim Fergus » Thu, 13 Aug 1998 04:00:00


Quote:>I havent tryed this but I think you can include the FirstNumberSeries
>function in the MDB database and your query should work fine.

Not from within VB. Only from within Access.

-- Jim Ferguson, FMS
   http://www.fmsinc.com

 
 
 

1. Calling a Function within an MS SQL Statement

I have a question about calling a UDF stored in a COM Object within an
SQL Statement.  Example would be

"Update [table] Set Points = " & GetUserPoints( param1, param2 ) & "
WHERE ID=9"

is this possible? if so, can you please give me some insite on this??
thanks alot!

JR

2. FoxPro 2.5 - LOCK()-ing with PC/TCP

3. Calling function within SQL statement

4. JDBC performance problems

5. Internal Function Call within a SELECT statement

6. ???.plan_table

7. Function calls within a function

8. Help DataReport

9. Function call from within SQL query

10. Calling a Stored Procedure within an SQL select statement

11. Calling a function from within an SQL string ?

12. Calling functions from within an SQL string ?

13. Call C program from within a PL/SQL function