Reset value of sqlite_sequence

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

Reset value of sqlite_sequence

Stephen Chrzanowski
How does one reset the sqlite_sequence values?

I'm trying with [ delete from sqlite_sequence where name='...' ] but my app
continuously goes and keeps adding up the number.  If I go into a 3rd party
tool and delete the table, the next time my app goes and populates data,
the counter starts at 1.  Is there a flush or something I need to run?

I essentially want to truncate the table, get rid of all data, reset the
insert counter.

This is a development action only while I'm preparing some data for the
database, and not to be used as a production activity.

I suppose what I could do is just forcibly set the ID at insert, but, was
rather curious to why this delete wasn't working on the sqlite_sequence
table.

Thanks!
_______________________________________________
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: Reset value of sqlite_sequence

Simon Slavin-3
On 11 Feb 2018, at 1:12am, Stephen Chrzanowski <[hidden email]> wrote:

> How does one reset the sqlite_sequence values?

See section 2.6.2 of

<https://sqlite.org/fileformat2.html#seqtab>

When considering how to modify that table, pay attention to the last paragraph of that section.

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: Reset value of sqlite_sequence

Stephen Chrzanowski
Did a bit more digging, and the code I wrote is doing what its supposed to
do.  There is (what I'm calling) a bug with the string handling in the
wrapper (That I didn't write) I'm using that doesn't know how to deal with
"strings" type, but it does work fine with ansistrings, and I think the
translation between strings and ansistring seems to be messing up the
actual bind.

What I had before was this:
db.ExecSQL('delete from sqlite_sequence where
name=?',[ansistring('Magazines')]);

However, when I changed the line to this:
db.ExecSQL('delete from sqlite_sequence where name=''Magazines''');

The code did what I needed it to do.

On Sat, Feb 10, 2018 at 8:19 PM, Simon Slavin <[hidden email]> wrote:

> On 11 Feb 2018, at 1:12am, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > How does one reset the sqlite_sequence values?
>
> See section 2.6.2 of
>
> <https://sqlite.org/fileformat2.html#seqtab>
>
> When considering how to modify that table, pay attention to the last
> paragraph of that section.
>
> 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: Reset value of sqlite_sequence

Simon Slavin-3


On 11 Feb 2018, at 1:39am, Stephen Chrzanowski <[hidden email]> wrote:

> What I had before was this:
> db.ExecSQL('delete from sqlite_sequence where
> name=?',[ansistring('Magazines')]);

Oh dear.  Don't you just hate getting lost in multiple text representations ?

C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects Unicode.  Anyway, glad you got it working.

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: Reset value of sqlite_sequence

Stephen Chrzanowski
Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
ago.  Its served me well in Delphi 7, but with the move to Unicode
materials, its got a few things to iron out that I've been able to work
around in code.  Like this threads sample. :]


On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin <[hidden email]> wrote:

>
>
> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > What I had before was this:
> > db.ExecSQL('delete from sqlite_sequence where
> > name=?',[ansistring('Magazines')]);
>
> Oh dear.  Don't you just hate getting lost in multiple text
> representations ?
>
> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
> Unicode.  Anyway, glad you got it working.
>
> 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: Reset value of sqlite_sequence

Stephen Chrzanowski
Half thought... SQLite Wrapper from Tim Anderson at itwriting.com

On Sat, Feb 10, 2018 at 9:06 PM, Stephen Chrzanowski <[hidden email]>
wrote:

> Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
> ago.  Its served me well in Delphi 7, but with the move to Unicode
> materials, its got a few things to iron out that I've been able to work
> around in code.  Like this threads sample. :]
>
>
> On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin <[hidden email]>
> wrote:
>
>>
>>
>> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski <[hidden email]>
>> wrote:
>>
>> > What I had before was this:
>> > db.ExecSQL('delete from sqlite_sequence where
>> > name=?',[ansistring('Magazines')]);
>>
>> Oh dear.  Don't you just hate getting lost in multiple text
>> representations ?
>>
>> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
>> Unicode.  Anyway, glad you got it working.
>>
>> 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: Reset value of sqlite_sequence

R Smith
In reply to this post by Stephen Chrzanowski

On 2018/02/11 3:12 AM, Stephen Chrzanowski wrote:
> How does one reset the sqlite_sequence values?

How about:
UPDATE sqlite_sequence SET seq = 1 WHERE name = 'myTable';
_______________________________________________
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: Reset value of sqlite_sequence

R Smith-2
In reply to this post by Stephen Chrzanowski
(Apologies if this is duplicate)

On 2018/02/11 3:12 AM, Stephen Chrzanowski wrote:

> How does one reset the sqlite_sequence values?

How about:
UPDATE sqlite_sequence SET seq = 1 WHERE name = 'myTable';


_______________________________________________
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: Reset value of sqlite_sequence

Stephen Chrzanowski
Thanks Ryan.  The problem I had was with the string "myTable" when I was
doing a Delete.  My wrapper isn't 100% Unicode friendly apparently, and I
had to do a minor and easy work around.  Even SQLite Expert wasn't
returning what I wanted from a select, so I eventually determined my insert
statement was bad.

On Sun, Feb 11, 2018 at 3:20 AM, R Smith <[hidden email]> wrote:

> (Apologies if this is duplicate)
>
> On 2018/02/11 3:12 AM, Stephen Chrzanowski wrote:
>
> How does one reset the sqlite_sequence values?
>>
>
> How about:
> UPDATE sqlite_sequence SET seq = 1 WHERE name = 'myTable';
>
>
>
> _______________________________________________
> 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