Re: [sqlite] How to save live data into sqlite database using c language?

[fnoyanisi]

He means, in your HOST struct, instead of defining a lot of in variables for 
each protocol type, just define your ENUM type to hold all possible protocol 
types, so you will insert only one protocol value into your sqlite db.

When you fetch data, it will be just a matter of simple switch/case statement 
to evaluate the protocol value of each record.

On 29/04/2013, at 2:25 PM, Newbie89  wrote:

> Teg-3 wrote
>> Hello Newbie89,
>> 
>> Sunday, April 28, 2013, 3:32:07 AM, you wrote:
>> 
>> N> Thanks for the correction
>> N> ok...I will check first.
>> 
>> 
>> 
>> N> --
>> N> View this message in context:
>> N>
>> http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.html
>> N> Sent from the SQLite mailing list archive at Nabble.com.
>> N> ___
>> N> sqlite-users mailing list
>> N>
> 
>> sqlite-users@
> 
>> N> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> I suspect you want one protocol field
>> 
>> enum _protocols
>> {
>> C_PROTOCOL_TCP,
>> C_PROTOCOL_UDP,
>> ..
>> };
>> 
>> 
>> CREATE TABLE host
>> (
>> Src_MAC char(18),
>> Src_IP char(16),
>> Cap_Bytes integer,
>> Protocol integer,
>> 
>> );
>> 
>> With  a single protocol tag per insert. Then you tag each packet insert
>> with the protocol field.
>> 
>> -- 
>> Best regards,
>> Tegmailto:
> 
>> Teg@
> 
>> 
>> ___
>> sqlite-users mailing list
> 
>> sqlite-users@
> 
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> What do you mean that I want one protocol field?Sorry that I not very
> understand...can you give some example?thanks
> 
> 
> 
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68544.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] How to save live data into sqlite database using c language?

[Newbie89]

Teg-3 wrote
> Hello Newbie89,
> 
> Sunday, April 28, 2013, 3:32:07 AM, you wrote:
> 
> N> Thanks for the correction
> N> ok...I will check first.
> 
> 
> 
> N> --
> N> View this message in context:
> N>
> http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.html
> N> Sent from the SQLite mailing list archive at Nabble.com.
> N> ___
> N> sqlite-users mailing list
> N> 

> sqlite-users@

> N> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> I suspect you want one protocol field
> 
> enum _protocols
> {
> C_PROTOCOL_TCP,
> C_PROTOCOL_UDP,
> ..
> };
> 
> 
> CREATE TABLE host
> (
> Src_MAC char(18),
> Src_IP char(16),
> Cap_Bytes integer,
> Protocol integer,
> 
> );
> 
> With  a single protocol tag per insert. Then you tag each packet insert
> with the protocol field.
> 
> -- 
> Best regards,
>  Tegmailto:

> Teg@

> 
> ___
> sqlite-users mailing list

> sqlite-users@

> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

What do you mean that I want one protocol field?Sorry that I not very
understand...can you give some example?thanks



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68544.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] How to save live data into sqlite database using c language?

[Newbie89]

Let say my C file contain this 2 variable,

struct Packet
{
   char Src_MAC[18], Dest_MAC[18];
   char Net_P[5],Trans_P[5]; 
   char Src_IP[16], Dest_IP[16]; 
   long int Src_Port,Dest_Port, Cap_Bytes;//[ long int Range:
−2,147,483,648 to 2,147,483,647]
};

/* Each Host Information Definition  Host Info Size = 18+16+4+14x2+= 66
Bytes */

struct HOST
{
   char Src_MAC[18];
   char Src_IP[16];
   long int Cap_Bytes;
   int TCP,UDP,ICMP,IP,ARP,OTH_Net, OTH_Trans;   // Protocols
   int ftp,ssh,telnet,domain,www,netbios,other;  // Services   
};

Is it this enough for me to create a table?
If the previous is use C file to save data into log file,then should only
the insert query can use only?




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68543.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] Bug in resolving aliases with parentheses in join

[James K. Lowden]

On Sun, 28 Apr 2013 12:23:42 +0200
Lucas Clemente  wrote:

> SELECT * FROM (t1 AS a) JOIN (t2 AS b) USING(k) WHERE a.k = 1;
...
> Looks like this is due to some problem with resolving the alias in
> the parantheses. Now, as far as I understand SQL this should just
> work since the () don't introduce a new scope.

http://www.sqlite.org/lang_select.html
http://www.sqlite.org/syntaxdiagrams.html#single-source

As far as I can tell, the syntax you're using is invalid.  If it's a
bug, it's not a failure to interpret your SQL correctly, but a failure
to report the error.  

The "single-source" section of the selection statement allows
parentheses around a join-source or around a single-source.  In either
case, the closing parenthesis must appear before the AS keyword.  

May I ask why you're using these parentheses in the first place?
I have never felt the need to use parentheses in SQL except to control
arithmetic or boolean precedence.  

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

Re: [sqlite] Index question

[Simon Slavin]

On 28 Apr 2013, at 10:18pm, Paolo Bolzoni  wrote:

> Interesting, so sqlite3 is smart enough to actually move the blob instead
> of copying and deleting? If it is the case it is indeed great.

SQLite3 keeps all the data for a row together on disk.  It rewrites the entire 
row any time any field in the row is changed.  So if you make all your changes 
to a row in one UPDATE command, the old row will be read into memory, a new row 
will be composed in memory, then the new row will be written out to disk.

Yes, by modern standards rewriting the whole row when any field changes is a 
questionable decision, but when SQLite was originally written it was intended 
as a tiny lite way of doing fast searches and nobody thought it would ever be 
used for huge databases of long rows.

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

Re: [sqlite] Question about binding

[Igor Korot]

Keith,

On Sun, Apr 28, 2013 at 2:21 PM, Keith Medcalf  wrote:

>
> Exactly ... Both a=? and c=?1 will use the same parameter.  With named
> parameters you would do something like:
>
> Where a = :a and b = :b and c = :a and d = :d
>
> sqlite3_bind_parameter_index(stmt, ":a") -> 1
> sqlite3_bind_parameter_index(stmt, ":b") -> 2
> sqlite3_bind_parameter_index(stmt, ":d") -> 3
>
> named parameters just create a mapping between unique names -> index so
> you do not have to track which index number corresponds to which parameter
> name.  If you have lots of parameters and/or a complicated query you can
> see where this greatly improves maintainability (at a small expense of
> speed and code size).  Many of the traditional types of SQL embeding (for
> example the EXEC SQL macro in older environments) or the tcl interpreter
> (for example) will automatically bind named parameters to local variables
> of the same .  Many SQLite wrappers can map parameters the same way,
> to a dictionary or to object attributes.
>

Thank you for confirmation.


>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Korot
> > Sent: Sunday, 28 April, 2013 14:53
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Question about binding
> >
> > Hi, Keith,
> >
> > On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf 
> > wrote:
> >
> > >
> > > http://www.sqlite.org/lang_expr.html#varparam
> > >
> > > They are what are called Named Parameters.  You use the
> > > sqlite3_bind_parameter_index to look up the index associated with a
> name
> > ...
> > >
> > > ?nnn simply means to use index nnn for that parameter.  Subsequent bare
> > ?
> > > parameter indexes are incremented by 1 from the largest parameter index
> > > thus far used.
> > >
> > > Ie where a= ? and b = ? and c = ?1 and d=?
> > >
> >
> > So it means that:
> >
> > [pseudo-code]
> > sqlite3_bind...( stmt, 1,...);
> > sqlite3_bind...( stmt, 2,...);
> > sqlite3_bind...( stmt, 3, ...);
> > [/pseudo-code]
> >
> > and a and c will have 1, b - 2 and c - 3, right?
> >
> > Thank you.
> >
> >
> > > Has 3 parameters.  A and C use the same bind index and thus the same
> > > parameter.
> > >
> > > ---
> > > ()  ascii ribbon campaign against html e-mail
> > > /\  www.asciiribbon.org
> > >
> > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > > boun...@sqlite.org] On Behalf Of Igor Korot
> > > > Sent: Sunday, 28 April, 2013 13:05
> > > > To: General Discussion of SQLite Database
> > > > Subject: [sqlite] Question about binding
> > > >
> > > > Hi, ALL,
> > > > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> > > >
> > > > [quote]
> > > > . that matches one of following templates:
> > > >
> > > >
> > > >- ?
> > > >- ?NNN
> > > >- :VVV
> > > >- @VVV
> > > >- $VVV
> > > >
> > > > [/quote]
> > > >
> > > >
> > > > What is the purpose of having "NNN" and "VVV"? Are those standard?
> How
> > do
> > > > I
> > > > use those templates?
> > > >
> > > > Everywhere I see an examples which uses just "?" and no other 4
> > > templates.
> > > >
> > > >
> > > > Thank you.
> > > > ___
> > > > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Keith Medcalf]

Exactly ... Both a=? and c=?1 will use the same parameter.  With named 
parameters you would do something like:

Where a = :a and b = :b and c = :a and d = :d

sqlite3_bind_parameter_index(stmt, ":a") -> 1
sqlite3_bind_parameter_index(stmt, ":b") -> 2
sqlite3_bind_parameter_index(stmt, ":d") -> 3

named parameters just create a mapping between unique names -> index so you do 
not have to track which index number corresponds to which parameter name.  If 
you have lots of parameters and/or a complicated query you can see where this 
greatly improves maintainability (at a small expense of speed and code size).  
Many of the traditional types of SQL embeding (for example the EXEC SQL macro 
in older environments) or the tcl interpreter (for example) will automatically 
bind named parameters to local variables of the same .  Many SQLite 
wrappers can map parameters the same way, to a dictionary or to object 
attributes.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Sunday, 28 April, 2013 14:53
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Question about binding
> 
> Hi, Keith,
> 
> On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf 
> wrote:
> 
> >
> > http://www.sqlite.org/lang_expr.html#varparam
> >
> > They are what are called Named Parameters.  You use the
> > sqlite3_bind_parameter_index to look up the index associated with a name
> ...
> >
> > ?nnn simply means to use index nnn for that parameter.  Subsequent bare
> ?
> > parameter indexes are incremented by 1 from the largest parameter index
> > thus far used.
> >
> > Ie where a= ? and b = ? and c = ?1 and d=?
> >
> 
> So it means that:
> 
> [pseudo-code]
> sqlite3_bind...( stmt, 1,...);
> sqlite3_bind...( stmt, 2,...);
> sqlite3_bind...( stmt, 3, ...);
> [/pseudo-code]
> 
> and a and c will have 1, b - 2 and c - 3, right?
> 
> Thank you.
> 
> 
> > Has 3 parameters.  A and C use the same bind index and thus the same
> > parameter.
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Igor Korot
> > > Sent: Sunday, 28 April, 2013 13:05
> > > To: General Discussion of SQLite Database
> > > Subject: [sqlite] Question about binding
> > >
> > > Hi, ALL,
> > > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> > >
> > > [quote]
> > > . that matches one of following templates:
> > >
> > >
> > >- ?
> > >- ?NNN
> > >- :VVV
> > >- @VVV
> > >- $VVV
> > >
> > > [/quote]
> > >
> > >
> > > What is the purpose of having "NNN" and "VVV"? Are those standard? How
> do
> > > I
> > > use those templates?
> > >
> > > Everywhere I see an examples which uses just "?" and no other 4
> > templates.
> > >
> > >
> > > Thank you.
> > > ___
> > > 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] Index question

[Paolo Bolzoni]

Interesting, so sqlite3 is smart enough to actually move the blob instead
of copying and deleting? If it is the case it is indeed great.

On Sun, Apr 28, 2013 at 5:12 PM, Simon Slavin  wrote:
>
> On 28 Apr 2013, at 3:51pm, Paolo Bolzoni  
> wrote:
>
>> So I should write my BLOB in another (not-indexed) table, UPDATE the
>> indexed table copying from the other,
>> and finally delete the line in the first table? All in one transaction?
>
> That would work and would be a good solution if you change one BLOB at a time.
>
> Or you can assemble the BLOB in memory and then write it all in one UPDATE 
> command.
>
> Or you can have another BLOB column, an unindexed one, in the existing table 
> and do your editing to the value in that column:
>
> BEGIN
> build up the BLOB
> UPDATE myTable SET permBLOB = tempBLOB, tempBLOB = 0 WHERE recID = 123456
> END
>
> This assumes that your BLOB is a long one which takes a lot of space.  If it 
> isn't, you can just leave the value there rather than zeroing it out.
>
> 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] Question about binding

[Igor Korot]

Hi, Keith,

On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf  wrote:

>
> http://www.sqlite.org/lang_expr.html#varparam
>
> They are what are called Named Parameters.  You use the
> sqlite3_bind_parameter_index to look up the index associated with a name ...
>
> ?nnn simply means to use index nnn for that parameter.  Subsequent bare ?
> parameter indexes are incremented by 1 from the largest parameter index
> thus far used.
>
> Ie where a= ? and b = ? and c = ?1 and d=?
>

So it means that:

[pseudo-code]
sqlite3_bind...( stmt, 1,...);
sqlite3_bind...( stmt, 2,...);
sqlite3_bind...( stmt, 3, ...);
[/pseudo-code]

and a and c will have 1, b - 2 and c - 3, right?

Thank you.


> Has 3 parameters.  A and C use the same bind index and thus the same
> parameter.
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Korot
> > Sent: Sunday, 28 April, 2013 13:05
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Question about binding
> >
> > Hi, ALL,
> > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> >
> > [quote]
> > . that matches one of following templates:
> >
> >
> >- ?
> >- ?NNN
> >- :VVV
> >- @VVV
> >- $VVV
> >
> > [/quote]
> >
> >
> > What is the purpose of having "NNN" and "VVV"? Are those standard? How do
> > I
> > use those templates?
> >
> > Everywhere I see an examples which uses just "?" and no other 4
> templates.
> >
> >
> > Thank you.
> > ___
> > 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] Index question

[Paolo Bolzoni]

I use only the C API. The function causing it in my program is:
  fprintf(stderr, "%s\n", sqlite3_errmsg(db));

and I think it comes out from:
  sqlite3.c:70718:  zErr = sqlite3MPrintf(db, "cannot open %s column
for writing", zFault);



On Sun, Apr 28, 2013 at 6:05 PM, Richard Hipp  wrote:
> On Sun, Apr 28, 2013 at 10:02 AM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> I get this error: "cannot
>> open indexed column for writing."
>>
>> What does it mean?
>>
>>
> That error is not coming from SQLite.  Are you using a wrapper program of
> some kind - or perhaps a third-party query tool?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Question about binding

[Keith Medcalf]

http://www.sqlite.org/lang_expr.html#varparam

They are what are called Named Parameters.  You use the 
sqlite3_bind_parameter_index to look up the index associated with a name ...

?nnn simply means to use index nnn for that parameter.  Subsequent bare ? 
parameter indexes are incremented by 1 from the largest parameter index thus 
far used.

Ie where a= ? and b = ? and c = ?1 and d=?

Has 3 parameters.  A and C use the same bind index and thus the same parameter. 

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Sunday, 28 April, 2013 13:05
> To: General Discussion of SQLite Database
> Subject: [sqlite] Question about binding
> 
> Hi, ALL,
> On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> 
> [quote]
> . that matches one of following templates:
> 
> 
>- ?
>- ?NNN
>- :VVV
>- @VVV
>- $VVV
> 
> [/quote]
> 
> 
> What is the purpose of having "NNN" and "VVV"? Are those standard? How do
> I
> use those templates?
> 
> Everywhere I see an examples which uses just "?" and no other 4 templates.
> 
> 
> Thank you.
> ___
> 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] Question about binding

[Igor Korot]

Hi, ALL,
On the page http://www.sqlite.org/c3ref/bind_blob.html it says:

[quote]
. that matches one of following templates:


   - ?
   - ?NNN
   - :VVV
   - @VVV
   - $VVV

[/quote]


What is the purpose of having "NNN" and "VVV"? Are those standard? How do I
use those templates?

Everywhere I see an examples which uses just "?" and no other 4 templates.


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

Re: [sqlite] Index question

[Richard Hipp]

On Sun, Apr 28, 2013 at 10:02 AM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:

> I get this error: "cannot
> open indexed column for writing."
>
> What does it mean?
>
>
That error is not coming from SQLite.  Are you using a wrapper program of
some kind - or perhaps a third-party query tool?


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

[sqlite] Bug in resolving aliases with parentheses in join

[Lucas Clemente]

Hi,

I hit something that looks like a bug when working with aliases in parentheses:

CREATE TABLE t1 (k);
CREATE TABLE t2 (k);

This works as expected:

SELECT * FROM (t1 AS a) JOIN (t2 AS b) USING(k) WHERE a.k = 1;

But chaging the a to b in the WHERE clause causes the following query to error 
(Error: no such column: b.k):

SELECT * FROM (t1 AS a) JOIN (t2 AS b) USING(k) WHERE b.k = 1;

Stripping this down a bit, the same problem appears in

SELECT * FROM t1 JOIN (t2 AS b) USING(k) WHERE b.k = 1;

but not when dropping the parantheses:

SELECT * FROM t1 JOIN t2 AS b USING(k) WHERE b.k = 1;

Looks like this is due to some problem with resolving the alias in the 
parantheses. Now, as far as I understand SQL this should just work since the () 
don't introduce a new scope.

I also found a previous reference to this problem: 
http://readlist.com/lists/sqlite.org/sqlite-users/12/62259.html

Is this a bug? If so, anybody up for fixing it, or giving me a hint where to 
start in the source?

Best,
Luke

___
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,

Here 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


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
  'we split up the comparisons, for a little bit more speed (VB
has no "early exit" in combined If-conditions)
500   If cRs.ValueMatrix(i, lCompareFieldNumber1) <> V1 Then
510 bDoGroupSwitch = True
520   Else
530 bDoGroupSwitch = False
540   End If

550   If bDoGroupSwitch Then  'set the next set of compare-values
560 V1 = cRs.ValueMatrix(i, lCompareFieldNumber1)
570 lGroupIdx = lGroupIdx + 1
580   End If

590   cCmd.SetInt32 1, lGroupIdx
600  

Re: [sqlite] Index question

[Simon Slavin]

On 28 Apr 2013, at 3:51pm, Paolo Bolzoni  wrote:

> So I should write my BLOB in another (not-indexed) table, UPDATE the
> indexed table copying from the other,
> and finally delete the line in the first table? All in one transaction?

That would work and would be a good solution if you change one BLOB at a time.

Or you can assemble the BLOB in memory and then write it all in one UPDATE 
command.

Or you can have another BLOB column, an unindexed one, in the existing table 
and do your editing to the value in that column:

BEGIN
build up the BLOB
UPDATE myTable SET permBLOB = tempBLOB, tempBLOB = 0 WHERE recID = 123456
END

This assumes that your BLOB is a long one which takes a lot of space.  If it 
isn't, you can just leave the value there rather than zeroing it out.

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

Re: [sqlite] Index question

[Paolo Bolzoni]

So I should write my BLOB in another (not-indexed) table, UPDATE the
indexed table copying from the other,
and finally delete the line in the first table? All in one transaction?

On Sun, Apr 28, 2013 at 4:23 PM, Simon Slavin  wrote:
>
> On 28 Apr 2013, at 3:02pm, Paolo Bolzoni  
> wrote:
>
>> And it seems quite an improvement, alas now I get this error: "cannot
>> open indexed column for writing."
>
> You have a column of type BLOB.
> It is now an indexed column.
> You are trying to use the BLOB editing routines to write into that BLOB 
> rather than just replacing the value in one operation.
>
> SQLite won't let you open an indexed BLOB value in editing mode because it 
> doesn't know what you want the index to reflect while the value is being 
> edited.
>
> You can replace the BLOB value in a single operation (using UPDATE).  Or you 
> can remove any index from that column. Sorry.
>
> 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] Index question

[Simon Slavin]

On 28 Apr 2013, at 3:02pm, Paolo Bolzoni  wrote:

> And it seems quite an improvement, alas now I get this error: "cannot
> open indexed column for writing."

You have a column of type BLOB.
It is now an indexed column.
You are trying to use the BLOB editing routines to write into that BLOB rather 
than just replacing the value in one operation.

SQLite won't let you open an indexed BLOB value in editing mode because it 
doesn't know what you want the index to reflect while the value is being edited.

You can replace the BLOB value in a single operation (using UPDATE).  Or you 
can remove any index from that column. Sorry.

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

Re: [sqlite] Index question

[Paolo Bolzoni]

Sorry, it seems gmail messed up the layout just before sending.

On Sun, Apr 28, 2013 at 4:02 PM, Paolo Bolzoni
 wrote:
> I was playing with indexes, I started checking one my queries with
> EXPLAIN QUERY PLAN and
> I got this result:
> selectidorder   fromdetail
> --  --  --
> -
> 0   0   1   SEARCH TABLE tour AS t2 USING
> INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0   1   0   SCAN TABLE tour AS t1 (~5000 rows)
>
> Adding an index on the column unsorted_path it becomes:
> selectidorder   fromdetail
> --  --  --
> -
> 0   0   1   SEARCH TABLE tour AS t2 USING
> INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0   1   0   SEARCH TABLE tour AS t1 USING
> INDEX t (unsorted_path=?) (~2 rows)
>
> And it seems quite an improvement, alas now I get this error: "cannot
> open indexed column for writing."
>
> What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index question

[Paolo Bolzoni]

I was playing with indexes, I started checking one my queries with
EXPLAIN QUERY PLAN and
I got this result:
selectidorder   fromdetail
--  --  --
-
0   0   1   SEARCH TABLE tour AS t2 USING
INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0   1   0   SCAN TABLE tour AS t1 (~5000 rows)

Adding an index on the column unsorted_path it becomes:
selectidorder   fromdetail
--  --  --
-
0   0   1   SEARCH TABLE tour AS t2 USING
INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0   1   0   SEARCH TABLE tour AS t1 USING
INDEX t (unsorted_path=?) (~2 rows)

And it seems quite an improvement, alas now I get this error: "cannot
open indexed column for writing."

What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Writing in a blob

[Eduardo]

On Fri, 26 Apr 2013 14:02:02 -0400
Roland Hughes  wrote:

> It actually does make sense to add chunking to sqlite.  There would be
> some computational overhead, but, that all depends on the chunk size and
> the cache size of the database.  It makes no sense to implement YAFS
> (Yet Another File System) inside of SQLite.

I agree with you that a generic YAFS is no sense, but a pseudo-fs for your app 
data, I think yes. 

