Enkel fråga om insert-statement

2014-12-03, 04:48 #1

Medlem

Reg: Aug 2014

Inlägg: 25

Hej,

Jag har lite funderingar kring insert i SQL, om vi tar scenariot.
Jag har två olika tabeller med en relation till varandra som nedan:

Tabell1
- Tabell1_ID
- Attribut

Tabell2
- Tabell2_ID
- Attribut
- ReferensTill_Tabell1_ID

Hur ska man gåtillväga med insert nya records i andra tabellen om båda tabellerna använder sig av inkrementell ökning av ID med +1.

Exempelvis:
Vi lägger in ett nytt record för tabell1(Vi vet inte vad dess nya ID blir) och vi ska använda detta ID nr som insert statement i tabell2(ReferensTill_Tabell1_ID).

Hur ska man identifiera ID från det nyinlagda record från tabell1 för att sedan kunna använda det i tabell2?

Citera

2014-12-03, 06:55 #2

Moderator

Reg: Feb 2008

Inlägg: 16 567

Citat:

Ursprungligen postat av DatabasTomten

Hej,

Jag har lite funderingar kring insert i SQL, om vi tar scenariot.
Jag har två olika tabeller med en relation till varandra som nedan:

Tabell1
- Tabell1_ID
- Attribut

Tabell2
- Tabell2_ID
- Attribut
- ReferensTill_Tabell1_ID

Hur ska man gåtillväga med insert nya records i andra tabellen om båda tabellerna använder sig av inkrementell ökning av ID med +1.

Exempelvis:
Vi lägger in ett nytt record för tabell1(Vi vet inte vad dess nya ID blir) och vi ska använda detta ID nr som insert statement i tabell2(ReferensTill_Tabell1_ID).

Hur ska man identifiera ID från det nyinlagda record från tabell1 för att sedan kunna använda det i tabell2?

Vilken RDBMS gäller det?

I både SQL server och MySQL finns det SQL-kommandon för att ta fram et senast genererade idt.

I sql server kan man använda

Kod:


@@SCOPE_IDENTITY

och i mysql kan man anvgända

Kod:


LAST_INSERT_ID()

.

Här är mysql-manualen för denna manöver:

http://dev.mysql.com/doc/refman/5.0/...unique-id.html

Citera

2014-12-03, 12:02 #3

Medlem

Reg: Aug 2014

Inlägg: 25

Citat:

Ursprungligen postat av Proton

Vilken RDBMS gäller det?

I både SQL server och MySQL finns det SQL-kommandon för att ta fram et senast genererade idt.

I sql server kan man använda

Kod:


@@SCOPE_IDENTITY

och i mysql kan man anvgända

Kod:


LAST_INSERT_ID()

.

Här är mysql-manualen för denna manöver:

http://dev.mysql.com/doc/refman/5.0/...unique-id.html

Hej Proton,

- Vad står RDBMS för?
- Vad är skillnaden mellan SQl och MySql?
- Jag använder mig av Sql server 2008 R2, är det vanlig SQL?

Jag skulle vilja skapa en procedur som lägger till ett record till tabell1 och sedan använda dess senste id för att lägga till ett kopplat record till tabell2 samt använda någon form av rollback.

Skulle du kunna hjälpa mig med pseudo-kod hur själva strukturen av denna lagrade procedur ska se ut?

Citera

2014-12-03, 18:03 #4

Moderator

Reg: Feb 2008

Inlägg: 16 567

Citat:

Ursprungligen postat av DatabasTomten

Hej Proton,

- Vad står RDBMS för?
- Vad är skillnaden mellan SQl och MySql?
- Jag använder mig av Sql server 2008 R2, är det vanlig SQL?

Jag skulle vilja skapa en procedur som lägger till ett record till tabell1 och sedan använda dess senste id för att lägga till ett kopplat record till tabell2 samt använda någon form av rollback.

Skulle du kunna hjälpa mig med pseudo-kod hur själva strukturen av denna lagrade procedur ska se ut?

RDBMS = Relational Database Management System.

