Using ElevateDB 2.05 Build 11, I have been successfully creating and populating databases using SQL commands from a VB .Net application using an ODBCConnection object via the ElevateDB 2 ODBC Driver. The target database, which is specified in the DSN connection string, is on a 'remote' session at 127.0.0.1

Using this method i have successfully created stored procedures and i can see them associated with the correct database with ElevateDB manager.

However, i have been unable to successfully call the stored procedures using the same ODBC method.

E.g. issuing "CALL MyStoredProc();"

i get :

ERROR [HY000] [Elevate Software][ElevateDB] ElevateDB Error #401 The routine MyStoredProc(); does not exist in the schema Default

I'm guessing here, but it seems as though, given a normal SQL query, Elevate / ODBC knows which database / session to work with, but given a CALL <SProc> command it doesn't know which DB to work with and so looks at the Default session.

I have tried dropping the trailing semi-colon, empty braces, the leading CALL and their permutations. The procedure definately exists in the DB specified by the connection string, and requires no arguments. What does it mean by 'schema Default', is it looking at the Default session, (that doesn't have the Stored Proc) ?


Also when i try to invoke "CALL MyStoredProc();" from an EDB manager 'new Statement' tab. I get

'There are no statements to execute.'.

I have to wrap it inside a script to get it to execute, i.e. this works from a 'New Script' tab
SCRIPT
BEGIN
call MyStoredProc();
END

Thanks again in advance for any clues
Tony Wood

Tony


I'm not sure if I'm reading you correctly but CALL is part of Tim's SQL/PSM programming language which is why you can use it inside a script and not otherwise.

I use Delphi so it may be different. A number of components come with ElevateDB eg TEDBEngine, TEDBDatabase... One is TEDBStoredProc and this one, in Delphi, is what is used to run a stored procedure. I'd guess you have something similar.

Finally

ERROR [HY000] [Elevate Software][ElevateDB] ElevateDB Error #401 The routine MyStoredProc(); does not exist in the schema Default

That refers to the session that's currently in use (I think) and its telling you that it can't find MyStoredProc in the (again I think) current database.


Roy Lambert [Team Elevate]

Hi Roy, thanks for your quick response

>>  CALL is part of Tim's SQL/PSM programming language which is why you can use it inside a script and not otherwise

I was assuming CALL was standard SQL. Please excuse my ignorance, but what is the SQL syntax for invoking a stored procedure ? (Presumably there must be valid SQL syntax for this else there is no point in having 'CREATE PROCEDURE' as a standard SQL query)

>> One is TEDBStoredProc and this one, in Delphi, is what is used to run a stored procedure. I'd guess you have something similar.

Not that i'm aware of...

cheers from a very wet Melbourne Oz,
Tony Wood

Hi Tony,

Did you set the OdbcCommand type to be "StoredProcedure"?

Something like this in C#

using (OdbcCommand command = OdbcConnection.CreateCommand())
{
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "MyStoredProc";
}

Not too sure about the CommandText as I do not use Stored Procedures
myself, but this should be about right.

Chris Holland
[Team Elevate]

On 29/09/2011 07:54, TonyWood wrote:
> Using ElevateDB 2.05 Build 11, I have been successfully creating and populating databases using SQL commands from a VB .Net application using an ODBCConnection object via the ElevateDB 2 ODBC Driver. The target database, which is specified in the DSN connection string, is on a 'remote' session at 127.0.0.1
>
> Using this method i have successfully created stored procedures and i can see them associated with the correct database with ElevateDB manager.
>
> However, i have been unable to successfully call the stored procedures using the same ODBC method.
>
> E.g. issuing "CALL MyStoredProc();"
>
> i get :
>
> ERROR [HY000] [Elevate Software][ElevateDB] ElevateDB Error #401 The routine MyStoredProc(); does not exist in the schema Default
>
> I'm guessing here, but it seems as though, given a normal SQL query, Elevate / ODBC knows which database / session to work with, but given a CALL<SProc>  command it doesn't know which DB to work with and so looks at the Default session.
>
> I have tried dropping the trailing semi-colon, empty braces, the leading CALL and their permutations. The procedure definately exists in the DB specified by the connection string, and requires no arguments. What does it mean by 'schema Default', is it looking at the Default session, (that doesn't have the Stored Proc) ?
>
>
> Also when i try to invoke "CALL MyStoredProc();" from an EDB manager 'new Statement' tab. I get
>
> 'There are no statements to execute.'.
>
>   I have to wrap it inside a script to get it to execute, i.e. this works from a 'New Script' tab
> SCRIPT
> BEGIN
> call MyStoredProc();
> END
>
> Thanks again in advance for any clues
> Tony Wood
>

--
Chris Holland
[Team Elevate]

Hi Chris

I tried setting OdbcCommand to "StoredProcedure", it didn't seem to make much difference.

I've also now set my Default session to point to 127.0.0.1 server (same as the code uses), still no good

Here's my code (cleaned up so as to just highlight the problem)  :

       Dim sConnString As String
       sConnString = "DSN=DB_Name;Database=DB_Name;Server=127.0.0.1;Uid=Administrator;Pwd=XXXXXXX"

       Dim myConn As New OdbcConnection(sConnString)
       Dim sqlCommand As String = "{ CALL MyStoredProc() }"
       
       Dim command As New OdbcCommand(sqlCommand)
       command.CommandType = CommandType.StoredProcedure
       
       command.Connection = myConn
       myConn.Open()
       command.ExecuteNonQuery()
       command.Connection.Close()

(very similar code works fine with 'normal' SQL)

The error is now :

ERROR [HY000] [Elevate Software][ElevateDB] ElevateDB Error #401 The routine CALL MyStoredProc() does not exist in the schema Default

Is it just that the ElevateDB ODBC driver doesn't support Stored Proc calls ?

I saw a comment somewhere on MSDN that OdbcCommand requires that you supply the full ODBC CALL syntax when calling a stored procedure. Does this mean having to use the leading and trailing curly braces ?

ta very much for your help
Tony Wood

Tony,

<< However, i have been unable to successfully call the stored procedures
using the same ODBC method. >>

With an ODBCConnection, just set the CommandType to StoredProcedure and then
set the CommandText to the name of the stored procedure.  You don't need the
CALL portion.

<< Also when i try to invoke "CALL MyStoredProc();" from an EDB manager 'new
Statement' tab. I get  >>

You can only use CALL from within SQL/PSM procedures/functions/scripts.  If
you want to execute a stored procedure in the EDB Manager directly, just use
the Execute Procedure task link for the actual procedure object in the
left-hand treeview (the option will be available once you click on the
procedure object).

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim
thanks for helping

>> With an ODBCConnection, just set the CommandType to StoredProcedure and then
>> set the CommandText to the name of the stored procedure.  You don't need the
>> CALL portion.

When i try that, i get :

ERROR[42000][Elevate Software][ElevateDB] ElevateDB Error #700 An Error was found in the statement at line 1 and column 1 (Expected SELECT, INSERT, etc........, DELETE FILE but instead found ARCustomer_procFix)

here's the VB code :

Dim command As New OdbcCommand(SProc_name, oODBCConnection)
command.CommandType = CommandType.StoredProcedure
command.CommandText = SProc_name

System.Console.WriteLine(num_queries_executed.ToString() & _
                                                        " **** CALL " & _DSN & "." & SProc_name)

Dim rowsAffected As Integer = command.ExecuteNonQuery()

where SProc_name is ARCustomer_procFix (it doesn't seem to matter if i add the empty trailing open and close brackets or not)

The stored proc definitely exists in the Database defined by the _DSN, and doesn't require arguments

Tony Wood

I think I finally figured out a working format of the stored procedure call which works for me when called from VB .Net :

you need to set the :
OdbcCommand.CommandType to CommandType.StoredProcedure (as stated above)
and
OdbcCommand.CommandText to {CALL SProc_Name}

i.e. open curly brace followed by CALL, a single space, the Stored Procedure name then close curly brace. Note that the stored procedure in question doesn't take any arguments.

Here's hoping this may help someone else
regards
Tony Wood

Tony,

<< I think I finally figured out a working format of the stored procedure
call which works for me when called from VB .Net : >>

You shouldn't need the curly-braces.  I just tried the same thing in C# and
it works fine with just the CALL <SPName> syntax:

private void button1_Click(object sender, EventArgs e)
       {
           OdbcConnection DataConnection = new
OdbcConnection("DSN=EDBTest");
           DataConnection.Open();

           OdbcCommand DataCommand = new OdbcCommand();
           DataCommand.Connection = DataConnection;
           DataCommand.CommandType = CommandType.StoredProcedure;
           DataCommand.CommandText = "CALL MyProcedure";
           DataCommand.ExecuteNonQuery();
       }

You *do* need the CALL portion for ODBC-called stored procedures, however.

--
Tim Young
Elevate Software
www.elevatesoft.com