BEGIN

DECLARE i INT DEFAULT 1;
CREATE TEMPORARY TABLE ascii_chart

(ascii_code int, ascii_char CHAR(1));

WHILE (i<=128) DO
INSERT INTO ascii_chart VALUES(i,CHAR(i)); SET i=i+1;

END WHILE;
select * from ascii_chart; drop table ascii_chart;

END$$
-------------------------------------------------------------------------------------
CREATE PROCEDURE merge_employee (IN old_id INT, IN new_id INT, OUT error VARCHAR(100))

SQL SECURITY DEFINER COMMENT 'This is the comment'BEGIN

DECLARE old_count INT DEFAULT 0; DECLARE new_count INT DEFAULT 0; DECLARE addresses_changed INT DEFAULT 0;

## check to make sure the old_id and new_id exists
SELECT count(*) INTO old_count FROM employee WHERE id = old_id;SELECT count(*) INTO new_count FROM employee WHERE id = new_id;

IF !old_count THEN
SET error = 'old id does not exist';

ELSEIF !new_count THEN
SET error = 'new id does not exist';

ELSE
UPDATE employee SET id = new_id WHERE id = old_id;SELECT row_count() INTO addresses_changed;

DELETE FROM employee WHERE id = old_id;

SELECT addresses_changed;END IF;

END

------------------------------------------------------------------------------------------
CREATE PROCEDURE CountOrderByStatus( IN orderStatus VARCHAR(25),OUT total INT)

BEGIN
SELECT count(orderNumber) INTO total
FROM orders
WHERE status = orderStatus;

END --------------------------------------------------------------------------------------- CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024)) BEGIN

DECLARE i INT DEFAULT 1; DECLARE myc, pc CHAR(1);

DECLARE outstr VARCHAR(1000) DEFAULT str; WHILE i <= CHAR_LENGTH(str) DO
SET myc = SUBSTRING(str, i, 1);
SET pc = CASE WHEN i = 1 THEN ' '

ELSE SUBSTRING(str, i - 1, 1) END;

IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN SET outstr = INSERT(outstr, i, 1, UPPER(myc));

END IF;

SET i = i + 1; END WHILE;

SET str = outstr; END

---------------------------------------------------------------- DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc()

BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255); SET x = 1;
SET str = '';
REPEAT

SET str = CONCAT(str,x,',');

SET x = x + 1; UNTIL x > 5

END REPEAT; SELECT str; END

----------------------------------------------------
CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2),OUT ph DECIMAL(8,2),OUT pa DECIMAL(8,2)) BEGIN

SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products;

END
--------------------------------------------------------
CREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,OUT ototal DECIMAL(8,2)) BEGIN

DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;

END IF;
SELECT total INTO ototal;

END;
--------------------------------------------------------- ---------------------------------------------------
CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT)

RETURNS INT DETERMINISTIC

BEGIN
DECLARE avg INT;
SET avg = (n1+n2+n3*2+n4*4)/8; RETURN avg;

END ------------------------------------------------------------------- DROP FUNCTION IF EXISTS `sampleFunc1` CREATE FUNCTION `sampleFunc1`(a INT,b INT)

RETURNS TINYINT(1) DETERMINISTIC

BEGIN
DECLARE run TINYINT DEFAULT 0; IF (a+b) > 100 THEN

SET run = 1; ELSE

SET run = 2; END IF;

RETURN run; END

------------------------------------------------------------------------------CREATE FUNCTION myFunction(

• ->  in_title VARCHAR(4),

• ->  in_gender CHAR(1),

• ->  in_firstname VARCHAR(20),

• ->  in_middle_initial CHAR(1),

• ->  in_surname VARCHAR(20)) ->

• ->  RETURNS VARCHAR(60)

• ->  BEGIN

• ->  DECLARE l_title VARCHAR(4);

-> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> ->

DECLARE l_name_string

IF ISNULL(in_title) THEN IF in_gender='M' THEN

SET l_title='Mr';ELSE

SET l_title='Ms';END IF;

END IF;

VARCHAR(60);

IF ISNULL(in_middle_initial) THEN
SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',in_surname);

ELSE
SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',

in_middle_initial,' ',in_surname);

END IF;

RETURN(l_name_string); END$$

------------------------------------------------------------------------CREATE FUNCTION myFunction (in_dob datetime) returns int

-> -> -> -> -> -> -> -> -> -> ->

CREATE FUNCTION myFunction(normal_price NUMERIC(8,2))

NO SQL

BEGIN

DECLARE l_age INT;
IF DATE_FORMAT(NOW(),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN

SET l_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y');ELSE

SET l_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;END IF;
RETURN(l_age);

END$$ ----------------------------------------------------------------------------

-> -> -> -> -> -> -> -> -> -> -> -> -> ->

RETURNS NUMERIC(8,2)BEGIN

DECLARE discount_price NUMERIC(8,2);

IF (normal_price>500) THEN
SET discount_price=normal_price*.8;

ELSEIF (normal_price>100) THEN SET discount_price=normal_price*.9;

ELSE
SET discount_price=normal_price;

END IF;

RETURN(discount_price); END$$

--------------------------------------------------------------------------------CREATE FUNCTION myFunction(in_status CHAR(1))

-> -> -> -> -> -> -> -> -> ->

-> -> -> -> -> -> -> ->

RETURNS VARCHAR(20)BEGIN

IF in_status = 'O' THEN RETURN('Overdue');

ELSEIF in_status = 'U' THEN RETURN('Up to date');ELSEIF in_status = 'N' THEN

RETURN('New');END IF;

END$$ ----------------------------------------------------------------------CREATE FUNCTION myFunction(in_rep_id INT)

RETURNS INT

READS SQL DATA

BEGIN

DECLARE customer_count INT;

SELECT COUNT(*) INTO customer_count

FROM employee

-> WHERE id=in_rep_id;
->
-> RETURN(customer_count); ->
-> END$$

---------------------------------------------------------------
CREATE FUNCTION myFunction (rush_ship INT(10)) RETURNS DECIMAL(10,2)

-> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> ->

BEGIN

DECLARE rush_shipping_cost DECIMAL(10,2);

CASE rush_ship WHEN 1 THEN

SET rush_shipping_cost = 20.00; WHEN 2 THEN

SET rush_shipping_cost = 15.00; WHEN 3 THEN

SET rush_shipping_cost = 10.00; ELSE

SET rush_shipping_cost = 0.00;END CASE;

RETURN rush_shipping_cost;

END

-------------------------------------------------------------------------------

SELECT P.productCode,P.productName,SUM(priceEach * quantityOrdered) total FROM orderdetails O INNER JOIN products P ON O.productCode = P.productCode GROUP by productCode ORDER BY total
-----------------------------------------

SELECT DISTINCT store_type FROM stores WHERE EXISTS(SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type)

SHOW COLUMNS FROM cu_pers like 'archiv'

ALTER TABLE `GER_COM`.`CU_PERS` DROP COLUMN `ARCHIV` ;
ALTER TABLE `CU_PERS` DROP `ARCHIV`; //Tabellen-Spalte löschen
ALTER TABLE `GER_COM`.`CU_PERS` ADD COLUMN `ARCHIV` DECIMAL(5,0) NULL DEFAULT NULL AFTER `MANDANTNO` ; // Tabellen-Spalte einfügen
ALTER TABLE `GER_COM`.`CU_PERS` CHANGE COLUMN `ARCHIV` `ARCHIV` VARCHAR(64) NULL DEFAULT NULL ;// Tabellen Spalte ändern

UPDATE `GER_COM`.`JD_BASE` SET `DATALONG`=24 WHERE `PK`='JD_DB_VERSION';// Datensatz ändern

SELECT COUNT(*) FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME='ARCHIV'
select count(*) from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME
= 'CU_PERS' and COLUMN_NAME = 'ARCHIV';
select count(*) from information_schema.COLUMNS where TABLE_SCHEMA = TABLE_NAME LIKE 'CU_PERS' and COLUMN_NAME LIKE 'ARCHIV';
select count(*) from information_schema.COLUMNS where TABLE_SCHEMA = 'GER_COM' and TABLE_NAME = 'CU_PERS' and COLUMN_NAME = 'ARCHIV';
SELECT * FROM CU_PERS WHERE ARCHIV = ARCHIV AND ARCHIV<>ARCHIV --------------------------------------------------------------------------------------------
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (N'CU_PERS') -------------------------------------------------------------------
IF SELECT COUNT(*) FROM CU_PERS WHERE ARCHIV = ARCHIV > 0

BEGIN
/* deny edit */

END ELSE

BEGIN
/* allow edit */

END -------------------------------------------------------------------------------------------------------------------

DELIMITER //

drop procedure if exists FAG_AddField; //

//
create procedure FAG_AddField(

in TableName
in FieldName
in FieldType
in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName and COLUMN_NAME = FieldName;

if @x = 0 then
set @s = concat( 'alter table `' , TableName , '` add `' , FieldName , '` ' , FieldType , ';' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

end;
//
DROP PROCEDURE IF EXISTS ARCHIV; //

CREATE PROCEDURE ARCHIV() BEGIN

set @x=0;

select count(*) into @x from information_schema.COLUMNS where TABLE_SCHEMA = 'GER_COM_test' and TABLE_NAME = 'CU_PERS' and COLUMN_NAME = 'ARCHIV';

) begin

text, text,

text,

IF (@x > 0) THEN
set @x=0;
ELSE
call FAG_AddField( 'CU_PERS', 'ARCHIV', 'DECIMAL(5,0)', 'Current Status for the person',

Mitarbeiter/Person'); END IF;

END // call ARCHIV();

'Aktueller Status der

-----------------------------------
/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYP E,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,

CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) , PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME='' ,

REFERENCE_COL_NAME=''

FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable

ON PKnUTable.object_id = PKnUKEY.parent_object_id INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id INNER JOIN sys.columns as PKnUKEYCol

ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl ON oParentColDtl.TABLE_NAME=PKnUTable.name

AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name UNION ALL

SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) , CONSTRAINT_TYPE='FK',

PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) , REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))

FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint

ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not

be sequential.*/
INNER JOIN sys.sysobjects oReference

ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs

might not be sequential.*/ )

select * from ALL_KEYS_IN_TABLE where

PARENT_TABLE_NAME in ('YOUR_TABLE_NAME')

or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME') ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

------------------------------------------------------------------------- ----------------------JD-SCRIPT--------------------------------------- ----------------------------------------------------
drop procedure if exists FAG_DebugPrint

go
drop procedure if exists FAG_AddTable
go
drop procedure if exists FAG_DropTable
go
drop procedure if exists FAG_DropTableIfEmpty
go
drop procedure if exists FAG_AddField
go
drop procedure if exists FAG_DropField
go
drop procedure if exists FAG_AddPrimaryKey
go
drop procedure if exists FAG_AddIndex
go
drop procedure if exists FAG_AddUniqueIndex
go
drop procedure if exists FAG_DropIndex
go
drop procedure if exists FAG_AddForeignKey
go
drop procedure if exists FAG_AddUniqueKey
go
drop procedure if exists FAG_AlterCol_EnlargeNVarchar go
drop procedure if exists FAG_AlterCol_EnlargeDecimal go
drop procedure if exists FAG_SetDatabaseVersion

go

create procedure FAG_DebugPrint( in param1 text

) begin

select param1 as 'debug output';