Also, distributed file systems use databases (Lustre, f.ex.) to store 
filesystem metadata, and a simple distributed file system with basic semantics, 
including locks, can easily be done with sqlite.


-- 
Eduardo 
___
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 28 Apr 2013, at 9:55am, Igor Korot  wrote:

> Apologies for noise.
> That was an error on my side, which was discovered after spending couple of
> hours debugging.

No problem.  Glad you figured it out.

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

Re: [sqlite] How to save live data into sqlite database using c language?

[Teg]

Hello Newbie89,

Sunday, April 28, 2013, 3:32:07 AM, you wrote:

N> Thanks for the correction
N> ok...I will check first.



N> --
N> View this message in context:
N> 
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.html
N> Sent from the SQLite mailing list archive at Nabble.com.
N> ___
N> sqlite-users mailing list
N> sqlite-users@sqlite.org
N> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


I suspect you want one protocol field

enum _protocols
{
C_PROTOCOL_TCP,
C_PROTOCOL_UDP,
..
};


CREATE TABLE host
(
Src_MAC char(18),
Src_IP char(16),
Cap_Bytes integer,
Protocol integer,

);

With  a single protocol tag per insert. Then you tag each packet insert
with the protocol field.

-- 
Best regards,
 Tegmailto:t...@djii.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

[Igor Korot]

Apologies for noise.
That was an error on my side, which was discovered after spending couple of
hours debugging.

Thank you all for the help and sorry once again.


On Sat, Apr 27, 2013 at 5:20 AM, Simon Slavin  wrote:

>
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to save live data into sqlite database using c language?

[Newbie89]

Thanks for the correction
ok...I will check first.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.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] How to save live data into sqlite database using c language?

[Igor Korot]

Hi,

On Sun, Apr 28, 2013 at 12:14 AM, Newbie89  wrote:

> previously It used log file to save data. I need  to identify the variables
> which need to logged and save into database. So I need to create the table
> from this file.c
> struct HOST
> {
>char Src_MAC[18];
>char Src_IP[16];
>long int Cap_Bytes;
>int TCP,UDP,ICMP,IP,ARP,OTH_Net, OTH_Trans;   // Protocols
>int ftp,ssh,telnet,domain,www,netbios,other;  // Services
> };
>
> into this
> create table HOST (Src_MAC[18] char,Src_IP[16] char,Cap_Bytes long int,TCP
> int,UDP int,ICMP int,IP int,ARP int,OTH_Net int,OTH_Trans int,ftp int,ssh
> int,telnet int,domain int,www int,netbios int,other int);
>

Nope.
The proper syntax is:

CREATE TABLE host( Src_MAC char(18), Src_IP char(16), Cap_Bytes integer,
TCP integer, UDP integer, ICMP integer);

But it's much more than that.
You should really check what you program does, which queries you will
issue, what other tables you will need, the relationships between different
tables and how you construct you queries for better performance.

Thank you.


>
> right?
> How to I link so that the live data is save into the database?
> I'm  not very well in the connection?
> can give me some guides?
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519.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

[sqlite] How to save live data into sqlite database using c language?

[Newbie89]

previously It used log file to save data. I need  to identify the variables
which need to logged and save into database. So I need to create the table
from this file.c
struct HOST
{
   char Src_MAC[18];
   char Src_IP[16];
   long int Cap_Bytes;
   int TCP,UDP,ICMP,IP,ARP,OTH_Net, OTH_Trans;   // Protocols
   int ftp,ssh,telnet,domain,www,netbios,other;  // Services   
};

into this
create table HOST (Src_MAC[18] char,Src_IP[16] char,Cap_Bytes long int,TCP
int,UDP int,ICMP int,IP int,ARP int,OTH_Net int,OTH_Trans int,ftp int,ssh
int,telnet int,domain int,www int,netbios int,other int);

right?
How to I link so that the live data is save into the database?
I'm  not very well in the connection?
can give me some guides?




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519.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