Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!



Share:
Notifications
Clear all

[Solved] ODBC issue when accessing MovieScanner SQL database

9 Posts
2 Users
0 Likes
574 Views
(@Anonymous)
Joined: 1 second ago
Posts: 0
Topic starter  

I have only just started to use this excellent piece of software.  I want to access the SQLite3 database output in MS Access 2007.

I successfully created the ODBC link and can import the tables in MS Access.

The only issue I have is that the LastModified field in the Files table is completely empty.

All the other fields seem to have been populated correctly.

If I access the SQLite3 database using the DB Browser for SQLite it shows that the field does indeed contain data.

Any suggestions for how I can get this working would be welcome.

Thanks

Glenn


   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2703
 

Hi Glenn,

I've never used ODBC to connect to a SQLite database - I usually use something like one of these two tools: Valentina Studio or DB Browser for SQLite -- both available fro FREE and for Windows, Linux and macOS.

Having said that, and you probably already tried that: Start a new database and add only one file. Optionally ad it again or do a refresh. Close MoveScanner and try accessing it through ODBC again or optionally one of the two tools I mentioned.

Depending on how conversion is handled, you should see a date or a large float number (something like: 2459751.04725694).
For example: Valentina Studio (takes a few clicks to get the free one) shows it properly as a date. DB Browser on the other hand shows it as a float (by default on my Mac anyway).

Note: Seems MS Access / ODBC used to have some issues with SQLiite field types, way back in the day. Not sure if this still is the case (am sure you ran into those posts as well when trying to find a fix).


   
ReplyQuote
(@Anonymous)
Joined: 1 second ago
Posts: 0
Topic starter  

Hi Hans

Yes, DB Browser displays it as a large float number.  I changed the Display Format to dd/mm/yy and then in displayed it as a date.  Strangely I couldn't do date and time but it was fine with a date.

My reason for getting the data into MS Access though is that I do lots more processing with it.  A bit more investigating at my end I think.


   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2703
 

Can you have MS Access (or ODBC) display the LastModified field as a float?
I'm sure MS Access can convert that on the fly to a date.

Note: SQLite does have some limitations when it comes to date and time datatypes, so developers can use their own interpretation (I used "Real" of course). This is probably why ODCB/Access has issue with this.

From the SQLite documentation (source):

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.


   
ReplyQuote
(@Anonymous)
Joined: 1 second ago
Posts: 0
Topic starter  

Unfortunately there's just nothing there in the field at all.  I did try to change the type temporarily but it is just an empty field.  Although an inconvenience I think for my purposes I may be able to ignore the field altogether without it being too much of a problem.


   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2703
 

Ah shoot ... (I think the format is number days as described with the "REAL" option, since November 24, 4714BC)
Did you try SQLiteODBC? (just something I found, trying to find a solution)


   
ReplyQuote
(@Anonymous)
Joined: 1 second ago
Posts: 0
Topic starter  

No I hadn't come across that.  I will have a look tomorrow.  Thanks for your suggestion.


   
ReplyQuote
(@Anonymous)
Joined: 1 second ago
Posts: 0
Topic starter  

I did install the SQLite driver you suggested but it is very difficult to work out what version I am actually using.  I can't therefore say which I was using but it made no difference.

However; when doing the ODBC Driver Connect from Access; one of the options is "Julian Day Conv.".  I ticked it and now the LastModified data appears when I import the table.

Who knew?

Anyway problem is now resolved.


   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2703
 

Oh wow! Nice! 

Who knew indeed! I didn't, that's for sure haha ... good to see you're got it to work though! 👍 


   
ReplyQuote
Share: