Error calling a stored procedure

arnib · #1 (**permalink**) 04-27-11, 08:35

I'm having a problem calling a stored procedure in Pervasive, the procedure exists and should only return one integer value:

HTML Code:

CREATE PROCEDURE  NextRecID (out :outVal INTEGER)
AS BEGIN
  START TRANSACTION;
  SELECT max(RecId) INTO :outVal FROM RecId; 
  set :outVal = :outVal + 1;
  update RecId set RecId = :outVal;
  COMMIT WORK;
END;

Then in C# I have a function to call the procedure and return the output value:

string s = "";
PsqlConnection con = new PsqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["PervasiveDB"].ConnectionString;
con.Open();
PsqlCommand command = new PsqlCommand(p, con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("outval", PsqlDbType.Integer, 0, "outval");
command.Parameters[0].Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
s = command.Parameters[0].Value.ToString();
command.Dispose();
con.Close();
con.Dispose();
return s;

The Connection String is defined in web.config and works in other cases:

<add name="PervasiveDB" connectionString="Server Name=pluto;Database Name=bakvordur;" providerName="Pervasive.Data.SqlClient" />

However this code failes then executing the command.ExecuteNonQuery(); line.

The error message is:

Pervasive.Data.SqlClient.Lna.k: [LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid procedure name.

Any clues on why this does not work?

mirtheil · #2 (**permalink**) 04-27-11, 09:10

You never specify the Stored Procedure name in your code. You need something like:

Code:

command.CommandText = "NextRecID";

One more suggestion, if you can change the database, if you change the RecId data type to Identity, you wouldn't need to increment the value manually. It would increment automatically when a record is inserted.

arnib · #3 (**permalink**) 04-28-11, 07:23

Hi and thanks for the reply

Actually I was pasting this from my function and the p variable contains the name of the procedure.
However I found a possible explanation for the error.
The database that I was connecting to is located on another machine and I do not have a ODBC connection to that database on my machine. The error indicated that it was ODBC related and when I switched to a database on my machine the function started to return values as normal. However I am a little puzzled by this. All other queries to the remote database worked normally and my connection string uses the Pervasive.Data.SqlClient.dll and I did not think that ODBC was being used. Maybe someone can shred a light on this issue?

mirtheil · #4 (**permalink**) 04-28-11, 07:56

Does the Stored Procedure exist in the remote database? The error you are getting indicates a problem with the stored procedure. Can you call the stored procedure from the PCC when connected to the remote database?
THe Pervasive.Data.SqlClient connects to the same engine on the server as the ODBC driver so any error returned from the engine might have "ODBC" in it.

arnib · #5 (**permalink**) 04-29-11, 04:51

Hi and thanks for the reply

Yes the procedure exists in the database. I do not think that I can call the procedure from PCC because it only returns an output value and I don't know how to call it - and retrieve the value from PCC.
The whole procedure is shown at the top of this thread. However this is not a big issue for me anymore. It is interesting to know that the Pervasive.dll connects to the same engine as ODBC. Do you know if it selects the 32 bit or 64 bit engine by default on 64 bit machines?

mirtheil · #6 (**permalink**) 04-29-11, 08:53

I missed that about the stored procedure. You can call a procedure with an output value by creating a second procedure that calls the first one and displays the output value. Something like:

Code:

CREATE Procedure GetOutValue()
AS BEGIN
  DECLARE :out INTEGER;
  EXEC NextRecID(:out);
  PRINT :out;
END;

The ADO.NET provider (Pervasive.Data.SqlClient.dll) is fully managed and will use the framework of the operating system. That means it will be a 64 bit app on a 64 bit OS and a 32bit app on a 32 bit OS if the application is compiled as AnyCPU. THe provider will connect to either the 32 bit or 64 bit remote engine.

Internet Services

Web Development

Digital Marketing

Consumer Tech