SQL är ett språk, Structured Query Language, MySQL är, i likhet med SQL Server, en databasserver. Rätt stor skillnad med andra ord.

SQL Server har sin egen dialekt av SQL, kallad T-SQL, men den är inte speciellt ovanlig för det, svårt att avgöra om SQL är vanlig eller ovanlig för den delen.

En enkel body till en SP som fixar det du efterfrågar skulle kunna se ut såhär:

Kod:


DECLARE @insertedId int



BEGIN TRANSACTION



BEGIN TRY

     INSERT INTO table1(namn) VALUES('kalle')

     SELECT @insertedId = SCOPE_IDENTITY()

     INSERT INTO table2(tab1id, tab2name)VALUES(@insertedId,'nisse')

END TRY

BEGIN CATCH

     ROLLBACK

END CATCH

IF((SELECT XACT_STATE()) = 1)

BEGIN

     COMMIT

END

Så nånting ska ju kunna funka. Det som återstår är naturligtvis att se till att du kqan ta emot parametrar till denna SP, för jag tror inte du vill använda en SP bara för att spara kalle och nisse vid varje anrop

Citera

2014-12-03, 18:08 #5

Medlem

Reg: Aug 2014

Inlägg: 25

Citat:

Ursprungligen postat av Proton

RDBMS = Relational Database Management System.

SQL är ett språk, Structured Query Language, MySQL är, i likhet med SQL Server, en databasserver. Rätt stor skillnad med andra ord.

SQL Server har sin egen dialekt av SQL, kallad T-SQL, men den är inte speciellt ovanlig för det, svårt att avgöra om SQL är vanlig eller ovanlig för den delen.

En enkel body till en SP som fixar det du efterfrågar skulle kunna se ut såhär:

Kod:


DECLARE @insertedId int



BEGIN TRANSACTION



BEGIN TRY

     INSERT INTO table1(namn) VALUES('kalle')

     SELECT @insertedId = SCOPE_IDENTITY()

     INSERT INTO table2(tab1id, tab2name)VALUES(@insertedId,'nisse')

END TRY

BEGIN CATCH

     ROLLBACK

END CATCH

IF((SELECT XACT_STATE()) = 1)

BEGIN

     COMMIT

END

Så nånting ska ju kunna funka. Det som återstår är naturligtvis att se till att du kqan ta emot parametrar till denna SP, för jag tror inte du vill använda en SP bara för att spara kalle och nisse vid varje anrop

Okej tack för tydliga förklaringen

- Vad betyder denna del i koden: IF((SELECT XACT_STATE()) = 1) ?
- Hur gör jag om jag vill returnera värdet från variabeln @insertedId tillbaka till applikationen?

Tänkte nämligen separera insert till table1 och insert för table2

Tack!

Citera

2014-12-03, 18:23 #6

Moderator

Reg: Feb 2008

Inlägg: 16 567

Citat:

Ursprungligen postat av DatabasTomten

Okej tack för tydliga förklaringen

- Vad betyder denna del i koden: IF((SELECT XACT_STATE()) = 1) ?
- Hur gör jag om jag vill returnera värdet från variabeln @insertedId tillbaka till applikationen?

Tänkte nämligen separera insert till table1 och insert för table2

Tack!

http://msdn.microsoft.com/en-us/library/ms189797.aspx är första träffen på google....

Finns 3 sätt du kan returnera ett värde på från en SP i sql server.

Som ett returvärde
Som en outputparameter.
Som vilket resultat som helst.

beroende på vilket sätt du väljer ser ju syntaxen i den anropande applikationen olika ut.

Se upp med transaktionen, meningen med den är att du ska kunna göra "allt eller inget" och att då spräcka de olika inserten motarbetar ju detta syfte rätt effektivt. Säg att du har 2 olika SP, en för tabell 1 och en för tabel 2.

Påbörjar du en transaktion i proceduren som skapar ett inlägg i tabell 1 bör du avsluta den i samma procedur, annars komme4r du få en massa lustiga beroenden i databasen mellan procedurer. Det går ju såklart att ha namngivna procedurer, men det känns väldigt krångligt för detta ändamål, då måste du kolla i procedur 2 om du har en sådan namngiven transation och sedan committa eller köra rollback på den beroende på om du fått fel i den första inserten (som ligger i en annan procedur än där du avslutar transaktionen).

Låter det rörigt?

Citera

2014-12-04, 06:36 #7

Medlem

Reg: Aug 2014

Inlägg: 25

Citat:

Ursprungligen postat av Proton

http://msdn.microsoft.com/en-us/library/ms189797.aspx är första träffen på google....

Finns 3 sätt du kan returnera ett värde på från en SP i sql server.

Som ett returvärde
Som en outputparameter.
Som vilket resultat som helst.

beroende på vilket sätt du väljer ser ju syntaxen i den anropande applikationen olika ut.

Se upp med transaktionen, meningen med den är att du ska kunna göra "allt eller inget" och att då spräcka de olika inserten motarbetar ju detta syfte rätt effektivt. Säg att du har 2 olika SP, en för tabell 1 och en för tabel 2.

Påbörjar du en transaktion i proceduren som skapar ett inlägg i tabell 1 bör du avsluta den i samma procedur, annars komme4r du få en massa lustiga beroenden i databasen mellan procedurer. Det går ju såklart att ha namngivna procedurer, men det känns väldigt krångligt för detta ändamål, då måste du kolla i procedur 2 om du har en sådan namngiven transation och sedan committa eller köra rollback på den beroende på om du fått fel i den första inserten (som ligger i en annan procedur än där du avslutar transaktionen).

Låter det rörigt?

haha skön spoiler

Problemet med att ha båda insert från tabell1 & tabell2 i samma procedure är att jag vill lägga in 1 record från tabell1 och 1-flera records från tabell2.

Tänkte dela upp de båda i 2 procedurer:
- Anropa den första(insert i tabell1)
- Hämta det senaste värdet från dess nya ID och tilldela värdet till en variabel "nyttID"
- Använda en foreach loop i koden för alla de records som användaren vill lägga in i tabell2
- Exekvera procedur två och använda variabel nyttID som pekar mot tabell1

Förstår att detta inte är en optimal lösning men en nybörjare som jag kan förstå strukturen bättre såhär tror ja

Några fler tips?

Citera

2014-12-04, 15:41 #8

Medlem

Reg: Aug 2014

Inlägg: 25

Hej igen,

Jag har suttit och försökt skapa proceduren nu men får detta fel:

http://pastebin.com/JZ5KVM3b

Ska retur satsen vara vid slutet eller i BEGIN TRY kod blocket?
Jag är rätt ny med lagrade procedurer och SQL så är förvirrad

Citat:

Ursprungligen postat av Proton

http://msdn.microsoft.com/en-us/library/ms189797.aspx är första träffen på google....

Finns 3 sätt du kan returnera ett värde på från en SP i sql server.

Som ett returvärde
Som en outputparameter.
Som vilket resultat som helst.

beroende på vilket sätt du väljer ser ju syntaxen i den anropande applikationen olika ut.

Se upp med transaktionen, meningen med den är att du ska kunna göra "allt eller inget" och att då spräcka de olika inserten motarbetar ju detta syfte rätt effektivt. Säg att du har 2 olika SP, en för tabell 1 och en för tabel 2.

Påbörjar du en transaktion i proceduren som skapar ett inlägg i tabell 1 bör du avsluta den i samma procedur, annars komme4r du få en massa lustiga beroenden i databasen mellan procedurer. Det går ju såklart att ha namngivna procedurer, men det känns väldigt krångligt för detta ändamål, då måste du kolla i procedur 2 om du har en sådan namngiven transation och sedan committa eller köra rollback på den beroende på om du fått fel i den första inserten (som ligger i en annan procedur än där du avslutar transaktionen).

Låter det rörigt?

Citera

2014-12-04, 21:07 #9

Medlem

Reg: Aug 2014

Inlägg: 25

Citat:

