Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible commit issue ? 1

Status
Not open for further replies.
litton1

litton1

Technical User
Apr 21, 2005
584
0
0
GB
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
 
Sort by date Sort by votes
Hi

Make retCompanyID an [tt]out[/tt] parameter :
Code: 
[b]DELIMITER[/b] $$
[b]CREATE[/b]  [b]PROCEDURE[/b] AMCompanySave
[teal]([/teal]
	pCompanyName VARCHAR[teal]([/teal][COLOR=#993399]50[/color][teal]),[/teal]
	pUserCompanyHash VARCHAR[teal]([/teal][COLOR=#993399]40[/color][teal]),[/teal]
	pUserHash VARCHAR[teal]([/teal][COLOR=#993399]40[/color][teal]),[/teal]
	pCompanyNumber VARCHAR[teal]([/teal][COLOR=#993399]15[/color][teal]),[/teal]
	pVatNo VARCHAR[teal]([/teal][COLOR=#993399]30[/color][teal]),[/teal]
        [b]OUT[/b] retCompanyID INT
[teal])[/teal]
[b]BEGIN[/b]
	[b]SELECT[/b] @pAMUserID [teal]:=[/teal] AMUserID [b]FROM[/b] AMUsers [b]WHERE[/b] PasswordHash [teal]=[/teal] pUserHash[teal];[/teal]
	
	[b]INSERT[/b] [b]INTO[/b] UserCompanies [teal]([/teal]UserCompanyName[teal],[/teal] AMUserID[teal],[/teal] CompanyNameHash[teal],[/teal] CompanyNumber[teal],[/teal] VatNo[teal],[/teal] DateAdded[teal])[/teal]
		[b]VALUES[/b] [teal]([/teal]pCompanyName[teal],[/teal] @pAMUserID[teal],[/teal] pUserCompanyHash[teal],[/teal] pCompanyNumber [teal],[/teal] pVatNo[teal],[/teal] NOW[teal]()[/teal] [teal]);[/teal]

	[b]SET[/b] retCompanyID [teal]=[/teal] LAST_INSERT_ID[teal]();[/teal]
[b]END[/b] $$
[b]DELIMITER[/b] [teal];[/teal]


Feherke.
feherke.ga
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hi

litton1 said:
Is there no way to return the last insert ID?
Click to expand...
Tricky question, because you can answer both yes and no :
[ul]
[li]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. )[/li]
[li]No, you can not use the MSSQL syntax to return a [tt]select[/tt]'s result. ( Though I may have missed some features of latest MySQL versions. )[/li]
[/ul]
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: 
[b]create[/b] [b]table[/b] UserCompanies [teal]([/teal]
    ID              [maroon]int[/maroon] [b]primary[/b] [b]key[/b] [b]auto_increment[/b][teal],[/teal]
    UserCompanyName [maroon]varchar[/maroon][teal]([/teal][COLOR=#993399]50[/color][teal]),[/teal]
    AMUserID        [maroon]int[/maroon][teal],[/teal]
    CompanyNameHash [maroon]varchar[/maroon][teal]([/teal][COLOR=#993399]40[/color][teal]),[/teal]
    CompanyNumber   [maroon]varchar[/maroon][teal]([/teal][COLOR=#993399]15[/color][teal]),[/teal]
    VatNo           [maroon]varchar[/maroon][teal]([/teal][COLOR=#993399]30[/color][teal]),[/teal]
    DateAdded       [maroon]timestamp[/maroon] [b]default[/b] current_timestamp
[teal]);[/teal]

[b]create[/b] [b]table[/b] AMUsers [teal]([/teal]
    AMUserID        [maroon]int[/maroon][teal],[/teal]
    PasswordHash    [maroon]varchar[/maroon][teal]([/teal][COLOR=#993399]40[/color][teal])[/teal]
[teal]);[/teal]

[b]insert[/b] [b]into[/b] AMUsers
[teal]([/teal] AMUserID [teal],[/teal] PasswordHash [teal])[/teal] [b]values[/b]
[teal]([/teal] [COLOR=#993399]1[/color]        [teal],[/teal] [i][green]'xxx'[/green][/i]        [teal]),[/teal]
[teal]([/teal] [COLOR=#993399]2[/color]        [teal],[/teal] [i][green]'yyy'[/green][/i]        [teal]);[/teal]

[small]
DELIMITER $$
[b]CREATE[/b]  [b]PROCEDURE[/b] AMCompanySave
[teal]([/teal]
    pCompanyName [maroon]VARCHAR[/maroon][teal]([/teal][COLOR=#993399]50[/color][teal]),[/teal]
    pUserCompanyHash [maroon]VARCHAR[/maroon][teal]([/teal][COLOR=#993399]40[/color][teal]),[/teal]
    pUserHash [maroon]VARCHAR[/maroon][teal]([/teal][COLOR=#993399]40[/color][teal]),[/teal]
    pCompanyNumber [maroon]VARCHAR[/maroon][teal]([/teal][COLOR=#993399]15[/color][teal]),[/teal]
    pVatNo [maroon]VARCHAR[/maroon][teal]([/teal][COLOR=#993399]30[/color][teal]),[/teal]
    OUT retCompanyID [maroon]INT[/maroon]
[teal])[/teal]
BEGIN
    [b]SELECT[/b] @pAMUserID [teal]:=[/teal] AMUserID [b]FROM[/b] AMUsers [b]WHERE[/b] PasswordHash [teal]=[/teal] pUserHash[teal];[/teal]

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

    [b]SET[/b] retCompanyID [teal]=[/teal] LAST_INSERT_ID[teal]();[/teal]
END $$
DELIMITER [teal];[/teal]
[/small]

call AMCompanySave[teal]([/teal][i][green]'The Name'[/green][/i][teal],[/teal] [i][green]'The Hash'[/green][/i][teal],[/teal] [i][green]'xxx'[/green][/i][teal],[/teal] [i][green]'The Number'[/green][/i][teal],[/teal] [i][green]'The VAT'[/green][/i][teal],[/teal] @id[teal]);[/teal]

[b]select[/b] @id [i][green]`just inserted a record with this id`[/green][/i][teal];[/teal]

call AMCompanySave[teal]([/teal][i][green]'Other Name'[/green][/i][teal],[/teal] [i][green]'Other Hash'[/green][/i][teal],[/teal] [i][green]'yyy'[/green][/i][teal],[/teal] [i][green]'Other Number'[/green][/i][teal],[/teal] [i][green]'Other VAT'[/green][/i][teal],[/teal] @id[teal]);[/teal]

[b]select[/b] @id [i][green]`just inserted a record with this id`[/green][/i][teal];[/teal]

[b]select[/b] [teal]*[/teal] [b]from[/b] UserCompanies[teal];[/teal]

Code: 
[blue]mysql>[/blue] \. 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
 
Upvote 0 Downvote
Thanks for that great explanation! Need to think on this one :)

Age is a consequence of experience
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

soushi01
  • Locked
  • Question
MySQL replication slave server re-sync issue
Replies
0
Views
78
soushi01
soushi01
Aging Programmer
  • Locked
  • Question
Simple Foxpro extract of client\t data using old SQL, Memo field issues
Replies
0
Views
34
Aging Programmer
Aging Programmer
admoore
  • Locked
  • Question
Issue w- MySQL 5.75 on OSX 10.10 - can't find .mysql_secret 1
Replies
6
Views
83
admoore
admoore
griffitd
  • Locked
  • Question
routine issue
Replies
12
Views
82
griffitd
griffitd
buttsp
  • Locked
  • Question
mysql query issue
Replies
1
Views
30
r937
r937

Part and Inventory Search

Sponsor

Back
Top