Pokušavam napraviti neke generičke procedure za CRUD operacije, zapravo nije bitno zašto.. Uglavnom više-manje stalno radim sa Sql Serverom (dakle TSQL), pa su mi stvari oko MySQL-a prilično nepoznate.
Na kraju sam uspio dobiti rezultat koji me zanimao, međutim muči me jedna stvar: u MySQL-u da bih napravio dinamički upit moram koristiti user-defined varijable. Koliko shvaćam, te varijable žive sve dok je session otvoren.
Ako imam slijedeću proceduru:
Code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `generic_GetEntity`(
in `Schema` varchar(128),
in TableName varchar(128),
in PkValue int
)
BEGIN
declare LSchemaName varchar(128);
declare LTableName varchar(128);
declare PkColName varchar(128);
declare stmt varchar(4000);
select
t.table_name collate utf8_unicode_ci
, k.COLUMN_NAME collate utf8_unicode_ci
, t.table_schema collate utf8_unicode_ci
into
LTableName
, PkColName
, LSchemaName
from
information_schema.table_constraints t
left join information_schema.key_column_usage k using(constraint_name, table_schema, table_name)
where t.constraint_type='PRIMARY KEY'
and t.table_schema=`Schema` collate utf8_general_ci
and t.table_name=TableName collate utf8_general_ci;
if LTableName is null then
signal sqlstate '45000' set message_text = 'Table not found!';
end if;
set stmt = CONCAT( 'select v.* from `', LSchemaName, '`.`', LTableName, '` as v where v.`', PkColName, '` = ?' );
set @getEntityStatement := stmt;
set @getEntityPkValue := PkValue;
prepare getEntityStatement from @getEntityStatement;
execute getEntityStatement using @getEntityPkValue;
deallocate prepare getEntityStatement;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `generic_GetEntity`(
in `Schema` varchar(128),
in TableName varchar(128),
in PkValue int
)
BEGIN
declare LSchemaName varchar(128);
declare LTableName varchar(128);
declare PkColName varchar(128);
declare stmt varchar(4000);
select
t.table_name collate utf8_unicode_ci
, k.COLUMN_NAME collate utf8_unicode_ci
, t.table_schema collate utf8_unicode_ci
into
LTableName
, PkColName
, LSchemaName
from
information_schema.table_constraints t
left join information_schema.key_column_usage k using(constraint_name, table_schema, table_name)
where t.constraint_type='PRIMARY KEY'
and t.table_schema=`Schema` collate utf8_general_ci
and t.table_name=TableName collate utf8_general_ci;
if LTableName is null then
signal sqlstate '45000' set message_text = 'Table not found!';
end if;
set stmt = CONCAT( 'select v.* from `', LSchemaName, '`.`', LTableName, '` as v where v.`', PkColName, '` = ?' );
set @getEntityStatement := stmt;
set @getEntityPkValue := PkValue;
prepare getEntityStatement from @getEntityStatement;
execute getEntityStatement using @getEntityPkValue;
deallocate prepare getEntityStatement;
END
postoji li opasnost da se paralelno unutar istog sessiona (nekakav multithreading ili slično) izvrši ta procedura više puta paralelno u okviru istog sessiona, te da jedan poziv te procedure utječe na rezultat drugog u smislu da prebriše neku od user-defined varijabli u nekom nezgodnom trenutku (nakon neke od set operacija)?
Hvala!
http://www.dropbox.com/referrals/NTQ0MTI2NDc5
https://www.agronomija.info/
Failure is not an option. It comes bundled with your Microsoft product.
https://www.agronomija.info/
Failure is not an option. It comes bundled with your Microsoft product.