Temat: Mój pierwszy projekt - pomożecie?

Witam,
Jestem właśnie w trakcie robienia projektu z BD w środowisku SQL Server Management.

Ogólnie robię bazę (zbiór albumów muzycznych) i do tego tabelę z pożyczonymi albumami (płytami) swoim znajomym.

Ogólnie jakoś idę do przodu jednak mam problem z procedurą:

create procedure dodaj_wypozyczenie
@tyt STRING,
@id_alb int,
@id_osob int,
@data_temp varchar
--@data_wyp date
AS
begin
declare @data_wyp date
select @id_alb = (select id_albumu from album where tytul = @tyt)

select @data_wyp = (cast(@data_temp as date))
insert into wypozyczenia (id_alb, id_os, data_wypozyczenia) values (@id_alb, @id_osob, @data_wyp)
end
go

--drop procedure dodaj_wypozyczenie

execute dodaj_wypozyczenie 'LB',5,'2011-09-22'

-----------
Ogólnie INSERT wygląda tak:

INSERT INTO wypozyczenia (id_alb, id_os, data_wypozyczenia) VALUES (2, 1, '2011-09-22')

jednak do tego musiałbym poszukać i wpisać odpowiednie id_albumu. Aby to ułatwić chciałem posłużyć się nazwą albumu.

Procedura kompiluje się (o ile tak to można nazwać), natomiast nie chcę się wykonać. Otrzymuje taki błąd:

Msg 8114, Level 16, State 5, Procedure dodaj_wypozyczenie, Line 0
Error converting data type varchar to int.

Niby wiem co on oznacza, ale nie wiem dlaczego tak jest. Jak poprawie podać datę?

Dla przykładu podam jeszcze procedurę, która jest bardzo podobna (na jej podstawie robiłem powyższą procedurę) i która działa poprawnie:

-- Procedura, która ułatwia dodawanie zespołów.
create procedure dodaj_zespol
@nazwa STRING,
@rok int,
@id_artysty01 int,
@id_artysty02 int = null,
@id_artysty03 int = null,
@id_artysty04 int = null
AS
begin
insert into zespol (nazwa, rok_zal) values (@nazwa, @rok)

declare @id_zesp int
select @id_zesp = (select id_zespolu from zespol where nazwa like @nazwa)

if (@rok < year(CURRENT_TIMESTAMP))
begin
insert into wykonawca values (@id_zesp, @id_artysty01)
if @id_artysty02 is null return
insert into wykonawca values (@id_zesp, @id_artysty02)
if @id_artysty03 is null return
insert into wykonawca values (@id_zesp, @id_artysty03)
if @id_artysty04 is null return
insert into wykonawca values (@id_zesp, @id_artysty04)
end
else
begin
return
end
end
go

drop procedure dodaj_zespol

--execute dodaj_zespol 'nowy2', 2012, 2

Temat: Mój pierwszy projekt - pomożecie?

Problem nie dotyczy daty. Wykonując
INSERT INTO wypozyczenia (id_alb, id_os, data_wypozyczenia)
chcesz wstawić rekord 'LB',5,'2011-09-22'

Pole id_alb prawdopodobnie jest typu liczbowego a Ty chcesz wstawić tam wartość 'LB' czyli ciąg znaków.

Temat: Mój pierwszy projekt - pomożecie?

Tak jak napisał przedmówca

masz 4 pola string, int, int, varchar

create procedure dodaj_wypozyczenie
@tyt STRING,
@id_alb int,
@id_osob int,
@data_temp varchar

a dodać próbujesz:
execute dodaj_wypozyczenie 'LB',5,'2011-09-22'
tzn
do pola string dajesz LB
do pola int dajesz 5
do pola int dajesz 2011-09-22
do pola data wstawiasz null

Problem dość banalny, mam nadzieje że sobie poradzisz, jak co to pisz

Temat: Mój pierwszy projekt - pomożecie?

Przemysław Kapica:
Problem nie dotyczy daty. Wykonując
INSERT INTO wypozyczenia (id_alb, id_os, data_wypozyczenia)
chcesz wstawić rekord 'LB',5,'2011-09-22'

PS Michał ma dwa insterty wypozyczenia i dodaj_wypozyczenie, problem tkwi w dodaj_wypozyczenia

Temat: Mój pierwszy projekt - pomożecie?

