Scripts and searching in database using attributes

English mAirList 7.x
1

Hello scripts specialists !

In a mairlist script, I try to do a search based on standard metadata to find a set of Playlist Item.

Here is my current code, but execution goes to an “access violation”.

var
    resultSearch: IPlaylist;
    mySearch: TDatabaseSearchOptions;
    handler: IDatabaseSearchHandler;
begin
    mySearch.Term := '''PODCAST-CLOSER''=''yes''';
    mySearch.Fields := [dbsfStandardAttributes];
    mySearch.Limit := 2;
    handler := Database(0).SearchItems(mySearch, resultSearch);

    //SystemLog(Database(0).GetStandardAttributes.GetNames.GetItem(0));
    //SystemLog('taille: ' + IntToStr(resultSearch.GetCount));
end.

I think this is the “Term” property which is wrong but I don’t know the correct syntax.
(My use case : find all items where the metadata named PODCAST-CLOSER equals to the value ‘yes’ - it’s a checkbox in mairlist)

Any idea ?

If ever I didn’t get an answer, in workaround, as I found how to get all the items from a folder in scripts, I will code myself a loop on all these items to find the correct ones (getting their metadata one by one), but I think there is a better way to achieve it. So is my question.

Note : No need to tell me that I can define a filter in my clock template, on a “random item from folder” item, because I already know that. But I do have to search in database within a script…

Regards

Sylvain

2

So far, I found a way to get the items I want to, without using this API method (for information: done lot of tries again without success…).

For those who comes into this topic expecting an answer, I’ll give you the workaround I set today. I
t works with a local mairlist DB (sqlite) and use the SQL command.

Here is the result :


var
	dbAccess: IDBAccess;
	sqlConnection: ISQLConnection;
	stmt: ISQLPreparedStatement;
	rs: ISQLResultSet;
begin
	dbAccess := IDBConnection(Database(0)).CreateOrReuseAccess(true);
	sqlConnection := ISQLDBBackend(dbAccess.GetBackend).GetConnection;
	stmt := sqlConnection.PrepareStatement('SELECT title FROM item_attributes, items WHERE item = idx and name=''CLOSER'' and value = ''yes''');
	rs := stmt.ExecuteQueryPrepared;
	while rs.Next do
	begin
		SystemLog(rs.GetString(1));
	end;
end.

This db request gets the title of the items where an attribute (called ‘CLOSER’) is set to the ‘yes’ value.
You can use it, modifying only the name into the request (replacing CLOSER by what you want), and the value by the one you’re looking for.

It’s a vey basic DB request (my final result is a bit complex). I think you should better use (as I did) the SetString method of the statement object to set the value used for filtering, outside of the string request… but, each one will modify it as it wants to.

Hope it will help someone :slight_smile:

Regards,

Sylvain

1 Like