Re: [sqlite] LOW performance with VIEW and ANDROID

[James K. Lowden]

On Sat, 27 Apr 2013 07:06:31 -0700 (PDT)
Gianni Sassanelli  wrote:

> I have a slow performance if i USE the view but only when i use it
> from ANDROID

I don't know if it will help, but the following query should produce
the same results and might execute faster:

SELECT id_arbox,
   cdstagioni,
   cdlinee,
   cdbox,
   ncolori,   
   radiato,   
   totalepz,
   cdar,
   cdtessuti,
   cdcart,
   case Q when 1 then cdcolori 
 else '-' 
  end as 'cdcolori'
   case Q when 1 then C.descrizione 
 else 'ASSORTITO' 
  end as 'colori_descrizione'
FROM (
select id_arboxAs id_arbox,
   cdstagioni  As cdstagioni,
   cdlinee As cdlinee,
   cdbox   As cdbox,
   nColori As ncolori,   
   radiato As radiato,   
   SUM(QTotRiga)   As totalepz,
   cdarAs cdar,
   cdarmp  As cdtessuti,
   cdcart  As cdcart,
   MIN(cdcolori)   As cdcolori,   
   COUNT(cdcolori) As Q
from arbox
group by
   id_arbox,
   cdstagioni,
   cdlinee,
   cdbox,
   nColori,   
   radiato,   
   cdar,
   cdarmp,
   cdcart  
) as AB
JOIN Colori As C ON C.CdColori = AB.CdColori
ORDER BY 
  AB.cdstagioni,
  AB.cdar,  
  AB.cdarmp,  
  AB.cdbox,  
  AB.cdcolori;

I cannot explain the behavior you reported.  By mentioning the main
table only once, though, you may make it easier for the query optimizer
to do its job.  

HTH.  

--jkl

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

[sqlite] LOW performance with VIEW and ANDROID

[Gianni Sassanelli]

Hi  experts,
i'm using a SQLLITE for my ANDROID APP

I have a slow performance if i USE the view but only when i use it from
ANDROID

the scenario is the following:

I have a table defined as 
CREATE TABLE ArMpCol (
   Id Integer PRIMARY KEY, 
   Id_Ar int, 
   Id_ArMpCol int, 
   CDAR char(20)  Collate RTRIM, 
   CdTessuti char(20)  Collate RTRIM, 
   CdStagioni char(6) Collate RTRIM, 
   CdLinee char(6) Collate RTRIM, 
   CdCart char(6) Collate RTRIM, 
   CdColori char Collate RTRIM, 
   Sequenza smallint, 
   Radiato bit);

this table have about 7 rows

i also have a view on this table:

CREATE VIEW V_Ar_Elenco_Box AS
SELECT 
   ab.id_arboxAs id_arbox,
   ab.cdstagioni  As cdstagioni,
   ab.cdlinee As cdlinee,
   ab.cdbox   As cdbox,
   ab.nColori As ncolori,   
   ab.radiato As radiato,   
   ab.QTotRigaAs totalepz,
   ab.cdarAs cdar,
   ab.cdarmp  As cdtessuti,
   ab.cdcart  As cdcart,
   ab.cdcoloriAs cdcolori,
   C.Descrizion   As colori_descrizione   
from arbox as AB
inner join Colori As C ON C.CdColori = Ab.CdColori
where 
  ab.ncolori = 1  
union
select 
   ab.id_arboxAs id_arbox,
   ab.cdstagioni  As cdstagioni,
   ab.cdlinee As cdlinee,
   ab.cdbox   As cdbox,
   ab.nColori As ncolori,   
   ab.radiato As radiato,   
   SUM(ab.QTotRiga)   As totalepz,
   ab.cdarAs cdar,
   ab.cdarmp  As cdtessuti,
   ab.cdcart  As cdcart,
   '   -  '   As cdcolori,   
   'ASSORTITO'As colori_descrizione
 
from arbox as AB
where 
  ab.ncolori > 1 
GROUP BY
   ab.id_arbox,
   ab.cdstagioni  ,
   ab.cdlinee ,
   ab.cdbox   ,
   ab.nColori ,   
   ab.radiato ,   
   ab.cdar,
   ab.cdarmp  ,
   ab.cdcart  
order by 
  Ab.Cdstagioni,
  Ab.Cdar,  
  Ab.CdArmp,  
  Ab.CdBox,  
  Ab.CdColori;


the problem is:

if i do from my pc this query:

SELECT *
FROM V_Ar_Elenco_Box 
WHERE CdStagioni = 'AI12' and CdAr = 'TC20A' and CdLinee = 'PAS'

the result is 
3 rows in 29 ms

now i copy this db on my Android device and run the some query on some Db
from android

the result is 
3 rows in 1890 ms 

now on Android i try to use the query without VIEW 

SELECT 
   ab.id_arboxAs id_arbox,
   ab.cdstagioni  As cdstagioni,
   ab.cdlinee As cdlinee,
   ab.cdbox   As cdbox,
   ab.nColori As ncolori,   
   ab.radiato As radiato,   
   ab.QTotRigaAs totalepz,
   ab.cdarAs cdar,
   ab.cdarmp  As cdtessuti,
   ab.cdcart  As cdcart,
   ab.cdcoloriAs cdcolori,
   C.Descrizion   As colori_descrizione   
from arbox as AB
inner join Colori As C ON C.CdColori = Ab.CdColori
where 
  ab.ncolori = 1 AND
  ab.cdstagioni = 'AI12' AND ab.cdar 'TC20A' and CdLinee = 'PAS'

union
select 
   ab.id_arboxAs id_arbox,
   ab.cdstagioni  As cdstagioni,
   ab.cdlinee As cdlinee,
   ab.cdbox   As cdbox,
   ab.nColori As ncolori,   
   ab.radiato As radiato,   
   SUM(ab.QTotRiga)   As totalepz,
   ab.cdarAs cdar,
   ab.cdarmp  As cdtessuti,
   ab.cdcart  As cdcart,
   '   -  '   As cdcolori,   
   'ASSORTITO'As colori_descrizione
 
from arbox as AB
where 
  ab.ncolori > 1  AND
  ab.cdstagioni = 'AI12' AND ab.cdar 'TC20A' and CdLinee = 'PAS'

GROUP BY
   ab.id_arbox,
   ab.cdstagioni  ,
   ab.cdlinee ,
   ab.cdbox   ,
   ab.nColori ,   
   ab.radiato ,   
   ab.cdar,
   ab.cdarmp  ,
   ab.cdcart  
order by 
  Ab.Cdstagioni,
  Ab.Cdar,  
  Ab.CdArmp,  
  Ab.CdBox,  
  Ab.CdColori;


the result on android device is 
3 rows in 31 ms 

is possible that in android device, the view are not well supported or i
have do some error ? 
can someone help me please?
thank's
Gianni



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LOW-performance-with-VIEW-and-ANDROID-tp68517.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Bart Smissaert]

Hi Hitesh,

Attached all the VB6 code to do with this. Ignore all the Debug stuff and
also all the RaiseEvent lines. Note that this uses the free VB SQLite
wrapper from Olaf Schmidt and if you don't use that then that is very much
recommended. Let me know if you want that and I will explain.

Also note that my code does something slightly different then what you want
to do, but still, it might be useful.

Bart


On Sat, Apr 27, 2013 at 2:12 PM, hiteshambaliya
wrote:

> Ya I am interested to know more.
>
> My mail ID hitesh.ambal...@gmail.com
>
> Thank you so much
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68515.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Public Function SetSequentialGroups(strDB As String, _
strTable As String, _
strGroupField As String, _
strIDField As String, _
strCompareField1 As String, _
Optional strCompareField2 As String, _
Optional strCompareField3 As String, _
Optional lFirstGroupNumber As Long, _
Optional bLog As Boolean, _
Optional bDebug As Boolean) As Long

Dim i As Long
Dim c As Long
Dim cRs As cRecordset
Dim cCmd As cCommand
Dim lGroupIdx As Long
Dim bDoGroupSwitch As Boolean
Dim lCompareFields As Long
Dim V1
Dim V2
Dim V3  'compare-values as variant
Dim lFieldCount As Long
Dim lCompareFieldNumber1 As Long  'all these 4 0-based for convenience
Dim lCompareFieldNumber2 As Long
Dim lCompareFieldNumber3 As Long
Dim lIDFieldNumber As Long

10  On Error GoTo ERROROUT

20  SetSQLiteConn strDB, , , False

30  If SQLiteTableExists(strTable, strDB, False, True) = False Then
40SetSequentialGroups = -1
50Exit Function
60  End If

70  If FieldNumberInTable(strDB, strTable, strGroupField, , False) < 1 Then
80SetSequentialGroups = -1
90Exit Function
100 End If

110 If FieldNumberInTable(strDB, strTable, strIDField, , False) < 1 Then
120   SetSequentialGroups = -1
130   Exit Function
140 End If

150 If FieldNumberInTable(strDB, strTable, strCompareField1, , False) < 1 
Then
160   SetSequentialGroups = -1
170   Exit Function
180 End If

190 lGroupIdx = lFirstGroupNumber  'initilize the first lGroupIdx

200 Set cRs = Cnn.OpenRecordset("SELECT * FROM " & strTable & _
" ORDER BY " & strIDField & " ASC")

210 lFieldCount = cRs.Fields.Count

220 If Len(strCompareField2) = 0 Then
230   lCompareFields = 1
240 Else
250   If Len(strCompareField3) > 0 Then
260 lCompareFields = 3
270   Else
280 lCompareFields = 2
290   End If
300 End If

'IndexInFieldList is zero based
'--
310 lIDFieldNumber = cRs.Fields(strIDField).IndexInFieldList
320 lCompareFieldNumber1 = cRs.Fields(strCompareField1).IndexInFieldList

330 If lCompareFields > 1 Then
340   lCompareFieldNumber2 = cRs.Fields(strCompareField2).IndexInFieldList
350 End If

360 If lCompareFields > 2 Then
370   lCompareFieldNumber3 = cRs.Fields(strCompareField3).IndexInFieldList
380 End If

390 If bDebug Then
400   MsgBoxDLL "lFieldCount" & vbTab & lFieldCount & vbCrLf & _
"lIDFieldNumber" & vbTab & lIDFieldNumber & vbCrLf & _
"lCompareFieldNumber1" & vbTab & lCompareFieldNumber1 & 
vbCrLf & _
"lCompareFieldNumber2" & vbTab & lCompareFieldNumber2 & 
vbCrLf & _
"lCompareFieldNumber3" & vbTab & lCompareFieldNumber3 & 
vbCrLf & _
"lCompareFields" & vbTab & lCompareFields, _
"Parameters of SetSequentialGroups", _
lFormColour:=lColourForm, bLineUpTabs:=True
410 End If

420 Set cCmd = Cnn.CreateCommand("UPDATE " & strTable & _
 " SET " & strGroupField & " = ? WHERE " & _
 strIDField & " = ?")

430 If bLog Then
440   ShowStatement "Procedure SetSequentialGroups", , , 2, True, , strDB
450 End If

460 BeginTransaction strDB, False

470 Select Case lCompareFields

  Case 1

'now we work with valuematrix for more speed
480 V1 = cRs.ValueMatrix(0, lCompareFieldNumber1)

490 For i = 0 To cRs.RecordCount - 1
 

Re: [sqlite] sequential row numbers from query

[hiteshambaliya]

Ya I am interested to know more.

My mail ID hitesh.ambal...@gmail.com

Thank you so much



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68515.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

[Simon Slavin]

On 27 Apr 2013, at 8:34am, Igor Korot  wrote:

> In the other area of the program I have a transaction that does 5 or 6
> inserts and no selects.
> This transaction works fine as I just verified.
> 
> Now in the failing case the flow goes like this:
> 
> First the program updates couple of tables, then it inserts this particular
> record.
> This algorithm is going thru the loop of the players vector.
> I'm using the same handle and the same statement object. Only different
> queries.
> 
> After every query I'm calling sqlite3_finalize() to free the memory and the
> statement object.
> 
> Am I doing it wrong?

See section 3.0 in



If you're not sure whether you're doing it right or not, stop doing it.  Do a 
_prepare() _step() _finalize() on each command and see if that fixes your 
problem.  Once you know what fixes it you'll know what to do in your final code.

You are not doing sufficient exploration yourself to tell us what's wrong.  You 
are combining both a complicated INSERT and a complicated SELECT and we have no 
way to tell which one isn't doing what you expected.

