Re: [sqlite] copying data from memory to file

[Igor Tandetnik]

Berryl Hesh  wrote:
> After generating an in memory db schema, I use the attach command to
> replicate that schema to a file 

Consider using backup API: http://www.sqlite.org/c3ref/backup_finish.html
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copying data from memory to file

[Simon Davies]

On 24 November 2010 18:16, Berryl Hesh  wrote:
> Hello:
>
> After generating an in memory db schema, I use the attach command to
> replicate that schema to a file - at least that is my intention. I get
> a no such table error, so something is wrong somewhere. Can someone
> get me on the right track?

I see nothing in your code that creates the schema. You are trying to
copy data to non-existent tables.

"SELECT SQL FROM sqlite_master WHERE type='table';"

will get you the SQL to execute in the attached db to recreate the
schema tables, then you can copy the data.

You may need to handle views, triggers and indices as well as tables.

>
> Cheers,
> Berryl
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] copying data from memory to file

[Berryl Hesh]

Hello:

After generating an in memory db schema, I use the attach command to
replicate that schema to a file - at least that is my intention. I get
a no such table error, so something is wrong somewhere. Can someone
get me on the right track?

Cheers,
Berryl

=
Code called after in memory db is created with test data:
=

public void ExportData(SQLiteConnection conn, string dataFile)
{
if (!File.Exists(dataFile)) {
SQLiteConnection.CreateFile(dataFile);
}
_attachDatabase(conn, dataFile);
foreach (var table in _getTableNames(conn)) {
_copyTableData(conn, table,
_getAttachedTableName(table));
}
_detachDatabase(conn);
}

private static IEnumerable
_getTableNames(SQLiteConnection conn) {
var tables = SQLiteMetaDataCollectionNames.Tables;
var dt = conn.GetSchema(tables);
return dt.Rows.Cast().Select(R => (string)
R["TABLE_NAME"]);
}

private static string _getAttachedTableName(string table)
{ return string.Format("{0}.{1}", ATTACHED_DB, table); }

private static void _attachDatabase(SQLiteConnection conn,
string dataFile) {
var cmd = new SQLiteCommand(conn)
  {
  CommandText = string.Format("ATTACH '{0}' AS
{1}", dataFile, ATTACHED_DB)
  };
_log.Debug(cmd.CommandText);
cmd.ExecuteNonQuery();
}

private static void _copyTableData(SQLiteConnection conn,
string source, string destination) {
var cmd = new SQLiteCommand(conn)
{
CommandText = string.Format("INSERT INTO {0} SELECT *
FROM {1}", destination, source)
};
_log.Debug(cmd.CommandText);
cmd.ExecuteNonQuery();
}

==
error, stack trace and log output
==

TestFixture failed: System.Data.SQLite.SQLiteException : SQLite error
no such table: asdfgaqwernb.ActorRole
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String
strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at
SQLiteTesting.Helpers.SQLiteLoader._copyTableData(SQLiteConnection
conn, String source, String destination) in C:\Users\Lord & Master
\Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers
\SQLiteLoader.cs:line 71
   at SQLiteTesting.Helpers.SQLiteLoader.ExportData(SQLiteConnection
conn, String dataFile) in C:\Users\Lord & Master\Documents\Projects
\Data\NHib projects\Cookbook\SQLiteTesting\Helpers
\SQLiteLoader.cs:line 33
   at SQLiteTesting.Helpers.QueryTests.OnFixtureSetUp() in C:\Users
\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook
\SQLiteTesting\Helpers\QueryTests.cs:line 32
   at SQLiteTesting.Helpers.BaseFixture.FixtureSetUp() in C:\Users
\Lord & Master\Documents\Projects\Data\NHib projects\Cookbook
\SQLiteTesting\Helpers\BaseFixture.cs:line 23
 2010-11-24 10:00:24,968 INFO processing cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for:
Eg.Core.Movie
 2010-11-24 10:00:24,968 INFO cascade NHibernate.Engine.CascadingAction
+SaveUpdateCascadingAction for collection: Eg.Core.Movie.Actors
 2010-11-24 10:00:24,970 INFO done cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for
collection: Eg.Core.Movie.Actors
 2010-11-24 10:00:24,970 INFO deleting orphans for collection:
Eg.Core.Movie.Actors
 2010-11-24 10:00:24,973 INFO done deleting orphans for collection:
Eg.Core.Movie.Actors
 2010-11-24 10:00:24,973 INFO done processing cascade
NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for:
Eg.Core.Movie
 2010-11-24 10:01:25,195 DEBUG ATTACH '...\SPUD.db3' AS asdfgaqwernb
 2010-11-24 10:03:22,362 DEBUG INSERT INTO asdfgaqwernb.ActorRole
SELECT * FROM ActorRole
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users