Moin,
ich verwede zwei Selbstgescriptete Datenbankscripte. Die funktionieren beide einzeln ohne Probleme. Zusammen werden sie zwar richtig im Baum angezeigt nur bei der Suche liefert die Erste von beiden Datenbanken einen Fehler zurück. Die zweite Dagegen läuft ohne schwierigkeiten.
Gruß Thomas
Was für ein Fehler ist es denn? Die Fehlermeldung wäre hilfreich zu wissen.
Ach, außerdem wäre interessant zu wissen, wie der Code von SearchItems aussieht. Vielleicht ist ja einfach nur ein Tippfehler in der SQL-Anfrage oder so?
Hi, das script kennst du warscheinlich schon ^^
var
connection: IZConnection;
ZenonUtil: TStringList;
iconutil: TStringList;
const
zenontypes = '(0,1,2,3,4,5,6,7,8,25,26)'; //Music Types
Jingeltypes = '(23,39,45,78)'; //Jingle Types
werbungtypes = '(30)'; //Advertisement
trashtypes = '(69)'; //Trash (older zenon Versions)
fzenonutil = 'C:\zenonutil.ini'; //path & filename of the Zenonutil.ini
ficonfile = 'C:\iconfile.ini'; //holds path & Filename to icons
useicons = 1;
fileext = '.mp2'; //fileextention for Soundfiles
sql_dontselectdeleted = ' deleted = 0 '; //Where Statement to hide the deleted files (newer Versions)
// sql_dontselectdeleted = ' num_types not in '+trashtypes+' '; //where statement to hide the deleted files (older version)
sql_defaultwhere=' num_type in '+zenontypes;
ntArtistRoot = ntCustom + 1;
ntArtist = ntCustom + 2;
ntRotationRoot = ntCustom + 3;
ntRotation = ntCustom + 4;
ntPlaylistYear = ntCustom + 5;
ntPlaylistWeek = ntCustom + 6;
ntPlaylistDay = ntCustom + 7;
ntJingelRoot = ntCustom + 8;
ntJingel = ntCustom + 9;
ntAdvRoot = ntCustom + 10;
ntadv = ntCustom + 11;
ntRotationArtist = ntCustom + 12;
ntTitleRoot = ntCustom + 13;
ntTitle = ntCustom + 14;
// The Login Data of the mysql server
procedure Connect;
begin
connection := ZeosDriverManager.GetConnectionWithLogin('zdbc:mysql-3.23://sqlserver/datenbank', 'username', 'password');
NotifyConnect;
end;
procedure Connectutil;
begin
zenonUtil := TStringList.Create;
ZenonUtil.LoadFromFile(fzenonutil);
end;
procedure Disconnectutil;
begin
ZenonUtil.Free;
end;
function GetPath(t: integer): string;
var
s_tmp: string;
begin
s_tmp := ZenonUtil.Values['Type' + IntToStr(t)];
if s_tmp = '' then
begin
s_tmp := ZenonUtil.Values['DefaultPath'];
end
Result := s_tmp;
end;
procedure Connecticon;
begin
iconUtil := TStringList.Create;
iconUtil.LoadFromFile(ficonfile);
end;
procedure Disconnecticon;
begin
iconUtil.Free;
end;
function Geticonfromfile(t: integer): string;
var
s_tmp: string;
begin
s_tmp := iconUtil.Values['Type' + IntToStr(t)];
if s_tmp = '' then
begin
s_tmp := iconUtil.Values['Defaulticon'];
end
Result := s_tmp;
end;
function Trailing0(i: integer): string;
begin
if i < 10 then
Result := '0' + IntToStr(i)
else
Result := IntToStr(i);
end;
function IdToPath(id: int64): string;
begin
Result :=
Trailing0(id div 100000000 mod 100) + '\' +
Trailing0(id div 1000000 mod 100) + '\' +
Trailing0(id div 10000 mod 100) + '\' +
Trailing0(id div 100 mod 100) + '\' +
Trailing0(id mod 100);
end;
procedure Disconnect;
begin
connection := nil;
NotifyDisconnect;
end;
function IsConnected: boolean;
begin
Result := connection <> nil;
end;
function GetUniqueID: string;
begin
Result := 'zenon32_v3b';
end;
function GetCaption: string;
begin
Result := 'Zenondatenbank';
end;
function GetCapabilities: TDatabaseCapabilities;
begin
Result := [dcUpdateItem, dcPersistentIDs];
end;
procedure FillDatabaseTree(iTree: IDatabaseTree; iParent: integer;
iThisDatabase: IDatabase; iNodeType: TDatabaseTreeNodeType; iCaption:
string;
iDuration: int64; iExpectChildren: boolean;
iData0, iData1, iData2, iData3: integer); var
stmt: IZPreparedStatement;
rs: IZResultSet;
i: integer;
begin
if iNodeType = ntRoot then begin
iTree.AddNode(iParent, iThisDatabase, ntArtistRoot, 'Artist', true, 0, 0, 0, 0, 0);
iTree.AddNode(iParent, iThisDatabase, ntTitleRoot, 'Titel', true, 0, 0, 0, 0, 0);
iTree.AddNode(iParent, iThisDatabase, ntJingelRoot, 'Jingels', true, 0, 0, 0, 0, 0);
iTree.AddNode(iParent, iThisDatabase, ntAdvRoot, 'Werbung', true, 0, 0, 0, 0, 0);
iTree.AddNode(iParent, iThisDatabase, ntRotationRoot, 'Rotations', true, 0, 0, 0, 0, 0);
end
else if iNodeType = ntArtistRoot then begin
stmt := connection.PrepareStatement('SELECT DISTINCT name FROM t_rotation where '+sql_defaultwhere+' AND '+sql_dontselectdeleted+' ORDER BY name');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntArtist, rs.GetString(1), true, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntArtist then begin
stmt := connection.PrepareStatement('SELECT num_rotation, title FROM t_rotation where '+sql_defaultwhere+' AND '+sql_dontselectdeleted+' AND name=? ORDER BY title');
stmt.SetString(1, iTree.GetNodeCaption(iParent));
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2), false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntTitleRoot then begin
stmt := connection.PrepareStatement('SELECT DISTINCT title FROM t_rotation where '+sql_defaultwhere+' AND '+sql_dontselectdeleted+' ORDER BY title');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntTitle, rs.GetString(1), true, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntTitle then begin
stmt := connection.PrepareStatement('SELECT num_rotation, name FROM t_rotation where '+sql_defaultwhere+' AND '+sql_dontselectdeleted+' AND title=? ORDER BY name');
stmt.SetString(1, iTree.GetNodeCaption(iParent));
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2), false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntjingelroot then begin
stmt := connection.PrepareStatement('SELECT num_rotation,id, name, title FROM t_rotation where num_type in '+jingeltypes+' AND '+sql_dontselectdeleted+' ORDER BY name, title');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2) + '- ' + rs.getstring(3)+' - ' + rs.getstring(4), false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntAdvRoot then begin
stmt := connection.PrepareStatement('SELECT num_rotation,id, name, title FROM t_rotation where num_type in '+werbungtypes+' AND '+sql_dontselectdeleted+' ORDER BY name, title');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2) + '- ' + rs.getstring(3)+' - ' + rs.getstring(4), false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = NtRotationRoot then begin
stmt := connection.PrepareStatement('SELECT t_rotation.num_type,t_type.name FROM t_rotation,t_type where (t_rotation.num_type in '+zenontypes+' or t_rotation.num_type in '+Jingeltypes+' ) AND '+ sql_dontselectdeleted + ' AND t_rotation.num_type = t_type.num_type group by num_type ORDER BY name');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntRotation, rs.GetString(2) , false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntRotationArtist then begin
stmt := connection.PrepareStatement('SELECT num_rotation, title FROM t_rotation where (num_type in '+zenontypes+' or num_type in '+ Jingeltypes+' ) and name = ? ORDER BY name, title');
stmt.SetString(1, iTree.GetNodeCaption(iParent));
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2) , false, 0, rs.GetInt(1), 0, 0, 0);
end
else if iNodeType = ntRotation then begin
stmt := connection.PrepareStatement('SELECT DISTINCT name FROM t_rotation where (num_type in '+zenontypes+' or num_type in '+ Jingeltypes+' ) AND '+ sql_dontselectdeleted + ' ORDER BY name');
stmt.SetInt(1, iData0);
rs := stmt.ExecuteQueryPrepared;
while rs.Next do
iTree.AddNode(iParent, iThisDatabase, ntRotationArtist, rs.GetString(1) , false, 0, rs.GetInt(1), 0, 0, 0);
end
end;
procedure CreatePlaylistItem(iData0, iData1, iData2, iData3: integer; var oResult: IPlaylistItem);
var
stmt: IZPreparedStatement;
rs: IZResultSet;
prepfilename: string;
workstring: string;
i: integer;
workdir: string;
retun: integer;
hossa : tstringlist;
art, tit: string;
dur: int64;
begin
stmt := Connection.PrepareStatement(
'SELECT name, title, num_rotation, length, num_type,CUT_IN, CUT_OUT, FADE_IN, FADE_OUT, INTRO,Outro,mix,Comment ' +
'FROM t_rotation ' +
'WHERE num_rotation=?');
stmt.SetInt(1, iData0);
rs := stmt.ExecuteQueryPrepared;
if rs.First then begin
Connectutil;
prepfilename := getpath(rs.getint(5));
disconnectutil;
prepfilename := prepfilename + IdToPath(rs.getint(3)) + fileext;
oResult := IPlaylistItem(CreateFilePlaylistItem(prepfilename, false));
art := rs.GetString(1);
tit := rs.GetString(2);
dur := rs.Getlong(4);
oResult.SetArtist(art);
oResult.SetTitle(tit);
oResult.SetDuration(dur*10000);
oResult.SetComment(rs.getstring(13));
if (rs.getlong(10) >100) then oResult.GetCuePosition(ptRamp).SetValue(rs.GetLong(10)*10000);
if (rs.getlong(6) >100) then oResult.GetCuePosition(ptCueIn).SetValue(rs.GetLong(6)*10000);
if (rs.getlong(11) >100) then oResult.GetCuePosition(ptOutro).SetValue((rs.GetLong(7)*10000)-rs.GetLong(11)*10000) ;
if (rs.getlong(9) >100) then oResult.GetCuePosition(ptFadeOut).SetValue((rs.GetLong(7)*10000)-(rs.GetLong(9)*10000));
if (rs.getlong(7) >100) then oResult.GetCuePosition(ptCueOut).SetValue(rs.GetLong(7)*10000);
if (rs.getlong(12) >100) then oResult.GetCuePosition(ptStartNext).SetValue((rs.GetLong(7)*10000)-(rs.GetLong(12)*10000));
if (useicons <>0) then
begin
connecticon;
oResult.loadiconfromfile(geticonfromfile(rs.getint(5)));
// oResult.SetComment(rs.getstring(13) + #10#13 + geticonfromfile(rs.getint(5)));
disconnecticon;
end;
end
else
RaiseException(erCustomError, 'Invalid song id: ' + IntToStr(iData0));
end;
procedure SearchItems(iSearchString: string; iResult: IPlaylist);
var
stmt: IZPreparedStatement;
rs: IZResultSet;
pi: IPlaylistItem;
begin
stmt := Connection.PrepareStatement(
'SELECT num_rotation ' +
'FROM t_rotation ' +
'WHERE (name LIKE ? OR title LIKE ? or id like ?) and (num_type in '+zenontypes + ' or num_type in '+ jingeltypes +' or num_type in '+werbungtypes +') AND ' + sql_dontselectdeleted +
'ORDER BY title, name limit 0,500');
stmt.SetString(1, '%' + iSearchString + '%');
stmt.SetString(2, '%' + iSearchString + '%');
stmt.SetString(3, '%' + iSearchString + '%');
rs := stmt.ExecuteQueryPrepared;
while rs.Next do begin
CreatePlaylistItem(rs.GetInt(1), 0, 0, 0, pi);
iResult.Add(pi);
end;
end;
procedure ExecuteSQL(iSQL: string);
var
stmt: IZStatement;
begin
stmt := connection.CreateStatement;
stmt.Execute(iSQL);
end;
begin
end.gruß Thomas
Vor dem ORDER BY fehlt vermutlich ein Leerzeichen (beliebter Fehler beim Zusammensetzen von Strings mit +).
@Thomas,
wenn Dein Script funktioniert, würdest Du mal kurz erklären was es macht (für so Script Dummys wie mich)? Ich ahne so grob worum es geht und das könnte sehr interessant sein.
Ich würde sagen, es verbindet sich mit einern Zenon-Datenbank 
:o Tatsächlich, Du könntest recht haben. Wer lesen kann ist klar im Vorteil…
Das scheint doch nach vorgeben einige Elemente in die Playliste zu schieben, kann man das auch auf die EldoDB umstricken. Ich hab das Scripting nämlich leider immer noch nicht verstanden, kommt hoffentlich noch.
So ein Datenbank-Script ist ein vollwertiger Ersatz/Ergänzung für die bereits fest eingebauten Datenbank-Typen (eldoDB, radioDB, iTunes, …). Damit ist es möglich, sich an Datenbanken zu koppeln, die mAirList von Haus aus nicht unterstützt.