@Mateusz - rzeczywiście problem banalny. Dzisiaj rano mnie olśniło, że chyba o to chodzi. Ogólnie na razie programuje tą bazę troche "po omacku".

Teraz procedura działa.

Jednak za pewne będę miał jeszcze trochę pytań/problemów w czasie tworzenia tej bazy.

I już mam kolejne pytanie. Mam procedurę (wykorzystująca kursor), która w pokazuje np. 10 najnowszych albumów.

Procedura raczej działa poprawnie i wygląda tak:

--Procedura (wykorzystująca kursor) wyświetlająca x najnowszych albumów (x jest parametrem wejściowym, domyślnie = 5)

create procedure x_najnowszych

@x int = 5

as

begin

	declare @id_alb int,

		@tytul STRING,

		@rok_wyd date,

		@licznik int

	set @licznik = 0



	declare najnowsze_albumy cursor for

	select id_albumu, tytul, rok_wydania from album a order by a.rok_wydania desc

	print 'LP' + char(9) + 'ID_ALB' + char(9) + char(9) + 'TYTUL' + char(9) + char(9) + char(9) + char(9) + char(9) + char(9) + 'ROK WYDANIA'

	print '--------------------------------------------------------------------'

	open najnowsze_albumy



	fetch next from najnowsze_albumy into @id_alb, @tytul, @rok_wyd

	while @@FETCH_STATUS = 0 and @licznik < @x

	begin

		set @licznik = @licznik + 1

		print cast(@licznik as varchar) + char(9) + cast(@id_alb as varchar) + char(9) + char(9) + char(9) + cast(@tytul as varchar) + char(9) + char(9) + char(9) + char(9) + char(9) + char(9) + cast(@rok_wyd as varchar)

		fetch next from najnowsze_albumy into @id_alb, @tytul, @rok_wyd

	end



	close najnowsze_albumy

	deallocate najnowsze_albumy

end



--drop procedure x_najnowszych



execute x_najnowszych

execute x_najnowszych 10

Jednak nie zadowala mnie wygląd wynikowy(sami zobaczcie):





Jak zrobić aby te daty były równo w każdym wierszu?

Temat: Mój pierwszy projekt - pomożecie?

W ten sposób nie będziesz miał nigdy wyrównane.
Dodajesz tabulatory (char(9)), niezaleznie od tego ile wczęsniejszy napis zwrócił znaków. Prawie zawsze bedziesz miał rozjazd...
To po 1.
Po drugie do wyciągnięcia N pierwszych rekordów od bodajże SQL version 7 służy klauzula TOP:
SELECT TOP x imie, nazwisko FROM osoby ORDER BY nazwisko
jako x może być zarówno liczba, liczba PERCENT - wtedy pobiera określony % rekordów, jak i wyrażenie (nie mam na to przykładu tak na szybko).
Używane jest również do ... sortowania podzapytań, bo tych nie wolno w MS SQLu sortować.
Robi się wtedy:
SELECT * FROM (SELECT TOP 100 PERCENT imie, nazwisko FROM osoby ORDER BY nazwisko) x

pozdrawiaMM

Temat: Mój pierwszy projekt - pomożecie?

Wiem, że można zastosować tutaj select.. jednak muszę w projekcie zastosować kursor i akurat tutaj chciałem go wykorzystać.

Czyli nie idzie tego w tym przypadku wyrównać?

--------
i jeszcze jedna kwestia.
Chciałem dodać procedurę, która sprawdza przed dodaniem albumu do tabeli 'wypożyczone albumy' czy album ten czasami nie jest już pożyczony.

Procedura wygląda tak:
tabela wypozyczenia: id_wypozyczen ; id_alb ; id_os ; data_wypozyczenia

create procedure dodaj_wypozyczenie

@tyt STRING,

@id_osob int,

@data_wyp date

AS

begin

    declare @id_a int

    declare @spr int

	select @id_a = (select id_albumu from album where tytul = @tyt)

    select @spr = (select id_alb from wypozyczenia where id_alb = @id_a)

    if (select @spr) != 0

    begin

    print 'album jest już wypożyczony'

    end

    else

    begin

	insert into wypozyczenia (id_alb, id_os, data_wypozyczenia) values (@id_a, @id_osob, @data_wyp)

	end

end

go



