Issue with updating database content (C++)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue with updating database content (C++)

Ali Dorri
Dear All,

I am using sqlite to store public key (PK), signature (Sig) and hashes
generated by crypto++ library in a C++ program. I encode the PKs to base64
strings and then store them in the database. Later in my program, I want to
update records of data by searching based on the PK, i.e.

*char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK =
null   where PK = '%q' ;", endoced_pub.c_str());

 *sqlite3_prepare_v2*(db, zSQL, -1, &stmt, NULL);

where BC is the name of my table and endoced_pub is the string containing
the encoded PK. But, it does not find any match thus it does nothing :(

I connect to the database and see the PK values. They exactly match with
the PKs printed in the main program. Thus it should work.

The only thing that comes to my mind is that when the database stores the
PK , it does some other encodings on that, and thus it does not match with
the key I send in update request. The PK is a BLOB type, i.e.,

   sql = "CREATE TABLE BC("  \

                "T_ID          TEXT    ," \

                "P_T_ID           TEXT   ," \

                "PK            BLOB    ," \

                "Signature        BLOB ," \

                "Block_ID         TEXT  );";

I also used the prepare command to ensure that my command is encoded based
on sqlite but it does not work.

sqlite3_stmt *stmt;


  *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK
= null   where PK = '%q' ;", endoced_pub.c_str());

  *sqlite3_prepare_v2*(db, zSQL, -1, &stmt, NULL);

  rc = *sqlite3_step*(stmt);


Here is how I store the PKs initially in database:

*char* *zSQL = *sqlite3_mprintf*("INSERT INTO BC ( T_ID , P_T_ID , PK ,
Signature , Block_ID ) VALUES ('%q','%q','%q','%q','%q');", BC.trans[l].
TransactionID.c_str() ,BC.trans[l].previousTransactionID.c_str() ,BC.trans
[l].PK.c_str() ,BC.trans[l].Sign.c_str(),BC.block_hash.c_str());


Any thoughts on how can I solve the issue?


Regards

Ali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Clemens Ladisch
Ali Dorri wrote:
> I encode the PKs to base64
> [...]
> The PK is a BLOB type, i.e.,
>    sql = "CREATE TABLE BC("  \
>                 "PK            BLOB    ," \

Why do you store a text value in a blob field?

> "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;

That does not update the PK column.

   UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;
                                  ^^^^

Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Ali Dorri
Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
   UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch <[hidden email]> wrote:

> Ali Dorri wrote:
> > I encode the PKs to base64
> > [...]
> > The PK is a BLOB type, i.e.,
> >    sql = "CREATE TABLE BC("  \
> >                 "PK            BLOB    ," \
>
> Why do you store a text value in a blob field?
>
> > "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>
> That does not update the PK column.
>
>    UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;
>                                   ^^^^
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Andy Ling-2
You're not deleting any rows, you're just changing the value of the data in the row.

What you probably want is something like

DELETE FROM BC WHERE PK = '%q';

HTH

Andy Ling


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Ali Dorri
Sent: Wed 30 August 2017 12:19
To: SQLite mailing list
Subject: Re: [sqlite] Issue with updating database content (C++)

Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
   UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch <[hidden email]> wrote:

> Ali Dorri wrote:
> > I encode the PKs to base64
> > [...]
> > The PK is a BLOB type, i.e.,
> >    sql = "CREATE TABLE BC("  \
> >                 "PK            BLOB    ," \
>
> Why do you store a text value in a blob field?
>
> > "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>
> That does not update the PK column.
>
>    UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;
>                                   ^^^^
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---------------------------------------------------------------------------------------
This email has been scanned for email related threats and delivered safely by Mimecast.
For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

R Smith
In reply to this post by Ali Dorri
Perhaps:

DELETE FROM BC WHERE PK = '%q';


Also note, the DB size may or may not decrease when deleting, it clears
data, not space.

You can use this SQL:

VACUUM;

to get rid of empty space in the DB file and re-pack it correctly.



On 2017/08/30 1:19 PM, Ali Dorri wrote:

> Hi,
>
> Thanks, now it works and removes all except for the PK. How can I remove
> the PK then? i.e. what is the correct way of doing the following?
>     UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;
>
> Another issue I have is that when I remove these entries, the size of the
> database does not decrease. I do the VACUUM after the program, but it does
> not work and the size of the database does not decrease while the data are
> removed.
> Can anyone help me in this regard?
>
> Thanks
>
> On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch <[hidden email]> wrote:
>
>> Ali Dorri wrote:
>>> I encode the PKs to base64
>>> [...]
>>> The PK is a BLOB type, i.e.,
>>>     sql = "CREATE TABLE BC("  \
>>>                  "PK            BLOB    ," \
>> Why do you store a text value in a blob field?
>>
>>> "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>> That does not update the PK column.
>>
>>     UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;
>>                                    ^^^^
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

R Smith
Clarification:

When I said:


>
> You can use this SQL:
>
> VACUUM;
>
> to get rid of empty space in the DB file and re-pack it correctly.

by "correctly" I really meant "tightly". There is nothing incorrect
about the data before the vacuum, of course.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Simon Slavin-3
In reply to this post by Ali Dorri


