[sqlite] backup memory database not working
Thank you Clemens, this way all is fine! Am 11.03.2016 um 16:27 schrieb Clemens Ladisch: > asdf asdf wrote: >> My intention: >> - loading a file db into a memory db. > This is not done with ATTACH, but by making a backup of the > file DB with the memory DB as destination. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why are there no Authenticode signatures on prebuilt DLLs or tools?
On 3/10/16, Kyle Hamilton wrote: > Is there a way to request that the build pipeline for prebuilt DLLs > and tools for Windows be modified to include Authenticode signatures? A 3-year DigiCert Authenticode signing cert costs $1K. How much of that cost would you be willing to underwrite? -- D. Richard Hipp drh at sqlite.org
[sqlite] backup memory database not working
asdf asdf wrote: > My intention: > - loading a file db into a memory db. This is not done with ATTACH, but by making a backup of the file DB with the memory DB as destination. Regards, Clemens
[sqlite] backup memory database not working
I am not sure if i understand you correctly. My intention: - loading a file db into a memory db. - work with memory db during runtime. so it could be changed - save mem db back to file db. Is this possible ? Thank you Am 11.03.2016 um 16:19 schrieb Clemens Ladisch: > asdf asdf wrote: >> - create a memory db >> - attach a file db >> - backup memory db >> this fails. > An attached database stays separate, i.e., its data is not merged into > the backup. > > To backup the file DB, give its name (and not "main") to > sqlite3_backup_init(). > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] backup memory database not working
asdf asdf wrote: > - create a memory db > - attach a file db > - backup memory db > this fails. An attached database stays separate, i.e., its data is not merged into the backup. To backup the file DB, give its name (and not "main") to sqlite3_backup_init(). Regards, Clemens
[sqlite] backup memory database not working
Hello,
meanwhile i could reproduce my situation more precisely.
Short:
- create a memory db
- attach a file db
- backup memory db
this fails.
Please watch my compile ready example (VS 2013) attached.
Thank you for help
Am 10.03.2016 um 21:56 schrieb R Smith:
>
>
> On 2016/03/10 10:41 PM, asdf asdf wrote:
>> Hello,
>> what do you mean, please ? What code is not shown:my own code (and what
>> could be the cause then) using the example or what i posted in
>> stackoverflow.
>>
>> I would be happy to solve it. Any information appreciated-
>
> He means that there is something else that is wrong in your code that
> you've used to test this Backup feature. We cannot see your full code,
> so we do not know what is wrong with it, but we know something is
> wrong because the backup from memory works when we do it, and works
> when done like the example. You can easily use the command line
> SQLite3.exe tool to test it.
>
> So, if it works correctly for us, meaning it is not an SQLite bug, but
> there might be something in your code that is missing or weird and we
> would like to help you find it, however, only if you show us the exact
> code you've used to test with. Also the DB schema - some SQL perhaps
> to populate the in-memory database. Perhaps something about the schema
> is strange and causes the problem, then it might even be a bug - but
> we can't know that until we have exactly what you have.
>
> Thanks,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-- next part --
#include "stdafx.h"
#include "sqlite3.h"
#include
#include
#include
int backupDb(
sqlite3 *pDb, /* Database to back up */
const char *zFilename, /* Name of file to back up to */
void(*xProgress)(int, int) /* Progress function to invoke */
){
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup;/* Backup handle used to copy data */
/* Open the database file identified by zFilename. */
rc = sqlite3_open(zFilename, );
if( rc==SQLITE_OK ){
/* Open the sqlite3_backup object used to accomplish the transfer */
pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
if( pBackup ){
/* Each iteration of this loop copies 5 database pages from database
** pDb to the backup database. If the return value of backup_step()
** indicates that there are still further pages to copy, sleep for
** 250 ms before repeating. */
do {
rc = sqlite3_backup_step(pBackup, 5);
if(xProgress != nullptr)
{
xProgress(
sqlite3_backup_remaining(pBackup),
sqlite3_backup_pagecount(pBackup)
);
}
if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
sqlite3_sleep(5);
}
} while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );
/* Release resources allocated by backup_init(). */
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pFile);
}
/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);
return rc;
}
booldo_select(sqlite3*db, const std::string& sql, const std::string&
comment = "")
{
if (db == nullptr)
std::cout << "\ndo_select: no database";;
sqlite3_stmt* stmt = nullptr;
std::cout << "\ndo_select("<";
int res = sqlite3_prepare_v2(db, sql.c_str(), -1, , 0);
if (res == SQLITE_OK)
{
/// cycle result rows
do{
res = sqlite3_step(stmt);
int row = 0;
int cols = sqlite3_column_count(stmt);
switch (res)
{
case SQLITE_DONE:
break;
case SQLITE_ROW:
{
/// cycle result columns
for (int col = 0; col < cols; col++)
{
std::string sres = (const char*)sqlite3_column_text(stmt,
0);
std::cout << "\n\t" <<"(" << row << "," << col << ") : " <<
sres.c_str();
}
}
break;
default:
{
sqlite3_finalize(stmt);
std::cout << "\n\t"<< "sql error: " << sql.c_str();
return false;
}
break;
} /// switch
} while (res == SQLITE_ROW);
sqlite3_finalize(stmt);
stmt = nullptr;
return true;
}
else
{
sqlite3_finalize(stmt);
std::cout << "\n\t" << "sql error: " << sql.c_str();
}
return false;
}
sqlite3*create_db(const std::string& name,bool make_table_and_data)
{
sqlite3*db = nullptr;
[sqlite] Mailing List Request
Any way we could have the mailing list strip tabs from the subject lines? I have been getting very odd subject formatting lately. Ex. Re: [sqlite] How to use sqlite3_blob_open when the blobcolumn is empty? Re: [sqlite] Are BLOB stored inline with the rest of the record fields? Re: [sqlite] How to check if connection to main database is still open or closed before new query ? This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Changes to VFS starting 3.8.3
On Thu, Mar 10, 2016 at 5:26 PM, Richard Hipp wrote: > Perhaps your in-memory VFS was relying on some unspecified behavior > that changed? Some tests finally led to the source of my problems. When I implemented the handlers of vfs interface before, I made xFileControl return SQLITE_ERROR instead of SQLITE_NOTFOUND. For whatever reasons sqlite versions below 3.8.3 allowed me to do this in some of the usage contexts. Today when I tested I saw that querying PRAGMA Encoding in 3.7.11 also gave an error even before any write-related queries. So in my initial case probably some minor logic change in 3.8.3 led to a critical xFileControl request while 3.8.2 was ok with the wrong implementation.
[sqlite] "Circular" order by
On Fri, 11 Mar 2016 09:05:27 +0200, R Smith wrote: > >On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: >> On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: >> >> >>> I do this kind of thing so often when filling a selection box for instance: >>>SELECT 'None' >>> UNION ALL >>>SELECT City FROM Countrylist WHERE Country = :1 >>> UNION ALL >>> SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City >>> >>> Which, as you can deduce, adds a 'None' to the option list, then the >>> selected country's capital city, then the other cities in alphabetical >>> order. I now think I need a more sophisticated method to ensure that output >>> doesn't get mangled. If SQLite ever changes this behaviour, lots of things >>> will break for me, but, that's life, I will start fixing them all. >>> >>> Heh, assumptions... that'll teach me! :) >>> Ryan >>> >> Not that I want to hijack the thread, but with the country list I got from >> here: >> https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql >> >> I came up with this simple modification to your query: >> >> SELECT 'None',0 as OrderNum >> UNION ALL >>SELECT Value,1 FROM List WHERE Value = :1 >> UNION ALL >>SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value > >Yeah, though I think this one might still be in danger of not ending up >in the correct order since the UNIONed section, although able to order, >doesn't guarantee order, nor influences the order in the other UNIONed >sections and output as a whole... a bit weird, but understandable. So >the foolproof way I think would be adapting your advice into this: > >SELECT V FROM ( > SELECT 'None' AS V, 0 as Ord > UNION ALL > SELECT Value,1 FROM List WHERE SomeIndex == :1 > UNION ALL > SELECT Value,2 FROM List WHERE SomeIndex <> :1 >) ORDER BY Ord, V That is not needed, as http://www.sqlite.org/lang_select.html#orderby says: "The ORDER BY clause [] In a compound SELECT statement, only the last or right-most simple SELECT may have an ORDER BY clause. That ORDER BY clause will apply across all elements of the compound. If the right-most element of a compound SELECT is a VALUES clause, then no ORDER BY clause is allowed on that statement." > Easy enough, but alas!, the amount of places I have to go and change... :) HTH ;) -- Regards, Kees Nuyt
[sqlite] "Circular" order by
On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: > On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: > > >> I do this kind of thing so often when filling a selection box for instance: >>SELECT 'None' >> UNION ALL >>SELECT City FROM Countrylist WHERE Country = :1 >> UNION ALL >> SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City >> >> Which, as you can deduce, adds a 'None' to the option list, then the >> selected country's capital city, then the other cities in alphabetical >> order. I now think I need a more sophisticated method to ensure that output >> doesn't get mangled. If SQLite ever changes this behaviour, lots of things >> will break for me, but, that's life, I will start fixing them all. >> >> Heh, assumptions... that'll teach me! :) >> Ryan >> > Not that I want to hijack the thread, but with the country list I got from > here: > https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql > > I came up with this simple modification to your query: > > SELECT 'None',0 as OrderNum > UNION ALL >SELECT Value,1 FROM List WHERE Value = :1 > UNION ALL >SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value Yeah, though I think this one might still be in danger of not ending up in the correct order since the UNIONed section, although able to order, doesn't guarantee order, nor influences the order in the other UNIONed sections and output as a whole... a bit weird, but understandable. So the foolproof way I think would be adapting your advice into this: SELECT V FROM ( SELECT 'None' AS V, 0 as Ord UNION ALL SELECT Value,1 FROM List WHERE SomeIndex == :1 UNION ALL SELECT Value,2 FROM List WHERE SomeIndex <> :1 ) ORDER BY Ord, V Easy enough, but alas!, the amount of places I have to go and change... :) Thanks, Ryan
[sqlite] Mailing List Request
On Fri, 11 Mar 2016, Rousselot, Richard A wrote: > Any way we could have the mailing list strip tabs from the subject lines? > I have been getting very odd subject formatting lately. Richard, That's dependent upon the mail user agent you use, not the mail list. The mail list management software passes on all messages as received. Rich
[sqlite] backup memory database not working
Thank you Ryan,
i wrote a small test routine using the same backup routine i use in my code
and in fact, here it works. Attached.
Need to investigate for differences.
Thanks to community for support
Am 10.03.2016 um 21:56 schrieb R Smith:
>
>
> On 2016/03/10 10:41 PM, asdf asdf wrote:
>> Hello,
>> what do you mean, please ? What code is not shown:my own code (and what
>> could be the cause then) using the example or what i posted in
>> stackoverflow.
>>
>> I would be happy to solve it. Any information appreciated-
>
> He means that there is something else that is wrong in your code that
> you've used to test this Backup feature. We cannot see your full code,
> so we do not know what is wrong with it, but we know something is
> wrong because the backup from memory works when we do it, and works
> when done like the example. You can easily use the command line
> SQLite3.exe tool to test it.
>
> So, if it works correctly for us, meaning it is not an SQLite bug, but
> there might be something in your code that is missing or weird and we
> would like to help you find it, however, only if you show us the exact
> code you've used to test with. Also the DB schema - some SQL perhaps
> to populate the in-memory database. Perhaps something about the schema
> is strange and causes the problem, then it might even be a bug - but
> we can't know that until we have exactly what you have.
>
> Thanks,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-- next part --
#include "stdafx.h"
#include "sqlite3.h"
#include
#include
#include
//#include "sqlite3ext.h"
int backupDb(
sqlite3 *pDb, /* Database to back up */
const char *zFilename, /* Name of file to back up to */
void(*xProgress)(int, int) /* Progress function to invoke */
){
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup;/* Backup handle used to copy data */
/* Open the database file identified by zFilename. */
rc = sqlite3_open(zFilename, );
if( rc==SQLITE_OK ){
/* Open the sqlite3_backup object used to accomplish the transfer */
pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
if( pBackup ){
/* Each iteration of this loop copies 5 database pages from database
** pDb to the backup database. If the return value of backup_step()
** indicates that there are still further pages to copy, sleep for
** 250 ms before repeating. */
do {
rc = sqlite3_backup_step(pBackup, 5);
if(xProgress != nullptr)
{
xProgress(
sqlite3_backup_remaining(pBackup),
sqlite3_backup_pagecount(pBackup)
);
}
if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
sqlite3_sleep(250);
}
} while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );
/* Release resources allocated by backup_init(). */
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pFile);
}
/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);
return rc;
}
boolbackup_test_sqlite_mem(void)
{
sqlite3*db=nullptr;
/// create memory db
if(sqlite3_open(":memory:", ) == SQLITE_OK)
{
/// create table
std::string sql = "CREATE TABLE IF NOT EXISTS [stock] ("
"[sid] VARCHAR(40) UNIQUE NOT NULL PRIMARY KEY,"
"[price] FLOAT "
" ) ";
char* db_err = nullptr;
if(sqlite3_exec(db, sql.c_str(), nullptr, nullptr, _err) ==
SQLITE_OK)
{
/// fill table with 1 records
for(int i=0;i < 1;i++)
{
std::ostringstream sstr;
sstr<<"INSERT INTO stock (sid,price) VALUES(";
sstr<<"'"<
