Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Red face Unanswered: Error calling a stored procedure

    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?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    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?

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Apr 2011
    Posts
    5

    calling remote procedure

    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?

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •