Questions about Time Formatting and Browser Tree

English Scripts
1

I’m currently working on a custom database implementation for an external postgresql database.

I have made good progress but have a couple of issues/questions:

Firstly I am using the following bit of code to show individual tracks for each Artist.

else if iNodeType = ntArtist then begin stmt := connection.PrepareStatement('SELECT id, track_title, length FROM cc_files WHERE artist_name = ? ORDER BY track_title'); stmt.SetString(1, iData); rs := stmt.ExecuteQueryPrepared; while rs.Next do //hourval := StrToInt(Copy(rs.GetString(3),0,2)) * 60 * 60; //minval := StrToInt(Copy(rs.GetString(3),4,2)) * 60; //secval := StrToInt(Copy(rs.GetString(3),7,2)); //totalval := hourval + minval + secval; //SystemLog(IntToStr(totalval)); iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2), false, 0, rs.GetString(1)); end

With the code as above (the few lines commented out) I am able to use the treeview normally. If I uncomment those lines when I try to expand any artist I get an error box which says ‘Row Data is Not Available’.

I have two questions:

  1. Why does the treeview seem to not work when anything other than the AddNode line is in the while loop?

  2. I am using those few lines of code to convert the time in the database into seconds. It’s by no means the best way of doing it i’m sure. Is the a function which I can use to convert a ‘00:00:00.000’ format duration into a TTimeValue?

(I am using the lines that convert the duration into seconds in the database search function and it works; so I don’t think its a problem with the lines themselves).

Thanks in advance.

2

I think you simply forgot to to add a begin…end block around the statements, like this:

    while rs.Next do begin
      hourval := StrToInt(Copy(rs.GetString(3),0,2)) * 60 * 60;
      minval := StrToInt(Copy(rs.GetString(3),4,2)) * 60;
      secval := StrToInt(Copy(rs.GetString(3),7,2));
      totalval := hourval + minval + secval;
      SystemLog(IntToStr(totalval)); 
      iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2), false, 0, rs.GetString(1));
    end;

Without the begin/end, the while loop would only be applied to the first statement (I have adjusted the indentation to make this clearer):

    while rs.Next do
      hourval := StrToInt(Copy(rs.GetString(3),0,2)) * 60 * 60;
    minval := StrToInt(Copy(rs.GetString(3),4,2)) * 60;
    secval := StrToInt(Copy(rs.GetString(3),7,2));
    totalval := hourval + minval + secval;
    SystemLog(IntToStr(totalval)); 
    iTree.AddNode(iParent, iThisDatabase, ntItem, rs.GetString(2), false, 0, rs.GetString(1));

So the error you see is from the “minval := …” line which tries to retrieve a value via GetString, but the loop has already ended, and there is no record available anymore.

Regarding the second question - if we’re talking about the Airtime database here, I believe the “length” column is actually a TIME WITHOUT TIME ZONE? If so, there is no need to convert the data into into a string and back, you can probably just use GetTime(3), which returns a Delphi TDateTime number (where 1 = one day), and multiply that by 86,400 (number of seconds per day) to get the number of seconds (which is the unit mAirList’s TTimeValue uses):

totalVal := GetTime(3) * 86400;
3

Thanks for the quick response Torben.

I can’t believe I didn’t spot that - spent a good hour messing about with that. Thank you.

Thanks for the information about the length provided by Airtime. I will give this a go this evening and let you know how I get on.

For anyone interested I will comment and post the database script once it is finished.

4

I managed to get the Tree to return a duration, which is great.

Unfortunately the suggestion to run this to get the duration doesn’t work.

 GetTime(3) * 86400;

The duration returned in the tree is blank.

Looking at the database it looks like the length column is an interval with the ‘default ‘00:00:00’::interval’ parameters.