Follow

PAL Script - Database Query (Advanced Scripting)

This is an advanced PAL Script for a number of reasons, it queries the database directly, it uses string lists and it requires user interaction.  The results of the database queries are displayed in both the PAL Script IDE output window as well as within text files.

This script must only be run when SAM Broadcaster is idle as the user interaction will cause SAM Broadcaster to wait for the selected input but because of this, we can increase the speed of the processing at the start of the script which speeds up the overall processing.

Do not be too concerned if SAM Broadcaster or the PAL IDE appear to not respond, this is normal when there is a large number of results to process or when you have a large number of tracks in your library.  Both the IDE window and SAM Broadcaster will start responding when the script has finished.

Whilst there is some great functionality contained within this script which can be very useful when considering tracks for playlists and scheduling, it should be remembered that the primary purpose of this script is as a teaching tool.  There is no error checking included which will be explained in a future script and lesson.  This script is to give an indication of how to query the database, the type of data we can find by querying the database as well as ancillary tasks like creating directories and files.


 

{*******************************************************************

 This Script is designed to enable DJs with no scripting experience
 to query the database and output the results to text files and to
 give examples of the data held within the database and the format
 
***  THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE ***
*** THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING ***
     
*******************************************************************}

PAL.LockExecution;                  // Speed up the Script processing

// Declare Constants
Const Option1 : string = 'Select 1 to Search by Artist';
Const Option2 : string = 'Select 2 to Search by Track';
Const Option3 : string = 'Select 3 to Search by Album';
Const Option4 : string = 'Select 4 to Search by a single Year';
Const Option5 : string = 'Select 5 to Search by Year Range';

// Declare Fixed Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 1;
var TrkTotal : integer = 0;
var StartYear : integer;
var EndYear : integer;
var ReqYear : integer;
var SearchArtist : string = '';
var SearchAlbum : string = '';
var SearchTrack : string = '';
var TempList : TStringList;
var Option : integer = 0;
var FilePath : string;

// MAIN -------------------------------------------------------------------------------------------
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
WriteLn('Output Directory: '+FilePath);
WriteLn(' ');
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2+#13#10+Option3+#13#10+Option4+#13#10+Option5+#13#10,''));

// Query based on artist (Option 1) ---------------------------------------------------------------
If Option = 1 then
  begin
  SearchArtist := InputBox('Artist Search','Enter the artist name to search for...','');
  Filepath := Filepath+SearchArtist+'.txt';
  WriteLn('Tracks for Artist: '+SearchArtist);
  WriteLn(' ');
  if SearchArtist <> '' then
    begin
      TempList.Add('Tracks for Artist: '+SearchArtist);
      TempList.Add(' ');
      Results := Query('SELECT * FROM songlist WHERE artist = :ArtistName',[SearchArtist],False);
      while not Results.EOF do
        begin
          writeln(FloatToSTr(TrkCount)+'. '+Results['title']);
          TrackName := Results['artist']+' - '+Results['title'];
          TrackName := FloatToStr(TrkCount)+'. '+TrackName;
          TempList.Add(TrackName);
          TrkCount := TrkCount + 1;
          TrkTotal := TrkTotal + 1;
          Results.Next;
        end;
    end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchArtist+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

