Re: [sqlite] seeking database design advice

[Kees Nuyt]

On Fri, 2 Mar 2012 13:26:23 +1100, BareFeetWare
 wrote:

> Hi Rich,
>
>> I wanted to solicit some opinions about how best to layout a database table 
>> for a project I am working on.  I have a distributed application that 
>> reports run time information back to a central machine.  Included in the 
>> report are the parameters used during runtime... approx 25 or so.  
>> Parameters are site specific and may be changed locally by whomever runs the 
>> application.  I have a table that records an application instance (columns 
>> such as location, version etc), and a table that records report instances 
>> (time, date, size).  I wanted to be able to query at a glance the most 
>> recent parameter set in use, so I came up with the following table design...
>> 
>> Design A)
>> primaryKey | appInstanceId | reportId | param1 | param2 | ... | param25
>
> so that would be:

[snipped for brevity]

> Note that this will only give you results that
> actually exist, no extra nulls etc that aren't
> needed. It will be listed as rows, not columns,
> which is far more manageable and predictable.
>
>> I realize design A may not be "best", but I
>> would prefer an friendly answer and not a
>> canned response like I get from co-workers.


> Hopefully this was friendly and leads you towards "enlightenment" ;-)
>
> Tom
>
>Tom Brodhurst-Hill
>BareFeetWare

Excellent analysis!

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

The backtrace
===


Program received signal SIGSEGV, Segmentation fault.
0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
pParse=0x2d4035c8) at sqlite3.c:106740
106740  }while( yymajor!=YYNOCODE && yypParser->yyidx>=0 );
#0  0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
pParse=0x2d4035c8) at sqlite3.c:106740
yyminorunion = {
  yyinit = 735636932,
  yy0 = {
z = 0x2bd8edc4 "FROM dir_table",
n = 4
  },
  yy4 = 735636932,
  yy90 = {
a = 735636932,
b = 0x4
  },
  yy118 = {
pExpr = 0x2bd8edc4,
zStart = 0x4 ,
zEnd = 0x2b697000 ""
  },
  yy203 = 0x2bd8edc4,
  yy210 = 196 '\304',
  yy215 = {
value = 735636932,
mask = 4
  },
  yy259 = 0x2bd8edc4,
  yy292 = {
pLimit = 0x2bd8edc4,
pOffset = 0x4
  },
  yy314 = 0x2bd8edc4,
  yy322 = 0x2bd8edc4,
  yy342 = {
eOperator = {
  z = 0x2bd8edc4 "FROM dir_table",
  n = 4
},
not = 728330240
  },
  yy384 = 0x2bd8edc4,
  yy387 = 0x2bd8edc4
}
yyact = 21104640
yyendofinput = 0
yypParser = 0x2d401e40
#1  0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4
"SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at
sqlite3.c:107465
nErr = 0
i = 36
pEngine = 0x2d401e40
tokenType = 119
lastTokenParsed = 
enableLookaside = 1 '\001'
db = 0x6a14b0
mxSqlLen = 10
#2  0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT
COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0,
pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212
pParse = 0x2d4035c8
zErrMsg = 0x0
rc = 
i = 
#3  0x2b65b468 in sqlite3LockAndPrepare (db=,
zSql=, nBytes=-1, ppStmt=,
pzTail=0x0) at sqlite3.c:90304
rc = 
#4  sqlite3_prepare (db=, zSql=,
nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831
rc = 21104640
#5  0x00466730 in sql_stmt (db=9, stmt=0xffc0 ) at /localhome/user/a/b/c/d/e/f/g/h/k.c:496
errmsg = 0x0
retval = 
__PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\000"








On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy  wrote:

> On 03/01/2012 10:54 PM, Sreekumar TP wrote:
>
>> version 3.7.7.1
>> The query works on x86, but fails on MIPS processor!
>>
>
> Are you able to post a stack trace? Ideally generated by
> the gdb "where full" command. Thanks.
>
> Dan.
>
>
>
>>
>> Sreekumar
>>
>>
>>
>> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy
>>  wrote:
>>
>>  On 03/01/2012 05:48 PM, Sreekumar TP wrote:
>>>
>>>  In my system, the statement causes sqlite3parser function to crash.
 My compiler is  mips , little endian, gcc version is 4.5.2


>>> SQLite version? 3.7.10 seems Ok here.
>>>
>>> Dan.
>>>
>>> ___
>>> 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] seeking database design advice

[Simon Slavin]

On 2 Mar 2012, at 3:44am, Mario Becroft  wrote:

> Making schema changes, such as adding or removing attributes, is not
> necessarily difficult. This is what DML is for. However one thing SQL
> does not handle well is temporal schemas, i.e. ones in effect during
> different periods of time. The main reason why you might need to use
> design B instead of A in this case is if you need to be aware of which
> fields were valid at past times or will be valid in the future; for
> instance, if you remove a field but you do not wish to lose the
> information previously reported while that field existed, or more
> subtly, if after adding a new field, you need to know that past reports
> explicitly did not include that field.

Reports change.  Data is eternal.  Your schema should reflect the data stored 
in it, not the format of the report you happen to want at the moment.

The people talking to you, Rich, were right.  But they were rubbish at 
explaining why.  One hint is that there's nothing special about the number 25.  
So any schema that allows you to immediately identify 25 should be treated with 
suspicion.  Use the two-table design, and don't forget SQLite's group_concat(X) 
function:

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

which allows you to gather all the data for one row of your report in one long 
string, as long as the structure you store it in is well thought out.

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

Re: [sqlite] seeking database design advice

[Mario Becroft]

I agree with what has been said so far regarding normalization, getting
the underlying structure right, and using views to access the data. I
would add a couple of points:

Database refactoring is not necessarily as hard as has been
suggested. In fact, a good design, separating data storage (tables) and
data access (views) will not only minimize the need for future changes,
but make such changes easy to do when required. For instance you can
change the tables while retaining the same views, or vice versa. Writing
queries to migrate data from old to new schemas without interrupting
operations (on databases suitably designed to enable this) is every-day
work for DBA's.

Normalizing your data is a good idea, but the question really is what is
normalized in this case?

Treating the set of fields and their names as data (as in your design B)
may be the best way. However, were this mode of reasoning taken too far,
you might end up with a database having no structure at all, besides
that implied by the data stored in it (which is open to
interpretation). The whole point of SQL is that it provides tools to
help specify the structure of your data.

Making schema changes, such as adding or removing attributes, is not
necessarily difficult. This is what DML is for. However one thing SQL
does not handle well is temporal schemas, i.e. ones in effect during
different periods of time. The main reason why you might need to use
design B instead of A in this case is if you need to be aware of which
fields were valid at past times or will be valid in the future; for
instance, if you remove a field but you do not wish to lose the
information previously reported while that field existed, or more
subtly, if after adding a new field, you need to know that past reports
explicitly did not include that field.

There are also other reasons why the field names seem more like data
than schema in this case and I would agree that, based on the
information provided so far, design B is almost certainly better. I am
just making the point that the question is a little more subtle than the
people saying 'you must normalize' have implied, and you are right to
try and better understand the reasoning behind that opinion.

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

Re: [sqlite] SQLite and Windows Metro Style

[Richard Hipp]

On Thu, Mar 1, 2012 at 7:41 PM, Simon Slavin  wrote:

>
> On 2 Mar 2012, at 12:33am, Richard Hipp  wrote:
>
> > We are told that metro style applications may not use many of the win32
> > APIs that SQLite currently does use (APIs such as LoadLibrary(),
> > SetFilePointer(), GetFileSize(), etc.)
>
> I'll have to talk to my colleague.  Is it possible he used posix-style
> calls instead of windows-style calls ?
>

I don't think so.  But I don't really know that much about it.  I'm curious
to know what is going on too.



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



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

Re: [sqlite] import a txt into sqlite

[Larry Brasfield]

YAN HONG YE wrote:

I have a txt file
myteam.txt:

ID;name;sex;match;chinese;english;phy;chem;total
CY001;cred;male;54;101;101;55;99;695
CY002;rose;female;55;105;95;119;101;693
CY003;helen;male;110;95;65;50;50;613
CY004;jack;female;55;65;110;51;50;641
CY005;tonny;male;55;55;111;55;55;514

when I import the txt to my sqlite table, could I import the the first row 
contains columnnames, not data.
like odbc "HDR=Yes"



I created a modified version of the SQLite3 shell which, among other 
features, acts as you suggest when a .import command is issued while 
headers are enabled.  It can be found at:

https://docs.google.com/viewer?a=v=explorer=true=0BzRwojdN0G2UMWY2MTg2NzgtNDU3YS00Y2RiLWI5MTktMDZiMDYxZGU5MGEx=en_US=1
(Watch line wrap -- that's a one line URL.)

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

Re: [sqlite] seeking database design advice

[BareFeetWare]

Hi Rich,

> I wanted to solicit some opinions about how best to layout a database table 
> for a project I am working on.  I have a distributed application that reports 
> run time information back to a central machine.  Included in the report are 
> the parameters used during runtime... approx 25 or so.  Parameters are site 
> specific and may be changed locally by whomever runs the application.  I have 
> a table that records an application instance (columns such as location, 
> version etc), and a table that records report instances (time, date, size).  
> I wanted to be able to query at a glance the most recent parameter set in 
> use, so I came up with the following table design...
> 
> Design A)
> primaryKey | appInstanceId | reportId | param1 | param2 | ... | param25

so that would be:

create table "Application Instance"
(   appInstanceId integer primary key not null
,   name text not null collate nocase
,   location text not null collate nocase
,   version text not null collate nocase
)
;
create table "Report"
(   reportId integer primary key not null
,   datetime real not null
,   size integer
)
;
create table "Runtime"
(   primaryKey integer primary key not null
,   appInstanceId integer not null
,   reportId integer not null
,   param1
,   param2
,   param3
,   param4
,   param5
,   param6
,   param7
,   param8
,   param10
,   param11
,   param12
,   param13
,   param14
,   param15
,   param16
,   param17
,   param18
,   param19
,   param20
,   param21
,   param22
,   param23
,   param24
,   param25
)

> I spoke to a few people at work, and they all say the same thing... "this 
> isn't normalized'.  I get the following suggestion:
> 
> Design B)
> primaryKey | appInstanceId | reportId |paramNum |paramValue
> ---with a table second table--
> paramNum | paramDescription

Which would be:

create table "Application Instance"
(   appInstanceId integer primary key not null
,   name text not null collate nocase
,   location text not null collate nocase
,   version text not null collate nocase
)
;
create table "Report"
(   reportId integer primary key not null
,   datetime real not null
,   size integer
)
;
create table "Param"
(   paramNum integer primary key not null
,   paramDescription text
)
;
create table "Runtime"
(   primaryKey integer primary key not null
,   appInstanceId integer not null
references "Application Instance" (appInstanceId) on delete 
cascade on update cascade
,   reportId integer not null
references "Report" (reportId) on delete cascade on update 
cascade
,   paramNum integer not null
references "Param" (paramNum) on delete restrict on update 
cascade
,   paramValue not null
)
;

> The reason for Design B:
>   New parameters are easily added (no schema change)
>   It is "Better Design" (throwing around the word normalization a lot)

Agreed.

> My reasons for Design A:
>   1:1 record to report (not 1:25 records)
>   meta data overhead per row is 1/9 (opposed to 4/1)

Those aren't problems. You can use views and other mechanisms to view the data 
in whatever layout you like (so you don't have to manually cross reference). 
But the underlying data should be stored efficiently and, yes, normalized.

> I'm not a database guru, so I can't fight back...but I feel I am hearing a 
> line straight from a textbook.  Critics of my design simply tells me it is 
> wrong and keep repeating the mantra "It is not normalized".

Yes, that is frustrating. I've been there, years ago, even on this list, had 
"normalized" thrown at me without explanation. The best thing to do is research 
what normalized means. I am a convert now ;-) The "Definitive Guide to SQLite" 
book is a good place to start, in the chapter on "Normalization" (though read 
the previous pages first). In part it states:

>> Normalization concerns itself with the organization of attributes within 
>> relations so as to minimize duplication of data. Data duplication, as you 
>> will see, has more deleterious effects than just taking up unnecessary 
>> space. It increases the opportunity for database inconsistencies to arise. 
>> Normalization is about designing your database to be more resistant to the 
>> ill effects of thoughtless users and buggy programs. 

>> As stated, the chief aim of normalization is to eradicate duplication. 
>> Relations that have duplication removed are said to be normalized. However, 
>> there are degrees of normalization. These degrees are called normal forms.

>> First normal form simply states that all attributes in a relation use 
>> domains that are made up of atomic values..., meaning simply “that which 
>> cannot be broken down further.”

>> To understand second and third normal form, you have to first understand 
>> functional dependencies. The simple 

Re: [sqlite] seeking database design advice

[Petite Abeille]

On Mar 2, 2012, at 1:28 AM, Rich Rattanni wrote:

> I realize design A may not be "best", but I would prefer an friendly
> answer and not a canned response like I get from co-workers.

[warning: canned answer ahead]

FWIW, there is some good wisdom in going down the "text book" way, aka 
normalization :)

http://en.wikipedia.org/wiki/Database_normalization

On the other hand, if your main goal is reporting, then perhaps a different 
form of modelization might be more effective:

http://en.wikipedia.org/wiki/Dimensional_modeling

Either way, applying a bit of method (the "text book" part) has its benefits.

 


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

Re: [sqlite] SQLite and Windows Metro Style

[Simon Slavin]

On 2 Mar 2012, at 12:33am, Richard Hipp  wrote:

> We are told that metro style applications may not use many of the win32
> APIs that SQLite currently does use (APIs such as LoadLibrary(),
> SetFilePointer(), GetFileSize(), etc.)

I'll have to talk to my colleague.  Is it possible he used posix-style calls 
instead of windows-style calls ?

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

Re: [sqlite] SQLite and Windows Metro Style

[Richard Hipp]

On Thu, Mar 1, 2012 at 6:51 PM, Himadri Sarkar  wrote:

> Hi,
> I am a newbie to the SQLite user group and was wondering if anyone has
> tried to use SQLite to build the new Windows metro style applications and
> if yes is there a step by step approach written down somewhere?
>

We are told that metro style applications may not use many of the win32
APIs that SQLite currently does use (APIs such as LoadLibrary(),
SetFilePointer(), GetFileSize(), etc.)  We are currently working on a new
version of SQLite that avoids the missing interfaces.  You can follow our
progress in this branch:

http://www.sqlite.org/src/timeline?r=winrt

Note that, as far as we can tell, there is no way to configure SQLite such
that it will work for both desktop applications (windows 7, vista, winXP,
winNT, win95) and metro style applications (winRT) at the same time.  You
have to choose one or the other at compile-time.  Currently the default is
for desktop applications and so if you want to run on winRT you have to add
the -DSQLITE_OS_WINRT=1 option to your compiler.  We'll remove that
requirement if and when we can figure out a way to make the platform
determination at run-time.



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



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

Re: [sqlite] read each line in sqlite database

[Simon Davies]

On 1 March 2012 18:43, Don V Nielsen  wrote:
> I think Simon's solution is in error, and Igor's is correct.  In Simon's
> case, Slevel will be set to 1 if price1 is greater than 30.

It will be set to 2, as required by OP.

> However, the
> original c function would set Slevel to 2 because price1 is greater than 12
> and it is greater than 30.  Two increments of i are executed in that
> scenario.  Igor's accumulating the boolean results gets you where you want
> to be; it's just a little cryptic.
>
> The original function was less than explicit.  I'm assuming some kind of
> weighted value is being generated.  My solution is probably the most wordy
> and least efficient performance wise, it is explicit in function.  It also
> allows you to set the weight of each price1/price2 level.
>
> UPDATE bb SET Slevel =
> (
>  (Case when price1>12 then 1
>        when price1>20 then 2
>        else 0
>   End) +
>  (Case when price2>20 then 1
>        when price2>30 then 2
>        when price2>80 then 3
>        else 0
>   End)
> );
>

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

[sqlite] seeking database design advice

[Rich Rattanni]

All:

I wanted to solicit some opinions about how best to layout a database
table for a project I am working on.  I have a distributed application
that reports run time information back to a central machine.  Included
in the report are the parameters used during runtime... approx 25 or
so.  Parameters are site specific and may be changed locally by
whomever runs the application.  I have a table that records an
application instance (columns such as location, version etc), and a
table that records report instances (time, date, size).  I wanted to
be able to query at a glance the most recent parameter set in use, so
I came up with the following table design...

Design A)
primaryKey | appInstanceId | reportId | param1 | param2 | ... | param25

I spoke to a few people at work, and they all say the same thing...
"this isn't normalized'.  I get the following suggestion:

Design B)
primaryKey | appInstanceId | reportId |paramNum |paramValue
---with a table second table--
paramNum | paramDescription

The reason for Design B:
   New parameters are easily added (no schema change)
   It is "Better Design" (throwing around the word normalization a lot)

My reasons for Design A:
   1:1 record to report (not 1:25 records)
   meta data overhead per row is 1/9 (opposed to 4/1)

I'm not a database guru, so I can't fight back...but I feel I am
hearing a line straight from a textbook.  Critics of my design simply
tells me it is wrong and keep repeating the mantra "It is not
normalized".  The way I see it the column itself describes the data,
which means less data is stored... efficient and simple.
I do see the appeal of Design A by effectively 'future-proofing' the
database table.  My application performing the inserts should not have
to change, I just always insert the parameters I find in each report.

I should add that I do expect future parameters to be added (maybe
5,10 more at most, but rather infrequently).  Old and new versions
will co-exist, but eventually all application instances should be
updated.  With Design A I would add new columns and set existing
records to NULL.  I would expect 100,000 reports a month or so.  When
I select from parameters I generally will show all parameters, so my
queries become "select * from tbl where appid=x and reportId=y" with 1
result set necessary (not iterating over 25 results).

I realize design A may not be "best", but I would prefer an friendly
answer and not a canned response like I get from co-workers.

Thanks in advance,
Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Windows Metro Style

[Simon Slavin]

On 1 Mar 2012, at 11:51pm, Himadri Sarkar  wrote:

> I am a newbie to the SQLite user group and was wondering if anyone has
> tried to use SQLite to build the new Windows metro style applications and
> if yes is there a step by step approach written down somewhere?

I have a colleague who has done it, with a pre-release version of Windows 8.  
There was no problem with it.  Just include the C source code for the API in 
you project and make sure your compiler knows it's C and not C++.

His project was a very simple demonstration and didn't use every facility of 
SQLite.  And he didn't put it through thorough testing.  So it's possible there 
is a problem hidden there.  But the basics (opening, exec INSERT and SELECT, 
closing) worked as expected.

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

[sqlite] SQLite and Windows Metro Style

[Himadri Sarkar]

Hi,
I am a newbie to the SQLite user group and was wondering if anyone has
tried to use SQLite to build the new Windows metro style applications and
if yes is there a step by step approach written down somewhere?
Regards,
-- 
Himadri
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Yum install of PHP not including sqlite3

[Rose, John B]

Hello

We are running Red Hat 6.2 with PHP 5.3.3 installed via yum

Among other things in the ./configure section I see '--without-sqlite3'


However, via phpinfo() we see a section ...


sqlite3
SQLite3 supportenabled
SQLite3 module version0.7-dev
SQLite Library3.6.20

DirectiveLocal ValueMaster Value
sqlite3.extension_dirno valueno value

But our test of PHP-to-SQLite does not seem to be working

I am new to yum. How do we get PHP installed “with” sqlite?

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

[sqlite] Typo in source code comment

[gwenn]

/*** EXPERIMENTAL ***
**
** Register a function to be invoked when a transaction comments.
** If the invoked function returns non-zero, then the commit becomes a
** rollback.
*/
SQLITE_API void *sqlite3_commit_hook(

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

[sqlite] Could not load file or assembly 'System.Data.SQLite'

[James Denton]

Hello!

I'm trying to use SQLite in an ASP.NET 2.0 application. I ran the x86
installer (sqlite-netFx35-setup-bundle-x86-2008-1.0.79.0.exe), then
added a reference in Visual Web Developer Express and copied the
version independent XML for DbProviderFactories from the included help
file into my app.config file.

When I visit the page in my browser, I get the following error:

---
Failed to find or load the registered .Net Framework Data Provider.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Configuration.ConfigurationErrorsException:
Failed to find or load the registered .Net Framework Data Provider.

Source Error:


Line 29:
Line 30:// Stuff things into DB
Line 31:DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SQLite");
Line 32:using (DbConnection conn = factory.CreateConnection())
Line 33:{


Source File: c:\Windows\Web\RDWeb\Pages\Scan3\Scan.aspxLine: 31

Stack Trace:


[ConfigurationErrorsException: Failed to find or load the registered
.Net Framework Data Provider.]
   System.Data.Common.DbProviderFactories.GetFactory(DataRow
providerRow) +2253507
   System.Data.Common.DbProviderFactories.GetFactory(String
providerInvariantName) +184
   ASP.scan3_scan_aspx.Page_Init(Object sender, EventArgs e) in
c:\Windows\Web\RDWeb\Pages\Scan3\Scan.aspx:31
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp,
Object o, Object t, EventArgs e) +25
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object
sender, EventArgs e) +42
   System.Web.UI.Control.OnInit(EventArgs e) +132
   System.Web.UI.Page.OnInit(EventArgs e) +16
   System.Web.UI.Control.InitRecursive(Control namingContainer) +143
   System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+11056792
   System.Web.UI.Page.ProcessRequest(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+11056490
   System.Web.UI.Page.ProcessRequest() +91
   System.Web.UI.Page.ProcessRequest(HttpContext context) +240
   ASP.scan3_scan_aspx.ProcessRequest(HttpContext context) in
c:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET
Files\rdweb_pages\decbd09c\9f1642a3\App_Web_z-cvbhdp.3.cs:0
   
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+599
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +171

---



So I went back and ran the test application that System.Data.SQLite
installer put on the start menu. It also throws an error:

---
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

** Exception Text **
System.BadImageFormatException: Could not load file or assembly
'System.Data.SQLite, Version=1.0.79.0, Culture=neutral,
PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An
attempt was made to load a program with an incorrect format.
File name: 'System.Data.SQLite, Version=1.0.79.0, Culture=neutral,
PublicKeyToken=db937bc2d44ff139'
   at System.RuntimeTypeHandle._GetTypeByName(String name, Boolean
throwOnError, Boolean ignoreCase, Boolean reflectionOnly,
StackCrawlMark& stackMark, Boolean loadTypeFromPartialName)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean
throwOnError, Boolean ignoreCase, Boolean reflectionOnly,
StackCrawlMark& stackMark)
   at System.RuntimeType.PrivateGetType(String typeName, Boolean
throwOnError, Boolean ignoreCase, Boolean reflectionOnly,
StackCrawlMark& stackMark)
   at System.Type.GetType(String typeName)
   at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
   at System.Data.Common.DbProviderFactories.GetFactory(String
providerInvariantName)
   at test.TestCasesDialog.runButton_Click(Object sender, EventArgs e)
in c:\dev\sqlite\dotnet\test\TestCasesDialog.cs:line 109
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value
[HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind
failure logging.
To turn this feature off, remove the registry value

Re: [sqlite] Constraint Error Messages

[Pete]

Thanks for all the responses - I seem to have opened up a can of worms!

Looking into his further, I see differences in how constraint failures are
reported.  In the case of a NOT NULL constraint, I got an error
". may not be NULL".  In the case of a UNIQUE constraint,
the error was "column  is not unique".  But if a CHECK constraint
failed, the error was simply "constraint failed" with no reference to the
column name or that it was a CHECK constraint that failed.

I could probably deal with any error message that included the column name
in terms of reinterpreting it for display to a user, just as I would if the
constraint name was reported, but the bare bones "constraint failed" error
is hard to deal with.  Maybe I will use a trigger with "RAISE()" instead of
check constraint.

It also seems that only one error is reported even if multiple constraint
violations occur.  Maybe that would change depending on the ON CONFLICT
action specified?

I guess all I can do is add my vote to the list of people who would like
improvements to the way constraint violations are reported, perhaps with a
PRAGMA to control the new logic.

Thanks,


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

Re: [sqlite] read each line in sqlite database

[Don V Nielsen]

Ya know, if you ever wanted to deviate from the SQL standard, a handy
upgrade to the CASE statement would permit split conditions on the case and
when statement, as in:

Case price1
   when > 12 then 1
   when > 30 then 2
   else 0
end

dvn

On Thu, Mar 1, 2012 at 12:43 PM, Don V Nielsen wrote:

> I think Simon's solution is in error, and Igor's is correct.  In Simon's
> case, Slevel will be set to 1 if price1 is greater than 30.  However, the
> original c function would set Slevel to 2 because price1 is greater than 12
> and it is greater than 30.  Two increments of i are executed in that
> scenario.  Igor's accumulating the boolean results gets you where you want
> to be; it's just a little cryptic.
>
> The original function was less than explicit.  I'm assuming some kind of
> weighted value is being generated.  My solution is probably the most wordy
> and least efficient performance wise, it is explicit in function.  It also
> allows you to set the weight of each price1/price2 level.
>
> UPDATE bb SET Slevel =
> (
>   (Case when price1>12 then 1
> when price1>20 then 2
> else 0
>End) +
>   (Case when price2>20 then 1
> when price2>30 then 2
> when price2>80 then 3
> else 0
>End)
> );
>
>
> On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik wrote:
>
>> YAN HONG YE  wrote:
>> > I have a sqlite database named bb:
>> >
>> >> NamePrice1Price2Slevel
>> >> A123 231  NULL
>> >> A22212 NULL
>> >> A3   21223   NULL
>> >
>> > My question is:
>> > I want to update culumn Slevel by function myfunc():
>> >
>> > int myfunc():
>> > {int i=0;
>> > if (price1 >12)
>> > i++;
>> > if (price1>30)
>> > i++;
>> > if (price2>20)
>> > i++;
>> > if (price2>30)
>> > i++;
>> > if (price2>80)
>> > i++;
>> > return i;
>> > }
>>
>> Just run this query:
>>
>> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
>> (price2>30) + (price2>80);
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] read each line in sqlite database

[Don V Nielsen]

I think Simon's solution is in error, and Igor's is correct.  In Simon's
case, Slevel will be set to 1 if price1 is greater than 30.  However, the
original c function would set Slevel to 2 because price1 is greater than 12
and it is greater than 30.  Two increments of i are executed in that
scenario.  Igor's accumulating the boolean results gets you where you want
to be; it's just a little cryptic.

The original function was less than explicit.  I'm assuming some kind of
weighted value is being generated.  My solution is probably the most wordy
and least efficient performance wise, it is explicit in function.  It also
allows you to set the weight of each price1/price2 level.

UPDATE bb SET Slevel =
(
  (Case when price1>12 then 1
when price1>20 then 2
else 0
   End) +
  (Case when price2>20 then 1
when price2>30 then 2
when price2>80 then 3
else 0
   End)
);


On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik  wrote:

> YAN HONG YE  wrote:
> > I have a sqlite database named bb:
> >
> >> NamePrice1Price2Slevel
> >> A123 231  NULL
> >> A22212 NULL
> >> A3   21223   NULL
> >
> > My question is:
> > I want to update culumn Slevel by function myfunc():
> >
> > int myfunc():
> > {int i=0;
> > if (price1 >12)
> > i++;
> > if (price1>30)
> > i++;
> > if (price2>20)
> > i++;
> > if (price2>30)
> > i++;
> > if (price2>80)
> > i++;
> > return i;
> > }
>
> Just run this query:
>
> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
> (price2>30) + (price2>80);
>
> --
> Igor Tandetnik
>
> ___
> 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] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

yes, will post the trace.

Sreekumar
On Mar 1, 2012 11:42 PM, "Dan Kennedy"  wrote:

> On 03/01/2012 10:54 PM, Sreekumar TP wrote:
>
>> version 3.7.7.1
>> The query works on x86, but fails on MIPS processor!
>>
>
> Are you able to post a stack trace? Ideally generated by
> the gdb "where full" command. Thanks.
>
> Dan.
>
>
>
>>
>> Sreekumar
>>
>>
>>
>> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy
>>  wrote:
>>
>>  On 03/01/2012 05:48 PM, Sreekumar TP wrote:
>>>
>>>  In my system, the statement causes sqlite3parser function to crash.
 My compiler is  mips , little endian, gcc version is 4.5.2


>>> SQLite version? 3.7.10 seems Ok here.
>>>
>>> Dan.
>>>
>>> ___
>>> 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] dcs0816

[Allen Fowler]

Make Income 0nIine with revoIutionary system
http://incident.zxq.net/pjob4jou/httpjob4journal0129.php?netjpageID=58

Thu, 1 Mar 2012 19:14:07
__
"  In modern times walls are always attacked with mortars and cannon" (c) 
jevaun visszaszallitasat
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Dan Kennedy]

On 03/01/2012 10:54 PM, Sreekumar TP wrote:

version 3.7.7.1
The query works on x86, but fails on MIPS processor!


Are you able to post a stack trace? Ideally generated by
the gdb "where full" command. Thanks.

Dan.





Sreekumar



On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy  wrote:


On 03/01/2012 05:48 PM, Sreekumar TP wrote:


In my system, the statement causes sqlite3parser function to crash.
My compiler is  mips , little endian, gcc version is 4.5.2



SQLite version? 3.7.10 seems Ok here.

Dan.

__**_
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] sqlite3.dll version information not available

[Jos Groot Lipman]

Using the sqlite3_libversion is different, it can only be used *after* the
DLL has been loaded.
With Thomas' solution he can check the version without/before loading the
DLL
--
Jos Groot Lipman

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: donderdag 1 maart 2012 14:17
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite3.dll version information not available
> 
> Thomas Funk  wrote:
> >> I use the sqlite3.dll for Win32, it works fine.
> >> It is possible to add the version information in the next 
> version. I 
> >> think it helps a lot of users, so it is possible to read 
> the version 
> >> in my application by a windows API function, and check for 
> the correct version.
> 
> http://sqlite.org/c3ref/libversion.html
> 
> --
> Igor Tandetnik
> 
> ___
> 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] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

version 3.7.7.1
The query works on x86, but fails on MIPS processor!


Sreekumar



On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy  wrote:

> On 03/01/2012 05:48 PM, Sreekumar TP wrote:
>
>> In my system, the statement causes sqlite3parser function to crash.
>> My compiler is  mips , little endian, gcc version is 4.5.2
>>
>
> SQLite version? 3.7.10 seems Ok here.
>
> Dan.
>
> __**_
> 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] Constraint error messages

[Petite Abeille]

On Mar 1, 2012, at 4:08 PM, Marc L. Allen wrote:

>> I don't understand why you need to have constraint declaration. Do you
>> have some kind of parser of table definition and your application
>> relies on it to find all constraints that SQLite enforces?
> 
> I believe it irks him that he cannot, in his own mind, provide a complete 
> table definition.  I think he wants something that, if looked at without 
> examining indexes associated with the table, still provide the constraint 
> information.

Precisely. 

The added irritation is that SQLite doesn't provide any mechanism to access 
that metadata in the first place. Oh, well...

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

Re: [sqlite] Constraint error messages

[Petite Abeille]

On Mar 1, 2012, at 3:13 PM, Igor Tandetnik wrote:

>> I would if I could. Unfortunately, in SQLite, in the case of unique 
>> constraints, this cannot be done as far as I know. Or?
> 
> Just create a unique index, in lieu of unique constraint.

I would rather see SQLite support some sort of USING INDEX [1] clause for 
constraints, e.g.:

constraint  fubar unique( baz ) using index( /* index clause */ )

Or something... I know, wishful thinking, but hope is eternal :D

> 
>> (2) If one create an unique, named index, one cannot use an unique constraint
> 
> Why would one want to?

For completeness' sake and the metadata it provides.

Otherwise, functionally, in SQLite, they are both identical in practice as one 
gets an unique index either ways. It's just another case where there is a 
disconnect in the data dictionary. Sigh...


[1] 
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: crash in selectExpander()

[Richard Hipp]

On Thu, Mar 1, 2012 at 10:11 AM, dead beef <0xdead.n...@gmail.com> wrote:

> The following query causes a crash (null-pointer dereference):
>
> $ sqlite3
> SQLite version 3.7.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT 1 FROM ( SELECT 1 ) INNER JOIN ( SELECT * ) USING ( c );
> Segmentation fault
>

I suspect that this is the same bug that was fixed here:
http://www.sqlite.org/src/info/c8c7846fb9



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



-- 
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 report: crash in selectExpander()

[dead beef]

The following query causes a crash (null-pointer dereference):

$ sqlite3
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT 1 FROM ( SELECT 1 ) INNER JOIN ( SELECT * ) USING ( c );
Segmentation fault
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[Marc L. Allen]

> I don't understand why you need to have constraint declaration. Do you
> have some kind of parser of table definition and your application
> relies on it to find all constraints that SQLite enforces?

I believe it irks him that he cannot, in his own mind, provide a complete table 
definition.  I think he wants something that, if looked at without examining 
indexes associated with the table, still provide the constraint information.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Pavel Ivanov]

> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?

That's a wrong approach. First, you don't need to modify functions in
SQLite code, you need to create your own. Your function will convert
using locale saved in some variable in your application. At
application startup you fill this variable with value from table
db_locale or with some default value if there's nothing in db_locale.
Then each time you change your application's variable you save new
value in table db_locale. That's it.


Pavel


On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
 wrote:
> I found the function that I would need to modify (see below).
>
> static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
> **apArg){
>   const UChar *zInput;
>   UChar *zOutput;
>   int nInput;
>   int nOutput;
>
>   UErrorCode status = U_ZERO_ERROR;
>   const char *zLocale = 0;
>
>   assert(nArg==1 || nArg==2);
>   if( nArg==2 ){
>     zLocale = (const char *)sqlite3_value_text(apArg[1]);
>   }
>
>   zInput = sqlite3_value_text16(apArg[0]);
>   if( !zInput ){
>     return;
>   }
>   nInput = sqlite3_value_bytes16(apArg[0]);
>
>   nOutput = nInput * 2 + 2;
>   zOutput = sqlite3_malloc(nOutput);
>   if( !zOutput ){
>     return;
>   }
>
>   if( sqlite3_user_data(p) ){
>     u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }else{
>     u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }
>
>   if( !U_SUCCESS(status) ){
>     icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
>     return;
>   }
>
>   sqlite3_result_text16(p, zOutput, -1, xFree);
> }
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 08:56, Grace Batumbya wrote:
>
> Is there an example extension you know that I could look at that does this?
> (i am a novice at SQLite)
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: March 1, 2012 8:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
> variable
>
> On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
>  wrote:
>
> You can simply register your
> own lower/upper with one argument that looks wherever you want to know
> what locale to use.
>
> The part of registering a function to override lower/upper I think I
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and
> reconnect it is still set to the last time I set it, how do I go about
> accomplishing this.
>
> Create a special table for that and store your last locale value in
> it. Then after reconnect read the locale value from this table.
>
>
> Pavel
> ___
> 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] Constraint error messages

[Pavel Ivanov]

On Thu, Mar 1, 2012 at 9:01 AM, Petite Abeille  wrote:
> On Mar 1, 2012, at 2:51 PM, Pavel Ivanov wrote:
>
>>> (2) If one create an unique, named index, one cannot use an unique 
>>> constraint as there is no way to add constraints after the table creation 
>>> (i.e. no alter add constraint …).
>>
>> You don't need to create a unique constraint when you created a unique
>> index. Unique index implies that you can't insert duplicate records.
>
> Precisely. In fact they are mutually exclusive.
>
> Nonetheless, the constraint declares an intent. An index provide an 
> enforcement mechanism. Therefore if one declares an unique constraint, one 
> gets an unique index to enforce it. I just wish I could name that index in a 
> meaningful way so I don't have to choose between declaring a constraint and 
> having a random index or having a meaningfully named index but no constraint 
> declaration.

I don't understand why you need to have constraint declaration. Do you
have some kind of parser of table definition and your application
relies on it to find all constraints that SQLite enforces?


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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Simon Batumbya]

I found the function that I would need to modify (see below).

   |static void icuCaseFunc16(sqlite3_context *p, int nArg,
   sqlite3_value **apArg){
  const UChar *zInput;
  UChar *zOutput;
  int nInput;
  int nOutput;

  UErrorCode status = U_ZERO_ERROR;
  const char *zLocale = 0;

  assert(nArg==1 || nArg==2);
  if( nArg==2 ){
zLocale = (const char *)sqlite3_value_text(apArg[1]);
  }

  zInput = sqlite3_value_text16(apArg[0]);
  if( !zInput ){
return;
  }
  nInput = sqlite3_value_bytes16(apArg[0]);

  nOutput = nInput * 2 + 2;
  zOutput = sqlite3_malloc(nOutput);
  if( !zOutput ){
return;
  }

  if( sqlite3_user_data(p) ){
u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale,
   );
  }else{
u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale,
   );
  }

  if( !U_SUCCESS(status) ){
icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
return;
  }

  sqlite3_result_text16(p, zOutput, -1, xFree);
   }
   | 

Given that there exists a table db_locale [CREATE TABLE db_locale 
(locale text)],
what lines of code would be used to query that table from inside this 
function?


*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca 

On 3/1/2012 08:56, Grace Batumbya wrote:

Is there an example extension you know that I could look at that does this? (i 
am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
  wrote:

You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.

The part of registering a function to override lower/upper I think I understand.

But if I wanted to persist the locale, so that even if I disconnect and 
reconnect it is still set to the last time I set it, how do I go about 
accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


Pavel
___
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] Constraint error messages

[Igor Tandetnik]

Petite Abeille  wrote:
> On Mar 1, 2012, at 2:27 PM, Igor Tandetnik wrote:
> 
>> If you want to refer to an index by name, I suggest you explicitly create 
>> this index with the name of your choosing.
> 
> I would if I could. Unfortunately, in SQLite, in the case of unique 
> constraints, this cannot be done as far as I know. Or?

Just create a unique index, in lieu of unique constraint.

> (2) If one create an unique, named index, one cannot use an unique constraint

Why would one want to?
-- 
Igor Tandetnik

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

Re: [sqlite] Constraint error messages

[Petite Abeille]

On Mar 1, 2012, at 2:51 PM, Pavel Ivanov wrote:

>> (2) If one create an unique, named index, one cannot use an unique 
>> constraint as there is no way to add constraints after the table creation 
>> (i.e. no alter add constraint …).
> 
> You don't need to create a unique constraint when you created a unique
> index. Unique index implies that you can't insert duplicate records.

Precisely. In fact they are mutually exclusive. 

Nonetheless, the constraint declares an intent. An index provide an enforcement 
mechanism. Therefore if one declares an unique constraint, one gets an unique 
index to enforce it. I just wish I could name that index in a meaningful way so 
I don't have to choose between declaring a constraint and having a random index 
or having a meaningfully named index but no constraint declaration.

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Batumbya]

Is there an example extension you know that I could look at that does this? (i 
am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
 wrote:
>>You can simply register your
>>own lower/upper with one argument that looks wherever you want to know
>>what locale to use.
>
> The part of registering a function to override lower/upper I think I 
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and 
> reconnect it is still set to the last time I set it, how do I go about 
> accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


Pavel
___
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] Set Locale for upper() and lower() using a pragma variable

[Pavel Ivanov]

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
 wrote:
>>You can simply register your
>>own lower/upper with one argument that looks wherever you want to know
>>what locale to use.
>
> The part of registering a function to override lower/upper I think I 
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and 
> reconnect it is still set to the last time I set it, how do I go about 
> accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


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

Re: [sqlite] Constraint error messages

[Pavel Ivanov]

> (2) If one create an unique, named index, one cannot use an unique constraint 
> as there is no way to add constraints after the table creation (i.e. no alter 
> add constraint …).

You don't need to create a unique constraint when you created a unique
index. Unique index implies that you can't insert duplicate records.


Pavel


On Thu, Mar 1, 2012 at 8:40 AM, Petite Abeille  wrote:
>
> On Mar 1, 2012, at 2:27 PM, Igor Tandetnik wrote:
>
>> If you want to refer to an index by name, I suggest you explicitly create 
>> this index with the name of your choosing.
>
> I would if I could. Unfortunately, in SQLite, in the case of unique 
> constraints, this cannot be done as far as I know. Or?
>
> (1) If one declares an unique constraint, SQLite will automaticaly create a 
> randomely named index.
>
> (2) If one create an unique, named index, one cannot use an unique constraint 
> as there is no way to add constraints after the table creation (i.e. no alter 
> add constraint …).
>
> So, either one can have a named constraint in the initial DDL, but a randomly 
> named index. Or no constraint at all, and a named index.
>
> Looks half-backed to me.
>
> To add insult to injury, none of the constraint names are available anywhere 
> anyway anyhow. Sigh...
>
>
> ___
> 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] Set Locale for upper() and lower() using a pragma variable

