Import of .dat file "successful". I can only see one table's contents

May 8, 2013 at 8:59 PM
I have a SQLite file with extension .dat. I've been able to create the PSDrive successfully and get the lists of tables in the db. I followed your instructions on how to add a table and fill it with data; that was successful as well. The isue I'm running into is reading data from tables that already exist in the database. I opened it up in the SQLite Manager for FireFox so I know there's data in it, I just can't access the data from PS. "Get-Childitem mozy:\mytable" returns info, but "get-childitem mozy:\fsitems" does not. -Verbose shows that "select * from fsitems" is being created but nothing is returned. I wouldn't think it was a permission error since I could create other tables in this db. "get-childitem mozy:\fsitems | get-member" returns an error: "no object has been specified to the get-member cmdlet".
Any thoughts?
Coordinator
May 9, 2013 at 12:47 PM
I've seen behavior similar to this when the database was created with a different version of sqlite, or when a database table has no primary key. Is the dat file something you could share? If so, please contact me outside of this thread via my profile (https://www.codeplex.com/site/users/view/beefarino) so we can keep your data off this public thread.

If you don't want to share, could you do the following for me:
$debugpreference='continue'
$verbosepreference='continue'
get-childitem mozy:\fsitems
This will cause the SQLite provider to spill a TON of info - if you could post that info to this thread that would be a huge help.
May 9, 2013 at 3:39 PM
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItemsDynamicParameters] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: +81 Opening session 81
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItemsDynamicParameters] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: +82 Opening session 82
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItemsDynamicParameters] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False; True] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItemsDynamicParameters]
DEBUG: -82 Closing session 82
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItemsDynamicParameters]
DEBUG: -81 Closing session 81
May 9, 2013 at 3:39 PM
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItemsDynamicParameters]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildName] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetRootPath] ( [] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetRootPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [fsitems] from [GetChildName]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetParentPath] ( [[Data Source=hidden\conf.dat]\fsitems; [Data Source=hidden\conf.dat]] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]] from [GetParentPath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [MakePath] ( [[Data Source=hidden\conf.dat]; fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [[Data Source=hidden\conf.dat]\fsitems] from [MakePath]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [ItemExists] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: +83 Opening session 83
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [ItemExists] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: +84 Opening session 84
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [ItemExists] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False; True] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [ItemExists]
DEBUG: -84 Closing session 84
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [ItemExists]
DEBUG: -83 Closing session 83
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [ItemExists]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [IsItemContainer] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: +85 Opening session 85
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [IsItemContainer] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: +86 Opening session 86
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [IsItemContainer] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False; True] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [IsItemContainer]
DEBUG: -86 Closing session 86
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [IsItemContainer]
DEBUG: -85 Closing session 85
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [True] from [IsItemContainer]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItems] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: +87 Opening session 87
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItems] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: +88 Opening session 88
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [GetChildItems] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False; True] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] >> Entering [CreateContext] ( [[Data Source=hidden\conf.dat]\fsitems; False; False] )
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [CodeOwls.PowerShell.Provider.PathNodeProcessors.Context] from [CreateContext]
VERBOSE: SELECT * FROM fsitems
DEBUG: filling data table
DEBUG: data table fill complete
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItems]
DEBUG: -88 Closing session 88
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItems]
DEBUG: -87 Closing session 87
DEBUG: [CodeOwls.PowerShell.SQLite.Provider.SQLiteProvider] << Returning [null] from [GetChildItems]
May 9, 2013 at 3:57 PM
I changed the source path from what it actually was to "hidden" before posting here. The table I'm looking at when viewed inside SQLite Manager in FF shows that you are correct on the part of the table doesn't have a primary key, but the one we created through PS does.

I copied the .dat file from the source so we are working with copies of the original, so there wouldn't be an issue in creating a new column with a primary key for that table. I am able to get info from all the other tables in this particular database that have a primary key set.
Coordinator
May 10, 2013 at 12:18 PM
I'm nearly 100% positive that the missing PK is the issue. This has come up several times in the past and I have a fix planned - would you mind testing a prerelease module?
May 13, 2013 at 2:50 PM
Not a problem.