// Query based on track (Option 2) ----------------------------------------------------------------
If Option = 2 then
  begin
  SearchTrack := InputBox('Track Search','Enter the track name to search for...','');
  Filepath := Filepath+SearchTrack+'.txt';
  WriteLn('Track Record for: '+SearchTrack);
  WriteLn(' ');
  TempList.Add('Track Record for: '+SearchTrack);
  TempList.Add(' ');
  if SearchTrack <> '' then
    begin
      Results := Query('SELECT * FROM songlist WHERE title = :TrackName',[SearchTrack],False);
      while not Results.EOF do
        begin
          // Write Record to PAL IDE Output
          WriteLn('Track ID: '+FloatToStr(Results['ID']));
          WriteLn('Filename: '+Results['filename']);
          WriteLn('Disk ID: '+FloatToStr(Results['DiskID']));
          WriteLn('Flags: '+Results['flags']);
          WriteLn('Song Type: '+Results['songtype']);
          WriteLn('Status: '+FloatToStr(Results['status']));
          WriteLn('Weight: '+FloatToStr(Results['weight']));
          WriteLn('Balance: '+FloatToStr(Results['balance']));
          WriteLn('Date Added: '+DateTimeToStr(Results['date_added']));
          WriteLn('Date Played: '+DateTimeToStr(Results['date_played']));
          WriteLn('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
          WriteLn('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
          WriteLn('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
          WriteLn('Duration: '+FloatToStr(Results['Duration']));
          WriteLn('Artist: '+Results['artist']);
          WriteLn('Title: '+Results['title']);
          WriteLn('Album: '+Results['album']);
          WriteLn('Label: '+Results['label']);
          WriteLn('PLine: '+Results['pline']);
          WriteLn('Track No: '+FloatToStr(Results['trackno']));
          WriteLn('Composer: '+Results['composer']);
          WriteLn('ISRC: '+Results['ISRC']);
          WriteLn('Catalog: '+Results['Catalog']);
          WriteLn('UPC: '+Results['UPC']);
          WriteLn('Fee Agency: '+Results['feeagency']);
          WriteLn('Album Year: '+Results['albumyear']);
          WriteLn('Genre: '+Results['genre']);
          WriteLn('Website: '+Results['website']);
          WriteLn('Buy CD: '+Results['buycd']);
          WriteLn('Info: '+Results['info']);
          WriteLn('Lyics: '+Results['lyrics']);
          WriteLn('Picture: '+Results['picture']);
          WriteLn('Count Played: '+FloatToStr(Results['count_played']));
          WriteLn('Count Requested: '+FloatToStr(Results['count_requested']));
          WriteLn('Last Requested: '+DateTimeToStr(Results['last_requested']));
          WriteLn('Count Performances: '+FloatToStr(Results['count_performances']));
          WriteLn('Crossfade: '+Results['xfade']);
          WriteLn('BPM: '+FloatToStr(Results['bpm']));
          WriteLn('Mood: '+Results['mood']);
          WriteLn('Rating: '+FloatToStr(Results['rating']));
          WriteLn('Overlay: '+Results['overlay']);
          WriteLn('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
          WriteLn('Playlimit Action: '+Results['playlimit_action']);
          WriteLn('Songrights: '+Results['songrights']);
          WriteLn(' ');
          WriteLn('Total Tracks found for album '+SearchTrack+': '+FloatToStr(TrkTotal));
          WriteLn(' ');
          WriteLn('- - - - - End of Record - - - - -');
          WriteLn(' ');
          
          // Write Record to Text File
          TempList.Add('Track ID: '+FloatToStr(Results['ID']));
          TempList.Add('Filename: '+Results['filename']);
          TempList.Add('Disk ID: '+FloatToStr(Results['DiskID']));
          TempList.Add('Flags: '+Results['flags']);
          TempList.Add('Song Type: '+Results['songtype']);
          TempList.Add('Status: '+FloatToStr(Results['status']));
          TempList.Add('Weight: '+FloatToStr(Results['weight']));
          TempList.Add('Balance: '+FloatToStr(Results['balance']));
          TempList.Add('Date Added: '+DateTimeToStr(Results['date_added']));
          TempList.Add('Date Played: '+DateTimeToStr(Results['date_played']));
          TempList.Add('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
          TempList.Add('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
          TempList.Add('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
          TempList.Add('Duration: '+FloatToStr(Results['Duration']));
          TempList.Add('Artist: '+Results['artist']);
          TempList.Add('Title: '+Results['title']);
          TempList.Add('Album: '+Results['album']);
          TempList.Add('Label: '+Results['label']);
          TempList.Add('PLine: '+Results['pline']);
          TempList.Add('Track No: '+FloatToStr(Results['trackno']));
          TempList.Add('Composer: '+Results['composer']);
          TempList.Add('ISRC: '+Results['ISRC']);
          TempList.Add('Catalog: '+Results['Catalog']);
          TempList.Add('UPC: '+Results['UPC']);
          TempList.Add('Fee Agency: '+Results['feeagency']);
          TempList.Add('Album Year: '+Results['albumyear']);
          TempList.Add('Genre: '+Results['genre']);
          TempList.Add('Website: '+Results['website']);
          TempList.Add('Buy CD: '+Results['buycd']);
          TempList.Add('Info: '+Results['info']);
          TempList.Add('Lyics: '+Results['lyrics']);
          TempList.Add('Picture: '+Results['picture']);
          TempList.Add('Count Played: '+FloatToStr(Results['count_played']));
          TempList.Add('Count Requested: '+FloatToStr(Results['count_requested']));
          TempList.Add('Last Requested: '+DateTimeToStr(Results['last_requested']));
          TempList.Add('Count Performances: '+FloatToStr(Results['count_performances']));
          TempList.Add('Crossfade: '+Results['xfade']);
          TempList.Add('BPM: '+FloatToStr(Results['bpm']));
          TempList.Add('Mood: '+Results['mood']);
          TempList.Add('Rating: '+FloatToStr(Results['rating']));
          TempList.Add('Overlay: '+Results['overlay']);
          TempList.Add('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
          TempList.Add('Playlimit Action: '+Results['playlimit_action']);
          TempList.Add('Songrights: '+Results['songrights']);
          TempList.Add(' ');
          TempList.Add('- - - - - End of Record - - - - -');
          TempList.SaveToFile(FilePath);
          TempList.Clear;
          Results.Next;
        end;
    end;
end;

// Query based on album (Option 3) ----------------------------------------------------------------
If Option = 3 then
  begin
  SearchAlbum := InputBox('Album Search','Enter the album name to search for...','');
  Filepath := Filepath+SearchAlbum+'.txt';
  WriteLn('Tracks for Album: '+SearchAlbum);
  WriteLn(' ');
  TempList.Add('Tracks for Album: '+SearchAlbum);
  TempList.Add(' ');
  if SearchAlbum <> '' then
    begin
      Results := Query('SELECT * FROM songlist WHERE album = :AlbumName',[SearchAlbum],False);
      while not Results.EOF do
        begin
          TrackName := Results['artist']+' - '+Results['title'];
          TrackName := FloatToStr(TrkCount)+'. '+TrackName;
          WriteLn(TrackName);
          TempList.Add(TrackName);
          TrkCount := TrkCount + 1;
          TrkTotal := TrkTotal + 1;
          Results.Next;
        end;
    end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

// Query based on single year (Option 4) ----------------------------------------------------------
If Option = 4 then
  begin
  StartYear := StrToInt(InputBox('Search Year','Please enter the year to search',''));
  ReqYear := StartYear;
    WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
    WriteLn(' ');
    TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
    TempList.Add(' ');
    Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
    Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);

    Results.First;
    while not Results.EOF do
      begin
      TrackName := Results['artist']+' - '+Results['title'];
      TrackName := FloatToStr(TrkCount)+'. '+TrackName;
      WriteLn(TrackName);
      TempList.Add(TrackName);
      TrkCount := TrkCount + 1;
      TrkTotal := TrkTotal + 1;
      Results.Next;
    end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for Year '+FloatToStr(ReqYear)+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

// Query based on a range of years (Option 5) -----------------------------------------------------
If Option = 5 then
  begin
  StartYear := StrToInt(InputBox('Starting Year','Please enter the year to search from',''));
  EndYear := StrToInt(InputBox('End Year','Please enter the year to search up to',''));
  ReqYear := StartYear;
 
  While (EndYear > StartYear) AND (ReqYear <= EndYear) do
  begin
    WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
    WriteLn(' ');
    TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
    TempList.Add(' ');
    Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
    Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);

    Results.First;
    while not Results.EOF do
      begin
      TrackName := Results['artist']+' - '+Results['title'];
      TrackName := FloatToSTr(TrkCount)+'. '+TrackName;
      WriteLn(TrackName);
      TempList.Add(TrackName);
      TrkCount := TrkCount + 1;
      TrkTotal := TrkTotal + 1;
      Results.Next;
    end;
    WriteLn(' ');
    WriteLn('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
    WriteLn(' ');
    WriteLn('- - - - - End of Record - - - - -');
    WriteLn(' ');
    TempList.Add(' ');
    TempList.Add('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
    TempList.Add(' ');
    TempList.Add('- - - - - End of Record - - - - -');
    TempList.Add(' ');
    TempList.SaveToFile(FilePath);
    TempList.Clear;
    ReqYear := ReqYear + 1;
    TrkCount := 1;
    end;
end;

// End of Main ------------------------------------------------------------------------------------

PAL.UnLockExecution;                // Return PAL Script processing to normal speed

// Housekeeping
Results.Free;

 

So let's break this script down in to the various sections to make it simpler to understand:


The Opening Remarks

It is always a good idea to add some comments to a script to give an indication of the script's purpose.  You could also consider including dates and code revision dates:

{*******************************************************************

 This Script is designed to enable DJs with no scripting experience
 to query the database and output the results to text files and to
 give examples of the data held within the database and the format
 
***  THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE ***
*** THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING ***
     
*******************************************************************}

 

As you can see, the comments are very simple but they do include a warning not to run this script unless SAM Broadcaster is idle.


Script Speed

PAL.LockExecution;                  // Speed up the Script processing

Because SAM Broadcaster is not streaming and because querying the database can provide a lot of search results, we want the script running as quickly as possible so we add this line as the first line of code after the opening comments.


Constant Declarations

Instead of using Variables, we have chosen to use Const to hold specific data strings.

// Declare Constants
Const Option1 : string = 'Select 1 to Search by Artist';
Const Option2 : string = 'Select 2 to Search by Track';
Const Option3 : string = 'Select 3 to Search by Album';
Const Option4 : string = 'Select 4 to Search by a single Year';
Const Option5 : string = 'Select 5 to Search by Year Range';

These can be referred to throughout the script but these are only used at the start of this script in conjunction with the user prompt dialog box which gives the user the option to select the type of query to run against the database.


Variable Declarations

It is usual to declare variables at the start of any script or program.  They are easier to find which can simplify troubleshooting.

// Declare Fixed Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 1;
var TrkTotal : integer = 0;
var StartYear : integer;
var EndYear : integer;
var ReqYear : integer;
var SearchArtist : string = '';
var SearchAlbum : string = '';
var SearchTrack : string = '';
var TempList : TStringList;
var Option : integer = 0;
var FilePath : string;

You will note we declare a variable of type TDataSet to hold the data from the database queries, we declare a number of both integer and string type variables, as well as a TStringList which is used to hold lists of strings.  This will become more obvious a little later.


Program Main

The start of the program.

// MAIN -------------------------------------------------------------------------------------------
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
WriteLn('Output Directory: '+FilePath);
WriteLn(' ');
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2+#13#10+Option3+#13#10+Option4+#13#10+Option5+#13#10,''));

The first line creates the TStringList container with the name TempList, we also get the current directory path in use, add the folder name Output to the end of the folder path and then we create the directory.  This is where we will output all of the search results in text file format.  You will note that to ensure we know where this folder is located, we write the folder path to the PAL Script IDE Window Output.

We also make use of the InputBox which in this case, prompts the user to make a selection and enter their choice depending upon the type of query they wish to run be it a search by Artist, Track, Album, a single Year or a range of Years.

The +#13#10 includes a carriage return and a new line which forces the different options to display on separate lines.  We could also have written +#13+#10 but the short form works just as well.

The purpose of this script is to run different queries against the database and as such, the rest of this article is broken down in to the 5 options available.


Option 1 - Searching by Artist

 You will note that a large percentage of the code under the different options is repeated from this section of code.  Essentially, querying the database is the same or similar process just using different search words with some variation in the way the results are written.

// Query based on artist (Option 1) ---------------------------------------------------------------
If Option = 1 then
  begin
  SearchArtist := InputBox('Artist Search','Enter the artist name to search for...','');
  Filepath := Filepath+SearchArtist+'.txt';
  WriteLn('Tracks for Artist: '+SearchArtist);
  WriteLn(' ');
  if SearchArtist <> '' then
    begin
      TempList.Add('Tracks for Artist: '+SearchArtist);
      TempList.Add(' ');
      Results := Query('SELECT * FROM songlist WHERE artist = :ArtistName',[SearchArtist],False);
      while not Results.EOF do
        begin
          writeln(FloatToSTr(TrkCount)+'. '+Results['title']);
          TrackName := Results['artist']+' - '+Results['title'];
          TrackName := FloatToStr(TrkCount)+'. '+TrackName;
          TempList.Add(TrackName);
          TrkCount := TrkCount + 1;
          TrkTotal := TrkTotal + 1;
          Results.Next;
        end;
    end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchArtist+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.Add(' ');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

 

To help explain the purpose of each line, we have this image with annoctations for each line or section.  A larger version is available to download at the bottom of the page entitled Option 1.png:

Option 1-2.png


Option 2 - Searching by Track

Whilst the method of capturing the search term is the same as Option 1, this section outputs every record field for the track which is hoped, will show the customer exactly what data is stored and how to acess this.  You could amend the Query statement to seach for any of the fields shown below and this would return the corresponding results.

There are no new keywords in this section but as you will see, there are a large number of records written to both the IDE output window and the text file.

// Query based on track (Option 2) ----------------------------------------------------------------
If Option = 2 then
begin
  SearchTrack := InputBox('Track Search','Enter the track name to search for...','');
  Filepath := Filepath+SearchTrack+'.txt';
  WriteLn('Track Record for: '+SearchTrack);
  WriteLn(' ');
  TempList.Add('Track Record for: '+SearchTrack);
  TempList.Add(' ');
  if SearchTrack <> '' then
begin
      Results := Query('SELECT * FROM songlist WHERE title = :TrackName',[SearchTrack],False);
      while not Results.EOF do
        begin
          // Write Record to PAL IDE Output
          WriteLn('Track ID: '+FloatToStr(Results['ID']));
          WriteLn('Filename: '+Results['filename']);
          WriteLn('Disk ID: '+FloatToStr(Results['DiskID']));
          WriteLn('Flags: '+Results['flags']);
          WriteLn('Song Type: '+Results['songtype']);
          WriteLn('Status: '+FloatToStr(Results['status']));
          WriteLn('Weight: '+FloatToStr(Results['weight']));
          WriteLn('Balance: '+FloatToStr(Results['balance']));
          WriteLn('Date Added: '+DateTimeToStr(Results['date_added']));
          WriteLn('Date Played: '+DateTimeToStr(Results['date_played']));
          WriteLn('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
          WriteLn('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
          WriteLn('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
          WriteLn('Duration: '+FloatToStr(Results['Duration']));
          WriteLn('Artist: '+Results['artist']);
          WriteLn('Title: '+Results['title']);
          WriteLn('Album: '+Results['album']);
          WriteLn('Label: '+Results['label']);
          WriteLn('PLine: '+Results['pline']);
          WriteLn('Track No: '+FloatToStr(Results['trackno']));
          WriteLn('Composer: '+Results['composer']);
          WriteLn('ISRC: '+Results['ISRC']);
          WriteLn('Catalog: '+Results['Catalog']);
          WriteLn('UPC: '+Results['UPC']);
          WriteLn('Fee Agency: '+Results['feeagency']);
          WriteLn('Album Year: '+Results['albumyear']);
          WriteLn('Genre: '+Results['genre']);
          WriteLn('Website: '+Results['website']);
          WriteLn('Buy CD: '+Results['buycd']);
          WriteLn('Info: '+Results['info']);
          WriteLn('Lyics: '+Results['lyrics']);
          WriteLn('Picture: '+Results['picture']);
          WriteLn('Count Played: '+FloatToStr(Results['count_played']));
          WriteLn('Count Requested: '+FloatToStr(Results['count_requested']));
          WriteLn('Last Requested: '+DateTimeToStr(Results['last_requested']));
          WriteLn('Count Performances: '+FloatToStr(Results['count_performances']));
          WriteLn('Crossfade: '+Results['xfade']);
          WriteLn('BPM: '+FloatToStr(Results['bpm']));
          WriteLn('Mood: '+Results['mood']);
          WriteLn('Rating: '+FloatToStr(Results['rating']));
          WriteLn('Overlay: '+Results['overlay']);
          WriteLn('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
          WriteLn('Playlimit Action: '+Results['playlimit_action']);
          WriteLn('Songrights: '+Results['songrights']);
          WriteLn(' ');
          WriteLn('Total Tracks found for album '+SearchTrack+': '+FloatToStr(TrkTotal));
          WriteLn(' ');
          WriteLn('- - - - - End of Record - - - - -');
          WriteLn(' ')

// Write Record to Text File
          TempList.Add('Track ID: '+FloatToStr(Results['ID']));
          TempList.Add('Filename: '+Results['filename']);
          TempList.Add('Disk ID: '+FloatToStr(Results['DiskID']));
          TempList.Add('Flags: '+Results['flags']);
          TempList.Add('Song Type: '+Results['songtype']);
          TempList.Add('Status: '+FloatToStr(Results['status']));
          TempList.Add('Weight: '+FloatToStr(Results['weight']));
          TempList.Add('Balance: '+FloatToStr(Results['balance']));
          TempList.Add('Date Added: '+DateTimeToStr(Results['date_added']));
          TempList.Add('Date Played: '+DateTimeToStr(Results['date_played']));
          TempList.Add('Date Artist Played: '+DateTimeToStr(Results['date_artist_played']));
          TempList.Add('Date Album Played: '+DateTimeToStr(Results['date_album_played']));
          TempList.Add('Date Title Played: '+DateTimeToStr(Results['date_title_played']));
          TempList.Add('Duration: '+FloatToStr(Results['Duration']));
          TempList.Add('Artist: '+Results['artist']);
          TempList.Add('Title: '+Results['title']);
          TempList.Add('Album: '+Results['album']);
          TempList.Add('Label: '+Results['label']);
          TempList.Add('PLine: '+Results['pline']);
          TempList.Add('Track No: '+FloatToStr(Results['trackno']));
          TempList.Add('Composer: '+Results['composer']);
          TempList.Add('ISRC: '+Results['ISRC']);
          TempList.Add('Catalog: '+Results['Catalog']);
          TempList.Add('UPC: '+Results['UPC']);
          TempList.Add('Fee Agency: '+Results['feeagency']);
          TempList.Add('Album Year: '+Results['albumyear']);
          TempList.Add('Genre: '+Results['genre']);
          TempList.Add('Website: '+Results['website']);
          TempList.Add('Buy CD: '+Results['buycd']);
          TempList.Add('Info: '+Results['info']);
          TempList.Add('Lyics: '+Results['lyrics']);
          TempList.Add('Picture: '+Results['picture']);
          TempList.Add('Count Played: '+FloatToStr(Results['count_played']));
          TempList.Add('Count Requested: '+FloatToStr(Results['count_requested']));
          TempList.Add('Last Requested: '+DateTimeToStr(Results['last_requested']));
          TempList.Add('Count Performances: '+FloatToStr(Results['count_performances']));
          TempList.Add('Crossfade: '+Results['xfade']);
          TempList.Add('BPM: '+FloatToStr(Results['bpm']));
          TempList.Add('Mood: '+Results['mood']);
          TempList.Add('Rating: '+FloatToStr(Results['rating']));
          TempList.Add('Overlay: '+Results['overlay']);
          TempList.Add('Playlimit Count: '+FloatToStr(Results['playlimit_count']));
          TempList.Add('Playlimit Action: '+Results['playlimit_action']);
          TempList.Add('Songrights: '+Results['songrights']);
          TempList.Add(' ');
          TempList.Add('- - - - - End of Record - - - - -');
          TempList.SaveToFile(FilePath);
          TempList.Clear;
          Results.Next;
        end;
    end;
end;

 


Option 3 - Searching by Album

Again, no new keywords are introduced int Option 3 and the only difference is the output which lists to both the IDE output window and the text file, the names of the tracks found from the album searched for:

// Query based on album (Option 3) ----------------------------------------------------------------
If Option = 3 then
  begin
  SearchAlbum := InputBox('Album Search','Enter the album name to search for...','');
  Filepath := Filepath+SearchAlbum+'.txt';
  WriteLn('Tracks for Album: '+SearchAlbum);
  WriteLn(' ');
  TempList.Add('Tracks for Album: '+SearchAlbum);
  TempList.Add(' ');
  if SearchAlbum <> '' then
    begin
      Results := Query('SELECT * FROM songlist WHERE album = :AlbumName',[SearchAlbum],False);
      while not Results.EOF do
        begin
          TrackName := Results['artist']+' - '+Results['title'];
          TrackName := FloatToStr(TrkCount)+'. '+TrackName;
          WriteLn(TrackName);
          TempList.Add(TrackName);
          TrkCount := TrkCount + 1;
          TrkTotal := TrkTotal + 1;
          Results.Next;
        end;
    end;
WriteLn(' ');
WriteLn('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

 


Option 4 - Searching by a Single Year

The same code is reused to list all tracks which have the required album year:

// Query based on single year (Option 4) ----------------------------------------------------------
If Option = 4 then
  begin
  StartYear := StrToInt(InputBox('Search Year','Please enter the year to search',''));
  ReqYear := StartYear;
    WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
    WriteLn(' ');
    TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
    TempList.Add(' ');
    Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
    Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);

    Results.First;
    while not Results.EOF do
      begin
      TrackName := Results['artist']+' - '+Results['title'];
      TrackName := FloatToStr(TrkCount)+'. '+TrackName;
      WriteLn(TrackName);
      TempList.Add(TrackName);
      TrkCount := TrkCount + 1;
      TrkTotal := TrkTotal + 1;
      Results.Next;
    end;
WriteLn(' ');
WriteLn('Total Number of Tracks Found for Year '+FloatToStr(ReqYear)+': '+FloatToStr(TrkTotal));
WriteLn(' ');
WriteLn('- - - - - End of Record - - - - -');
WriteLn(' ');
TempList.Add(' ');
TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal));
TempList.Add(' ');
TempList.Add('- - - - - End of Record - - - - -');
TempList.SaveToFile(FilePath);
TempList.Clear;
end;

Option 5 - Searching by a Range of Years

This section differs slightly in it runs Option 4 for each of the required years.  It also prompts the user for two years, a year to start searching from and a year to search up to.  For each of these years, in simple terms, Option 4 is run however, there are a number of lines which either increment or reset a specific variable.  The results from each year is written to its own text file.  There is very little error checking and if you search for the same years multiple times, the existing files will be overwritten:

 // Query based on a range of years (Option 5) -----------------------------------------------------
If Option = 5 then
  begin
  StartYear := StrToInt(InputBox('Starting Year','Please enter the year to search from',''));
  EndYear := StrToInt(InputBox('End Year','Please enter the year to search up to',''));
  ReqYear := StartYear;
 
  While (EndYear > StartYear) AND (ReqYear <= EndYear) do
  begin
    WriteLn('Tracks for Year: '+FloatToStr(ReqYear));
    WriteLn(' ');
    TempList.Add('Tracks for Year: '+FloatToStr(ReqYear));
    TempList.Add(' ');
    Filepath := Filepath+FloatToStr(ReqYear)+'.txt';
    Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False);

    Results.First;
    while not Results.EOF do
      begin
      TrackName := Results['artist']+' - '+Results['title'];
      TrackName := FloatToSTr(TrkCount)+'. '+TrackName;
      WriteLn(TrackName);
      TempList.Add(TrackName);
      TrkCount := TrkCount + 1;
      TrkTotal := TrkTotal + 1;
      Results.Next;
    end;
    WriteLn(' ');
    WriteLn('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
    WriteLn(' ');
    WriteLn('- - - - - End of Record - - - - -');
    WriteLn(' ');
    TempList.Add(' ');
    TempList.Add('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1));
    TempList.Add(' ');
    TempList.Add('- - - - - End of Record - - - - -');
    TempList.Add(' ');
    TempList.SaveToFile(FilePath);
    TempList.Clear;
    ReqYear := ReqYear + 1;
    TrkCount := 1;
    end;
end;

End

The end of a program or script is usually where we tidy up any loose ends.  You can see that we return the PAL Script speed to its normal 1 line per second speed and we release or free up the memory used by the database query.  As we Clear the list of strings at the end of each option, there is no need to also add TempList.Free to the end of the script.

// End of Main ------------------------------------------------------------------------------------

PAL.UnLockExecution;                // Return PAL Script processing to normal speed

// Housekeeping
Results.Free;

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments