Pervasive SQL System Stored Procedure from ADO.NET error

Question

I'm just trying to return a list of columns and their attributes through a system stored procedure. What documentation I have seems to say the below code should work, but I get "Pervasive.Data.SqlClient.Lna.k: [LNA][Pervasive][ODBC Engine Interface]Invalid or missing argument." on the execute. This is PSQL v11, .NET 4.5.

using (PsqlConnection conn = new PsqlConnection(cs))
    {
        PsqlCommand locationCmd = new PsqlCommand();
        PsqlParameter tableParam = new PsqlParameter();
        PsqlParameter returnParam = new PsqlParameter();
        returnParam.Direction = ParameterDirection.ReturnValue;
        locationCmd.CommandText = "psp_columns";
        locationCmd.Connection = conn;
        locationCmd.CommandType = CommandType.StoredProcedure;
        locationCmd.Parameters.Add(tableParam).Value = table;
        locationCmd.Parameters.Add(returnParam);
        conn.Open();
        locationCmd.ExecuteNonQuery();

    }

Are you using a parameter to specify the table name? That's not supported in any database I know, however I really don't know pervasive so... what is the code of the stored procedure? — Steve, Aug 09 '13 at 20:02
Yes, I am parameterizing the table name. I know that this is normally not supported and you have to plug in a string, but the documentation seems to say that this works as params in a stored procedure are not marked. I did try "psql_columns " + table and that did not work either. This is a Pervasive system stored procedure, I do not have a way to access the actual code. — Thomas O., Aug 09 '13 at 20:16
Usually there is something like GetSchema on the Connection object that could return the information that you need. Doing some research now. — Steve, Aug 09 '13 at 20:36

score 0 · Answer 1 · answered Aug 09 '13 at 20:30

0

I would think the problem is this line:

  locationCmd.Parameters.Add(tableParam).Value = table;

You should set the value before adding the parameter, not afterwards.

  tableParam.Value = table;
  locationCmd.Parameters.Add(tableParam);

I don't know about Psql but for MSSQL normally you also need to define the parameter name as its found in the stored procedure, or at least that's what I do.

  SqlParameter param = new SqlParameter("@tableParam", value);

answered Aug 09 '13 at 20:30

developerwjk

8,619
2
17
33

Thanks, but it is ok to set the param value at the time you add it, I did try it explicitly before and same problem though. And Pervasive does not support named parameters, just the '?' placeholder. Makes inserts with 150 fields super fun. – Thomas O. Aug 09 '13 at 20:39

Steve · Answer 2 · 2013-08-09T20:50:17.570

You should try to get the information of the table SCHEMA using the provided GetSchema method from the Psqlconnection. I have searched a bit on their support site and it seems that this method is supported although I haven't find a direct example using the Tables collection.

This is just an example adapted from a test on mine on SqlServer, I don't have Pervasive install, but you could try if the results are the same

using(PsqlConnection cn = new PsqlConnection("your connection string here"))
{
     cn.Open(); 
     string[] selection = new string[] { null, null, table }; 
     DataTable tbl = cn.GetSchema("Columns", selection); 
     foreach (DataRow row in tbl.Rows)
     { 
         Console.WriteLine(row["COLUMN_NAME"].ToString() + " " + 
                           row["IS_NULLABLE"].ToString() + " " +
                           row["DATA_TYPE"].ToString() 
         );
     } 
}

Thanks, I will try that. I have had schema troubles before with ADO since none of our tables have primary keys. It's tough to work with but I have no control over this, it's just what we're given. — Thomas O., Aug 09 '13 at 20:47

score 0 · Answer 3 · answered Aug 09 '13 at 20:49

The psp_Columns system stored procedure is defined as call psp_columns(['database_qualifier'],'table_name', ['column_name']). I know that it says the database qualifier is optional, but I think it's required. You could try passing an empty string for the qualifier. Something like:

using (PsqlConnection conn = new PsqlConnection(cs))
    {
        PsqlCommand locationCmd = new PsqlCommand();
        PsqlParameter dbParam = new PsqlParameter();
        PsqlParameter tableParam = new PsqlParameter();
        PsqlParameter returnParam = new PsqlParameter();
        returnParam.Direction = ParameterDirection.ReturnValue;
        locationCmd.CommandText = "psp_columns";
        locationCmd.Connection = conn;
        locationCmd.CommandType = CommandType.StoredProcedure;

        locationCmd.Parameters.Add(dbParam).Value = ""; //might need two single quotes ('')
        locationCmd.Parameters.Add(tableParam).Value = table;
        locationCmd.Parameters.Add(returnParam);
        conn.Open();
        locationCmd.ExecuteNonQuery();

    }

Just tried it a few ways, no luck. Interestingly, if I change the commandtext to: locationCmd.CommandText = @"psp_columns(,'RWS',)";, I get: Syntax Error: call psp_columns(,'RWS',)<< ??? >>(?,?) — Thomas O., Aug 09 '13 at 21:14
Yeah, I tried a few different things, I don't thing you can call the system procedures using parameters. — mirtheil, Aug 09 '13 at 22:19

score 0 · Answer 4 · answered Aug 29 '13 at 14:16

i was trying to figure this out as well, but with the tables procedure. even though the database and table names are optional, you still have to provide values. for optional parameters, pass in DBNull.Value

this worked for me:

PsqlCommand cm = new PsqlCommand();
cm.CommandText = "psp_tables";
cm.CommandType = CommandType.StoredProcedure;
cm.Connection = new PsqlConnection();
cm.Connection.ConnectionString = <your connection string>;
cm.Parameters.Add(":database_qualifier", DBNull.Value);
cm.Parameters.Add(":table_name", DBNull.Value);
cm.Parameters.Add(":table_type", "User table");

Pervasive SQL System Stored Procedure from ADO.NET error

4 Answers4