Create a new table with just one integer column.  Write code in your language to

Count the rows in your table.
Insert a new row in that.
Count the rows again.

Do it first with _exec instead of _prepare() _step() _finalize().  If your code 
doesn't work you now have something incredibly simple to debug.  If it does 
work, gradually add the complications of your original example back in until it 
stops working.  At that point you will have identified what it is you're doing 
that isn't working properly.

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

Re: [sqlite] Port SQLite to VxWorks 6.8

[nikvoron]

Just put:

pNew->ctrlFlags |= UNIXFILE_DELETE;

instead of 

isDelete = 0;

And remove :

if( isDelete ) pNew->ctrlFlags |= UNIXFILE_DELETE; 

Worked for me, but I built SQLite for RTP. Does not metter here.

Regards



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Port-SQLite-to-VxWorks-6-8-tp68430p68474.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Bart Smissaert]

Have a look at this thread in the archive:
find sequential groups
It can be done with SQL, but it is slow and it can be done enormously
faster in code.
I did this in VB6 and let me know if you are interested and I mail you the
code off-list.

RBS



On Sat, Apr 27, 2013 at 8:10 AM, hiteshambaliya
wrote:

> You are absolutely right but,
> I am using VB 6.0 and i have global general function which fill the data in
> grid so there is if serial number column in query is easy way so..
>
> Any way to do in query???
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68506.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

[Paolo Bolzoni]

Thanks everyone, I cannot apply all the suggestions (for example I do
use foreign keys), but probably I can improve the performance of the
calculations.

On Sat, Apr 27, 2013 at 6:48 AM, David King  wrote:
>> The idea of temporary tables in-memory is nice, but I do not know how
>> to apply it.
>> I see in the documentation I can use the TEMP in CREATE TABLE, but I am not
>> sure of the effect.
>> Does it mean that the table is created in memory and it is lost in
>> sqlite3_close?
>
>
>
> There are a few things at work here. You can have your entire database in 
> memory (and is lost on close) by giving sqlite_open the filename :memory:
>
> Additionally and unrelatedly, you can create temporary tables (CREATE TEMP 
> TABLE, as you say). These are tables that disappear on close. They are 
> associated with the database connection, not the database file itself. They 
> are usually stored in temporary files in a different on-disk location from 
> your main database file.
>
> With the pragma temp_store set to MEMORY, the temporary tables can be 
> entirely in memory, even if the rest of your database isn't. Using this, you 
> can mix on-disk tables (in your regular database) and in-memory tables 
> (temporary tables, when temp_store=MEMORY). That lets you easily keep some 
> intermediate stuff in memory and only flush out the disk tables when you're 
> ready. Sometimes (depending on your usage patterns of course) this batching 
> can help speed things up.
>
>
>>
>> On Fri, Apr 26, 2013 at 8:07 PM, David King > (mailto:dk...@ketralnis.com)> wrote:
>> > auto_vacuum Turn off autovacuum and just run it yourself when you're idle
>> > foreign_keys Turn off foreign keys checks (or just don't use foreign keys)
>> > ignore_check_constraints Same
>> > journal_mode OFF might actually be faster than MEMORY, but disables 
>> > rollback support
>> > locking_mode EXCLUSIVE can be mildly faster in some cases
>> >
>> >
>> > secure_delete OFF
>> >
>> >
>> > synchronous OFF as you said
>> >
>> >
>> > cache_size beef up as you said. this won't always make everything faster 
>> > though, since it can starve the other processes on your machine for memory 
>> > even for rarely-used sqlite data when they could potentially make better 
>> > use of the OS page cache.
>> > temp_store set to MEMORY, this will help if you have queries that create 
>> > temporary tables, even if you're not doing so yourself (e.g. unindexed 
>> > group bys)
>> >
>> >
>> >
>> > If you can, use an entirely :memory: database. This may not work for you, 
>> > but if it does, great.
>> >
>> > Since you're not using journal_mode = WAL this is moot for you, but if you 
>> > were I'd say turn off wal_autocheckpoint and wal_checkpoint yourself when 
>> > you're idle
>> >
>> > For my somewhat-similar use case I find that writing intermediate changes 
>> > to an in-memory table (insert into my_temp_table) and periodically 
>> > flushing those to disk (insert into real_table select from my_temp_table; 
>> > delete from my_temp_table) can help speed things up if a lot of 
>> > index-updating is involved in the on-disk table.
>> >
>> > Make sure you're doing all of your inserts in a transaction. inserting is 
>> > pretty cheap, but committing a transaction is expensive, and if you're not 
>> > in a transaction each insert is its own transaction
>> >
>> > Make sure you're re-using your prepared statements
>> >
>> > Play with page_size to get it right for your write patterns
>> >
>> > Don't use a connection concurrently, it's doing internal locking anyway. 
>> > If you must, use the shared page cache. If you're doing it from multiple 
>> > processes, use WAL mode.
>> >
>> >
>> >
>> >
>> > On Friday, 26 April, 2013 at 10:44, Paolo Bolzoni wrote:
>> >
>> > > The subject pretty much says it all, I use sqlite3 as a way to save
>> > > temporary results from a calculation.
>> > >
>> > > In this context I do not care about safety of the data. If the program
>> > > fails or there is a blackout I will just delete the sqlite3 file, 
>> > > eventually
>> > > fix the bug, and restart.
>> > >
>> > > At the moment I use this pragmas:
>> > >
>> > > PRAGMA synchronous = OFF;
>> > > PRAGMA journal_mode = MEMORY;
>> > > PRAGMA cache_size = -10240;
>> > >
>> > > Is there anything other I can do to speed-up sqlite3 at expenses of
>> > > safety?
>> > >
>> > > Thanks,
>> > > Paolo
>> > > ___
>> > > sqlite-users mailing list
>> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
>> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> >
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org 

Re: [sqlite] Programming API vs console

[Igor Korot]

Amit,

On Sat, Apr 27, 2013 at 1:00 AM, Amit Chaudhuri
wrote:

> CREATE TABLE playersdrafted(playerid integer, id ineteger,
>
> Don't think ineteger is what you really mean.:)
> Seen it twice now so guessing it's actually in the code
>

Why you say so?
It is a foreign key representation, so integer is perfectly normal column
type.

Thank you.


>
>
> On Sat, Apr 27, 2013 at 8:34 AM, Igor Korot  wrote:
>
> > Simon,
> > In the other area of the program I have a transaction that does 5 or 6
> > inserts and no selects.
> > This transaction works fine as I just verified.
> >
> > Now in the failing case the flow goes like this:
> >
> > First the program updates couple of tables, then it inserts this
> particular
> > record.
> > This algorithm is going thru the loop of the players vector.
> > I'm using the same handle and the same statement object. Only different
> > queries.
> >
> > After every query I'm calling sqlite3_finalize() to free the memory and
> the
> > statement object.
> >
> > Am I doing it wrong?
> >
> > Thank you.
> >
> >
> > On Sat, Apr 27, 2013 at 12:10 AM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 27 Apr 2013, at 8:05am, Igor Korot  wrote:
> > >
> > > > No. Using straight insert with values does not work.
> > > > Record still not inserted.
> > >
> > > Okay, so you now don't have to worry about parameters, or a sub-select
> or
> > > any of those things.  What you have identified is that a simple INSERT
> > with
> > > all values supplied returns SQLITE_OK but doesn't insert a record.
> > >
> > > Make up a simpler TABLE and try inserting into that one.  A table with
> > one
> > > INTEGER column ?  In fact, see if you can get any INSERT command
> working
> > at
> > > all, then work your way up to one that doesn't work and see if you can
> > find
> > > the thing that makes the INSERT stops working.
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

[Amit Chaudhuri]

CREATE TABLE playersdrafted(playerid integer, id ineteger,

Don't think ineteger is what you really mean.:)
Seen it twice now so guessing it's actually in the code


On Sat, Apr 27, 2013 at 8:34 AM, Igor Korot  wrote:

> Simon,
> In the other area of the program I have a transaction that does 5 or 6
> inserts and no selects.
> This transaction works fine as I just verified.
>
> Now in the failing case the flow goes like this:
>
> First the program updates couple of tables, then it inserts this particular
> record.
> This algorithm is going thru the loop of the players vector.
> I'm using the same handle and the same statement object. Only different
> queries.
>
> After every query I'm calling sqlite3_finalize() to free the memory and the
> statement object.
>
> Am I doing it wrong?
>
> Thank you.
>
>
> On Sat, Apr 27, 2013 at 12:10 AM, Simon Slavin 
> wrote:
>
> >
> > On 27 Apr 2013, at 8:05am, Igor Korot  wrote:
> >
> > > No. Using straight insert with values does not work.
> > > Record still not inserted.
> >
> > Okay, so you now don't have to worry about parameters, or a sub-select or
> > any of those things.  What you have identified is that a simple INSERT
> with
> > all values supplied returns SQLITE_OK but doesn't insert a record.
> >
> > Make up a simpler TABLE and try inserting into that one.  A table with
> one
> > INTEGER column ?  In fact, see if you can get any INSERT command working
> at
> > all, then work your way up to one that doesn't work and see if you can
> find
> > the thing that makes the INSERT stops working.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

[Igor Korot]

Simon,
In the other area of the program I have a transaction that does 5 or 6
inserts and no selects.
This transaction works fine as I just verified.

Now in the failing case the flow goes like this:

First the program updates couple of tables, then it inserts this particular
record.
This algorithm is going thru the loop of the players vector.
I'm using the same handle and the same statement object. Only different
queries.

After every query I'm calling sqlite3_finalize() to free the memory and the
statement object.

Am I doing it wrong?

Thank you.


On Sat, Apr 27, 2013 at 12:10 AM, Simon Slavin  wrote:

>
> On 27 Apr 2013, at 8:05am, Igor Korot  wrote:
>
> > No. Using straight insert with values does not work.
> > Record still not inserted.
>
> Okay, so you now don't have to worry about parameters, or a sub-select or
> any of those things.  What you have identified is that a simple INSERT with
> all values supplied returns SQLITE_OK but doesn't insert a record.
>
> Make up a simpler TABLE and try inserting into that one.  A table with one
> INTEGER column ?  In fact, see if you can get any INSERT command working at
> all, then work your way up to one that doesn't work and see if you can find
> the thing that makes the INSERT stops working.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[hiteshambaliya]

You are absolutely right but,
I am using VB 6.0 and i have global general function which fill the data in
grid so there is if serial number column in query is easy way so..

Any way to do in query???



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68506.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

[Simon Slavin]

On 27 Apr 2013, at 8:05am, Igor Korot  wrote:

> No. Using straight insert with values does not work.
> Record still not inserted.

Okay, so you now don't have to worry about parameters, or a sub-select or any 
of those things.  What you have identified is that a simple INSERT with all 
values supplied returns SQLITE_OK but doesn't insert a record.

Make up a simpler TABLE and try inserting into that one.  A table with one 
INTEGER column ?  In fact, see if you can get any INSERT command working at 
all, then work your way up to one that doesn't work and see if you can find the 
thing that makes the INSERT stops working.

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

Re: [sqlite] Programming API vs console

[Igor Korot]

Simon,

On Fri, Apr 26, 2013 at 7:48 PM, Simon Slavin  wrote:

>
> On 27 Apr 2013, at 3:29am, Igor Korot  wrote:
>
> > sqlite> SELECT ownerid FROM owners WHERE ownername = 'Team 1' AND id = 1;
> > 53
>
> For testing, kill the sub-select in your INSERT command and just put a 53
> in there.  See if that changes anything.
>

Nope. Record is still not inserted.


>
> > sqlite>
>
> Please retrieve the values returned by
>
> last_insert_rowid()
>
> or the C function
>
> sqlite3_last_insert_rowid(sqlite3*)
>
> before and after the insert and see if the insert changes the value.
>  After doing that ...
>

This insert is inside transaction. It is first query in it and it's only an
insert. There are about 300 updates as well.
So at which point I need to retrieve the last inserted id: right after
insert or when transaction finishes?


>
> Please substitute your INSERT command with the simplest possible INSERT
> command you can think of and see if that has the desired effect.  First try
> one which inserts fixed values.  If that works  properly ...
>

No. Using straight insert with values does not work.
Record still not inserted.

Thank you.

>
> Try putting the sub-SELECT back in.  Then try putting in one of the
> parameters.  Then add more parameters.  See if you can spot the point at
> which the command stops working.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users