Re: [sqlite] a question about data synchronization

[Gelin Yan]

On Thu, Jun 2, 2016 at 12:08 AM, Hick Gunter  wrote:

> Process B can see only data that is committed before it's read transaction
> is started.
>
> I suspect you are creating a transaction in process B right after
> connecting to the database that remains open for the lifetime of the
> connection.
>
> This will force Process B to see the state as it was before it's
> transaction started, i.e. before Process A committed it's changes.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:
> sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Gelin Yan
> Gesendet: Mittwoch, 01. Juni 2016 17:16
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [sqlite] a question about data synchronization
>
> Hi All
>
> I have a question about data synchronization. The question is:
>
>   I have two process A & B. Both of them connect to the same database
>
> In Process A
>
>  insert a value into a table XX and commit.
>
> In Process B
>
>  select from the same table XX
>
>and I didn't find the inserted record.
>
>   If I close the connection in Process B and connect it again,  I can
> select this
>
> inserted record.
>
> Did I do something wrong? I expect the inserted data is available
> without reconnection.
>
> BTW: I use the latest Sqlite version and WAL mode.
>
> Regards
>
> gelin yan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Hi Hick

   I used python & its sqlite3 module for this trial, the autocommit mode
is default on. After I explicitly called "BEGIN" & "COMMIT" for selecting
record, I could see the inserted record.

Regards

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

Re: [sqlite] a question about data synchronization

[Gelin Yan]

On Thu, Jun 2, 2016 at 1:51 AM, Simon Slavin  wrote:

>
> On 1 Jun 2016, at 4:15pm, Gelin Yan  wrote:
>
> > In Process A
> >
> > insert a value into a table XX and commit.
> >
> > In Process B
> >
> > select from the same table XX
> >
> >   and I didn't find the inserted record.
>
> Did you do process A by explicitly declaring BEGIN or did you let SQLite
> make its own transaction ?
>
> Did you do all of A before you started B ?
>
> Are you using exec() or prepare(), step(), finalize() ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Hi All

  After I added conn.execute("BEGIN") and conn.execute("COMMIT") in
Process B, I can see the inserted record. It makes sense now.

   I would appreciate it if your guys can tell me the reason.

Regards

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

Re: [sqlite] Limit to 5 records per Group / Top N results per group

[Igor Tandetnik]

On 6/1/2016 5:36 PM, Russell, Rory wrote:

In SQLIte, I have 2 tables, one is for a list of Projects (about 50
projects) and the other is a list of key dates for each project. Each
project has about 20 key dates.

How can run a query that will only return the top/first 5 dates for each
project in one query.


Something along these lines, perhaps:

select ProjectName, KeyDate
from Projects p, KeyDates using (ProjectID)
where KeyDate in (
  select d2.KeyDate from KeyDates d2
  where d2.ProjectId = p.ProjectId
  order by d2.KeyDate limit 5);

--
Igor Tandetnik

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

[sqlite] Limit to 5 records per Group / Top N results per group

[Russell, Rory]

All,

 

Please help.I have been trying but can't find an answer.

In SQLIte, I have 2 tables, one is for a list of Projects (about 50
projects) and the other is a list of key dates for each project. Each
project has about 20 key dates.

How can run a query that will only return the top/first 5 dates for each
project in one query. 

 

What I am trying to do is get a query that will only return 250 items (ie 50
projects by 5 key dates) max as I am showing these on a report but space is
limited.

 

Thanks,

Rory

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

Re: [sqlite] a question about data synchronization

[Simon Slavin]

On 1 Jun 2016, at 4:15pm, Gelin Yan  wrote:

> In Process A
> 
> insert a value into a table XX and commit.
> 
> In Process B
> 
> select from the same table XX
> 
>   and I didn't find the inserted record.

Did you do process A by explicitly declaring BEGIN or did you let SQLite make 
its own transaction ?

Did you do all of A before you started B ?

Are you using exec() or prepare(), step(), finalize() ?

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

Re: [sqlite] a question about data synchronization

[Hick Gunter]

Process B can see only data that is committed before it's read transaction is 
started.

I suspect you are creating a transaction in process B right after connecting to 
the database that remains open for the lifetime of the connection.

This will force Process B to see the state as it was before it's transaction 
started, i.e. before Process A committed it's changes.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Gelin Yan
Gesendet: Mittwoch, 01. Juni 2016 17:16
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] a question about data synchronization

Hi All

I have a question about data synchronization. The question is:

  I have two process A & B. Both of them connect to the same database

In Process A

 insert a value into a table XX and commit.

In Process B

 select from the same table XX

   and I didn't find the inserted record.

  If I close the connection in Process B and connect it again,  I can select 
this

inserted record.

Did I do something wrong? I expect the inserted data is available without 
reconnection.

BTW: I use the latest Sqlite version and WAL mode.

Regards

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] a question about data synchronization

[Gelin Yan]

Hi All

I have a question about data synchronization. The question is:

  I have two process A & B. Both of them connect to the same database

In Process A

 insert a value into a table XX and commit.

In Process B

 select from the same table XX

   and I didn't find the inserted record.

  If I close the connection in Process B and connect it again,  I can
select this

inserted record.

Did I do something wrong? I expect the inserted data is available
without reconnection.

BTW: I use the latest Sqlite version and WAL mode.

Regards

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

[sqlite] minor formattig flaw in documentation

[Wolfgang Enzinger]

I just came across a minor formattig flaw in the VDBE documentation
(https://www.sqlite.org/opcode.html); in the opcodes table, the description
for the Cast opcode reads like this:

---
Force the value in register P1 to be the type defined by P2.

  TEXT  BLOB  NUMERIC  INTEGER  REAL 

A NULL value is not changed by this routine. It remains NULL.
---

Obviously, handling the HTML tags went wrong here.

Wolfgang

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