If you can solve this - you're good

mweichert · #1 (**permalink**) 12-30-05, 11:04

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),
--

tate 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),
--

tate 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!

I appreciate any of your help guys.
Mike

mirtheil · #2 (**permalink**) 12-30-05, 11:33

What versoin of PSQL are you using?
When I use PSQL V8.7 with the following statements, it works:

Code:

create table CustomerIdentity (ID identity, 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)
	)
	#
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 (ID, BusinessName, ContactName, AddressLine1, AddressLine2, CityName) VALUES(0, :BusinessName, :ContactName, :AddressLine1, :AddressLine2, :CityName);
SELECT @@IDENTITY;
END;#
call InsertBusiness('MyBusiness','Me','1234 Any Street', '','MyCity')#
select * from customeridentity

mweichert · #3 (**permalink**) 12-30-05, 13:47

PSQL 8.0

Our SQL statements are the same; however, I was testing the stored procedure from another stored procedure...which gave the error. However, I am stilll having a problem with the CityName field - but it's when using OleDB or ADO.NET. I keep getting an error saying that the string isn't in the correct format.

Quote:

Originally Posted by mirtheil

What versoin of PSQL are you using?
When I use PSQL V8.7 with the following statements, it works:

Code:

create table CustomerIdentity (ID identity, 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)
	)
	#
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 (ID, BusinessName, ContactName, AddressLine1, AddressLine2, CityName) VALUES(0, :BusinessName, :ContactName, :AddressLine1, :AddressLine2, :CityName);
SELECT @@IDENTITY;
END;#
call InsertBusiness('MyBusiness','Me','1234 Any Street', '','MyCity')#
select * from customeridentity

mirtheil · #4 (**permalink**) 12-30-05, 14:29

Okay.. Post your code that fails.
Does the Stored Procedure work from the PCC (without calling it from another Stored Procedure)?

Internet Services

Web Development

Digital Marketing

Consumer Tech