Skip to Content
It is currently November 13th, 2019, 4:48 am

All times are UTC - 6 hours [ DST ]




Post new topic Reply to topic  [ 11 posts ] 
Author Message
PostPosted: September 18th, 2018, 7:43 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
Hey guys... need a little help with this PAL. Trying to do a query where it picks a song that has been added within the last 14 days. Here's what I have been testing but it doesn't work. I am getting the error: Could not convert variant of type (String) into type (Date) and it is not finding any new music...
Code:
// Declare variables
var EmptyP    : TPlayer;
var theSongChooser : TDataSet;
var Song, NewSong     : TSongInfo;
var y,m,d : Integer;
var a,b : DateTime;
a := Now;
b := Now - 14; // Number of days back from now to consider a song new
PAL.Loop := False;

DecodeDate(b,y,m,d);
PAL.LockExecution;
Song := ActivePlayer.GetSongInfo();
EmptyP := IdlePlayer;

// Choose random song by band ID artist   
   theSongChooser := Query('SELECT artist, title, filename, id, date_added ' +
        'FROM songlist ' +
        'WHERE date_added >= ' + DateToStr(b) +' '+
        'AND songtype = ''S'' ' +
        'AND genre != ''Christmas'' ' +
        'AND genre != ''Instrumental'' ' +
        ' order by rand() limit 1',[],True);
      
      WriteLn('Artist: ' +theSongChooser['artist']);
      WriteLn('Title: ' +theSongChooser['title']);
      WriteLn('Added:' +theSongChooser['date_added']);
      
   PAL.UnlockExecution;
   theSongChooser.Free

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 8:30 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7880
Location: Aachen (Germany)
#1 Use SQL if you can and avoid inserting calculations from PAL unless absolutely necessary.
#2 Use prepared statements and parameters for stuff that is likely to change (i.e. the interval and the genres)
#3 In order to use the data from the dataset, you should (not sure if that's a should or a must) call QuerySet.First first.
#4 Free the DataSet object after you're done using it.

Code:
// Declare variables
var EmptyP    : TPlayer;
var theSongChooser : TDataSet;
var Song, NewSong     : TSongInfo;
PAL.Loop := False;
PAL.LockExecution;
Song := ActivePlayer.GetSongInfo();
EmptyP := IdlePlayer;

// Choose random song by band ID artist   
   theSongChooser := Query('SELECT artist, title, filename, id, date_added ' +
        'FROM songlist ' +
        'WHERE DATEDIFF(CURRENT_DATE(), date_added) < :interval ' +
        'AND songtype = :songtype ' +
        'AND genre != :genre1 ' +
        'AND genre != :genre2 ' +
        ' order by rand() limit 1',[14, 'S', 'Christmas', 'Instrumental'],True);
     
   theSongChooser.First;

   WriteLn('Artist: ' +theSongChooser['artist']);
   WriteLn('Title: ' +theSongChooser['title']);
   WriteLn('Added:' +theSongChooser['date_added']);
 
   theSongChooser.Free;   

   PAL.UnlockExecution;
   theSongChooser.Free

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Last edited by Mastacheata on September 18th, 2018, 9:35 am, edited 2 times in total.
surplus single quote and missing space


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 8:49 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
Cool... thanks Mastacheata. I tried it and I am getting an error.

ERROR: Query error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'songtype = 'S' genre != 'Christmas' AND genre != 'Instrumental' order by rand()' at line 1
ERROR: SQL: SELECT artist, title, filename, id, date_added FROM songlist WHERE DATEDIFF(CURRENT_DATE(), date_added) < :intervalAND songtype = :songtypeAND genre != :genre1 AND genre != :genre2 order by rand() limit 1

I removed the extra apostrophe from the songtype but still getting that. will have to play around.. thanks again

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 8:57 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
I was getting an error on this line:
Code:
'WHERE DATEDIFF(CURRENT_DATE(), date_added) < :interval' +

so I changed it to:
Code:
'WHERE DATEDIFF(CURRENT_DATE(), date_added < :interval)' +

and the error went away but it is not getting any new songs and I have plenty.
Not sure if it is because i move the ')'

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 9:33 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7880
Location: Aachen (Germany)
There was a surplus single quote on the songtype line and a missing space at the end of the datediff line, sorry.

And no you can't just move the brace.
DATEDIFF only accepts exactly 2 parameters, both dates or datetimes between which it calculates the difference in date.

This calculates the difference between today and the date_added field (usually in days):
DATEDIFF(CURRENT_DATE(), date_added)

If you move the brace to include the less than symbol, this turns the second parameter from a date field into a boolean value. (date_added < 14 is most likely never true, so it always evaluates to FALSE or 0)
That makes the statement calculate the datediff between today and 0, which is a huge number, but unlikely to be either true or false (which is what you want a condition to be)

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 9:48 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
ah... thank you my friend... it works!

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 11:11 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
It's working great.

_________________
https://deadtoselfradio.com
https://fuelradio.net


Last edited by jtthomas on September 18th, 2018, 11:12 am, edited 1 time in total.

Top
 Profile  
Reply with quote  
PostPosted: September 18th, 2018, 11:12 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
One more question, how do I just get the songs according to the rotation rules... in other words, I just want it to choose from songs that don't defy those rules... sorry, i have some PAL script experience but I have a few areas to learn... thanks

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 19th, 2018, 6:58 am 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
Yeah... after testing it, it does pick songs that shouldn't play but does because the rotation rules are not considered in this script so if someone could give me a little help and direction, it would be much appreciated.. other than that, it is working great.

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
PostPosted: September 19th, 2018, 12:28 pm 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7880
Location: Aachen (Germany)
Rebuild your repeat rules in database conditions.
Despite the name (date_played, date_artist_played etc.) these fields are datetime fields.

i.e. Don't repeat tracks from the same album within 60 minutes:
Code:
SELECT * FROM songlist WHERE TIMESTAMPDIFF(MINUTE, date_album_played, NOW()) > 60;

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: September 19th, 2018, 12:36 pm 
Offline
Senior User
Senior User

Joined: June 1st, 2005, 11:46 pm
Posts: 113
Location: Cosmopolis, Washington
ahhh, you sir are a gentleman and a scholar.. thank you

_________________
https://deadtoselfradio.com
https://fuelradio.net


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC - 6 hours [ DST ]


Who is online

Users browsing this forum: MSN [Bot] and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group