Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Exclamation Unanswered: If you can solve this - you're good

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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 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
    Dec 2005
    Posts
    2
    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

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Okay.. Post your code that fails.
    Does the Stored Procedure work from the PCC (without calling it from another Stored Procedure)?
    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.

Posting Permissions

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