Ursprungligen postat av DatabasTomten

Hej igen,

Jag har suttit och försökt skapa proceduren nu men får detta fel:

http://pastebin.com/JZ5KVM3b

Ska retur satsen vara vid slutet eller i BEGIN TRY kod blocket?
Jag är rätt ny med lagrade procedurer och SQL så är förvirrad

Jag har skapat en test procedur för att testa så att det fungerar att returnera rätt värde, vilket fungerar.

Koden för den lagra proceduren:

Kod:

CREATE PROCEDURE [dbo].[TestSP]
	@paramRecipeName nvarchar(max),
	@paramRecipeDescription nvarchar(max),
	@paramRecipeTime decimal(18,0),
	@paramUserId int,
	@new_identity int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	INSERT INTO Recipe(RecipeName, [Description], RecipeTime, UserId)
	VALUES(@paramRecipeName, @paramRecipeDescription, @paramRecipeTime, @paramUserId);
	SELECT @new_identity = SCOPE_IDENTITY();
	RETURN @new_identity
END

Sen koden i min klass ser ut såhär:

Kod:

SqlConnection conn = null;
        SqlDataReader rdr = null;
        SqlCommand cmd = null;
var memberId = WebSecurity.GetUserId(User.Identity.Name);
            int newRecipeId;




conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["RecipeContext"].ConnectionString;
                    //conn.Open();

                    //Använder ett command objekt för att peka gentemot lagrade proceduren
                    cmd = new SqlCommand("TestSP", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Definerar och deklererar alla parameter värden till lagrade proceduren
                    cmd.Parameters.Add(new SqlParameter("@paramRecipeName", SqlDbType.NVarChar));
                    cmd.Parameters["@paramRecipeName"].Value = appModel.RecipeName;

                    cmd.Parameters.Add(new SqlParameter("@paramRecipeDescription", SqlDbType.NVarChar));
                    cmd.Parameters["@paramRecipeDescription"].Value = appModel.Description;

                    cmd.Parameters.Add(new SqlParameter("@paramRecipeTime", SqlDbType.Decimal));
                    cmd.Parameters["@paramRecipeTime"].Value = appModel.RecipeTime;

                    cmd.Parameters.Add(new SqlParameter("@paramUserId", SqlDbType.Int));
                    cmd.Parameters["@paramUserId"].Value = memberId;

                    conn.Open();
                    newRecipeId = (int)cmd.ExecuteScalar();

När jag testar att exekvera lagra proceduren direkt i SQL så returneras det rätt värde, så måste nog vara något fel i min klass som försöker använda ADO.NET?

Detta fel får jag:
An exception of type 'System.NullReferenceException' occurred in ProjectName.dll but was not handled in user code
Additional information: Object reference not set to an instance of an object.

Vid denna kod snutt:
newRecipeId = (int)cmd.ExecuteScalar();

Jag försöker fånga upp SCOPE_IDENTITY() värdet som returneras från den lagrade proceduren, förstår inte riktigt vad jag gjort för fel?

Någon som kan hjälpa mig och förklara felet

Citera

2014-12-06, 13:33 #10

Moderator

Reg: Feb 2008

Inlägg: 16 567

Citat:

Ursprungligen postat av DatabasTomten

Jag har skapat en test procedur för att testa så att det fungerar att returnera rätt värde, vilket fungerar.

Koden för den lagra proceduren:

Kod:

CREATE PROCEDURE [dbo].[TestSP]
	@paramRecipeName nvarchar(max),
	@paramRecipeDescription nvarchar(max),
	@paramRecipeTime decimal(18,0),
	@paramUserId int,
	@new_identity int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	INSERT INTO Recipe(RecipeName, [Description], RecipeTime, UserId)
	VALUES(@paramRecipeName, @paramRecipeDescription, @paramRecipeTime, @paramUserId);
	SELECT @new_identity = SCOPE_IDENTITY();
	RETURN @new_identity
END

Sen koden i min klass ser ut såhär:

Kod:

SqlConnection conn = null;
        SqlDataReader rdr = null;
        SqlCommand cmd = null;
var memberId = WebSecurity.GetUserId(User.Identity.Name);
            int newRecipeId;




conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["RecipeContext"].ConnectionString;
                    //conn.Open();

                    //Använder ett command objekt för att peka gentemot lagrade proceduren
                    cmd = new SqlCommand("TestSP", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Definerar och deklererar alla parameter värden till lagrade proceduren
                    cmd.Parameters.Add(new SqlParameter("@paramRecipeName", SqlDbType.NVarChar));
                    cmd.Parameters["@paramRecipeName"].Value = appModel.RecipeName;

                    cmd.Parameters.Add(new SqlParameter("@paramRecipeDescription", SqlDbType.NVarChar));
                    cmd.Parameters["@paramRecipeDescription"].Value = appModel.Description;

                    cmd.Parameters.Add(new SqlParameter("@paramRecipeTime", SqlDbType.Decimal));
                    cmd.Parameters["@paramRecipeTime"].Value = appModel.RecipeTime;

                    cmd.Parameters.Add(new SqlParameter("@paramUserId", SqlDbType.Int));
                    cmd.Parameters["@paramUserId"].Value = memberId;

                    conn.Open();
                    newRecipeId = (int)cmd.ExecuteScalar();

När jag testar att exekvera lagra proceduren direkt i SQL så returneras det rätt värde, så måste nog vara något fel i min klass som försöker använda ADO.NET?

Detta fel får jag:
An exception of type 'System.NullReferenceException' occurred in ProjectName.dll but was not handled in user code
Additional information: Object reference not set to an instance of an object.

Vid denna kod snutt:
newRecipeId = (int)cmd.ExecuteScalar();

Jag försöker fånga upp SCOPE_IDENTITY() värdet som returneras från den lagrade proceduren, förstår inte riktigt vad jag gjort för fel?

Någon som kan hjälpa mig och förklara felet

Om du nu ska returnera värdet från proceduren, varför har du deklarerat det som en outputparameter? Du har dels ett returnvärde i proceduren och dels en uotputparameter, använd EN av dem, i detta fallet borde du ta bortoutput efter new_value eftersom den är felaktig.

Din Connections öppnande har du kommenterat ut av oklar anledning, varför?

Här är en tråd som diskuterar samma sak:
http://stackoverflow.com/questions/1...ure-in-asp-net

För övrigt bör du ta en titt på using statements, din kod skulle må bra av lite automagisk resurshantering, som till exempel att städa upp efter din Connection som bara ligger och skräpar efter det att du kört allt nu.

Kodhint:

Finns med andra ord en hel del för dig att läsa på om.

Citera

2014-12-07, 16:57 #11

Medlem

Reg: Aug 2014

Inlägg: 25

Citat:

Ursprungligen postat av Proton

Om du nu ska returnera värdet från proceduren, varför har du deklarerat det som en outputparameter? Du har dels ett returnvärde i proceduren och dels en uotputparameter, använd EN av dem, i detta fallet borde du ta bortoutput efter new_value eftersom den är felaktig.

Din Connections öppnande har du kommenterat ut av oklar anledning, varför?

Här är en tråd som diskuterar samma sak:
http://stackoverflow.com/questions/1...ure-in-asp-net

För övrigt bör du ta en titt på using statements, din kod skulle må bra av lite automagisk resurshantering, som till exempel att städa upp efter din Connection som bara ligger och skräpar efter det att du kört allt nu.

Kodhint:

Finns med andra ord en hel del för dig att läsa på om.

Tack

Som sagt jag nickar inte databastomten utan orsak

Citera

2014-12-07, 16:59 #12

Moderator

Reg: Feb 2008

Inlägg: 16 567

Citat:

Ursprungligen postat av DatabasTomten

Tack

Som sagt jag nickar inte databastomten utan orsak

Så hur gick det nu då, fick du det att funka som det skulle och hur ser koden ut för det i sådana fall?

Citera

Enkel fråga om insert-statement

Stöd Flashback