Hi All,
I found something to workaround the problem, something very rough but effective, needed to run that in a scheduled task...
I cleanup DB entries according to the files in the dropbox folder, then I populate the DB with the attached script (a lot of format make-up, but anyway...).
This loop works, I can open files from display/player menu; though, in the MIP library screen I can't see those entries pushed by script...
If anyone had some time to test it, I'd appreciate
BR,
Andrea
' Populates library table with metadata & writes a log with the list of inserted records
Const adOpenFwdOnly = 0
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\LocalRepo")
objConnection.Open "DSN=LibraryDB;"
objRecordset.CursorLocation = adUseClient
For Each objFile In objFolder.Files
'marked tablecolumns are populated by script:
'**full_path**, relative_path, **file_name**, **file_size**, **make_date**, **reg_date**, resolution, play_time, **file_type**,
'**category**, **attribute**, upper_location, musician, album_name, **bitrate**, Fourcc, image_event, comment, Add_On
strFullname = Split(objFile.Name, ".")
strSQL = "SELECT file_name FROM MEDIA_FILE WHERE file_name ='" & strFullname(0) & "'"
objRecordset.Open strSQL, objConnection, adOpenStatic, adLockOptimistic
blnTest = objRecordset.EOF
objRecordset.Close
if blnTest then
dttm_string = objFile.DateCreated
date_parts = Split(dttm_string, "/")
result_dttm = Left(date_parts(2), 4) & "-" & date_parts(1) & "-" & date_parts(0)
time_parts = Split(dttm_string, ".")
if Right(time_parts(0), 2) \ 12 then
result_dttm = result_dttm & " PM " & Format(Right(time_parts(0), 2) - 12, "00")
else
result_dttm = result_dttm & " AM " & Right(time_parts(0), 2)
end if
result_dttm = result_dttm & ":" & time_parts(1) & ":" & time_parts(2)
Select Case Len(CStr(objFile.Size))
Case 1, 2, 3
strFilesize = objFile.Size & " B"
Case 4, 5, 6
strFilesize = Round(objFile.Size / 1024, 0) & " KB"
Case Else
strFilesize = Round(objFile.Size / 1024 / 1024, 1) & " MB"
End Select
strSQL = "INSERT INTO MEDIA_FILE (full_path, file_name, file_size, " & _
"make_date, reg_date, file_type, category, attribute, bitrate)" & _
" VALUES ('" & _
objFile.Path & "', '" & strFullname(0) & "', '" & strFilesize & "', '" & result_dttm
dttm_string = objFile.DateLastAccessed
date_parts = Split(dttm_string, "/")
result_dttm = Left(date_parts(2), 4) & "-" & date_parts(1) & "-" & date_parts(0)
time_parts = Split(dttm_string, ".")
if Right(time_parts(0), 2) \ 12 then
result_dttm = result_dttm & " PM " & Format(Right(time_parts(0), 2) - 12, "00")
else
result_dttm = result_dttm & " AM " & Right(time_parts(0), 2)
end if
result_dttm = result_dttm & ":" & time_parts(1) & ":" & time_parts(2)
strSQL = strSQL & "', '" & result_dttm & "', '." & strFullname(1) & "'"
Select case strFullname(1)
case "xls", "xlt", "xla", "xlm", "csv", "doc", "dot", "pdf", "ppt", "pps", "pot"
strCat = "Office"
case "jpg", "jpeg", "gif", "bmp", "png"
strCat = "Image"
case "fla", "swf"
strCat = "Flash"
case else
strCat = ""
end select
strSQL = strSQL & ", '" & strCat & "', false, 0)"
objConnection.Execute strSQL
end if
Next 'objFolder.Files
objConnection.Close
Set objConnection = nothing
Set objRecordset = nothing
Set objFSO = nothing
Set objFolder = nothing
Set objFile = nothing