Hi Guys,

I'm stupified. I don't know what's going on here, but here's my
situation. I am just trying to learn how stored procedures work in
Pervasive. I'm doing something very simple - passing in some
parameters, and inserting a record into a table. Then the stored
procedure returns the last used identity, so that I can get the id of
the record inserted. I have the following:

CREATE PROCEDURE InsertBusiness
(
IN :BusinessName VARCHAR(35),
:ContactName VARCHAR(35),
:AddressLine1 VARCHAR(35),
:AddressLine2 VARCHAR(35)
--:CityName VARCHAR(35),
--:ZipCode VARCHAR(10),
--:State CHAR(2),
--:BusPhoneNumber VARCHAR(20),
--:HomePhoneNumber VARCHAR(20),
--:CellPhoneNumber VARCHAR(20),
--:EmailAddress VARCHAR(40)
)
RETURNS (CustomerID INT);
BEGIN
INSERT INTO CustomerIdentity VALUES(0, 'test');
SELECT @@IDENTITY;
END;

The above works great! No problem! However, as soon as I uncomment
CityName to make the stored procedure look like this:

CREATE PROCEDURE InsertBusiness
(
IN :BusinessName VARCHAR(35),
:ContactName VARCHAR(35),
:AddressLine1 VARCHAR(35),
:AddressLine2 VARCHAR(35),
:CityName VARCHAR(35)
--:ZipCode VARCHAR(10),
--:State CHAR(2),
--:BusPhoneNumber VARCHAR(20),
--:HomePhoneNumber VARCHAR(20),
--:CellPhoneNumber VARCHAR(20),
--:EmailAddress VARCHAR(40)
)
RETURNS (CustomerID INT);
BEGIN
INSERT INTO CustomerIdentity VALUES(0, 'test');
SELECT @@IDENTITY;
END;

Now, I get the following error:
A serious error occurred.
ODBC Engine is unable to supply native error code and error
description.

If any one can fix this... you're good!

Okay, I fixed this error by not testing this procedure from another
procedure. However, there is still a problem when I uncomment the
CityName argument.

When I try calling the stored procedure from Pervasive's .NET data
provider, I get the following exception:

"Input string was not in a correct format"

I'm passing a simple string, "Test". However, when I look at the
parameter for the command object it's properties are as follows:
DbType: Int32
Precision: 10
PsSqlType: Integer
Scale: 0
Size: 4

I used CommandBuilder to generate the command and parameters. I was
expecting to see VarChar as the DbType? But then again, the other
parameters used Decimal as the DbType - so I don't know what to think
anymore

Just a guess.

The Parameter-Type-Name (in your case IN) is only written once at the
beginning of your vars. The doc seems to hint that you need to state
IN or OUT for each variable.


CREATE PROCEDURE Enrollstudent(in :Stud_id integer, in :Class_Id
integer, IN :GPA REAL);
BEGIN
INSERT INTO Enrolls VALUES(:Stud_id, :Class_id, :GPA);

END;