end;
go
create procedure FAG_AddTable(

in TableName
in FieldName
in FieldType
in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.TABLES where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName;

if @x = 0 then
set @s = concat('create table `' , TableName , '` ( `' , FieldName , '` ' , FieldType , ' ) engine=innodb;' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

end;
go
create procedure FAG_DropTable(

) begin

text, text,

text,

in TableName text, in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.TABLES where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName;

) begin

end;

go

if @x > 0 then
set @s = concat('drop table `' , TableName, '`;' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

create procedure FAG_DropTableIfEmpty( in TableName text,

in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.TABLES where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName;

if @x > 0 then
set @s = concat('select count(*) into @x from `' , TableName, '`;' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;

) begin

if @x = 0 then
set @s = concat('drop table `' , TableName, '`;' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;
end if;

end;
go
create procedure FAG_AddField(

in TableName
in FieldName
in FieldType
in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName and COLUMN_NAME = FieldName;

if @x = 0 then
set @s = concat( 'alter table `' , TableName , '` add `' , FieldName , '` ' , FieldType , ';' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

end;
go
create procedure FAG_DropField(

) begin

text, text,

text,

in TableName text, in FieldName text,
in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName and COLUMN_NAME = FieldName;

if @x > 0 then
set @s = concat( 'alter table `' , TableName , '` drop column `' , FieldName , '`;' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

end;
go
create procedure FAG_AddPrimaryKey(

) begin

in TableName text, in FieldName text,
in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.TABLES as T left join information_schema.TABLE_CONSTRAINTS as TC on T.TABLE_NAME = TC.TABLE_NAME

where T.TABLE_SCHEMA = ( select DATABASE() ) and TC.CONSTRAINT_TYPE = 'Primary Key' and T.TABLE_NAME = TableName;

) begin

if @x = 0 then
set @s = concat( 'alter table `' , TableName , '` add primary key (`' , FieldName , '`);' ); prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
call FAG_DebugPrint(@s);
end if;

end;
go
create procedure FAG_AddIndex(

in TableName text, in FieldName text,
in xCommentEng text, in xCommentGer text

set @MyIndexName = concat( TableName , '_' , FieldName , '_IND' );

set @x=0;

select count(*) into @x from information_schema.STATISTICS where TABLE_SCHEMA = ( select DATABASE() ) and INDEX_NAME = @MyIndexName and TABLE_NAME = TableName;

) begin

end;

go

if @x = 0 then
set @s = concat( 'create index `' , @MyIndexName, '` on `', TableName , '` (`' , FieldName , '`);' );

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

create procedure FAG_AddUniqueIndex( in TableName text,
in FieldName text,

in xCommentEng text, in xCommentGer text

set @MyIndexName = concat( TableName , '_' , FieldName , '_UIND' );

set @x=0;

select count(*) into @x from information_schema.STATISTICS where TABLE_SCHEMA = ( select DATABASE() ) and INDEX_NAME = @MyIndexName and TABLE_NAME = TableName;

if @x = 0 then
set @s = concat( 'create unique index `' , @MyIndexName, '` on `', TableName , '` (`' , FieldName , '`);' );

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

end;
go
create procedure FAG_DropIndex(

) begin

) begin

in TableName text, in IndexName text, in xCommentEng text, in xCommentGer text

set @x=0;

select count(*) into @x from information_schema.STATISTICS where TABLE_SCHEMA = ( select DATABASE() ) and INDEX_NAME = IndexName and TABLE_NAME = TableName;

if @x > 0 then
set @s = concat( 'drop index `' , IndexName , '` on `' , TableName , '`;' );

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

end;
go
create procedure FAG_AddForeignKey(

in Table1Name text, in Field1Name text, in Table2Name text, in Field2Name text, in xCommentEng text, in xCommentGer text

) begin

set @MyConstraintName = concat( Table1Name , '_' , Field1Name , '_' , Table2Name , '_' , replace(replace(Field2Name collate latin1_general_ci, ',', '_') collate latin1_general_ci, ' ', '') , '_FOREIGN_KEY' );

set @x=0;

select count(*) into @x from information_schema.TABLES as T left join information_schema.TABLE_CONSTRAINTS as TC on T.TABLE_NAME = TC.TABLE_NAME

where T.TABLE_SCHEMA = ( select DATABASE() ) and TC.CONSTRAINT_TYPE = 'FOREIGN KEY' and T.TABLE_NAME = Table1Name and TC.CONSTRAINT_NAME = @MyConstraintName;

if @x = 0 then

set @s = concat( 'alter table `' , Table1Name, '` add constraint `', @MyConstraintName , '` foreign key (`' , Field1Name , '`) references `', Table2Name, '` (`' , Field2Name, '`);' );

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

end;
go
create procedure FAG_AddUniqueKey(

in TableName text, in FieldName text,
in xCommentEng text, in xCommentGer text

) begin

set @MyConstraintName = concat( TableName , '_' , replace(replace(FieldName collate latin1_general_ci, ',', '_') collate latin1_general_ci, ' ', '') , '_UNIQUE' );

set @x=0;

select count(*) into @x from information_schema.TABLES as T left join information_schema.TABLE_CONSTRAINTS as TC on T.TABLE_NAME = TC.TABLE_NAME

where T.TABLE_SCHEMA = ( select DATABASE() ) and TC.CONSTRAINT_TYPE = 'UNIQUE' and T.TABLE_NAME = TableName and TC.CONSTRAINT_NAME = @MyConstraintName;

if @x = 0 then
set @s = concat( 'alter table `' , TableName, '` add constraint `', @MyConstraintName , '` unique (' , FieldName , ');' );

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

end;

go

create procedure FAG_AlterCol_EnlargeNVarchar( in TableName text,

in FieldName text, in MinFieldLength int,
in xCommentEng text, in xCommentGer text

set @x=0;

select CHARACTER_MAXIMUM_LENGTH into @x from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName and COLUMN_NAME = FieldName;

if @x < MinFieldLength then

set @s = concat( 'alter table `' , TableName, '` change column `', FieldName , '` `', FieldName, '` varchar(', MinFieldLength, ');' );

) begin

end;

go

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

create procedure FAG_AlterCol_EnlargeDecimal( in TableName text,

in FieldName
in FieldSize int,
in FieldScale int,
in xCommentEng text, in xCommentGer text

set @x=0;

select NUMERIC_SCALE into @x from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = TableName and COLUMN_NAME = FieldName;

if @x < FieldScale then

set @s = concat( 'alter table `' , TableName, '` change column `', FieldName , '` `', FieldName, '` decimal(', FieldSize, ', ', FieldScale, ');' );

) begin

end;

go

prepare stmt1 from @s; execute stmt1;
deallocate prepare stmt1; call FAG_DebugPrint(@s); end if;

create procedure FAG_SetDatabaseVersion( in VersionMajor int,

) begin

in VersionMinor int, in VersionRevision int, in xCommentEng text, in xCommentGer text

set @MyVersion = concat( 'Database version is now ' , VersionMajor , '.' , VersionMinor , '.' , VersionRevision ); start transaction ;
set @x=0;
select count(*) into @x from IS_SETUP where IDENT = 99001;

if @x=0 then set @MyID=0;

text,

end;
go
call FAG_AddTable(

' ') go

'JD_BASE',

'PK',

'varchar(254) not null',

' ',

select (ifnull( max( NO ), 0) + 1) into @MyID from IS_SETUP;
insert into IS_SETUP ( NO, IDENT, STAT0, STAT1, STAT2, CNAME ) values ( @MyID, 99001, 0, 0, 0, 'SQL-SCRIPT' ); select count(*) into @x from IS_NUM where NO = 0;
if @x > 0 then
update IS_NUM set INTNO18 = @MyID where NO = 0;
end if;
end if;
update IS_SETUP set STAT0 = VersionMajor, STAT1 = VersionMinor, STAT2 = VersionRevision where IDENT = 99001; commit ;
call FAG_DebugPrint(@MyVersion);

call FAG_AddPrimaryKey(
Code (Not a Number, because also externals can store Informatin her inside by API (With Prefix)<br>Prefix by Type:<br>1 = JD_DB_VERSION<br>2 = JD_PK-<TableName><br>3 = JD_PAR_GLOBAL-<ALL>/<Parameter-No> (Unique No will be set by Program/Developer)<br>4 = JD_PAR_LOCAL-<PCNAME oder IP>/<Parameter-No><br>5 = JD_PAR_USER-<JDUSERNAME>/ <Parameter-No><br>6 = UD_<Partner-Prefix>_PAR_GLOBAL-<ALL>/<Parameter-No><br>7 = UD_<Partner-Prefix>_PAR_LOCAL- <PCNAME oder IP>/<Parameter-No><br>8 = UD_<Partner-Prefix>_PAR_USER-<JDUSERNAME>/<Parameter-No>',

'Primärschlüssel<br>ID-Code (Keine Zahl, da hier auch andere per API Parameter anlegen können (Mit Prefix)<br>Prefix nach Typ:<br>1 = JD_DB_VERSION<br>2 = JD_PK-<TableName><br>3 = JD_PAR_GLOBAL-<ALL>/<Parameter-No> (Eindeutige No wird vom Programm/Entwickler Festgelegt)<br>4 = JD_PAR_LOCAL-<PCNAME oder IP>/<Parameter-No><br>5 = JD_PAR_USER-<JDUSERNAME>/<Parameter-No><br>6 = UD_<Partner-Prefix>_PAR_GLOBAL-<ALL>/<Parameter-No><br>7 = UD_<Partner-Prefix>_PAR_LOCAL-<PCNAME oder IP>/<Parameter-No><br>8 = UD_<Partner-Prefix>_PAR_USER- <JDUSERNAME>/<Parameter-No>')
go
call FAG_AddField( 'JD_BASE', 'TYPE', 'decimal(10,0) not null',

'TYP:<br>1 = JD Datenbase Version<br>2 = JD PK Administration of JD Tables (Like IS-NUM, but 1 Table is now 1 Row not 1 Column)<br>3 = JD Parameter Global<br>4 = JD Parameter Local (by PC Name or IP)<br>5 = JD Parameter User (By User) <br>6 = User / Partner defined Parameter Global<br>7 = User / Partner defined Parameter Local<br>8 = User / Partner defined Parameter User<br>100 to 999 Reserverd for JD Basis Administration info like Conutry Settings or other smaller things.<br>1000 to 9999 Reserviert for JD Detail-Info Extensions of other Tables', 'TYP:<br>1 = JD Datenbank Version<br>2 = JD PK Verwaltung der JD Tabellen (Wie IS-NUM, aber eine Tabelle ist 1 Zeile und nicht 1 Spalte)<br>3 = JD Parameter Global<br>4 = JD Parameter Local (Rechnername oder IP bezogen)<br>5 = JD Parameter User (Anwenderbezogen) <br>6 = User / Partner defined Parameter Global<br>7 = User / Partner defined Parameter Local<br>8 = User / Partner defined Parameter User<br>100 bis 999 Reserviert für JD Basis Verwaltungsinfos wie Ländercodes oder andere kleine Infos die selten benötigt werden.<br>1000 bis 9999 Reserviert für JD Detail-Info Erweiterungen anderer Tabellen')
go
call FAG_AddField(

'Optional Foreign Key String',

go
call FAG_AddField(

'JD_BASE', 'Optionaler Fremdschlüssel Integer')

'decimal(10,0)',

go
call FAG_AddField(

'decimal(10,0) not null', 5=DateTime',

go
call FAG_AddField( DataTyp = 0',
go
call FAG_AddField( DataTyp = 1',
go
call FAG_AddField( DataTyp = 2',
go
call FAG_AddField(

'datetime', bei DataTyp = 3')

go
call FAG_AddField(

go

'JD_BASE',
'0=String(Memo), 1=Long Integer, 2=Double, 3=Date, 4=Time, 5=DateTime')

'decimal(10,0)',
'Reihenfolge wenn TYP >= 1000 <= 9999')

'JD_BASE',

'PK',

'Primary Key<br>ID-

'JD_BASE',

'FKS', 'FKI',

'varchar(254)',
'Optionaler Fremdschlüssel String')

'Optional Foreign Key Integer',

'JD_BASE',
'JD_BASE',
'JD_BASE',
'JD_BASE', 'DATADATE',

'Filled by DataTyp = 3',

'JD_BASE', 'SEQUENCE',

'Filled by 'Filled by 'Filled by

'Befüllt

'Sequence if Type >=1000 <=9999',

'DATATYPE',
'0=String(Memo), 1=Long Integer, 2=Double, 3=Date, 4=Time,

'DATAMEMO', 'text', 'Befüllt bei DataTyp = 0')

'DATALONG', 'decimal(10,0)', 'Befüllt bei DataTyp = 1')

'DATADOUBLE', 'decimal(15,8)', 'Befüllt bei DataTyp = 2')

call FAG_AddField( Name',
go
call FAG_AddField( Date',

go
call FAG_AddField( Name',
go
call FAG_AddField( Date',

go

call FAG_AddTable( '',

go
call FAG_AddPrimaryKey( Key',
go
call FAG_AddField(

'JD_BASE', 'JD_BASE', 'JD_BASE', 'JD_BASE',

'CNAME', 'Erstellt von')

'CDATE', 'Erstellt am')

'CHNAME',
'Letzte Änderung von')

'Creation 'Creation 'Change 'Change

'varchar(64) not null', 'Primary

'JD_MDC_FUNCTIONS', 'V5 Functiongroup compatibility',

'V5GROUP',
'V5 Tätigkeitsgruppe für Kompatible Daten bei V5')

go
call FAG_AddField( 'JD_MDC_FUNCTIONS',

'Name/Description for this Function',

'NAME',

'varchar(254)', 'Name / Beschreibung für diese

Tätigkeit')
go
call FAG_AddField(

'varchar(64)',

'JD_MDC_FUNCTIONS', 'Verweis auf Ablauf-ID')

'FKWORKFLOW',
'Reference to Workflow-ID',

'FKLAYOUTWIN',
'Reference to Layout (Windows)',

'FKLAYOUTWEB', 'varchar(64)', 'Verweis auf Layout (Web)')

'ISACTIVE', 'decimal(10,0) not null', '0 = Nicht Aktiv, 1=Aktiv')

'IPFILTER',

'LOGINFILTER',

'CNAME', 'varchar(64)', 'Erstellt von')

'CDATE', 'datetime', 'Erstellt am')

'CHNAME', 'varchar(64)', 'Letzte Änderung von')

'CHDATE', 'datetime', 'Letzte Änderung am')

'PK', 'varchar(64) not null', '')

'PK', 'Primärschlüssel')

go
call FAG_AddField(

'varchar(64)',

go
call FAG_AddField(

'Reference to Layout (Web)',

go
call FAG_AddField(

'0=not active, 1=active',

go
call FAG_AddField(

'varchar(254)', 'Filter mit IP bezug')

go
call FAG_AddField(

'varchar(254)',
'Filter mit Login bezug')

go
call FAG_AddField(

'Creation Name',

go
call FAG_AddField(

'Creation Date',

go
call FAG_AddField(

'Change Name',

go
call FAG_AddField(

'Change Date',

go

call FAG_AddTable( '',

go
call FAG_AddPrimaryKey(

go

'Primary Key',

'JD_MDC_FUNCTIONS',

'PK',

'JD_MDC_FUNCTIONS',

'PK',

'JD_MDC_FUNCTIONS', 'Verweis auf Layout (Windows)')

'JD_MDC_FUNCTIONS',

'JD_MDC_FUNCTIONS',

'JD_MDC_FUNCTIONS', 'Filter by IP',

'JD_MDC_FUNCTIONS', 'Filter by Login',

'JD_MDC_FUNCTIONS', 'JD_MDC_FUNCTIONS', 'JD_MDC_FUNCTIONS', 'JD_MDC_FUNCTIONS',

'JD_MDC_LOGINS', 'JD_MDC_LOGINS',

'CHDATE',
'Letzte Änderung am')

'datetime',

'') 'Primärschlüssel')

'varchar(64)', 'datetime', 'varchar(64)',

'decimal(10,0) not null',

call FAG_AddField(

go
call FAG_AddField(

go
call FAG_AddField(

go
call FAG_AddField(

'Name') 'varchar(254)',

'nvarchar(254)',

'JD_MDC_LOGINS', 'Name',

'JD_MDC_LOGINS',

'JD_MDC_LOGINS', 'Beschreibung')

'NAME', 'varchar(64)',

'PASSWORD', 'Password',

'Passwort')

'DESCR', 'Description',

go
call FAG_AddField( null',
go
call FAG_AddField(

'JD_MDC_LOGINS', '0=not active, 1=active',

go
call FAG_AddField(

'DESCR', 'Description',

'PRESETID',

'JD_MDC_LAYOUTS',
'User defined Text for Tab-Caption1',

'JD_MDC_LAYOUTS', 'TABTEXT2', 'User defined Text for Tab-Caption2',

'JD_MDC_LAYOUTS', 'TABTEXT3', 'User defined Text for Tab-Caption3',

'JD_MDC_LAYOUTS', 'TABTEXT4', 'User defined Text for Tab-Caption4',

go
call FAG_AddField(

'nvarchar(64)',
'Verweis auf Mitarbeiter (optional)')

'WORKERID',

'ISACTIVE', 'decimal(10,0) not '0 = Nicht Aktiv, 1=Aktiv')

'Creation Name',

go
call FAG_AddField(

'Creation Date',

go
call FAG_AddField(

'Change Name',

go
call FAG_AddField(

'Change Date',

go

call FAG_AddTable(
'',

go
call FAG_AddPrimaryKey( Key',
go
call FAG_AddField(

'JD_MDC_LOGINS', 'JD_MDC_LOGINS', 'JD_MDC_LOGINS', 'JD_MDC_LOGINS',

'JD_MDC_LAYOUTS',

'CNAME', 'Erstellt von')

'CDATE', 'Erstellt am')

'CHNAME',
'Letzte Änderung von')

'CHDATE',
'Letzte Änderung am')

'nvarchar(64)', 'datetime', 'nvarchar(64)', 'datetime',

'JD_MDC_LAYOUTS', 'Name',

'JD_MDC_LAYOUTS', 'Beschreibung')

'JD_MDC_LAYOUTS',

'nvarchar(64)',
'Vorlage für verwendetes Layout')

'NAME', 'nvarchar(64)',

'Name') 'nvarchar(254)',

go
call FAG_AddField(

'nvarchar(64)',

Tab-Element1')
go
call FAG_AddField(

'nvarchar(64)',

Tab-Element2')
go
call FAG_AddField(

'nvarchar(64)',

Tab-Element3')
go
call FAG_AddField(

'nvarchar(64)', Tab-Element4')

'TABTEXT1',

'JD_MDC_LOGINS',
'Reference to Worker (optional)',

'JD_MDC_LAYOUTS', 'PK', 'Primärschlüssel')

'Preset for Layout',

'PK',

'nvarchar(64) not null', 'Primary

'')

'Benutzerdefinierter Text für

'Benutzerdefinierter Text für

'Benutzerdefinierter Text für

'Benutzerdefinierter Text für

go
call FAG_AddField(

Sichtbar, 1=Sichtbar') go
call FAG_AddField(

Sichtbar, 1=Sichtbar') go
call FAG_AddField(

Sichtbar, 1=Sichtbar') go
call FAG_AddField(

Sichtbar, 1=Sichtbar') go
call FAG_AddField(

'decimal(5,0)',

go
call FAG_AddField(

'Script for GUI Load Event',

go
call FAG_AddField(

'Creation Name',

go
call FAG_AddField(

'Creation Date',

go
call FAG_AddField(

'Change Name',

go
call FAG_AddField(

'Change Date',

go

call FAG_AddTable(
'',

go
call FAG_AddPrimaryKey( Key',
go
call FAG_AddField(

'nvarchar(64)',

'JD_MDC_LAYOUTS', '0=not Visible, 1=Visible',

'JD_MDC_LAYOUTS', '0=not Visible, 1=Visible',

'JD_MDC_LAYOUTS', '0=not Visible, 1=Visible',

'JD_MDC_LAYOUTS', '0=not Visible, 1=Visible',

'JD_MDC_LAYOUTS',
'0 = Kein Touch, 1=Touch')

'JD_MDC_LAYOUTS', 'JD_MDC_LAYOUTS', 'JD_MDC_LAYOUTS', 'JD_MDC_LAYOUTS', 'JD_MDC_LAYOUTS',

'JD_MDC_LAYOUTDATA',

'TABVISIBLE1', 'decimal(5,0)',

'TABVISIBLE2', 'decimal(5,0)',

'TABVISIBLE3', 'decimal(5,0)',

'TABVISIBLE4', 'decimal(5,0)',

'TOUCHMODE',
'0=No Touch, 1=Touch',

'SCRIPT_LOAD', 'text',
'Script bei GUI Load Ereignis')

'CNAME', 'nvarchar(64)', 'Erstellt von')

'CDATE', 'datetime', 'Erstellt am')

'CHNAME', 'nvarchar(64)', 'Letzte Änderung von')

'CHDATE', 'datetime', 'Letzte Änderung am')

'0 = Nicht

'0 = Nicht

'0 = Nicht

'0 = Nicht

go
call FAG_AddField(

go
call FAG_AddField(

'nvarchar(64)',

Element')
go
call FAG_AddField(

'nvarchar(64)',

GUI-Element')
go
call FAG_AddField(

'LAYER',

'JD_MDC_LAYOUTDATA', 'Reference GUI Input ElementID',

'JD_MDC_LAYOUTDATA', 'PROPERTYID', 'Property ID to GUI Input Element',

'JD_MDC_LAYOUTDATA', 'ALTERNATECAPTION', 'Alternative Caption',

go

'nvarchar(16)',

'nvarchar(64)', 'Alternativ-Titel')

'JD_MDC_LAYOUTDATA', 'Primärschlüssel')

'JD_MDC_LAYOUTDATA', 'Verweis auf Layout')

'JD_MDC_LAYOUTDATA', 'Ebene (Eingabebox, Buttons, Tabs...)')

'PK', 'nvarchar(64) not null', '')

'PK', 'Primary 'LFK',

'Foreign Key to Layot',

'Layer (Inputbox, Button, Tab...)',

'Verweis auf GUI-

'PropertyID zum

'ELEMENTID',

call FAG_AddField( 'nvarchar(64)',

'Vorlage Text')

go
call FAG_AddField(

'nvarchar(24)',

go
call FAG_AddField(

'nvarchar(64)', 'Barcode-Schlüssel')

go
call FAG_AddField(

'decimal(5,0)',

go
call FAG_AddField(

'decimal(5,0)',

go
call FAG_AddField(

'decimal(5,0)',

'JD_MDC_LAYOUTDATA', 'Preset for Input',

'JD_MDC_LAYOUTDATA',

'FORMAT (HH:MM:SS...)')

'JD_MDC_LAYOUTDATA', 'Barcode-Key',

'PRESETSTRING',

'VIEWFORMAT',
'FORMAT (HH:MM:SS...)',

'BARCODEKEY',

go
call FAG_AddField(

'JD_MDC_LAYOUTDATA', '1 = Sichtbarkeit per Workflow möglich')

go
call FAG_AddField( 'JD_MDC_LAYOUTDATA',

'decimal(5,0)',
'1 = An/Abschaltung per Workflow möglich')

go
call FAG_AddField( 'JD_MDC_LAYOUTDATA',

'decimal(5,0)',
'1 = Keine Leere Eingabe oder 0 Erlaubt')

go
call FAG_AddField( 'JD_MDC_LAYOUTDATA',

'decimal(5,0)',
'1 = Eingabevalidierung bei Lostfocus')

go
call FAG_AddField( 'JD_MDC_LAYOUTDATA',

'decimal(5,0)',
'1 = Farbe bei Eingabevalidierung anzeigen')

go
call FAG_AddField( 'JD_MDC_LAYOUTDATA',

'nvarchar(64)', 'Property Value', 'Property Wert zum GUI-Element')

'ALLOWVISIBLEWF',
'1=Allow Change for Visible by Workflow',

'ALLOWENABLEWF',
'1=Allow Chnage for Enabled by Workflow',

'DONOTALLOWNOINPUT',
'1=Do not allow empty Input-Textbox',

'VALIDATELOSTFOCUS',
'1=Validation Checks on Textinput Lostfocus',

'SHOWVALIDATERESULT',
'1=Show Color for Validation Checks',

'PROPERTYVALUE',

'PROPERTYMEMO',
'Property Memo for Scripts',

go

'decimal(5,0)',

go
call FAG_AddField(

'text',

go
call FAG_AddField(

'decimal(5,0)',

Tab')
go
call FAG_AddField(

'Creation Name',

go
call FAG_AddField(

'Creation Date',

go
call FAG_AddField(

'Change Name',

go
call FAG_AddField(

'Property

'JD_MDC_LAYOUTDATA', Wert für Scripts')

'Change Date',

'JD_MDC_LAYOUTDATA',
'0 = Kein Barcoe, 1 = Barcode verwenden')

'JD_MDC_LAYOUTDATA', '0 = Nicht Sichtbar, 1=Sichtbar')

'ISVISIBLE',
'0=Not Visible, 1=Visible',

'JD_MDC_LAYOUTDATA',
'0 = Abgeschaltet, 1=Angeschaltet')

'JD_MDC_LAYOUTDATA',
'Element Position for Tab-Elememt Sequence',

'JD_MDC_LAYOUTDATA', 'JD_MDC_LAYOUTDATA', 'JD_MDC_LAYOUTDATA', 'JD_MDC_LAYOUTDATA',

'USEBARCODE',
'0=No Barcode, 1=Barcode',

'ISENABLED',
'0=Not Enabled, 1=Enabled',

'ELEMENTPOSITION',
'Elememt Reihenfolge für Anordnung im

'CNAME', 'nvarchar(64)', 'Erstellt von')

'CDATE', 'datetime', 'Erstellt am')

'CHNAME', 'nvarchar(64)', 'Letzte Änderung von')

'CHDATE', 'datetime', 'Letzte Änderung am')

call FAG_AddTable( '',

go
call FAG_AddPrimaryKey( Key',

go
call FAG_AddField(

'TYP (Future use)',

zukünftige Entwicklungen') go
call FAG_AddField(

'Name',

go
call FAG_AddField(

'Description and comments',

go
call FAG_AddField(

'JD_MESDUMP', 'JD_MESDUMP',

'JD_MESDUMP',

'JD_MESDUMP',

'JD_MESDUMP',

'PK', 'nvarchar(254) not null', '')

'PK', 'Primary 'Primärschlüssel')

go

'JD_MESDUMP', 'Filled with DUMP as XML as ZIP',

'DESCR', 'text',
'Beschreibung und Kommentar')

'DATAMEMO', 'longtext',
'Befüllt mit DUMP in XML als ZIP')

go
call FAG_AddField(

'Schedule Date from',

go
call FAG_AddField(

'JD_MESDUMP',

'JD_MESDUMP',

'JD_MESDUMP', 'Count Singleparts',

'JD_MESDUMP', 'Count Fragments',

'JD_MESDUMP', 'Count Green',

'JD_MESDUMP', 'Count Light Green',

'JD_MESDUMP', 'Count Yellow',

'JD_MESDUMP', 'Count Orange',

'JD_MESDUMP', 'Count Red',

'JD_MESDUMP', 'JD_MESDUMP', 'JD_MESDUMP', 'JD_MESDUMP',

'SCHEDULEFROM', 'Planung von')

'SCHEDULETO', 'datetime', 'Planung bis')

'Schedule Date to', call FAG_AddField(

'datetime',

'decimal(10,0)',

'decimal(10,0)',

'decimal(10,0)',

'decimal(10,0)',

'decimal(10,0)',

'decimal(10,0)',

'decimal(10,0)',

'nvarchar(64)',

go

'Anzahl Einzelteile') call FAG_AddField(

'COUNTSINGLEPARTS',

'COUNTFRAGMENTS',

'COUNT1',

'COUNT2',

'COUNT3',

'COUNT4',

'COUNT5',

'CNAME',

go

'Anzahl Fragmente') call FAG_AddField(

'Anzahl Grün') call FAG_AddField(

go

'Anzahl Hellgrün') call FAG_AddField(

go

'Anzahl Gelb') call FAG_AddField(

go

'Anzahl Orange') call FAG_AddField(

'Anzahl Rot')

go
call FAG_AddField(

'Creation Name',

go
call FAG_AddField(

'Creation Date',

go
call FAG_AddField(

'Change Name',

go
call FAG_AddField(

go

go

'Change Date',

'CDATE', 'datetime', 'Erstellt am')

'CHNAME', 'nvarchar(64)', 'Letzte Änderung von')

'CHDATE', 'datetime', 'Letzte Änderung am')

'TYPE',

'decimal(10,0) not null', 'TYP Für

'NAME', 'nvarchar(64)',

'Erstellt von')

'Name')

call FAG_AddTable( 'JD_NOTE', 'nvarchar(254) not null',

'',

'FK',

'FK',

'') go

call FAG_AddIndex(
'Foreign Key (see type)',

'JD_NOTE',

go
call FAG_AddField(

go
call FAG_AddIndex(

'TYP',

go
call FAG_AddField(

go
call FAG_AddField(

von')
go
call FAG_AddField(

'Erstellt am') call FAG_AddField(

go

'JD_NOTE', 'decimal(10,0) not null',

'TYP 1=Fragment') 'JD_NOTE',

'TYPE', 'TYP (1=Fragment)',

'TYPE',

'NOTEDATA', 'Notiz Daten')

'text',

'nvarchar(64)',

'JD_NOTE',

'JD_NOTE',
'Creation Name',

'CNAME',

'CDATE',

'CHNAME',

'CHDATE',

'TYP') 'Note Data',

'Erstellt

'nvarchar(64)', 'Letzte Änderung von')

go
call FAG_AddField(

go

'datetime', 'Letzte Änderung am')

go

call FAG_AddTable( 'nvarchar(64) not null',

'') go

call FAG_AddIndex( Translation',

go
call FAG_AddField(

'decimal(10,0) not null',

Geändert)')
go
call FAG_AddIndex(

go
call FAG_AddField(

'nvarchar(16) not null', (DEU, DEA, DES... <ALL>)')

go
call FAG_AddIndex(

'Country',

go
call FAG_AddField(

'nvarchar(2048)',

go
call FAG_AddField(

'JD_TRANSLATION', '',

'JD_TRANSLATION',

'JD_TRANSLATION',
'TYP (0=Standard, 1

'TXTID',

'TXTID',

'ID for

'nvarchar(16)',

'PARAMETERID',
'Parameter ID if TYP = 10',

'datetime',

'JD_NOTE',
'Creation Date',

'JD_NOTE',
'Change Name',

'JD_NOTE',
'Change Date',

'JD_TRANSLATION',

'JD_TRANSLATION', 'Country (DEU, DEA,

'JD_TRANSLATION',

'JD_TRANSLATION', 'Übersetzter Text')

'JD_TRANSLATION', 'Parameter ID wenn TYP = 10)')

'TYP')

'TYPE',

'COUNTRY', DES... <ALL>)',

'COUNTRY',

'TRANSLATION', 'Translated Text',

'TYP (0=Standard, 1= User, 2 = 'TYP',

'Verweis auf... (je nach Typ)')

'ID für Übersetzung')

'TYPE',
= User, 2 = Changed, 10 = Parameter)',

'Land

'Land')

call FAG_AddField( 'decimal(10,0)',

Time, 4 = Integer, 5 = Double)', Integer, 5 = Double)')
go
call FAG_AddField(

'nvarchar(64)',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'nvarchar(64)',

von')
go
call FAG_AddField(

'datetime',

am')
go
call FAG_AddTable(

'nvarchar(64) not null', '')

go
call FAG_AddIndex(

(GUID)',

go
call FAG_AddField(

'nvarchar(64)',

go
call FAG_AddIndex(

Config PK)',

go
call FAG_AddField(

'decimal(10,0) not null', 'Sequenz-Nr. für Sortierung')

'JD_TRANSLATION', 'PARAMETERTYPE',
'Parameter Type (0=String, 1 = Date, 2 = DateTime, 3 =

'Parameter TYP (0=String, 1 = Date, 2 = DateTime, 3 = Time, 4 =

go
call FAG_AddField(

go
call FAG_AddField(

'JD_ADDINFO', 'decimal(10,0) not null',

'Modus ()') 'JD_ADDINFO',

'MODE', 'Mode ',

'TYPE',

'decimal(10,0) not null',

go
call FAG_AddIndex(

'',

go

call FAG_AddField( 'text',

call FAG_AddField( 'nvarchar(254)',

'VALUE',

Combobox)',
'Vorlage oder Eingabe-Wert2 (z.Bspl. ID bei Combobox)')

'VALUE2',
'Preset or Dataentry Value 2 (exx. ID when

'FORMAT',

go

call FAG_AddField( 'JD_ADDINFO',

'JD_TRANSLATION', 'Creation Name',

'JD_TRANSLATION', 'Creation Date',

'JD_TRANSLATION', 'Change Name',

'JD_TRANSLATION', 'Change Date',

'CNAME', 'CDATE', 'CHNAME',

'CHDATE',

'PK',

'PK',

'Erstellt von') 'Erstellt am') 'Letzte Änderung

'Letzte Änderung

'Primary Key

'Foreign Key (To

'JD_ADDINFO',

'JD_ADDINFO',

'JD_ADDINFO',

'JD_ADDINFO',

'JD_ADDINFO',
'Sequence-No for Sort',

'') 'JD_ADDINFO',

'',

'TYPE',
'Typ (0=String, 1=Datum, 2=Uhrzeit, 3=DatumUhrzeit, 4=Ganzzahl, 5=Gleitkomma, 6=Combobox, 7=Ja/Nein)')

1=Date, 2=Time, 3=DT, 4=Int, 5=Dbl, 6=Combo, 7=Yes/No)', go

'Type (0=String,

'Name für

'Preset or Dataentry Value',

call FAG_AddField(

Datensatz') go

'nvarchar(64)',

'JD_ADDINFO', 'NAME', 'Name for Dataentry',

'JD_ADDINFO', 'Vorlage oder Eingabe-Wert')

'JD_ADDINFO',

'Primärschlüssel (GUID)') 'FK',
'')
'FK',

'Fremdschlüssel (auf Konfig Datensatz)')

'SEQNUM',

'',

go

go

call FAG_AddField( 'nvarchar(254)',

call FAG_AddIndex(

'JD_ADDINFO',

'JD_ADDINFO',

'JD_ADDINFO',
'Script für das füllen wenn z.Bspl. Combobox')

go

go

go

go

go

go

go

go

call FAG_AddIndex( 'Foreign Key',

call FAG_AddField(

'JD_BOM', 'nvarchar(64) not null', '',

'nvarchar(32)', 'Format if Type is Date or Number', 'Format falls Type = Datum oder Nummer')

Foreign Key based by Mode dependant Key', go

call FAG_AddField( 'text',

Combobox...', go

'SCRIPT',

von') go

am') go

call FAG_AddField( 'nvarchar(64)',

call FAG_AddField( 'datetime',

'JD_ADDINFO', 'Creation Name',

'JD_ADDINFO', 'Creation Date',

'CNAME',

'CDATE',

'Erstellt

'Erstellt

call FAG_AddField( 'nvarchar(64)',

von') go

call FAG_AddField( 'datetime',

am')
go
call FAG_AddTable(

'JD_ADDINFO', 'CHNAME', 'Change Name',

'JD_ADDINFO', 'CHDATE', 'Change Date',

'JD_BOM', 'PK',

'Letzte Änderung

'Letzte Änderung

'Primär Schlüssel')

'Verweis auf PK')

call FAG_AddIndex( 'Primary Key',

'JD_BOM',

'JD_BOM',

'PK',

'FK',

'TLFK',

go
call FAG_AddField(

'FK',

call FAG_AddField(

'JD_BOM', 'nvarchar(254) not null',

'')

'nvarchar(64) not null',

'',

'nvarchar(64) not null', '')

'', 'JD_BOM',

call FAG_AddIndex(
'Foreign Key to PK on Top Level',

'') 'JD_BOM',

'TLFK',
'Verweis auf PK der obersten Ebene')

'TYPEFK',

'') 'Foreign Key to External Table/File by NodeType',

call FAG_AddIndex(

'JD_BOM',

'TYPEFK',
'Verweis auf PK einer externen Tabelle oder Datei je nach NodeType')

call FAG_AddField(

'JD_BOM', 'nvarchar(4000) not null',

'PATH',

'',

'MATCHCODE',

'MATCHCODE',
'Verweiß basierend auf Dynamischen Matchkey')

'')

'')

'',

'',

'Script for Fill Type

'Matchcode

call FAG_AddIndex( 'JD_BOM', 'Path for simple selection with wildcrads',

go
call FAG_AddField( 'JD_BOM',

'PATH',
'Pfad für einfache Selection mit Platzhaltern')

go

go

go

go

go

go

go

go

go

call FAG_AddField(

'JD_BOM', 'decimal(10,0) not null',

'LEVEL',

'Stufe (0=Oberste Ebene)') 'SEQNUM',

'SEQNUM',
'Reihenfolge auf der gleichen Ebene') 'NODETYPE',

'decimal(10,0) not null',

call FAG_AddIndex( 'Level (0=Top-Level)',

'', 'JD_BOM',

call FAG_AddIndex( 'Sequence on same Level',

'JD_BOM',

'')

'')

'')

'',

'',

'NODETYPE',
'TYP zu externen daten zu diesem Knoten')

'PPARTS',

'',

'PPARTS', 'Sollstück wenn Einzelteil nur als Link')

call FAG_AddField(

'JD_BOM', 'decimal(10,0) not null',

call FAG_AddIndex(
'TYPE for External Data to this Node',

'JD_BOM',

call FAG_AddField(

call FAG_AddIndex(
'Planned Parts when Singlepart is Linked',

call FAG_AddField( 'Erstellt von')
call FAG_AddField( 'Erstellt am')

'nvarchar(64)',

'JD_BOM',
'Creation Name',

'JD_BOM',
'Creation Date',

'CNAME',

'CDATE',

'CHNAME',

'CHDATE',

go
call FAG_AddField(

'datetime', 'JD_BOM',

Änderung von') go

call FAG_AddField( 'datetime',

Änderung am')
go
call FAG_AddTable(

'nvarchar(254) not null',

go
call FAG_AddIndex( Key',

go
call FAG_AddField(

'PK',

'nvarchar(64)',

'Change Name',

'JD_BOM',
'Change Date',

'JD_REPORTS', '',

'') 'JD_REPORTS',

'JD_REPORTS',

'Letzte

'Letzte

'nvarchar(254)',
'Pfad und dateiname des Reports')

go
call FAG_AddField( 'JD_REPORTS',

go

'JD_BOM', 'decimal(10,8) not null',

'nvarchar(254)', 'Description of Report', 'Beschreibung des Reports')

'')

'JD_BOM',

'Path to Report-File',

'PK', 'Primary 'Primärschlüssel')

'FULLPATH',

'DESCR',

'LEVEL',

call FAG_AddField( 'decimal(10,0)',

der Kopien beim Druck') go
call FAG_AddField(

'nvarchar(254)', 'Vorlage des Druckers')

go
call FAG_AddField(

'decimal(10,0)', 'Druckerschacht')

go
call FAG_AddField(

'text',

go
call FAG_AddField(

'text',

go
call FAG_AddField(

'decimal(10,0)', 'Anzeige Firmenlogo')

go
call FAG_AddField(

'decimal(10,0)', 'Spezieller Parameter')

go
call FAG_AddField(

'JD_REPORTS',
'Number of Copys to Print',

'JD_REPORTS', 'Preset Printer-Name',

'JD_REPORTS', 'Tray to Print',

'JD_REPORTS',

'JD_REPORTS',

'JD_REPORTS', 'Show Company

'DUPLICATES',

'PRINTERNAME',

'PRINTERTRAY',

'Anzahl

'decimal(10,0)',
'Automatisches Upadate (1=Ja)')

go
call FAG_AddField(

'nvarchar(64)',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'nvarchar(64)',

von')
go
call FAG_AddField(

'datetime',

am') go

call FAG_AddTable( 'nvarchar(254) not null',

go
call FAG_AddIndex( Key',

go
call FAG_AddField(

'nvarchar(254)', 'Planbezeichnung')

go
call FAG_AddField(

'decimal(10,0)',
'0=Nur Leserecht,1=Schreib- und leserecht')

go
call FAG_AddField(

'nvarchar(64)',

go
call FAG_AddField(

'JD_EXCHANGE_PLAN', 'Creation Name',

'JD_EXCHANGE_PLAN', 'Creation Date',

go

'datetime',

'JD_REPORTS', 'Special Parameter',

'JD_REPORTS',

'SPECIAL',

'CANUPDATE',

'CNAME', 'CDATE', 'CHNAME',

'CHDATE',

'PK',

'PK',

'SYNCPLAN',

'READONLY',

'CNAME', 'CDATE',

'Automatic Update (1=YES)',

'JD_REPORTS', 'Creation Name',

'JD_REPORTS', 'Creation Date',

'JD_REPORTS', 'Change Name',

'JD_REPORTS', 'Change Date',

'JD_EXCHANGE_PLAN', '',

'') 'JD_EXCHANGE_PLAN',

'JD_EXCHANGE_PLAN', 'Name of the plan',

'JD_EXCHANGE_PLAN',

'Primärschlüssel')

'Erstellt von') 'Erstellt am') 'Letzte Änderung

'Letzte Änderung

'Primary

'Erstellt von') 'Erstellt am')

'0=readonly,1=all',

Logo',

'TAKEOVER', 'Übergabe an Report') 'VBSCRIPT',

'VB-SCript') 'COMPANYLOgo',

'Takeover',

'VB-Script',

call FAG_AddField( 'nvarchar(64)',

von')
go
call FAG_AddField(

'datetime',

am') go

call FAG_AddTable( 'nvarchar(254) not null',

'JD_EXCHANGE_PLAN', 'Change Name',

'JD_EXCHANGE_PLAN', 'Change Date',

'JD_EXCHANGE_USER', '',

'') 'JD_EXCHANGE_USER',

'JD_EXCHANGE_USER', 'Username',

'CHNAME',

'CHDATE',

'PK',

'PK',

'NAME',

'Letzte Änderung

'Letzte Änderung

'Primary

'Verweis auf

'Konto-

'Konto-

'Erstellt von') 'Erstellt am') 'Letzte Änderung

'Letzte Änderung

go
call FAG_AddIndex( Key',

go
call FAG_AddField(

go
call FAG_AddField(

'decimal(10,0)',

gültigen Syncplan') go
call FAG_AddField(

'nvarchar(254)', 'Serverbezeuchnung')

go
call FAG_AddField(

'nvarchar(254)',

Bennutzername') go
call FAG_AddField(

'nvarchar(254)',

Passwort')
go
call FAG_AddField(

'decimal(10,0)',
'0=Benutzer deaktiviert,1=Benutzer aktiv')

'Primärschlüssel')

'nvarchar(254)', 'Benutzername')

go
call FAG_AddField(

'nvarchar(64)',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'nvarchar(64)',

von')
go
call FAG_AddField(

'datetime',

am')
go
call FAG_AddTable(

go
call FAG_AddField( 'JD_REPORT_TEMP',

'nvarchar(254)', 'Temp Takeover String 02', 'Temporärer Übergabe String 02')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'nvarchar(254)', 'Temp Takeover String 03',

'nvarchar(254)',

'JD_EXCHANGE_USER',
'Foreign Key to PK Exchange_Plan',

'JD_EXCHANGE_USER', 'Servername',

'JD_EXCHANGE_USER', 'Name from Useraccount',

'JD_EXCHANGE_USER', 'Password of Useraccount',

'JD_EXCHANGE_USER',

'SERVER',

'ACCOUNTNAME',

'PASSWORD',

'STATUS',

'CNAME', 'CDATE', 'CHNAME',

'CHDATE',

'0=not activ, 1=activ',

'JD_EXCHANGE_USER', 'Creation Name',

'JD_EXCHANGE_USER', 'Creation Date',

'JD_EXCHANGE_USER', 'Change Name',

'JD_EXCHANGE_USER', 'Change Date',

'JD_REPORT_TEMP', 'Temporärer Übergabe String 01')

'JD_REPORT_TEMP_STR01', 'Temp Takeover String 01',

'JD_REPORT_TEMP_STR02',

'JD_REPORT_TEMP_STR03',

'PLANID',

'Temporärer Übergabe String

go
call FAG_AddField(

'nvarchar(254)',
'Temporärer Übergabe String

go
call FAG_AddField(

'nvarchar(254)',
'Temporärer Übergabe String

go
call FAG_AddField(

03')

'JD_REPORT_TEMP', 'Temp Takeover String 04', 04')

'JD_REPORT_TEMP', 'Temp Takeover String 05', 05')

'JD_REPORT_TEMP',

'decimal(10,0)',
'Temporäre Übergabe Long-Int 01')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(10,0)', 'Temp Takeover Long 02', 'Temporäre Übergabe Long-Int 02')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(10,0)', 'Temp Takeover Long 03', 'Temporäre Übergabe Long-Int 03')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(10,0)', 'Temp Takeover Long 04', 'Temporäre Übergabe Long-Int 04')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(10,0)', 'Temp Takeover Long 05', 'Temporäre Übergabe Long-Int 05')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(15,8)', 'Temp Takeover Double 01', 'Temporäre Übergabe Double 01')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(15,8)', 'Temp Takeover Double 02', 'Temporäre Übergabe Double 02')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(15,8)', 'Temp Takeover Double 03', 'Temporäre Übergabe Double 03')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(15,8)', 'Temp Takeover Double 04', 'Temporäre Übergabe Double 04')

go
call FAG_AddField( 'JD_REPORT_TEMP',

'decimal(15,8)', 'Temp Takeover Double 05', 'Temporäre Übergabe Double 05')

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'datetime',

go
call FAG_AddField(

'datetime',

'JD_REPORT_TEMP', 'Temporäre Übergabe 'JD_REPORT_TEMP', 'Temporäre Übergabe 'JD_REPORT_TEMP', 'Temporäre Übergabe 'JD_REPORT_TEMP', 'Temporäre Übergabe 'JD_REPORT_TEMP', 'Temporäre Übergabe

'Temp Takeover Long 01',

'JD_REPORT_TEMP_STR04',

'JD_REPORT_TEMP_STR05',

'JD_REPORT_TEMP_LNG01',

'JD_REPORT_TEMP_LNG02',

'JD_REPORT_TEMP_LNG03',

'JD_REPORT_TEMP_LNG04',

'JD_REPORT_TEMP_LNG05',

'JD_REPORT_TEMP_DBL01',

'JD_REPORT_TEMP_DBL02',

'JD_REPORT_TEMP_DBL03',

'JD_REPORT_TEMP_DBL04',

'JD_REPORT_TEMP_DBL05',

'JD_REPORT_TEMP_DAT01',
'Temp Takeover Date 01',

Datum 01')

'JD_REPORT_TEMP_DAT02',
'Temp Takeover Date 02',

Datum 02')

'JD_REPORT_TEMP_DAT03',
'Temp Takeover Date 03',

Datum 03')

'JD_REPORT_TEMP_DAT04',
'Temp Takeover Date 04',

Datum 04')

'JD_REPORT_TEMP_DAT05',
'Temp Takeover Date 05',

Datum 05')

go
DROP PROCEDURE IF EXISTS EXTWORKCAPACITYRESOURCE; go
CREATE PROCEDURE EXTWORKCAPACITYRESOURCE()

BEGIN
set @x=0;

set @y=0;
select count(*) into @x from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OR_OP'; IF (@x > 0) THEN

select count(*) into @y from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = 'OR_OP' and COLUMN_NAME = 'EXTWORKCAPACITYRESOURCE';

IF (@y > 0) THEN set @y=0;

ELSE

call FAG_AddField( 'NVARCHAR(16)',

Fremdvergabe alternativer Kalender'); END IF;

ELSE

set @y=0; END IF;

'OR_OP', 'EXTWORKCAPACITYRESOURCE', 'Alternative Calendar for External Work', 'Bei

END
go
call EXTWORKCAPACITYRESOURCE();
go
DROP PROCEDURE IF EXISTS ARDORGRESOURCE; go
CREATE PROCEDURE ARDORGRESOURCE()
BEGIN

set @x=0; set @y=0;

select count(*) into @x from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OR_OP'; IF (@x > 0) THEN

select count(*) into @y from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = 'OR_OP' and COLUMN_NAME = 'ARDORGRESOURCE';

IF (@y > 0) THEN set @y=0;

ELSE

call FAG_AddField( 'OR_OP', 'ARDORGRESOURCE', 'NVARCHAR(16)', 'Original Resource before ARD Movement to alternative Resource', 'Original Resource bevor die Automatische Verteilung die Resource geändert hat');

END IF; ELSE
set @y=0;

END IF; END

go
call ARDORGRESOURCE();
go
DROP PROCEDURE IF EXISTS ARCHIV; go
CREATE PROCEDURE ARCHIV()
BEGIN

set @x=0; set @y=0;

select count(*) into @x from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CU_PERS'; IF (@x > 0) THEN

select count(*) into @y from information_schema.COLUMNS where TABLE_SCHEMA = ( select DATABASE() ) and TABLE_NAME = 'CU_PERS' and COLUMN_NAME = 'ARCHIV';

ELSE

END IF; ELSE

set @y=0; END IF;

END

call FAG_AddField(

'CU_PERS',
'Aktueller Status der Mitarbeiter/Person');

IF (@y > 0) THEN set @y=0;

'DECIMAL(5,0)',

'ARCHIV',
'Current Status for the person',

go
call ARCHIV();
go
DROP PROCEDURE IF EXISTS JD_DB_VERSION; go
CREATE PROCEDURE JD_DB_VERSION()

BEGIN
set @x=0;

select count(*) into @x from JD_BASE WHERE PK = 'JD_DB_VERSION'; IF (@x > 0) THEN

Update JD_BASE SET DATATYPE = 1, DATALONG = 25, CHDATE = current_date( ), CHNAME = 'JobDISPO Update Script' WHERE PK = 'JD_DB_VERSION';

ELSE
INSERT INTO JD_BASE

(PK,TYPE,FKS,FKI,DATATYPE,DATAMEMO,DATALONG,DATADOUBLE,DATADATE,SEQUENCE,CNAME,CDATE) VALUES ('JD_DB_VERSION',1,'',0,1,NULL,6,NULL,NULL,NULL,'JobDISPO Update Script',GetDate());

END IF; END

****************************************************************************************