On 30 Aug 2017, at 12:19pm, Ali Dorri <[hidden email]> wrote:

> Thanks, now it works and removes all except for the PK. How can I remove
> the PK then? i.e. what is the correct way of doing the following?
>   UPDATE BC set Signature = null ,    PK = null   where PK = '%q' ;

That syntax is correct.  In your previous example you were using "AND" where you should have had a comma.

> Another issue I have is that when I remove these entries, the size of the
> database does not decrease.

The above command does not delete a row.  It overwrites a couple of fields in the row with nulls.  Which will cause confusion because one of those fields is your primary key and it won’t work.

To delete a row, which is probably what you should be doing, use the DELETE command, e.g.

DELETE FROM BS WHERE PK = '%q';

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Jens Alfke-2
In reply to this post by Ali Dorri


> On Aug 29, 2017, at 6:22 PM, Ali Dorri <[hidden email]> wrote:
>
> *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK =
> null   where PK = '%q' ;", endoced_pub.c_str());

FYI, your PK values are not being stored as blobs, rather as hex-encoded strings. Maybe not a big deal since they're not very large; but f you do want to store them as blobs, there's a special prefix before a string literal (an "x"? Can't recall) that makes it a hex-encoded blob.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Dominique Devienne
On Wed, Aug 30, 2017 at 5:48 PM, Jens Alfke <[hidden email]> wrote:

> > On Aug 29, 2017, at 6:22 PM, Ali Dorri <[hidden email]> wrote:
> >
> > *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK
> =
> > null   where PK = '%q' ;", endoced_pub.c_str());
>
> FYI, your PK values are not being stored as blobs, rather as hex-encoded
> strings. Maybe not a big deal since they're not very large; but f you do
> want to store them as blobs, there's a special prefix before a string
> literal (an "x"? Can't recall) that makes it a hex-encoded blob.


it's x'abcd01'. But much better yet, don't use literal SQL and printf, you
proper binding [1].
you'll save yourself from SQL injections, and get better performance too.
--DD

[1] https://sqlite.org/c3ref/bind_blob.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Ali Dorri
Thanks all,

It works. I think I should a way to calculate this reduction in the size of
the database as sometimes it seems there is no difference in size (after
VACUUM) between removing one row or two or three.
This is a research work so I need it to show me exactly how much data is
removed.

Any help would be much appreciated.

Regards
Ali

On Thu, Aug 31, 2017 at 1:56 AM, Dominique Devienne <[hidden email]>
wrote:

> On Wed, Aug 30, 2017 at 5:48 PM, Jens Alfke <[hidden email]> wrote:
>
> > > On Aug 29, 2017, at 6:22 PM, Ali Dorri <[hidden email]> wrote:
> > >
> > > *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and
> PK
> > =
> > > null   where PK = '%q' ;", endoced_pub.c_str());
> >
> > FYI, your PK values are not being stored as blobs, rather as hex-encoded
> > strings. Maybe not a big deal since they're not very large; but f you do
> > want to store them as blobs, there's a special prefix before a string
> > literal (an "x"? Can't recall) that makes it a hex-encoded blob.
>
>
> it's x'abcd01'. But much better yet, don't use literal SQL and printf, you
> proper binding [1].
> you'll save yourself from SQL injections, and get better performance too.
> --DD
>
> [1] https://sqlite.org/c3ref/bind_blob.html
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Simon Slavin-3


On 31 Aug 2017, at 12:10am, Ali Dorri <[hidden email]> wrote:

> It works. I think I should a way to calculate this reduction in the size of
> the database as sometimes it seems there is no difference in size (after
> VACUUM) between removing one row or two or three.
> This is a research work so I need it to show me exactly how much data is
> removed.

SQLite databases are stored as pages of a certain length.  Each table, each index, is a sequence of pages.  You can delete some data but unless you free up an entire page, the file won’t get shorter.  You can find out how long a page is using this command

    PRAGMA page_size

documented here:

<https://sqlite.org/pragma.html#pragma_page_size>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Ali Dorri
Great, thanks for your help. I will have a look at that.


On Thu, Aug 31, 2017 at 9:28 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 31 Aug 2017, at 12:10am, Ali Dorri <[hidden email]> wrote:
>
> > It works. I think I should a way to calculate this reduction in the size
> of
> > the database as sometimes it seems there is no difference in size (after
> > VACUUM) between removing one row or two or three.
> > This is a research work so I need it to show me exactly how much data is
> > removed.
>
> SQLite databases are stored as pages of a certain length.  Each table,
> each index, is a sequence of pages.  You can delete some data but unless
> you free up an entire page, the file won’t get shorter.  You can find out
> how long a page is using this command
>
>     PRAGMA page_size
>
> documented here:
>
> <https://sqlite.org/pragma.html#pragma_page_size>
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with updating database content (C++)

Simon Slavin-3


On 31 Aug 2017, at 12:36am, Ali Dorri <[hidden email]> wrote:

> Great, thanks for your help. I will have a look at that.

See also this page to understand more about SQLite’s database file format:

<https://sqlite.org/fileformat.html>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users