INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Possible commit issue ?

Possible commit issue ?

(OP)
Hi all, I am new to MYSQL but not to databases, I am used to SQL Server..
My problem is I have written an SP that inserts a new record from C#. The SP does not return the correct ID column? It returns an ID from the previously run SP. The previously run SP inserts a user and that ID is then picked up in this SP. The value returned is the UserID from LAST_INSERT_ID()? An thoughts?

Thanks in advance. The culprit is below

CODE

DELIMITER $$
CREATE  PROCEDURE AMCompanySave
(
	pCompanyName VARCHAR(50),
	pUserCompanyHash VARCHAR(40),
	pUserHash VARCHAR(40),
	pCompanyNumber VARCHAR(15),
	pVatNo VARCHAR(30)
)
BEGIN
	declare retCompanyID int;
	SELECT @pAMUserID := AMUserID FROM AMUsers WHERE PasswordHash = pUserHash;
	
	INSERT INTO UserCompanies (UserCompanyName, AMUserID, CompanyNameHash, CompanyNumber, VatNo, DateAdded)
		VALUES (pCompanyName, @pAMUserID, pUserCompanyHash, pCompanyNumber , pVatNo, NOW() );

	SET retCompanyID = LAST_INSERT_ID();
	select retCompanyID;

END $$
DELIMITER ; 

Age is a consequence of experience

RE: Possible commit issue ?

Hi

Make retCompanyID an out parameter :

CODE --> MySQL

DELIMITER $$
CREATE  PROCEDURE AMCompanySave
(
	pCompanyName VARCHAR(50),
	pUserCompanyHash VARCHAR(40),
	pUserHash VARCHAR(40),
	pCompanyNumber VARCHAR(15),
	pVatNo VARCHAR(30),
        OUT retCompanyID INT
)
BEGIN
	SELECT @pAMUserID := AMUserID FROM AMUsers WHERE PasswordHash = pUserHash;
	
	INSERT INTO UserCompanies (UserCompanyName, AMUserID, CompanyNameHash, CompanyNumber, VatNo, DateAdded)
		VALUES (pCompanyName, @pAMUserID, pUserCompanyHash, pCompanyNumber , pVatNo, NOW() );

	SET retCompanyID = LAST_INSERT_ID();
END $$
DELIMITER ; 

Feherke.
feherke.ga

RE: Possible commit issue ?

(OP)
Hi and thanks.

I could try that but it would mean changing all my data layer for all inserted records. Is there no way to return the last insert ID? I thought it was the same as scope_identity in sql serveer. I was trying to port an app from sql server but this could put a stop on that...

Thanks again.

Age is a consequence of experience

RE: Possible commit issue ?

Hi

Quote (litton1)

Is there no way to return the last insert ID?
Tricky question, because you can answer both yes and no :
  • Yes, you can find out the last inserted ID. ( Not sure how far you can go with calling procedure from procedure and wrapping any or more of them in transactions, but in the simple case you showed certainly works. )
  • No, you can not use the MSSQL syntax to return a select's result. ( Though I may have missed some features of latest MySQL versions. )
So unless someone confirms that your original MSSQL-like code is supported in MySQL, I suggest to forget it.

The following works. If you use it in different circumstances, then please post it.

CODE --> litton1.sql

create table UserCompanies (
    ID              int primary key auto_increment,
    UserCompanyName varchar(50),
    AMUserID        int,
    CompanyNameHash varchar(40),
    CompanyNumber   varchar(15),
    VatNo           varchar(30),
    DateAdded       timestamp default current_timestamp
);

create table AMUsers (
    AMUserID        int,
    PasswordHash    varchar(40)
);

insert into AMUsers
( AMUserID , PasswordHash ) values
( 1        , 'xxx'        ),
( 2        , 'yyy'        );


DELIMITER $$
CREATE  PROCEDURE AMCompanySave
(
    pCompanyName VARCHAR(50),
    pUserCompanyHash VARCHAR(40),
    pUserHash VARCHAR(40),
    pCompanyNumber VARCHAR(15),
    pVatNo VARCHAR(30),
    OUT retCompanyID INT
)
BEGIN
    SELECT @pAMUserID := AMUserID FROM AMUsers WHERE PasswordHash = pUserHash;

    INSERT INTO UserCompanies (UserCompanyName, AMUserID, CompanyNameHash, CompanyNumber, VatNo, DateAdded)
        VALUES (pCompanyName, @pAMUserID, pUserCompanyHash, pCompanyNumber , pVatNo, NOW() );

    SET retCompanyID = LAST_INSERT_ID();
END $$
DELIMITER ;


call AMCompanySave('The Name', 'The Hash', 'xxx', 'The Number', 'The VAT', @id);

select @id `just inserted a record with this id`;

call AMCompanySave('Other Name', 'Other Hash', 'yyy', 'Other Number', 'Other VAT', @id);

select @id `just inserted a record with this id`;

select * from UserCompanies; 

CODE --> mysql

mysql> \. litton1.sql
Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

+------------------------+
| @pAMUserID := AMUserID |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.03 sec)

+-------------------------------------+
| just inserted a record with this id |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

+------------------------+
| @pAMUserID := AMUserID |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.06 sec)

+-------------------------------------+
| just inserted a record with this id |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
1 row in set (0.00 sec)

+----+-----------------+----------+-----------------+---------------+-----------+---------------------+
| ID | UserCompanyName | AMUserID | CompanyNameHash | CompanyNumber | VatNo     | DateAdded           |
+----+-----------------+----------+-----------------+---------------+-----------+---------------------+
|  1 | The Name        |        1 | The Hash        | The Number    | The VAT   | 2015-02-06 09:38:36 |
|  2 | Other Name      |        2 | Other Hash      | Other Number  | Other VAT | 2015-02-06 09:38:36 |
+----+-----------------+----------+-----------------+---------------+-----------+---------------------+
2 rows in set (0.00 sec) 

Feherke.
feherke.ga

RE: Possible commit issue ?

(OP)
Thanks for that great explanation! Need to think on this one :)

Age is a consequence of experience

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close