RE: [sqlite] return table data without fields

[RB Smissaert]

That would be true if for example I only had to dump the array to text
or Excel or some other simple manipulations, but I have to do quite a few
different and complex things with these arrays and it would be much simpler
if these arrays didn't have the fields in the first place.
Now with the fields I would need to alter a lots of complex procedures.

RBS

-Original Message-
From: Eric Pankoke [mailto:[EMAIL PROTECTED] 
Sent: 27 January 2007 21:38
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] return table data without fields

Since you know how many columns are in the result set, can't you just
ignore the first row when referencing your data?  It seems to me at that
point you won't have any significant performance penalties.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Saturday, January 27, 2007 11:22 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] return table data without fields

Thanks for that information.
Taking the first row out of the array will have a big performance
penalty as
you say as you will have to write a whole new array. I know near enough
nil
about C, so altering the source will be difficult. Will think of
something.

Didn't know about the NULL handling and that is worth knowing and will
see
if it affects my app in some way.

RBS


-Original Message-
From: Trey Mack [mailto:[EMAIL PROTECTED] 
Sent: 27 January 2007 15:57
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] return table data without fields

I cannot find a PRAGMA for turning column names on/off either. You may
be 
thinking of ".headers on/off" in the SQLite shell..

I don't think there's a way to turn this off, except rewriting the code
for 
sqlite_get_table (which, thankfully, he's provided). Maybe to include a
4th 
parameter that would be a flag to include column headers or not.

Or you could wrap all your calls through a VB function of your design
that 
strips that first row (the column headers) out of the array, but I'd
worry 
about performance with that method.

One thing worth mentioning about this wrapper is that it doens't handle
NULL

return values as you might expect. If you issue a query that should
return 
NULL.. like:

create table t (a integer); -- no records..
select max(a) from t; -- should return NULL, since there is no max
select typeof(max(a)) from t; -- proves that SQLite returns NULL

you'll receive "" (the empty string) in the spot in the array where your

result ends up.

But again, thankfully, he's provided the source, so you can add the 
following lines

else { // I hope the following is safe, as I'm definitely NOT a
C/C++ 
guru.. especially with memory allocation / deallocation
tmpVariant.vt = VT_NULL;
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
VariantClear(&tmpVariant);
}

at line 103 in my copy of VBSQL.c (I may have done some formatting, I 
forget), as the else for the following if:

if (SQL_Results[sqlite_return_array_int]) {

then, the Variant that will be sent to VB6 will be of type Null and can
be 
tested with the VB6 function IsNull(..) properly.

Regards,
Trey

- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, January 27, 2007 7:01 AM
Subject: [sqlite] return table data without fields


> Using the VB wrapper dll from Todd Tanner:
> http://www.tannertech.net/sqlite3vb/index.htm
>
> and it has this function to return table rows:
>
> Private Declare Function sqlite_get_table _
>  Lib "SQLite3VB.dll" _
>  (ByVal DB_Handle As Long, _
>   ByVal SQLString As String, _
>   ByRef ErrStr As String) As Variant()
>
> This will by default include the table field names.
> Is there a way to return the table data without these field names?
> I thought there was a Pragma command for this, but I couldn't find it.
>
> RBS
>
>
>
>


-
> To unsubscribe, send email to [EMAIL PROTECTED]
>


-
>
> 




-
To unsubscribe, send email to [EMAIL PROTECTED]


-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send emai

RE: [sqlite] return table data without fields

[Eric Pankoke]

Since you know how many columns are in the result set, can't you just
ignore the first row when referencing your data?  It seems to me at that
point you won't have any significant performance penalties.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Saturday, January 27, 2007 11:22 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] return table data without fields

Thanks for that information.
Taking the first row out of the array will have a big performance
penalty as
you say as you will have to write a whole new array. I know near enough
nil
about C, so altering the source will be difficult. Will think of
something.

Didn't know about the NULL handling and that is worth knowing and will
see
if it affects my app in some way.

RBS


-Original Message-
From: Trey Mack [mailto:[EMAIL PROTECTED] 
Sent: 27 January 2007 15:57
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] return table data without fields

I cannot find a PRAGMA for turning column names on/off either. You may
be 
thinking of ".headers on/off" in the SQLite shell..

I don't think there's a way to turn this off, except rewriting the code
for 
sqlite_get_table (which, thankfully, he's provided). Maybe to include a
4th 
parameter that would be a flag to include column headers or not.

Or you could wrap all your calls through a VB function of your design
that 
strips that first row (the column headers) out of the array, but I'd
worry 
about performance with that method.

One thing worth mentioning about this wrapper is that it doens't handle
NULL

return values as you might expect. If you issue a query that should
return 
NULL.. like:

create table t (a integer); -- no records..
select max(a) from t; -- should return NULL, since there is no max
select typeof(max(a)) from t; -- proves that SQLite returns NULL

you'll receive "" (the empty string) in the spot in the array where your

result ends up.

But again, thankfully, he's provided the source, so you can add the 
following lines

else { // I hope the following is safe, as I'm definitely NOT a
C/C++ 
guru.. especially with memory allocation / deallocation
tmpVariant.vt = VT_NULL;
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
VariantClear(&tmpVariant);
}

at line 103 in my copy of VBSQL.c (I may have done some formatting, I 
forget), as the else for the following if:

if (SQL_Results[sqlite_return_array_int]) {

then, the Variant that will be sent to VB6 will be of type Null and can
be 
tested with the VB6 function IsNull(..) properly.

Regards,
Trey

- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, January 27, 2007 7:01 AM
Subject: [sqlite] return table data without fields


> Using the VB wrapper dll from Todd Tanner:
> http://www.tannertech.net/sqlite3vb/index.htm
>
> and it has this function to return table rows:
>
> Private Declare Function sqlite_get_table _
>  Lib "SQLite3VB.dll" _
>  (ByVal DB_Handle As Long, _
>   ByVal SQLString As String, _
>   ByRef ErrStr As String) As Variant()
>
> This will by default include the table field names.
> Is there a way to return the table data without these field names?
> I thought there was a Pragma command for this, but I couldn't find it.
>
> RBS
>
>
>
>


-
> To unsubscribe, send email to [EMAIL PROTECTED]
>


-
>
> 




-
To unsubscribe, send email to [EMAIL PROTECTED]


-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: An SQL question (Not directly related to SQLite)

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

Actually, my query is something like
SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
if i put that group by... will it group all rows, or only those with
the same pid?


GROUP BY works on whatever rows remain after WHERE test.

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Version 3.3.12

[Rich Shepard]

On Sat, 27 Jan 2007, Rich Shepard wrote:


 I have a Slackware-11.0 package available for anyone who wants a copy. It
includes the md5 sum for integrity checking.


  They are available using wget:

wget http://www.appl-ecosys.com/temp-files/sqlite3-3.3.12-i486-1rbs.tgz
wget http://www.appl-ecosys.com/temp-files/sqlite3-3.3.12-i486-1rbs.tgz.md5

  I'll keep them there a couple of days.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Version 3.3.12

[Rich Shepard]

On Sat, 27 Jan 2007, [EMAIL PROTECTED] wrote:


Version 3.3.12 is now available on the website


  I have a Slackware-11.0 package available for anyone who wants a copy. It
includes the md5 sum for integrity checking.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] enforcing Foreign Keys

[Mag Gam]

So...anyone?


On 1/25/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:


Dan McDaniel wrote:
> Can someone tell me how to unsubscribe. I have sent
> two messages to the link and have had no luck thank
> you.
> --- Mag Gam <[EMAIL PROTECTED]> wrote:
>
Send a mail to [EMAIL PROTECTED] - it has directions on how
to unsubscribe if the standard method fails. I just tried it and
(assuming all the timestamps are correct) got a response back in a
fraction over a minute.

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re[2]: [sqlite] Re: An SQL question (Not directly related to SQLite)

[ivailo91]

On Saturday, January 27, 2007, 6:09:59 PM, Trey Mack 
wrote:
>> Actually, my query is something like
>> SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
>> if i put that group by... will it group all rows, or only those with
>> the same pid?

> Use a subquery

> SELECT price, sum(count) FROM (
> -- your original query here
> SELECT price, count FROM ... WHERE `pid` = (SELECT `id` FROM ...)
> ) GROUP BY price;

> And you treat your subquery as just any other table. That way it's clear
> where your GROUPing is applied.

> Side note: I'd be careful about naming columns names like 'count'. That's a
> function in SQL, and some will simply not allow you to use it as a column
> name. Some will allow you to use it as a column name with some special
> handling (like "[count]"). SQLite appears to allow a column named "count",
> but did not for some others ("limit" comes to mind). "Precision" is another
> example in SQLServer.. It's easy to get around, just prepend some 
> description to the word, like "UnitPrice, ItemCount"...

> Regards,
> Trey


> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -

Well thanks for the advice :) I'll keep that in mind, although that
`count` was only for the example, the table schema actually contains
`download_count` but I wrote that example on hand and taking in account my 
laziness...

Best Regards,
Ivailo Karamanolev


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] return table data without fields

[RB Smissaert]

Thanks for that information.
Taking the first row out of the array will have a big performance penalty as
you say as you will have to write a whole new array. I know near enough nil
about C, so altering the source will be difficult. Will think of something.

Didn't know about the NULL handling and that is worth knowing and will see
if it affects my app in some way.

RBS


-Original Message-
From: Trey Mack [mailto:[EMAIL PROTECTED] 
Sent: 27 January 2007 15:57
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] return table data without fields

I cannot find a PRAGMA for turning column names on/off either. You may be 
thinking of ".headers on/off" in the SQLite shell..

I don't think there's a way to turn this off, except rewriting the code for 
sqlite_get_table (which, thankfully, he's provided). Maybe to include a 4th 
parameter that would be a flag to include column headers or not.

Or you could wrap all your calls through a VB function of your design that 
strips that first row (the column headers) out of the array, but I'd worry 
about performance with that method.

One thing worth mentioning about this wrapper is that it doens't handle NULL

return values as you might expect. If you issue a query that should return 
NULL.. like:

create table t (a integer); -- no records..
select max(a) from t; -- should return NULL, since there is no max
select typeof(max(a)) from t; -- proves that SQLite returns NULL

you'll receive "" (the empty string) in the spot in the array where your 
result ends up.

But again, thankfully, he's provided the source, so you can add the 
following lines

else { // I hope the following is safe, as I'm definitely NOT a C/C++ 
guru.. especially with memory allocation / deallocation
tmpVariant.vt = VT_NULL;
hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
VariantClear(&tmpVariant);
}

at line 103 in my copy of VBSQL.c (I may have done some formatting, I 
forget), as the else for the following if:

if (SQL_Results[sqlite_return_array_int]) {

then, the Variant that will be sent to VB6 will be of type Null and can be 
tested with the VB6 function IsNull(..) properly.

Regards,
Trey

- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, January 27, 2007 7:01 AM
Subject: [sqlite] return table data without fields


> Using the VB wrapper dll from Todd Tanner:
> http://www.tannertech.net/sqlite3vb/index.htm
>
> and it has this function to return table rows:
>
> Private Declare Function sqlite_get_table _
>  Lib "SQLite3VB.dll" _
>  (ByVal DB_Handle As Long, _
>   ByVal SQLString As String, _
>   ByRef ErrStr As String) As Variant()
>
> This will by default include the table field names.
> Is there a way to return the table data without these field names?
> I thought there was a Pragma command for this, but I couldn't find it.
>
> RBS
>
>
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: An SQL question (Not directly related to SQLite)

[Trey Mack]

Actually, my query is something like
SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
if i put that group by... will it group all rows, or only those with
the same pid?


Use a subquery

SELECT price, sum(count) FROM (
   -- your original query here
   SELECT price, count FROM ... WHERE `pid` = (SELECT `id` FROM ...)
) GROUP BY price;

And you treat your subquery as just any other table. That way it's clear 
where your GROUPing is applied.


Side note: I'd be careful about naming columns names like 'count'. That's a 
function in SQL, and some will simply not allow you to use it as a column 
name. Some will allow you to use it as a column name with some special 
handling (like "[count]"). SQLite appears to allow a column named "count", 
but did not for some others ("limit" comes to mind). "Precision" is another 
example in SQLServer.. It's easy to get around, just prepend some 
description to the word, like "UnitPrice, ItemCount"...


Regards,
   Trey


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Version 3.3.12

[drh]

Version 3.3.12 is now available on the website

  http://www.sqlite.org/download.html

This version fixes another bug in the IS NULL
optimization that was introduced in 3.3.9.  And
there is an enhancement to PRAGMA integrity_check
that limits the amount of output.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] return table data without fields

[Trey Mack]

I cannot find a PRAGMA for turning column names on/off either. You may be 
thinking of ".headers on/off" in the SQLite shell..


I don't think there's a way to turn this off, except rewriting the code for 
sqlite_get_table (which, thankfully, he's provided). Maybe to include a 4th 
parameter that would be a flag to include column headers or not.


Or you could wrap all your calls through a VB function of your design that 
strips that first row (the column headers) out of the array, but I'd worry 
about performance with that method.


One thing worth mentioning about this wrapper is that it doens't handle NULL 
return values as you might expect. If you issue a query that should return 
NULL.. like:


   create table t (a integer); -- no records..
   select max(a) from t; -- should return NULL, since there is no max
   select typeof(max(a)) from t; -- proves that SQLite returns NULL

you'll receive "" (the empty string) in the spot in the array where your 
result ends up.


But again, thankfully, he's provided the source, so you can add the 
following lines


   else { // I hope the following is safe, as I'm definitely NOT a C/C++ 
guru.. especially with memory allocation / deallocation

   tmpVariant.vt = VT_NULL;
   hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
   VariantClear(&tmpVariant);
   }

at line 103 in my copy of VBSQL.c (I may have done some formatting, I 
forget), as the else for the following if:


   if (SQL_Results[sqlite_return_array_int]) {

then, the Variant that will be sent to VB6 will be of type Null and can be 
tested with the VB6 function IsNull(..) properly.


Regards,
   Trey

- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, January 27, 2007 7:01 AM
Subject: [sqlite] return table data without fields



Using the VB wrapper dll from Todd Tanner:
http://www.tannertech.net/sqlite3vb/index.htm

and it has this function to return table rows:

Private Declare Function sqlite_get_table _
 Lib "SQLite3VB.dll" _
 (ByVal DB_Handle As Long, _
  ByVal SQLString As String, _
  ByRef ErrStr As String) As Variant()

This will by default include the table field names.
Is there a way to return the table data without these field names?
I thought there was a Pragma command for this, but I couldn't find it.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: An SQL question (Not directly related to SQLite)

[ivailo91]

On Saturday, January 27, 2007, 4:59:49 PM, Igor 
Tandetnik wrote:
> [EMAIL PROTECTED] wrote:
>> I've got a query, let's say query "A" which returns data in the
>> following form:
>> 
>> price | count
>> 3   5
>> 3   8
>> 4   2
>> 4   9
>> 4   12
>> 6   10
>> 
>> What I want to do is get unique prices and sum the count of the
>> duplicates, so it gets
>> 
>> price | count
>> 3   13
>> 4   23
>> 6   10

> select price, sum(count)
> from ... where ...
> group by price;

> Igor Tandetnik

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -

Actually, my query is something like
SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
if i put that group by... will it group all rows, or only those with
the same pid?

Best Regards,
Ivailo Karamanolev


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: An SQL question (Not directly related to SQLite)

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

I've got a query, let's say query "A" which returns data in the
following form:

price | count
3   5
3   8
4   2
4   9
4   12
6   10

What I want to do is get unique prices and sum the count of the
duplicates, so it gets

price | count
3   13
4   23
6   10


select price, sum(count)
from ... where ...
group by price;

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] An SQL question (Not directly related to SQLite)

[ivailo91]

Sorry for the previous trash message

I've got a query, let's say query "A" which returns data in the
following form:

price | count
3   5
3   8
4   2
4   9
4   12
6   10

What I want to do is get unique prices and sum the count of the
duplicates, so it gets

price | count
3   13
4   23
6   10

Best Regards,
Ivailo Karamanolev


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] An SQL question (Not directly related to SQLite)

[ivailo91]

I've got a query, let's say query "A" which returns data in the
following form:

Best Regards,
Ivailo Karamanolev


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] return table data without fields

[RB Smissaert]

Using the VB wrapper dll from Todd Tanner:
http://www.tannertech.net/sqlite3vb/index.htm

and it has this function to return table rows:

Private Declare Function sqlite_get_table _
  Lib "SQLite3VB.dll" _
  (ByVal DB_Handle As Long, _
   ByVal SQLString As String, _
   ByRef ErrStr As String) As Variant()

This will by default include the table field names.
Is there a way to return the table data without these field names?
I thought there was a Pragma command for this, but I couldn't find it.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Last call for bugs...

[Ralf Junker]

>I plan to release 3.3.12 later today or tomorrow.
>If you know about any unreported problems, please
>get those bug reports in quickly.  Tnx.

My vote for ticket #2183: It causes SQLite to crash with an access violation. I 
am keeping my fingers crossed ...

Regards,

Ralf 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-