[Grace Batumbya]

>You can simply register your
>own lower/upper with one argument that looks wherever you want to know
>what locale to use.

The part of registering a function to override lower/upper I think I understand.

But if I wanted to persist the locale, so that even if I disconnect and 
reconnect it is still set to the last time I set it, how do I go about 
accomplishing this.

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

Re: [sqlite] Constraint error messages

[Petite Abeille]

On Mar 1, 2012, at 2:27 PM, Igor Tandetnik wrote:

> If you want to refer to an index by name, I suggest you explicitly create 
> this index with the name of your choosing.

I would if I could. Unfortunately, in SQLite, in the case of unique 
constraints, this cannot be done as far as I know. Or?

(1) If one declares an unique constraint, SQLite will automaticaly create a 
randomely named index. 

(2) If one create an unique, named index, one cannot use an unique constraint 
as there is no way to add constraints after the table creation (i.e. no alter 
add constraint …).

So, either one can have a named constraint in the initial DDL, but a randomly 
named index. Or no constraint at all, and a named index.

Looks half-backed to me.

To add insult to injury, none of the constraint names are available anywhere 
anyway anyhow. Sigh...


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

Re: [sqlite] Constraint error messages

[Igor Tandetnik]

Petite Abeille  wrote:
> For example, one can define a named unique key constraint:
> 
> constraint  header_uk unique( name ),
> 
> This will result in SQLite automagically creating an unique index to support 
> the constraint. What's the name of that index? No
> one knows for sure, as it's automatically generated (e.g. 
> sqlite_autoindex_header_1 or something) instead of simply using the
> provided name of the constraint.  

It is legal to have a named constraint, and an unrelated index with the same 
name. There may be databases like this out there. With the proposed change, 
those databases would stop working.

> This might not have mattered much in the past as one couldn't refer to 
> indexes by name.
> 
> But now  SQLite provides the INDEXED BY phrase [1]. Where one can refer to 
> indexes by name.

If you want to refer to an index by name, I suggest you explicitly create this 
index with the name of your choosing.

> SQLite took it upon itself to randomly generate a totally unrelated name. 

Also known as "SQLite took care to avoid name collisions".
-- 
Igor Tandetnik

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Dan Kennedy]

On 03/01/2012 05:48 PM, Sreekumar TP wrote:

In my system, the statement causes sqlite3parser function to crash.
My compiler is  mips , little endian, gcc version is 4.5.2


SQLite version? 3.7.10 seems Ok here.

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

Re: [sqlite] read each line in sqlite database

[Igor Tandetnik]

YAN HONG YE  wrote:
> I have a sqlite database named bb:
> 
>> NamePrice1Price2Slevel
>> A123 231  NULL
>> A22212 NULL
>> A3   21223   NULL
> 
> My question is:
> I want to update culumn Slevel by function myfunc():
> 
> int myfunc():
> {int i=0;
> if (price1 >12)
> i++;
> if (price1>30)
> i++;
> if (price2>20)
> i++;
> if (price2>30)
> i++;
> if (price2>80)
> i++;
> return i;
> }

Just run this query:

update bb set Slevel = (price1>12) + (price1>30) + (price2>20) + (price2>30) + 
(price2>80);

-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3.dll version information not available

[Igor Tandetnik]

Thomas Funk  wrote:
>> I use the sqlite3.dll for Win32, it works fine.
>> It is possible to add the version information in the next version. I
>> think it helps a lot of users, so it is possible to read the version in
>> my application by a windows API function, and check for the correct version.

http://sqlite.org/c3ref/libversion.html

-- 
Igor Tandetnik

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

[sqlite] sqlite3.dll version information not available

[Thomas Funk]

Hello,

I use the sqlite3.dll for Win32, it works fine.
It is possible to add the version information in the next version. I 
think it helps a lot of users, so it is possible to read the version in 
my application by a windows API function, and check for the correct version.
I have attached a example resource file for the VC++ compiler to include 
the version information very simply.





Best regards,
Thomas Funk
//Microsoft Developer Studio generated resource script.
//
#include "resource.h"

#define APSTUDIO_READONLY_SYMBOLS
/
//
// Generated from the TEXTINCLUDE 2 resource.
//
#include "windows.h"

/
#undef APSTUDIO_READONLY_SYMBOLS

/
// Deutsch (Deutschland) resources

#if !defined(AFX_RESOURCE_DLL) || defined(AFX_TARG_DEU)
#ifdef _WIN32
LANGUAGE LANG_GERMAN, SUBLANG_GERMAN
#pragma code_page(1252)
#endif //_WIN32

#ifdef APSTUDIO_INVOKED
/
//
// TEXTINCLUDE
//

1 TEXTINCLUDE DISCARDABLE
BEGIN
"resource.h\0"
END

2 TEXTINCLUDE DISCARDABLE 
BEGIN
"#include ""windows.h""\r\n"
"\0"
END

3 TEXTINCLUDE DISCARDABLE 
BEGIN
"\r\n"
"\0"
END

#endif// APSTUDIO_INVOKED


#ifndef _MAC
/
//
// Version
//

VS_VERSION_INFO VERSIONINFO
 FILEVERSION 1,0,0,0
 PRODUCTVERSION 1,0,0,0
 FILEFLAGSMASK 0x3fL
#ifdef _DEBUG
 FILEFLAGS 0x1L
#else
 FILEFLAGS 0x0L
#endif
 FILEOS 0x40004L
 FILETYPE 0x2L
 FILESUBTYPE 0x0L
BEGIN
BLOCK "StringFileInfo"
BEGIN
BLOCK "040904b0"
BEGIN
VALUE "Comments", "??\0"
VALUE "CompanyName", "?\0"
VALUE "LegalCopyright", "?\0"
VALUE "FileDescription", "\0"
VALUE "FileVersion", "1, 0, 5, 0\0"
VALUE "InternalName", "\0"
VALUE "LegalTrademarks", "\0"
VALUE "OriginalFilename", "sqlite3.dll\0"
VALUE "PrivateBuild", "\0"
VALUE "ProductName", "sqlite3.dll\0"
VALUE "ProductVersion", "1, 0, 0, 0\0"
VALUE "SpecialBuild", "\0"
END
END
BLOCK "VarFileInfo"
BEGIN
VALUE "Translation", 0x409, 1200
END
END

#endif// !_MAC

#endif// Deutsch (Deutschland) resources
/



#ifndef APSTUDIO_INVOKED
/
//
// Generated from the TEXTINCLUDE 3 resource.
//