execute dodaj_wypozyczenie 'Tabasko',5,'2011-09-22'

Procedura niestety nie działa poprawnie :( Pierwszy alarm pojawia się tutaj:

select @spr = (select id_alb from wypozyczenia where id_alb = @id_a)

i nie wiem dlaczego. Mam takie info:

sg 512, Level 16, State 1, Procedure dodaj_wypozyczenie, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Nie rozumiem za bardzo dlaczego tutaj jest ten błąd. Dalej ten if
if (select @spr) != 0
też nie wiem czy jest poprawny. Michał Rodzynek edytował(a) ten post dnia 10.10.11 o godzinie 22:59

Temat: Mój pierwszy projekt - pomożecie?

Dziwna sytuacja nastąpiła. Dzisiaj ta SAMA procedura działa poprawnie! (nie wiedzieć czemu, wczoraj nie chciała działać. No chyba, że nadal coś z nią jest nie tak).

Natomiast odnośnie błędu:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

nadal mam pytanie. Dzisiaj testowałem takie polecenia:

select imie from osoba

where id_osoby = (select id_os from wypozyczenia)



select imie from osoba

where id_osoby = (select id_os from wypozyczenia where data_wypozyczenia > '2011-05-01' and id_alb = 2)

W przypadku pierwszego polecenia występuje błąd (ten co zamieściłem powyżej).
W przypadku drugiego nie ma tego błedu.

Od czego to zależy?

Temat: Mój pierwszy projekt - pomożecie?

proponuje następujące rozwiązanie:

select imie from osoba

where id_osoby IN (select id_os from wypozyczenia)

a dlaczego? chyba teraz nie jest to takie skomplikowane :)

Zapytanie nr zwraca najprawdopodobniej jeden rekord?Tomasz Filipkowski edytował(a) ten post dnia 11.10.11 o godzinie 13:46

Temat: Mój pierwszy projekt - pomożecie?

Z tym IN sobie też testowałem i działa poprawnie.

Ale mi chodziło raczej o wyjaśnienie - dlaczego tak jest, że z IN działa a z = nie dziala.

Polecenie z IN zwraca 6 rekordów.

Temat: Mój pierwszy projekt - pomożecie?

Michał Rodzynek:
Z tym IN sobie też testowałem i działa poprawnie.

Ale mi chodziło raczej o wyjaśnienie - dlaczego tak jest, że z IN działa a z = nie dziala.

Polecenie z IN zwraca 6 rekordów.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

To jest związane ze składnią - operator '=' w SQL może przyjmowac tylko pojedynczą wartość. To co chciałes zrobić to coś w stylu

select col1 from tab1 where tab1_ID = 1,2,3,4

Temat: Mój pierwszy projekt - pomożecie?

Aha! Teraz już widzę to jaśniej. Dzięki.

Chyba na obecną chwilę to będzie wszystko. Projekt skończony. Ale w przyszłości na pewno się odezwę :)

Pozdrawiam

Temat: Mój pierwszy projekt - pomożecie?

To znowu ja :)
Projekt był trochę za "płytki" i musiałem dorobić kilka opcji.
W czasie robienia jednego z triggerów pojawiło się pewne pytanie.

Mam taki trigger:

--triger, który przy usuwaniu albumu z bazy (w wyniku nie odzyskania wypożyczonej płyty) 

--usuwa również informacje z o wypozyczeniu oraz o wykonawcy/artyście (jeśli nie ma innych jego płyt)

create trigger usuwanie_albumu on album

instead of delete

as

begin

	declare @id int

	declare @id_a int

	declare @temp int

	declare @ile_alb int

	declare @id_art int



	select @id = (select id_wyk from deleted)     -- id=5

	select @temp = (select id_albumu from deleted)  --temp=3

	select @id_a = (select id_wypozyczen from wypozyczenia where id_alb = @temp) --id_alb=3

	--id_a = 2

	delete from wypozyczenia where id_wypozyczen = @id_a

	delete from album where id_albumu = @temp

    select @ile_alb = (select count(id_wyk) from album where id_wyk = @id)

    select @id_art = (select id_artys from wykonawca where id_wykonawcy = @id)

	if (select @ile_alb) != 0

    begin

       print 'Album został usunięty z bazy.'

    end

    else

    begin

	   delete from wykonawca where id_wykonawcy = @id

	   delete from artysta where id_artysty = @id_art

	   print 'Album został usunięty z bazy.'

	   print 'Informacje o wykonawcy/artysty zostały usunięte (nie ma w bazie więcej płyt tego artysty).'

	end

