VBScript, MySQL and MP3s

[Loxias]

Alright, I've written a script that searches network machines and polls the computer for all the mp3s on a machine. It outputs the results to an html file as a report. Works great (albeit a little slow) so now my manager wants reports more often. To combat this I figured I'd have it write to a database and then he can query what he wants. Great idea but I've got a couple of snags...

I'll paste the code below but basically I've been able to attach to the database and write the username and computer name to their tables. The problem I'm having is getting the file info to write to it's table. Here's the code and then I'll explain what I've done


'*Establish connection with database
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=MediaSearch;"

'*Get computer name and store in database
strComputer = objDictionary.Item(objItem)
objRecordset.CursorLocation = adUseClient

strSQLQuery = "INSERT INTO computer (compname) VALUES ('"& strComputer &"')"
objRecordset.Open strSQLQuery , objConnection, _
adOpenStatic, adLockOptimistic

'*Store user in database
strSQLQuery = "INSERT INTO user (username) VALUES ('"& strValue &"')"
objRecordset.Open strSQLQuery , objConnection, _
adOpenStatic, adLockOptimistic

'*Store file info in database
strSQLQuery = "INSERT INTO test (filename) VALUES ('"& scanfilename &"')"
objRecordset.Open strSQLQuery, objConnection, _
adOpenStatic, adLockOptimistic



That's snippets of how I'm inserting the values. I've verified that the filename value is populated before the query is run but I still don't get the value in the database. That query is actually scaled down for testing because I obviously want more info than just the file name. When I was trying to put all info I thought maybe it was the backslashes in the filepath that was causing the issue but the small version doesn't work either. Any suggestions on where to go from here is greatly appreciated.

Thanks

[TheShaner]

I would first write a little debug that would output the entire query string for storing the file name into the database. You may find that there are characters in your filename that are not allowing a proper insert into the database that would require you to use the Replace function for escaping bad characters. But then that would generate errors for a bad mySQL command.

Also, are we to assume that the code ran without errors, but when checking the table test, the field filename didn't contain any values?

If so, then that debug should show nothing stored in your variable scanfilename, which would mean that your method for grabbing the shortened version of the file name is not doing its job.

Also, how are your tables set up? What type have you declared the field names? Varchar? If varchar, how large are they?

Also, I may be wrong, but shouldn't you be doing:
Set objRecordset = objConnection.execute(strSQLQuery)
Instead of attempting to open a recordset with a query that is inserting into that recordset? Not sure if that would have anything to do with it or not though.

If you are getting errors, then it'd be best to post those in your next post, because that would help immensely.

-Shane

[Loxias]

Bad characters was my original thought when trying to insert all of the file info. I know there are backslashes in the path which is why I started testing with just the filename. The test field for the file name is a varchar(45). I'm using 100 in the real table.

As far as I can tell there are no errors being thrown (although I haven't checked for errors on the query itself). I'm currently echoing the variable and each time it loops it shows me a different filename. So the variable is being set properly.

I'll try changing from Open to Execute and see if that helps. Not sure if it matters or not myself, but it is working for the computer name and the user name so I stuck with it for the file info.

If needed I can post the full code for a better picture of what I'm doing. However, everything else is just the gathering of the file data.

[TheShaner]

I would attempt to look at the query string before calling the open command and see what the actual string looks like.

As for Open vs. Execute, I have no idea if it would matter or not. I was just throwing an idea out, hehe. Like you said, if it worked for the others, it seems it'd work for that query too.

A varchar(45) should be able to hold the filenames, so that wouldn't be the problem.

If no errors are being thrown, I'm sort of at a lost too. Check the query string and see where that gets you. I think someone smarter than me is going to have to help you, haha.

-Shane

[neocool00]

Actually, if you are just inserting data and not returning any values, you shouldn't even use a recordset, you should just call the Execute method of your Connection object. It will save on overhead if you are doing this in a looping fashion. Other than that, I would agree with Shane on looking at the sql statements.

[Loxias]

Alright, I'm progressing. So far I've added the size and drive but the path bombs out due to the backslashes. Any suggestions on escaping those characters on the variable when inserting?

[TheShaner]

In vbscript, escaping characters is a matter of doubling them. If I wanted to escape quotations, I would double them like this: "". That would give me one quotation mark in a string. So you would do the same with backslaskes. If you called your variable for the file path, filePath, you would do this:

filePath = Replace(filePath,"\","\\")

This then puts a \\ for each \ in your variable. So then when your vbscript sees the double backslashes, it won't attempt to use it as a special character and print a single backslash for each double it encounters in the string.

-Shane

[Loxias]

Found Replace right after my post and just got it inserting into the db properly. Thanks for all your help Shane. Now the real work begins :D

[TheShaner]

Np. Good luck!