How do I escape invalid path characters

[jmaver]

I am trying to call QuerySongs with the complete file path like this:

songname = "F:\!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI(Rebirth)\01-Angelic Voices [Rebirth Remix]-{B-Tribe}.mp3"
QuerySongs( "AND SongPath LIKE '%" + songname + "%'" );

It chokes on the "F:" and the [ and ]. What can I replace these with so I can call QuerySongs or ExecSQL?

[psyXonova]

Shouldn't it be:
QuerySongs("'SongPath LIKE '%" & songname & "%'")
or am i missing something??

[jmaver]

not in C#. String concat is + not &.
Or are you talking about the quoting?

[jmaver]

When I say chokes, I mean I get a sql error from MM rather than an error on the client side.

[psyXonova]

I am not a MM scripter but i do now SQL and VB...
3 questions:
a)R U talking about a script? If yes what language are you using? I thought that most people use VB script with MM. If this is the case the concat is done with "&"
b) Why r u using "AND". Does the QuerySong contains other criteria too? (e.g. QuerySongs('Rating = 5 AND SongPath Like 'c:\Music'')
c) Can you post the SQL error?

Keep in mind that in order to pass the parameter "songname" to Access you must concat it on the client side (since the access side has no way of determining the parameter "songname", and thus will produce an SQL error

not in C#. String concat is + not &.
Or are you talking about the quoting?

I was talking both for concat and quoting.

Anyway, i think that you should use opensql to complete the job like this:

SDB.Database.OpenSQL( "SELECT * FROM Songs WHERE SongPath Like '%" & songname & "%'")

[jmaver]

a) I am using C#. I am using the SongsDB com object via COM Interop. In c#, the simplest string concat is +.
b) I am forced to use AND by MM. If I take your example and try to call it:

Code: Select all

ISDBApplication songsApp = new SDBApplicationClass();
SDBSongIterator MMiter = (SDBSongIterator)songsApp.Database.QuerySongs( @"Rating = 5 AND SongPath Like 'c:\Music' " );
SDBSongData MMsong;
if ( !MMiter.EOF ) {
  MMsong = MMiter.Item;
}

It compiles and I get an error dialog in MM.

Code: Select all

"There was a problem querying the database: 42000:[Microsoft ODBC Microsoft Access Driver] Syntax error ( missing operator) in query expression 'Songs.IDAlbum=Albums.ID AND Songs.IDArtist=Artists.ID AND AlbArt.ID=Albums.IDArtist Rating = 5 AND SongPath Like 'C:\Music' '

Note, that the sql query from MM seems to be missing an AND between Album.IDArtist and Rating. That is why I add it. Then I don't get that error. However, the query returns an iterator set to EOF.
The next step was to add the % around the songpath line. Still get iter = EOF.
The next step was to start manually replacing "bad" chars. If I replace the c: with __ then I get a value back for the iterator.

Code: Select all

SDBSongIterator MMiter = (SDBSongIterator)songsApp.Database.QuerySongs( @"AND SongPath Like '%__\Music%'" );

I can do the same thing, replacing [ and ] and ( and ) with _, and ' with ' '.
The part that still doesn't really work is the drive prefix.
From the original:

Code: Select all

F:\!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI(Rebirth)\01-Angelic Voices [Rebirth Remix]-{B-Tribe}.mp3

This works:

Code: Select all

%_\!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI_Rebirth_\01-Angelic Voices _Rebirth Remix_-{B-Tribe}.mp3%

as does this:

Code: Select all

%__!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI_Rebirth_\01-Angelic Voices _Rebirth Remix_-{B-Tribe}.mp3%

but this doesn't:

Code: Select all

%f:_\!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI_Rebirth_\01-Angelic Voices _Rebirth Remix_-{B-Tribe}.mp3%

nor does this:

Code: Select all

%F_\!Audio\!Music\!Chillout\Buddha-Bar\Buddha-Bar VI_Rebirth_\01-Angelic Voices _Rebirth Remix_-{B-Tribe}.mp3%

So, I don't get it. I don't know what is wrong with f:\ at the front, and I don't understand why the _ doesn't work as expected. If I remove f:\ entirely, everything works fine.

So, my problem isn't anything on the client side other than replacing the correct set of chars in the songname string before passing it to QuerySongs.

[psyXonova]

Ok, i think i found your problem....

I open the MM database within access and guess what...
the songpath field stores the information without the drive letter!!!
So e.g. instead of C:\My Music\Madonna\Isla Bonita.mp3
it stores :\My Music\Madonna\Isla Bonita.mp3

This is why none of your code samples that contains the drive letter works!!

Is it absolutely necessary for you to refer to the specific drive?

Case No
Just trim your songname variable to exlude the first character on the left (which is the drive letter)

Case Yes
You must figure out how the database is designed by opening it in access.
From what i can see, there is a table called media and MM stores in that table every information concerning e.g. hard-drives, Network locations and CDs. Inside that table there is a field called driveletter but it contains numeric data (e.g. instead of "G" contains "6") which i believe is stored somewhere inside the system registry.
The media table has a one to many relationship with the Songs table on the field MediaID
So in case you need the drive letter then you must find a way to determine the number that this letter applies to (in my case G is 6), take the MediaID for this Driver from the Media table and then query the songs table adding one more parameter: AND MediaID = "the value you found out".
In anycase you still need to trim the first character on the left of the songname variable (which in your case is the actual path) since the drive letter is not saved in the songs table... (but the ":" is saved).

Cheers

[onkel_enno]

...
Inside that table there is a field called driveletter but it contains numeric data (e.g. instead of "G" contains "6") which i believe is stored somewhere inside the system registry.
...

Simply add 65 to this value and you get the Ascii.Code for your drive letter: Chr(6+65) = "G"

[jmaver]

Wow. Thanks for your help with this. I give that a try in a bit.

[jmaver]

That worked, although I had to change it a little. I can't add the MediaID to the query string, because I can't get it until I have the song. So, I query for the driveless path, and then if it is found, I compare it's driveletter to the one passed in.
Thanks for all the help.

[psyXonova]

Glad that helped

[nachtgieger]

It's not a bug it's a feature - and it is very clever. MediaMonkey identifies the drive via it's serial number, so you can put cds in different drives or us external HDs without caring about the drive letter - MM will find the correct database entries anyway