end

go

zapewne idzie go zrobić lepiej. Dla mnie ważne jest że działa.
Tylko mam teraz pytanie czy polecenie instead of delete powoduje, że zamiast polecenia:

delete from album where tytul = 'Music'

wykonywane jest wszystko to, co jest w triggerze?

Bo po wykonaniu usuwania otrzymuje taką wiadomość:




Nie wiem dlaczego jest ten ta trzecia wiadomość (pierwsze dwie to w triggerze są zapewne te polecenia:

delete from wypozyczenia where id_wypozyczen = @id_a
delete from album where id_albumu = @temp )

Temat: Mój pierwszy projekt - pomożecie?

a jak zrobisz del na kilku rekordach?

Temat: Mój pierwszy projekt - pomożecie?

Dodaj sobie komentarze po każdym dele'cie - i to różne, będziesz wiedział co się dzieje i w którym miejscu:

--triger, który przy usuwaniu albumu z bazy (w wyniku nie odzyskania wypożyczonej płyty) 

--usuwa również informacje z o wypozyczeniu oraz o wykonawcy/artyście (jeśli nie ma innych jego płyt)

create trigger usuwanie_albumu on album

instead of delete

as

begin

	declare @id int

	declare @id_a int

	declare @temp int

	declare @ile_alb int

	declare @id_art int



	select @id = (select id_wyk from deleted)     -- id=5

	select @temp = (select id_albumu from deleted)  --temp=3

	select @id_a = (select id_wypozyczen from wypozyczenia where id_alb = @temp) --id_alb=3

	--id_a = 2

	delete from wypozyczenia where id_wypozyczen = @id_a

        print 'Wypożyczenia zostały usunięte z bazy.'

	delete from album where id_albumu = @temp

    select @ile_alb = (select count(id_wyk) from album where id_wyk = @id)

    select @id_art = (select id_artys from wykonawca where id_wykonawcy = @id)

	if (select @ile_alb) != 0

    begin

       print 'Album został usunięty z bazy (1).'

    end

    else

    begin

	   delete from wykonawca where id_wykonawcy = @id

           print 'Wykonawca został usunięty z bazy.'

	   delete from artysta where id_artysty = @id_art

	   print 'Album został usunięty z bazy (2).'

	   print 'Informacje o wykonawcy/artysty zostały usunięte (nie ma w bazie więcej płyt tego artysty).'

	end

end

go

Jak kod zadziała to możesz to sobie ewent. usunąć, chociaż dla póżniejszych bugów może być pomocne, a samego outputu nie zaśmieca zbytnio.

Pytanie też, czy nie lepiej zamiast usuwać rekordy oznaczać je jako usunięte - dodać na tabelach kolumny w stylu is_deleted char(1), delete_date (date) - dla celów audytowych pewnie korzystniej. Można też dodać procedurę 'dedeletującą' wykonawcę, jeśli po usunięciu ostatniego albumu kiedyś pojawi się nowy - zamiast dodawać dubla w bazie na wykonawcach.Adam Ościłowski edytował(a) ten post dnia 18.10.11 o godzinie 19:07

Temat: Mój pierwszy projekt - pomożecie?

@Adam Ościłowski - zrobiłem sobie, tak jak pisałeś, po każdym dele'cie komentarz i wygląda to tak:




(wykonują się polecenia po else - @ile_alb == 0 )

I nadal nie wiem dlaczego jest ta ostatnia operacja ;/

P.S. Z tym oznaczaniem to nie jest głupi pomysł, ale na razie zostawię to chyba tak jak jest.

Temat: Mój pierwszy projekt - pomożecie?

Wydaje mi się, że problem jest tutaj:

if (select @ile_alb) != 0

SELECT bez przypisania wartości do zmiennej nie zwraca 'affected'
natomiast 'zwykly' SELECT już tak.

Spróbuj to zmienić na:

if (@ile_alb) != 0 

I obejrzeć wynik triggera.Tomasz Filipkowski edytował(a) ten post dnia 19.10.11 o godzinie 14:25

Temat: Mój pierwszy projekt - pomożecie?

Po zmianie to samo :(