/
#endif// not APSTUDIO_INVOKED

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

Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Profiler Output

[Frank Chang]

 



From: frank_chan...@hotmail.com
To: sqlite-users@sqlite.org
Subject: RE: Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is 
I/O Bound and uses all the Physical Memory
Date: Wed, 29 Feb 2012 11:59:56 -0500








Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler 
output is attached) but the profiler is full of sqlite functions and the 
application runs slower because it is I/O bound and uses almost all the 
physical memory. 
I was thinking maybe we could write only one SQLITE SELECT statement and cache 
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement 
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned 
long*/ int*& SubGraphBlob_,
  int *Size_) {
 int Size;

  //sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);

 // Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
 //   we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
 //
 // Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
 //   dedupe the subgraph, some records will be consolidated into others. The
 //   donor record's BLOB gets zapped because all of it's BLOB was rolled into 
the
 //   donee (All your BLOB are belong to us!)
 
 // First time, open the BLOB for real, else we can re-open (faster):
   char SelectStatement[256];
   char WhereClause[256];
 strcpy(SelectStatement, "select [Rows] from AggregatedData");
   sprintf(WhereClause," where [RowId] = %d",SubGraph_->IteratorPos+1);
   strcat(SelectStatement, WhereClause);
   int ReturnValue=sqlite3_prepare(SubGraph_->Database,
SelectStatement,-1,
_->Statement);
 
   int status =  sqlite3_step(SubGraph_->Statement);
   if (status == SQLITE_ROW) {
  
SubGraphBlob_ =  (int*)sqlite3_column_blob(SubGraph_->Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_->Statement,0)); //2);
// if (SubGraph_->hBlob==0)
//  
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,_->hBlob);
// else
//  sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned 
long),0);
// SubGraphBlob_[Size]=0;
 if (Size_!=0)
  *Size_=Size;
 }
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int 
*IntersectionBlob_,
  /*unsigned long*/int *SubGraphBlob_) {
 int Pos1,Pos2,PosOut;
 GetSubGraphBlob(SubGraph_,SubGraphBlob_);
 // Perform the intersection. We walk though the two blobs, if the blobs 
contain the same
 //   value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
 //   incremented so it can 'catch up' to the other:
 Pos1=Pos2=PosOut=0;
 while (IntersectionBlob_[Pos1]!=0 && SubGraphBlob_[Pos2]!=0) {
  if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
   IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
   Pos2++;
  } else if (IntersectionBlob_[Pos1]

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

In my system, the statement causes sqlite3parser function to crash.
My compiler is  mips , little endian, gcc version is 4.5.2


-Sreekumar



On Thu, Mar 1, 2012 at 2:59 PM, Simon Davies
wrote:

> On 1 March 2012 09:22, Sreekumar TP  wrote:
> > doesnt work either.
> >
>
> In what way does it fail to live up to your expectations?
>
> Regards,
> 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] Constraint error messages

[Petite Abeille]

On Feb 29, 2012, at 10:28 PM, Richard Hipp wrote:

> But that would require a lot of
> extra code space, which is not "lite".

There is a fine line between being parsimonious and cutting corners. 

For example, one can define a named unique key constraint:

constraint  header_uk unique( name ),

This will result in SQLite automagically creating an unique index to support 
the constraint. What's the name of that index? No one knows for sure, as it's 
automatically generated (e.g. sqlite_autoindex_header_1 or something) instead 
of simply using the provided name of the constraint.

This might not have mattered much in the past as one couldn't refer to indexes 
by name.

But now  SQLite provides the INDEXED BY phrase [1]. Where one can refer to 
indexes by name. Index names one has no ideas about, as SQLite took it upon 
itself to randomly generate a totally unrelated name.

Sigh…

It's my opinion that SQLite does itself a disservice by systematically ignoring 
all the meta information  painstakingly provided in the DDL in the first place.

Just my 2¢.

[1] http://www.sqlite.org/lang_indexedby.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Simon Davies]

On 1 March 2012 09:34, Petite Abeille  wrote:
>
> On Mar 1, 2012, at 10:29 AM, Simon Davies wrote:
>
>>> doesnt work either.
>>>
>>
>> In what way does it fail to live up to your expectations?
>
> Simon? Are you impersonating Igor?!? :D
>

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Petite Abeille]

On Mar 1, 2012, at 10:29 AM, Simon Davies wrote:

>> doesnt work either.
>> 
> 
> In what way does it fail to live up to your expectations?

Simon? Are you impersonating Igor?!? :D

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Simon Davies]

On 1 March 2012 09:22, Sreekumar TP  wrote:
> doesnt work either.
>

In what way does it fail to live up to your expectations?

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Petite Abeille]

On Mar 1, 2012, at 10:22 AM, Sreekumar TP wrote:

> doesnt work either.

Sure it does.

sqlite> select count( distinct name ) from header;
899


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

Re: [sqlite] read each line in sqlite database

[Simon Davies]

On 1 March 2012 08:44, YAN HONG YE  wrote:
> I have a sqlite database named bb:
>
>> Name    Price1    Price2    Slevel
>> A1        23             231          NULL
>> A2        22            12             NULL
>> A3       21            223           NULL
>
> My question is:
> I want to update culumn Slevel by function myfunc():
>
> int myfunc():
> {int i=0;
> if (price1 >12)
> i++;
> if (price1>30)
> i++;
> if (price2>20)
> i++;
> if (price2>30)
> i++;
> if (price2>80)
> i++;
> return i;
> }
>

For a table named bb:

UPDATE bb SET Slevel =
CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;

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

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

doesnt work either.



On Thu, Mar 1, 2012 at 2:50 PM, Petite Abeille wrote:

>
> On Mar 1, 2012, at 9:46 AM, Sreekumar TP wrote:
>
> > Is SELECT COUNT (DISTINCT column_name FROM table)  syntax supported ?
>
> try:
>
> select count( distinct foo ) from bar
>
> ___
> 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] SELECT COUNT (DISTINCT column_name FROM table)

[Petite Abeille]

On Mar 1, 2012, at 9:46 AM, Sreekumar TP wrote:

> Is SELECT COUNT (DISTINCT column_name FROM table)  syntax supported ?

try:

select count( distinct foo ) from bar

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

[sqlite] SELECT COUNT (DISTINCT column_name FROM table)

[Sreekumar TP]

Hi,

Is SELECT COUNT (DISTINCT column_name FROM table)  syntax supported ?

The above causes a crash in sqlite3Parser function..

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

[sqlite] read each line in sqlite database

[YAN HONG YE]

I have a sqlite database named bb:

> NamePrice1Price2Slevel
> A123 231  NULL
> A22212 NULL
> A3   21223   NULL

My question is:
I want to update culumn Slevel by function myfunc():

int myfunc():
{int i=0;
if (price1 >12)
i++;
if (price1>30)
i++;
if (price2>20)
i++;
if (price2>30)
i++;
if (price2>80)
i++;
return i;
}

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