Re: [sqlite] "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON

[preeti.yadav1]

 
 In my case, the DB is created by the older version of application and
now the new version of application is installed and End user has option
to see old data with selecting old version of DB so its completely on
end user wish to choose the location for DB. And also new tables in DB
are created to new version of application, on select of old DB,
application has to create those tables in OLD DB also to synch.

Application is successfully executing select query on UAC-ON however
while executing DDL and DML it is throwing "Unable to open Database".

Please guide us on this issue.  

Please let me know if it is not clear to you all.

Thanks,
Preeti
 
 



From: Preeti Yadav (WT01 - Healthcare and Services) 
Sent: Monday, July 19, 2010 1:47 PM
To: 'sqlite-users@sqlite.org'
Subject: "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON


Hello Team,
 
I am using SQLite DB for  java/Swings based desktop application. We are
facing below issue on Win-7 & Vista (64 bit) Operating System only on
UAC -ON (User access Control).
 
Current version of application is unable to do any DDL/ DML on DB
Created by older version of application on  Win-7 & Vista (64 bit)
Operating Systems only on UAC -ON (User access Control). We found that
DB is throwing "Unable to Open DB".  Everything is working perfectly if
we set UAC to Never Notify.
 
We do not have any problems with other OS like Win-xp,  Mac, etc. 
 
We request you to please guide us on this issue.  
 
Please let me know if you need more information.
 
Thanks in advance for your kind help!!
 
Warm Regards,
Preeti
 

Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

Re: [sqlite] Cost of PRAGMA database_list

[Simon Slavin]

On 21 Jul 2010, at 12:42am, Sam Carleton wrote:

> There are two equally
> important requires, one is to connect to the second "EventDB", the other is
> that the system admin can change the EventDB at any time.

You mean change the contents of that file, or change which file is nominated as 
the EventDB file ?

> The second
> requirement means that connection can be attached to a database called
> EventDB, but it is the wrong (old) DB.

What signifies the 'right' EventDB ?  Are you creating at least two different 
files (a right one and a wrong one) and giving them the same filename in the 
same directory ?  If they're different files they should have different 
filenames.


It seems to me that you either need to attach an EventDB file only as long as a 
command is trying to use it.  If you can't do that you need a way to tell your 
application that the EventsDB file it's currently connected to is out of date, 
and it's time to go find a new EventDB file.

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

Re: [sqlite] Cost of PRAGMA database_list

[Sam Carleton]

On Mon, Jul 19, 2010 at 9:46 PM, Simon Slavin  wrote:

>
> On 20 Jul 2010, at 2:01am, Sam Carleton wrote:
>
> > On Mon, Jul 19, 2010 at 3:51 PM, Simon Slavin 
> wrote:
> >
> >> You know, I think that the most efficient way to do what you want will
> >> probably to always issue the 'ATTACH' command.  If EventsDB is already
> >> attached, you should get a specific error code, which you can notice but
> >> ignore.
> >
> > So, there is foul simply trying to attach and ignoring the error if there
> is
> > one?  That does sound ideal.
>
> Test it.  Try it and see what happens.  Ideally either you'll get a SQLITE_
> error code, or the second attach will be silently dropped.  But it's
> possible that the PRAGMA will show you've found a bug in SQLite in which
> case please post here.
>
> If you get an error code, ignore that specific one only, and not all errors
> returned by the ATTACH.
>

Ok, the distractions are gone, back on task!  There are two equally
important requires, one is to connect to the second "EventDB", the other is
that the system admin can change the EventDB at any time.  The second
requirement means that connection can be attached to a database called
EventDB, but it is the wrong (old) DB.

Given the connection is attached to DB fileA as EventDB, is the behavior
defined on what should happen when the ATTACH command is issued to attach DB
FileB as EventDB?  The keyword here is defined, fore if it will simply drop
the old and attach to the new but this is not the defined behavior, it will
be safer to simply do the check (which is already coded) in case the current
behavior changes at some later date.

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

Re: [sqlite] how to retrieve the result of an sqlite_exec()

[Jay A. Kreibich]

On Tue, Jul 20, 2010 at 10:41:04PM +0100, Simon Slavin scratched on the wall:
> 
> On 20 Jul 2010, at 7:42pm, dmsmsm wrote:

> > if (sqlite3_exec(db, [rowcount UTF8String], NULL, NULL, ) != SQLITE_OK)

> Second, I see no reason why your code wouldn't work.  Did you try it ? 
> What happened ?  Below I'm just suggesting some changes.

  The lack of a callback to actually extract the results might have
  something to do with it.  I'm sure the code is running just fine, but
  since the callback is NULL (3rd param), the results are being discarded.
  
  Where exactly were you expecting the result to show up?

> It should be faster to name a specific column rather than use '*':

  Definitely not, for many reasons.  It may not even provide the
  same answer.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to retrieve the result of an sqlite_exec()

[Simon Slavin]

On 20 Jul 2010, at 7:42pm, dmsmsm wrote:

> Iam new to sqlite3. I want to get the number of rows in a table. I am using
> sqlite_exec(), but not sure, this is the correct function to get the number
> of rows.
> Please help me
> my code is 
> 
> NSString *rowcount = [NSString stringWithFormat:@"SELECT COUNT(*) FROM
> DB;"];
> 
> char *err;
> if (sqlite3_exec(db, [rowcount UTF8String], NULL, NULL, ) != SQLITE_OK)
> {
>sqlite3_close(db);
>NSAssert(0, @"Failed to get Number of Rows");
> }

First, you should know that most people here don't know Objective-C or Cocoa so 
they won't understand your code.  You are writing in a language which only some 
Mac or iPhone programmers will recognise.

Second, I see no reason why your code wouldn't work.  Did you try it ?  What 
happened ?  Below I'm just suggesting some changes.

> NSString *rowcount = [NSString stringWithFormat:@"SELECT COUNT(*) FROM DB;"];

You do not need the semi-colon since the SQL operating accepts only whole 
commands.  It should be faster to name a specific column rather than use '*':

NSString *rowcount = [NSString stringWithFormat:@"SELECT COUNT(rowid) FROM DB"];

> char *err;
> if (sqlite3_exec(db, [rowcount UTF8String], NULL, NULL, ) != SQLITE_OK)

> {
>sqlite3_close(db);
>NSAssert(0, @"Failed to get Number of Rows");
> }


No need to close the database unless your application is immediately quitting.  
Even if your application quits entirely without closing the database your data 
will still be okay.

It will be useful to report all available error information as part of your 
error message:

char *sqlErrorMsg = NULL;
int sqlResult = sqlite3_exec(db, [rowcount UTF8String],
NULL, NULL, );
NSAssert2(sqlResult == SQLITE_OK,
@"Get Number of Rows failed with error %i: %s.",
sqlResult, sqlErrorMsg);

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

[sqlite] SQLite database corruption

[Grzegorz Russek]

Hi,

We're experiencing strange database corruptions using SQLite database (we
are using it for over three years). We are reporting this now because one of
databases got corrupted on less sensitive data which we can give you
for analysis and because we wanted to reject all other possibilities of
error on our side, we also separated SQLite library from the rest of
application to exclude memory management problems. But when problem occurred in
our experimental project which was using .NET Compact Framework it was sure
that there is something not right.

First of all, we are using SQLite 3.6.23.1 on Pocket PC 2003 SE/Windows
Mobile 5.0 - 6.5 platform. SQLite is compiled using eVC 4.0 SP4. Described
situation occurs mostly on newer versions of OS, but it may be hardware
dependent, because we have few types of devices on which this problem did
not occur, but on most of available devices this problem happens, so this
problem can include other developers using Windows Mobile and SQlite.

We were unable to create tests that would trigger this issue, but it mostly
happens when we synchronize data between server and mobile device.
Synchronization downloads from server SQLite database with updated data and
copy that data using single query per table. If table is big, then we use
limit and offset to copy in chunks. Downloaded database has similar
structure and identical settings (encoding, page size, etc.) Query looks
like that:

REPLACE INTO "main"."ProductPriceList" (
"Product_Id",
"PriceList_Code",
"NettoVal",
"Deleted")
SELECT
"BigSync_TH_SYNC"."ProductPriceList"."Product_Id",
"BigSync_TH_SYNC"."ProductPriceList"."PriceList_Code",
"BigSync_TH_SYNC"."ProductPriceList"."NettoVal",
"BigSync_TH_SYNC"."ProductPriceList"."Deleted"
FROM "BigSync_TH_SYNC"."ProductPriceList" *LIMIT 16536 OFFSET 0*;

In this case we are positive that this corruption occurred while executing
this query, because at the moment only synchronization modifies that table.
We don't know whether limit offset was applied, because we don't know how
big change was in database. (If synchronization is successful we remove
incoming database). Database also did not report any error, until something
else referenced that table.

This issue mostly takes place on this query:

REPLACE INTO "main"."Clients" (
"Id_Client",
"Code",
"ShortName",
"Name",
"Street",
"No",
"City",
"Region_Id",
"Latitude",
"Longitude",
"GPSData",
"Modified",
"Deleted",
...)
SELECT
"BigSync_TH_SYNC"."Clients"."Id_Client",
"BigSync_TH_SYNC"."Clients"."Code",
"BigSync_TH_SYNC"."Clients"."ShortName",
"BigSync_TH_SYNC"."Clients"."Name",
"BigSync_TH_SYNC"."Clients"."Street",
"BigSync_TH_SYNC"."Clients"."No",
"BigSync_TH_SYNC"."Clients"."City",
"main"."Clients"."Region_Id",
"main"."Clients"."Latitude",
"main"."Clients"."Longitude",
"main"."Clients"."GPSData",
"main"."Clients"."Modified",
"BigSync_TH_SYNC"."Clients"."Deleted",
...
FROM "BigSync_TH_SYNC"."Clients"
LEFT OUTER JOIN "main"."Clients" ON
"main"."Clients"."Id_Client"="BigSync_TH_SYNC"."Clients"."Id_Client";

Database in attachment has corrupted index on table ProductPriceList. Data
in other tables was wiped, because those are real client
data. Fortunately this did not change output of SQLite analyzer  and
integrity check. (also in attachment).

Hope that you will be able to resolve our issue. I hope that information I
provided will be useful. If you have any questions please feel free to ask.

Cheers

PS. I'm also curious if there is any faster way to transfer data between
databases. On desktop this is not a problem, but on mobile if table Clients
has 5000 rows replace into takes from 1 to 6 minutes, depending on processor
and flash speed.
Analyzing table AbsenceReasons...
Analyzing table Absences...
Analyzing table Addresses...
Analyzing table Cache_Clients...
Analyzing table Cache_Group...
Analyzing table Cache_Product_Filter...
Analyzing table Cache_Promotions_Products...
Analyzing table ClientCategories...
Analyzing table ClientGroupDiscounts...
Analyzing table ClientGroups...
Analyzing table ClientHardware...
Analyzing table ClientLicences...
Analyzing table ClientPackages...
Analyzing table ClientPrices...
Analyzing table ClientProductDiscounts...
Analyzing table ClientPromotions...
Analyzing table ClientSpecialPrices...
Analyzing table ClientStandards...
Analyzing table Client_Tasks...
Analyzing table Clients...
Analyzing table ClientsCatalog...
Analyzing table Corporations...
Analyzing table DocumentTypes...
Analyzing table FreebeProducts...
Analyzing table GasUsage...
Analyzing table Groups...
Analyzing table GroupsGroups...
Analyzing table Hardware...
Analyzing table HardwareTypes...
Analyzing table InvoiceHeads...
Analyzing table InvoicePositions...
Analyzing table InvoiceTypes...
Analyzing table KP...
Analyzing table LastOrdered...
Analyzing table Licences...
Analyzing table 

[sqlite] 3rd Call For Papers, 17th Annual Tcl/Tk Conference 2010

[Andreas Kupries]

[[
Get your papers in.

The deadline for abstracts and proposals is
less than two weeks away.
]]

17th Annual Tcl/Tk Conference (Tcl'2010)
http://www.tcl.tk/community/tcl2010/

October 11 - 15, 2010
Hilton Suites/Conference Center

Chicago/Oakbrook Terrace, Illinois, USA

Important Dates:

Abstracts and proposals due   August   1, 2010
Notification to authors   August  15, 2010
WIP and BOF reservations open August   1, 2010
Author materials due  October  1, 2010
Tutorials Start   October 11, 2010
Conference starts October 13, 2010

Email Contact:tclconfere...@googlegroups.com

Submission of Summaries

Tcl/Tk 2010 will be held in Chicago/Oakbrook Terrace, Illinois USA
from October 11 - 15, 2010. The program committee is asking for papers
and presentation proposals from anyone using or developing with Tcl/Tk
(and extensions). Past conferences have seen submissions covering a
wide variety of topics including:

* Scientific and engineering applications
* Industrial controls
* Distributed applications and Network Managment
* Object oriented extensions to Tcl/Tk
* New widgets for Tk
* Simulation and application steering with Tcl/Tk
* Tcl/Tk-centric operating environments
* Tcl/Tk on small and embedded devices
* Medical applications and visualization
* Use of different programming paradigms in Tcl/Tk and proposals for new
  directions.
* New areas of exploration for the Tcl/Tk language

This year is the third year that the Tcl community is participating in
the Google Summer of Code.  The conference program committee would
like to encourage submissions that report on the Tcl projects selected
for Google SoC 2010.

Submissions should consist of an abstract of about 100 words and a
summary of not more than two pages, and should be sent as plain text
to  no later than August 15,
2010. Authors of accepted abstracts will have until October 1, 2010 to
submit their final paper for the inclusion in the conference
proceedings. The proceedings will be made available on digital media,
so extra materials such as presentation slides, code examples, code
for extensions etc. are encouraged.

Printed proceedings will be produced as an on-demand book at lulu.com

The authors will have 25 minutes to present their paper at the
conference.

The program committee will review and evaluate papers according to the
following criteria:

* Quantity and quality of novel content
* Relevance and interest to the Tcl/Tk community
* Suitability of content for presentation at the conference

Proposals may report on commercial or non-commercial systems, but
those with only blatant marketing content will not be accepted.

Application and experience papers need to strike a balance between
background on the application domain and the relevance of Tcl/Tk to
the application. Application and experience papers should clearly
explain how the application or experience illustrates a novel use of
Tcl/Tk, and what lessons the Tcl/Tk community can derive from the
application or experience to apply to their own development efforts.

Papers accompanied by non-disclosure agreements will be returned to
the author(s) unread. All submissions are held in the highest
confidentiality prior to publication in the Proceedings, both as a
matter of policy and in accord with the U. S. Copyright Act of 1976.

The primary author for each accepted paper will receive registration
to the Technical Sessions portion of the conference at a reduced rate.

Other Forms of Participation

The program committee also welcomes proposals for panel discussions of
up to 90 minutes. Proposals should include a list of confirmed
panelists, a title and format, and a panel description with position
statements from each panelist. Panels should have no more than four
speakers, including the panel moderator, and should allow time for
substantial interaction with attendees. Panels are not presentations
of related research papers.

Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather
sessions (BOFs) are available on a first-come, first-served basis
starting in August 1, 2010. Specific instructions for reserving WIP
and BOF time slots will be provided in the registration information
available in June 2010. Some WIP and BOF time slots will be held open
for on-site reservation. All attendees with an interesting work in
progress should consider reserving a WIP slot.

Registration Information

More information on the conference is available the conference Web
site (http://www.tcl.tk/community/tcl2010/) and will be published on
various Tcl/Tk-related information channels.

Reservations for hotel suites and $25 airport shuttle rides can be
made at
http://www.hilton.com/en/hi/groups/personalized/CHIOTHS-TCL-20101010/index.jhtml?WT.mc_id=POG

To keep in touch with news regarding the conference and Tcl events in
general, subscribe to the tcl-announce list. See:
http://aspn.activestate.com/ASPN/Mail/ to subscribe to the
tcl-announce 

Re: [sqlite] "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON

[Pavel Ivanov]

If your end user controls where to put the database file then after
receiving error "Unable to open database" you should tell him to
change permissions on that file to be accessible to everybody. Don't
forget to tell him to change permissions on the directory where
database resides too.


Pavel

On Tue, Jul 20, 2010 at 4:28 AM,   wrote:
> In my case, the DB is created by the older version of application and
> now the new version of application is installed and End user has option
> to see old data with selecting old version of DB so its completely on
> end user wish to choose the location for DB. And also new tables in DB
> are created to new version of application, on select of old DB,
> application has to create those tables in OLD DB also to synch.
>
> Application is successfully executing select query on UAC-ON however
> while executing DDL and DML it is throwing "Unable to open Database".
>
> Please guide us on this issue.
>
> Please let me know if it is not clear to you all.
>
> Thanks,
> Preeti
>
>
>
> 
>
> From: Preeti Yadav (WT01 - Healthcare and Services)
> Sent: Monday, July 19, 2010 1:47 PM
> To: 'sqlite-users@sqlite.org'
> Subject: "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON
>
>
> Hello Team,
>
> I am using SQLite DB for  java/Swings based desktop application. We are
> facing below issue on Win-7 & Vista (64 bit) Operating System only on
> UAC -ON (User access Control).
>
> Current version of application is unable to do any DDL/ DML on DB
> Created by older version of application on  Win-7 & Vista (64 bit)
> Operating Systems only on UAC -ON (User access Control). We found that
> DB is throwing "Unable to Open DB".  Everything is working perfectly if
> we set UAC to Never Notify.
>
> We do not have any problems with other OS like Win-xp,  Mac, etc.
>
> We request you to please guide us on this issue.
>
> Please let me know if you need more information.
>
> Thanks in advance for your kind help!!
>
> Warm Regards,
> Preeti
>
>
> Please do not print this email unless it is absolutely necessary.
>
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email.
>
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to retrieve the result of an sqlite_exec()

[dmsmsm]

Hi,
Iam new to sqlite3. I want to get the number of rows in a table. I am using
sqlite_exec(), but not sure, this is the correct function to get the number
of rows.
Please help me
my code is 

NSString *rowcount = [NSString stringWithFormat:@"SELECT COUNT(*) FROM
DB;"];

char *err;
if (sqlite3_exec(db, [rowcount UTF8String], NULL, NULL, ) != SQLITE_OK)
{
sqlite3_close(db);
NSAssert(0, @"Failed to get Number of Rows");
}


here in this code, from where can i extract the row count..
Please help!!!

dmsmsm
-- 
View this message in context: 
http://old.nabble.com/how-to-retrieve-the-result-of-an-sqlite_exec%28%29-tp29216324p29216324.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Max Vlasov]

>   In the case of SQLite, it is also very unlikely to save space.  In
>  fact, because of the way integer values are stored, it is very
>  likely to use more space.
>

Jay, In most cases yes, but there are ones with several integers that should
be used together in ordering, the space is wasted since sqlite saves the
table b-tree with the rowid + these integers and also index b-tree with the
integers and rowid reference. When such tables contains thousands or
millions records, it starts making sense. Using rowid in this case as a
packed value should help.

Ironically sqlite being db right before you with all the statistics
available encourages experimenting opposite to for example mysql looking
like a mountain far away )

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Jay A. Kreibich]

On Tue, Jul 20, 2010 at 04:43:26PM +0400, Max Vlasov scratched on the wall:
> Hi,
> as long as I see currently bitwise right does not use index

  Doing this requires recognizing when an inverse expression exists,
  and then computing it.  It is extremely difficult in the general
  case.  If you want to use an index, you need to refer to a direct value.

> But the first syntax is more straightforward.

  Having a proper column is even more straightforward.  It also makes
  this whole issue go away and makes your indexes work correctly.

> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally

  This breaks First Normal Form and is very un-relational.  If you want
  the database to do what it does best, use it properly.  Doing stuff
  like this is thinking like a C programmer, not a database programmer.

  In the case of SQLite, it is also very unlikely to save space.  In
  fact, because of the way integer values are stored, it is very
  likely to use more space.

  It also screams of premature optimization.  In most cases it won't
  save you much, if anything, and will cause all kinds of problems
  (like this!).  You also loose the ability to index anything other
  than the left-most field.  
  
  Finally, bit operations are not part of the SQL standard,
  making this kind of approach very non-portable.  Many databases
  don't even use binary integers to store natural-number values.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Horrendous slowdown when updating versions

[Max Vlasov]

On Tue, Jul 20, 2010 at 7:38 PM, Ed Hawke <
edward.ha...@hawkeyeinnovations.co.uk> wrote:

> I was running a system using an old sqlite3.dll (version 3.0.8, I believe).
>
> Updating to the latest version (3.6.23) causes my program to run
> incredibly slowly.
>
>
Can you provide more details about the query, maybe narrow it to some easily
understandable sql fragment

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

Re: [sqlite] how to get the number of rows in a a table?

[Timothy A. Sawyer]

Select count(*) from table

--Original Message--
From: dmsmsm
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: [sqlite]  how to get the number of rows in a a table?
Sent: Jul 20, 2010 09:41


how to get the number of rows in a a table? what is the function to get that?
Please add a sample code to achive that.
thanks
dmsmsm
-- 
View this message in context: 
http://old.nabble.com/how-to-get-the-number-of-rows-in-a-a-table--tp29216126p29216126.html
Sent from the SQLite mailing list archive at Nabble.com.

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


** Sent from my mobile device with the tiny keys **
Timothy A. Sawyer, CISSP
Managing Director
MBD Consulting, LLC
55 Madison Av., Suite 400 
Morristown, NJ 07960
Phone: (973) 285-3261 | Fax: (973) 538-0503
Web: http://www.mybowlingdiary.com
Email: tsaw...@mybowlingdiary.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the number of rows in a a table?

[P Kishor]

On Tue, Jul 20, 2010 at 11:41 AM, dmsmsm  wrote:
>
> how to get the number of rows in a a table? what is the function to get that?
> Please add a sample code to achive that.


SELECT Count(*) FROM table;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to get the number of rows in a a table?

[dmsmsm]

how to get the number of rows in a a table? what is the function to get that?
Please add a sample code to achive that.
thanks
dmsmsm
-- 
View this message in context: 
http://old.nabble.com/how-to-get-the-number-of-rows-in-a-a-table--tp29216126p29216126.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] ON conditions vs. WHERE conditions

[Jean-Christophe Deschamps]

> > What is the rationale about placing complex conditions in the ON part
> > of an inner join rather than in an WHERE clause?
>
>Except for outer joins, the difference is purely stylistic. They are 
>functionally equivalent. In fact, SQLite internally rewrites the 
>former to the latter, before generating the query plan.
>
>For outer joins (of which SQLite only supports LEFT JOIN), the 
>distinction is significant.

That was my impression and consistent with my real-world findings (wall 
clock made).  Just wanted to avoid a possible pitfall.

Outer is another beast.

Thanks Igor.

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

[sqlite] Horrendous slowdown when updating versions

[Ed Hawke]

Hi all,

I was running a system using an old sqlite3.dll (version 3.0.8, I believe).

Updating to the latest version (3.6.23) causes my program to run 
incredibly slowly.

As an example, selecting approximately 30,000 records was taking 10 
seconds with the old dll, and with the updated version takes 70 seconds.

Whereas 10 was just about acceptable, 70 is definitely not; can anyone 
think of a reason why this might be, and what steps I could take to 
rectify it?

Regards,

Ed

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

Re: [sqlite] ON conditions vs. WHERE conditions

[P Kishor]

On Tue, Jul 20, 2010 at 9:43 AM, Jean-Christophe Deschamps
 wrote:
> What is the rationale about placing complex conditions in the ON part
> of an inner join rather than in an WHERE clause?

My sense is that it is not so much about "complexity" but more about
the logic of the process. The ON conditions are a part of the
description of the "source table," which is really a virtual table
made up by JOINing several tables using certain conditions, the ON
conditions.

Once the virtual table is described, you tell what to get out of that
(the SELECT conditions), and tell how to restrict/filter that
information (the WHERE conditions).


>
> I understand that the ON clause will limit the temporary table
> processed by a subsequent WHERE clause, while a larger table will be
> filtered by the WHERE part if no selective ON clause is present.
> But with a complex condition (20+ sub-conditions on various part of
> joined tables) requiring full tables scan, aren't both solutions
> essentially equivalent?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON conditions vs. WHERE conditions

[Igor Tandetnik]

Jean-Christophe Deschamps  wrote:
> What is the rationale about placing complex conditions in the ON part
> of an inner join rather than in an WHERE clause?

Except for outer joins, the difference is purely stylistic. They are 
functionally equivalent. In fact, SQLite internally rewrites the former to the 
latter, before generating the query plan.

For outer joins (of which SQLite only supports LEFT JOIN), the distinction is 
significant.
-- 
Igor Tandetnik


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

[sqlite] ON conditions vs. WHERE conditions

[Jean-Christophe Deschamps]

What is the rationale about placing complex conditions in the ON part 
of an inner join rather than in an WHERE clause?

I understand that the ON clause will limit the temporary table 
processed by a subsequent WHERE clause, while a larger table will be 
filtered by the WHERE part if no selective ON clause is present.
But with a complex condition (20+ sub-conditions on various part of 
joined tables) requiring full tables scan, aren't both solutions 
essentially equivalent?

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Max Vlasov]

On Tue, Jul 20, 2010 at 5:14 PM, Pavel Ivanov  wrote:

> > Are there reasons not to implement optimization in the first case? Except
> > for this is not most requested one :)
>
> I guess because this case is highly specific and it's behavior should
> depend on particular constants used. Put there for example Id >> 54 =
> 1000 and now we should make optimizer guess right away that query will
> never return any rows.
>
>
Pavel, I thought about this a little more and I can see a little problem

For example, if we have an abstract function F, that we can guarantee:
- if a <=b  F(a)<=F(b)
- if a >=b  F(a)>=F(b)
we actually should perform a kind of range search, but less effective than
general range search. General range search knows what are the limits so
search only for them regardless of the rows to be found, but this query
should find any value and after that move left while F() is true and move
right while F() is true.

On the other size this kind of search will have either the same
effectiveness as a full scan (in worst case) or better. I suppose this
limitation is also why the queries with complex left parts (even WHERE id +
1 =) also does not use optimizer (CMIIW)

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

Re: [sqlite] error in sum function

[Max Vlasov]

SQLite Expert (which I use extensively in the Pro version) enforces
> displaying types as declared.  So if you declare your column as INTEGER
> (or INT, ...) then it will display integers regardless of the actual
> individual data type using common conversions.  This is a side effect
> of the Delphi grid component that Bogdan uses.
>
>
Although the topic is no longer active, the problem is interesting and I
thought about this recently
There's something sqlite-aware administration tools would implement. It's
not that hard when data retrieval is in progress to compare the type one
want to retrieve to the result of sqlite3_column_type function. Since some
lines of the conversion table listed at
http://www.sqlite.org/c3ref/column_blob.html will likely have lossy
conversions, it is always possible to detect such state and show a warning
about possible conflicts. If the user does not want it, ok, select "never
show again" and forget about it, but personally I'd never switch it off.

Sure it's not a problem for tools that always use sqlite3_bind_text for
display purpuses.

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Pavel Ivanov]

> Are there reasons not to implement optimization in the first case? Except
> for this is not most requested one :)

I guess because this case is highly specific and it's behavior should
depend on particular constants used. Put there for example Id >> 54 =
1000 and now we should make optimizer guess right away that query will
never return any rows.

> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally

Only for the field placed in the highest valued bits. Fields in lower
valued bits will have to use '&' operator which can't use index
anyway.


Pavel

On Tue, Jul 20, 2010 at 8:43 AM, Max Vlasov  wrote:
> Hi,
> as long as I see currently bitwise right does not use index
>
> CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT);
>
> EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id >> 12 = 1000;
> : TABLE TestTable
>
> Sure I can replace it with the following query
>
> EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id BETWEEN 1000 << 12 AND
> (1001 << 12)-1;
> : TABLE TestTable USING PRIMARY KEY
>
> But the first syntax is more straightforward.
> Are there reasons not to implement optimization in the first case? Except
> for this is not most requested one :)
> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally
>
> Thanks,
>
> Max Vlasov
> maxerist.net
> ___
> 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] suggestion: bitwise shift right optimization

[Max Vlasov]

Hi,
as long as I see currently bitwise right does not use index

CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT);

EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id >> 12 = 1000;
: TABLE TestTable

Sure I can replace it with the following query

EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id BETWEEN 1000 << 12 AND
(1001 << 12)-1;
: TABLE TestTable USING PRIMARY KEY

But the first syntax is more straightforward.
Are there reasons not to implement optimization in the first case? Except
for this is not most requested one :)
Btw, actually, sometimes when several small fields exists and they should be
indexed, one can pack them into id(rowid) to save space and the first syntax
will allow querying more naturally

Thanks,

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

Re: [sqlite] Problem writing vector to blob

[Andy Gibbs]

On Tuesday, July 20, 2010 12:37 PM, Robert McVicar wrote:


>I have some code which uses the sqlite3_blob_write() function, but I
> observe some odd behaviour.
> When I pass it a QVector (Qt vector class - similar to std::vector,
> contiguous memory) of a class C (for example) when the vector is of
> significant size (over 200 entries), towards the end of the blob, the
> data is just set to 0 - rather than the values in the vector members.
> Class C just consists of 6 floats and a short (as well as some functions
> in its definition).  The precise point where this happens in the blob
> seems to vary between machines.
> I have checked the sqlite limits and the blob handle is of the desired
> size.  I pass the function the value of constData() which returns a
> const pointer to the data in the vector.
>
> The call is as follows
> (body is a QVector,
> i_blobHandle is sqlite3_blob* where sqlite3_blob_bytes(i_blobHandle) ==
> ( length of vector * size of class C in bytes) )
>
> sqlite3_blob_write(i_blobHandle, (body.constData()), ( body.size() *
> SIZE_OF_C ), 0);
>
> If I immediately read the blob afterwards as follows
> QVector rmcv_d(body.size());
>
>  sqlite3_blob_read(i_blobHandle, (rmcv_d.constData()), (body.size()*
> SIZE_OF_C), 0);
>  for ( int rmcv_a(0); rmcv_a < rmcv_d.size(); ++rmcv_a )
>  {
>if( d != body[rmcv_a] )
>{
>  // GET HERE AROUND THE  rmcv_a == 230 mark (though this varies)
>}
>  }
>
> Has anybody seen this type of behaviour before?  Is it a known
> limitation/bug with BLOB writing?  If I write the items one at a time in
> a loop, there is no problem.  But I thought to save time, passing in the
> address of the vector would be enough - yet it behaves in this odd way.
>
> Any feedback appreciated.
>

I use the sqlite3_blob_read/write commands a lot.  I've found them to be 
generally stable for blobs of several tens of megabytes.  I see that you are 
reserving enough space for your blob, but do you check for any errors on 
your call to the write function?

I may be worth using the streaming operators to be sure of avoiding any 
quirks in the Qt library (see 
http://doc.trolltech.com/4.6/qvector.html#operator-lt-lt-75).  You can then 
do something along the lines of...


QBuffer b;
b.open(QBuffer::WriteOnly);
QDataStream s();
s << vector;
res = sqlite3_blob_write(blob, b.buffer().constData(), b.size(), 0);

... to write, and ...

QByteArray ba;
int data_size = sqlite3_blob_bytes(blob);
ba.reserve(data_size);
res = sqlite3_blob_read(blob, b.buffer().data(), data_size, 0);

QBuffer b();
b.open(QBuffer::ReadOnly);
QDataStream s();

QVector ret;
s >> ret;

... to read.  I haven't actually tested this code, but it follows along the 
general line of code that I use in my own software.

Hope this helps...

Andy


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

[sqlite] Problem writing vector to blob

[Robert McVicar]

I have some code which uses the sqlite3_blob_write() function, but I 
observe some odd behaviour.
When I pass it a QVector (Qt vector class - similar to std::vector, 
contiguous memory) of a class C (for example) when the vector is of 
significant size (over 200 entries), towards the end of the blob, the 
data is just set to 0 - rather than the values in the vector members.
Class C just consists of 6 floats and a short (as well as some functions 
in its definition).  The precise point where this happens in the blob 
seems to vary between machines.
I have checked the sqlite limits and the blob handle is of the desired 
size.  I pass the function the value of constData() which returns a 
const pointer to the data in the vector.

The call is as follows
(body is a QVector,
i_blobHandle is sqlite3_blob* where sqlite3_blob_bytes(i_blobHandle) == 
( length of vector * size of class C in bytes) )

sqlite3_blob_write(i_blobHandle, (body.constData()), ( body.size() * 
SIZE_OF_C ), 0);

If I immediately read the blob afterwards as follows
 QVector rmcv_d(body.size());

  sqlite3_blob_read(i_blobHandle, (rmcv_d.constData()), (body.size()* 
SIZE_OF_C), 0);
  for ( int rmcv_a(0); rmcv_a < rmcv_d.size(); ++rmcv_a )
  {
if( d != body[rmcv_a] )
{
  // GET HERE AROUND THE  rmcv_a == 230 mark (though this varies)
}
  }

Has anybody seen this type of behaviour before?  Is it a known 
limitation/bug with BLOB writing?  If I write the items one at a time in 
a loop, there is no problem.  But I thought to save time, passing in the 
address of the vector would be enough - yet it behaves in this odd way.

Any feedback appreciated.

R



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

Re: [sqlite] bug report: data race in multi-threaded execution ofsqlite3

[Pavel Ivanov]

> BTW, I found that the huge number of data race warnings can easily be
> removed. The found races are benign--it just assign same static mutex id
> whenever pthreadMutexAlloc() is called. by not assigning the mutex id once
> it is initialized -- as in MAKE_DRD_HAPPY --, most of race warnings are
> removed.

All race warnings in any tool detecting that should be considered as
warnings which have a very good probability of false positive. And in
this particular case it probably easy fixable but it makes worse
readable code without any benefit. Then why it should be done?
You can run your application with --tool=helgrind and see how many
complaints it will give for the code inside pthread_mutex_lock. And
all of them are false positives.


Pavel

On Mon, Jul 19, 2010 at 5:36 PM, heechul Yun  wrote:
> Yes, retry on SQLITE_SCHEMA solve the problem. Should the FAQ be updated?
>
> BTW, I found that the huge number of data race warnings can easily be
> removed. The found races are benign--it just assign same static mutex id
> whenever pthreadMutexAlloc() is called. by not assigning the mutex id once
> it is initialized -- as in MAKE_DRD_HAPPY --, most of race warnings are
> removed.
>
> static sqlite3_mutex *pthreadMutexAlloc(int iType){
>  static sqlite3_mutex staticMutexes[] = {
> ...
> ...
>    default: {
>      assert( iType-2 >= 0 );
>      assert( iType-2 < ArraySize(staticMutexes) );
>      p = [iType-2];
> #if MAKE_DRD_HAPPY
>      if ( p->id == 0 )  // first call
>      p->id = iType; // initialize
>      else
>      assert(p->id == iType);
> #else /* original */
>      p->id = iType;
> #endif
>
>      break;
>    }
>  }
>  return p;
> }
>
>
> On Mon, Jul 19, 2010 at 11:36 AM, Dave Toll  wrote:
>
>> Hi Heechul
>>
>> I have used this test code as well, and I found that you have to treat
>> SQLITE_SCHEMA the same way as SQLITE_LOCKED - you should retry the operation
>> that returned this error. I believe it indicates that the master table is
>> locked by another thread due to a DDL command (e.g. CREATE TABLE, DROP
>> TABLE). It does seem to contradict FAQ #15.
>>
>> Anyone else, please feel free to correct me if I'm wrong - I use code that
>> makes this assumption and I hope I haven't messed something up... I should
>> mention that I am using shared cache mode.
>>
>> Cheers,
>> Dave.
>>
>>
>> -Original Message-
>> From: Heechul [mailto:heechul@gmail.com]
>> Sent: Sunday, July 18, 2010 10:15 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] bug report: data race in multi-threaded execution
>> ofsqlite3
>>
>>
>> Hi,
>>
>> I think there are data races that cause problems when running
>> multi-threaded applications.
>>
>> I downloaded sqlite-3.6.23.1 source distribution and tested a
>> multi-threaded program found in test/threadtest1.c (slightly modified
>> due to obvious errors in original code) on my Intel Core2Quad running
>> ubutu 10.04 (2.6.32-23-generic #37-Ubuntu SMP)
>>
>> I compiled it as follows.
>>
>> $ gcc -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTHREADSAFE=1 -g -o
>> threadtest-bugreport -lpthread  threadtest-bugreport.c ../sqlite3.c
>>
>> The code simply create multiple threads each performs the following
>> sequences:
>>
>>  open db
>>  create table
>>  insert 100 entries
>>  select
>>  drop table
>>
>> For every two thread work on the same datafile file but work on
>> different table. For example, thread 1 and thread 2 open the same
>> "testdb-1". but thread 1 create "t1" table and thread 2 create "t2"
>> table.
>>
>> Example of correct run is as follows:
>>
>> $ ./threadtest-bugreport 10
>> 10 threads
>> 2.testdb-2: END
>> 1.testdb-5: END
>> 2.testdb-4: END
>> 1.testdb-3: END
>> 1.testdb-2: END
>> 1.testdb-4: END
>> 2.testdb-5: END
>> 1.testdb-1: END
>> 2.testdb-1: END
>> 2.testdb-3: END
>>
>> However, I got following intermittent errors
>>
>> $ ./threadtest-bugreport 10
>> 10 threads
>> 1.testdb-3: command failed: DROP TABLE t1; - database schema has changed
>> Exit with code 1
>>
>> All operations are performed using sqlite3_exec() API, Therefore,
>> according to FAQ (q.15), I should not see SQLITE_SCHEMA error at least.
>>
>> Then, I used valgrind data-race detector (valgrind --tools=drd) and
>> found lots of data races as follows:
>>
>> valgrind --tool=drd ./threadtest-bugreport  2
>> ==23995== drd, a thread error detector
>> ==23995== Copyright (C) 2006-2009, and GNU GPL'd, by Bart Van Assche.
>> ==23995== Using Valgrind-3.6.0.SVN-Debian and LibVEX; rerun with -h for
>> copyright info
>> ==23995== Command: ./threadtest-bugreport 2
>> ==23995==
>> 2 threads
>> ==23995== Thread 2:
>> ==23995== Conflicting store by thread 2 at 0x080c2058 size 4
>> ==23995==    at 0x804D3E3: pthreadMutexAlloc (sqlite3.c:15601)
>> ==23995==    by 0x804D270: sqlite3MutexAlloc (sqlite3.c:14918)
>> ==23995==    by 0x8052D06: unixEnterMutex (sqlite3.c:22329)
>> ==23995==    by 0x8054828: fillInUnixFile (sqlite3.c:25756)
>> ==23995==    by 0x805518B: unixOpen 

Re: [sqlite] bug report: data race in multi-threaded execution ofsqlite3

[heechul Yun]

Yes, retry on SQLITE_SCHEMA solve the problem. Should the FAQ be updated?

BTW, I found that the huge number of data race warnings can easily be
removed. The found races are benign--it just assign same static mutex id
whenever pthreadMutexAlloc() is called. by not assigning the mutex id once
it is initialized -- as in MAKE_DRD_HAPPY --, most of race warnings are
removed.

static sqlite3_mutex *pthreadMutexAlloc(int iType){
  static sqlite3_mutex staticMutexes[] = {
...
...
default: {
  assert( iType-2 >= 0 );
  assert( iType-2 < ArraySize(staticMutexes) );
  p = [iType-2];
#if MAKE_DRD_HAPPY
  if ( p->id == 0 )  // first call
  p->id = iType; // initialize
  else
  assert(p->id == iType);
#else /* original */
  p->id = iType;
#endif

  break;
}
  }
  return p;
}


On Mon, Jul 19, 2010 at 11:36 AM, Dave Toll  wrote:

> Hi Heechul
>
> I have used this test code as well, and I found that you have to treat
> SQLITE_SCHEMA the same way as SQLITE_LOCKED - you should retry the operation
> that returned this error. I believe it indicates that the master table is
> locked by another thread due to a DDL command (e.g. CREATE TABLE, DROP
> TABLE). It does seem to contradict FAQ #15.
>
> Anyone else, please feel free to correct me if I'm wrong - I use code that
> makes this assumption and I hope I haven't messed something up... I should
> mention that I am using shared cache mode.
>
> Cheers,
> Dave.
>
>
> -Original Message-
> From: Heechul [mailto:heechul@gmail.com]
> Sent: Sunday, July 18, 2010 10:15 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] bug report: data race in multi-threaded execution
> ofsqlite3
>
>
> Hi,
>
> I think there are data races that cause problems when running
> multi-threaded applications.
>
> I downloaded sqlite-3.6.23.1 source distribution and tested a
> multi-threaded program found in test/threadtest1.c (slightly modified
> due to obvious errors in original code) on my Intel Core2Quad running
> ubutu 10.04 (2.6.32-23-generic #37-Ubuntu SMP)
>
> I compiled it as follows.
>
> $ gcc -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTHREADSAFE=1 -g -o
> threadtest-bugreport -lpthread  threadtest-bugreport.c ../sqlite3.c
>
> The code simply create multiple threads each performs the following
> sequences:
>
>  open db
>  create table
>  insert 100 entries
>  select
>  drop table
>
> For every two thread work on the same datafile file but work on
> different table. For example, thread 1 and thread 2 open the same
> "testdb-1". but thread 1 create "t1" table and thread 2 create "t2"
> table.
>
> Example of correct run is as follows:
>
> $ ./threadtest-bugreport 10
> 10 threads
> 2.testdb-2: END
> 1.testdb-5: END
> 2.testdb-4: END
> 1.testdb-3: END
> 1.testdb-2: END
> 1.testdb-4: END
> 2.testdb-5: END
> 1.testdb-1: END
> 2.testdb-1: END
> 2.testdb-3: END
>
> However, I got following intermittent errors
>
> $ ./threadtest-bugreport 10
> 10 threads
> 1.testdb-3: command failed: DROP TABLE t1; - database schema has changed
> Exit with code 1
>
> All operations are performed using sqlite3_exec() API, Therefore,
> according to FAQ (q.15), I should not see SQLITE_SCHEMA error at least.
>
> Then, I used valgrind data-race detector (valgrind --tools=drd) and
> found lots of data races as follows:
>
> valgrind --tool=drd ./threadtest-bugreport  2
> ==23995== drd, a thread error detector
> ==23995== Copyright (C) 2006-2009, and GNU GPL'd, by Bart Van Assche.
> ==23995== Using Valgrind-3.6.0.SVN-Debian and LibVEX; rerun with -h for
> copyright info
> ==23995== Command: ./threadtest-bugreport 2
> ==23995==
> 2 threads
> ==23995== Thread 2:
> ==23995== Conflicting store by thread 2 at 0x080c2058 size 4
> ==23995==at 0x804D3E3: pthreadMutexAlloc (sqlite3.c:15601)
> ==23995==by 0x804D270: sqlite3MutexAlloc (sqlite3.c:14918)
> ==23995==by 0x8052D06: unixEnterMutex (sqlite3.c:22329)
> ==23995==by 0x8054828: fillInUnixFile (sqlite3.c:25756)
> ==23995==by 0x805518B: unixOpen (sqlite3.c:26272)
> ==23995==by 0x804CC3A: sqlite3OsOpen (sqlite3.c:12604)
> ==23995==by 0x805ADD6: sqlite3PagerOpen (sqlite3.c:35419)
> ==23995==by 0x805F668: sqlite3BtreeOpen (sqlite3.c:40349)
> ==23995==by 0x80B5D4E: sqlite3BtreeFactory (sqlite3.c:97729)
> ==23995==by 0x80B65EF: openDatabase (sqlite3.c:98123)
> ==23995==by 0x80B67D2: sqlite3_open (sqlite3.c:98237)
> ==23995==by 0x8049698: worker_bee (threadtest-bugreport.c:205)
> ==23995== Allocation context: BSS section of
> ul/Papers/cs523/dpthread/papps/sqlite/test/threadtest-bugreport
> ==23995== Other segment start (thread 3)
> ==23995==at 0x402D531: pthread_mutex_lock
> (drd_pthread_intercepts.c:580)
> ==23995==by 0x804D419: pthreadMutexEnter (sqlite3.c:15660)
> ==23995==by 0x804D2AE: sqlite3_mutex_enter (sqlite3.c:14936)
> ==23995==by 0x8052D0E: unixEnterMutex (sqlite3.c:22329)
> ==23995==by 0x8054CB0: findReusableFd (sqlite3.c:26010)
> ==23995==by 

Re: [sqlite] Getting declared datatype of a column in C

[i]

--
追求自由，崇尚简单~




 
 
 
-- Original --
From: "Jay A. Kreibich";
Date: 2010年7月7日(星期三) 下午4:41
To: "General Discussion of SQLite Database";
Subject: Re: [sqlite] Getting declared datatype of a column in C

 


On Tue, Jul 06, 2010 at 11:27:09PM +0100, Andrew Wood scratched on the wall:
> Ive been looking at the function
> 
> sqlite3_column_decltype
> 
> and am a little confused as to its use.
> 
> How can I find out the declared type of a column.

  If you just want to know the declared type of a column you already
  know about, it use "PRAGMA table_info".

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

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
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] C++ struct declarations

[Pavel Ivanov]

> Is there a working (with C++) sqlite3.h equivalent available
> somewhere, or do I need to hack on it?

A lot of people on this list including me use sqlite3.h in their C++
applications and don't see any problems compiling that as is. So you
should look at how you use that header and/or how you compile your
application. If you can narrow your non-compiling C++ file to some
short piece of code you can post it here and we'll look what's wrong
with it.


Pavel

On Tue, Jul 20, 2010 at 1:51 AM, Christopher Vance  wrote:
> I've just started playing with SQLite on MacOS, Linux and OpenBSD,
> with the intent of compiling on Windows too.
>
> Using C, I think I'm getting the hang of it. Things compile fine, and
> seem to work.
>
> I've just tried using sqlite3.h (from the 3.6.23.1 amalgamation) with
> C++ and find that it contains stuff which won't compile on C++ (I'm
> currently using Apple's g++ 4.0.1).
>
> My understanding is that declarations like
>
>   typedef struct sqlite3 sqlite3;
>
> are improper in C++, because the struct declaration already makes
> sqlite3 a typedef. The error message says
>
>  error: forward declaration of ‘struct sqlite3’
>
> Unfortunately, there are a bunch of these. Perhaps making the typedef
> name different from the struct tag is sufficient to resolve the
> problem, although I'm guessing the compiler thinks it needs to know
> not only that sqlite3 is a name for a type, but also what the contents
> of the struct are.
>
> Is there a working (with C++) sqlite3.h equivalent available
> somewhere, or do I need to hack on it?
>
> Of course, I may have missed something obvious...
>
> --
> Christopher Vance
> ___
> 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] "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON

[preeti.yadav1]

In my case, the DB is created by the older version of application and
now the new version of application is installed and End user has option
to see old data with selecting old version of DB so its completely on
end user wish to choose the location for DB. And also new tables in DB
are created to new version of application, on select of old DB,
application has to create those tables in OLD DB also to synch.

Application is successfully executing select query on UAC-ON however
while executing DDL and DML it is throwing "Unable to open Database".

Please guide us on this issue.  

Please let me know if it is not clear to you all.

Thanks,
Preeti
 
 



From: Preeti Yadav (WT01 - Healthcare and Services) 
Sent: Monday, July 19, 2010 1:47 PM
To: 'sqlite-users@sqlite.org'
Subject: "Unable to Open DB" on Win-7 & Vista (64 bitt) on UAC -ON


Hello Team,
 
I am using SQLite DB for  java/Swings based desktop application. We are
facing below issue on Win-7 & Vista (64 bit) Operating System only on
UAC -ON (User access Control).
 
Current version of application is unable to do any DDL/ DML on DB
Created by older version of application on  Win-7 & Vista (64 bit)
Operating Systems only on UAC -ON (User access Control). We found that
DB is throwing "Unable to Open DB".  Everything is working perfectly if
we set UAC to Never Notify.
 
We do not have any problems with other OS like Win-xp,  Mac, etc. 
 
We request you to please guide us on this issue.  
 
Please let me know if you need more information.
 
Thanks in advance for your kind help!!
 
Warm Regards,
Preeti
 

Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

[sqlite] [ANN] SQLiteManager 3.5

[Marco Bambini]

Viadana, Italy - SQLabs has announced SQLiteManager 3.5, the most powerful 
sqlite database manager tool for MacOS X and Windows. SQLiteManager is a 
powerful GUI database manager for sqlite databases. It combines an incredible 
easy to use interface with blazing speed and advanced features.

SQLiteManager allows you to open and work with sqlite 2, sqlite 3, in memory 
databases, AES 128 encrypted databases and with REAL Server databases. It 
allows you to create and browse tables, views, triggers and indexes. It enables 
you to insert, delete and updates records in a very intuitive way, it supports 
you arbitrary SQL commands and much more. Version 3.5 features a new powerful 
table editor with foreign key support and an advanced low level sqlite 3 
database analyzer plus a lot of other improvements.

What's new in this version:
* Added a new detailed analyzer feature
* Added Foreign Key support
* Added a Copy RecordSet new menu item
* Added Views to the Export dialog
* Brand new CREATE/ALTER table dialog
* Improved the importer engine
* Improved the exporter engine
* Improved display of BLOB images
* Improved NULL values handling
* Improved handling of table names that contains special characters
* Improved both the Manage and SQL panel
* Fixed a Win32 Edit Table menu issue
* Fixed a couple of minor Win32 related issues
* Fixed a case insensitive bug in inline editing
* Fixed a bug that occurs while updating a row that contains BLOB columns
* Fixed some issues related to BOOLEAN values
* Fixed an issue that occurs while editing values with an sqlite 2 database
* Fixed the incorrect Database Seems Encrypted message
* Fixed an issue related to the RSReport engine
* Fixed an issue related to saved SQL commands
* Updated sqlite to the latest 3.6.23.1 version
* Updated DoD ListBox to version 2.3
* Updated User's Manual and RBScript Manual
* Updated Language Reference
* Updated SQLite3ProfessionalPlugin to version 3.9
* Updated RSReport engine to version 2010.2.1
* A lot of other small fixes and optimizations

Some features include:
* SQLite2 and SQLite3 support
* REAL Server support
* In-Memory database support
* AES 128 encrypted SQLite 3 databases support
* Browse tables, views, and indexes
* Create new tables, views, indexes and triggers
* Create notes and script
* Drop tables, views, indexes, triggers
* Full alter tables support
* Manage tables by inserting, editing, and deleting records 
* Built-in inline editing
* Built-in virtual machine analyzer
* Built-in query optimizer
* Full-text search support
* Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records
* Save frequently used SQL commands for later use
* Convert SQLite 2 databases to SQLite 3
* Powerful reports generation with flexible report templates
* Advanced import and export capabilities
* Built-in language reference
* Embed notes and stickies in your databases
* Load native sqlite 3 extensions
* Script language support for automate repetitive tasks
* New record editor to easily modify/add rows
* Real-time BLOB preview
* SQL history
* Log window
* New CSV import engine that supports multi gigabity files
* Improved export engine
* New powerful print engine with real-time preview and PDF export
* Attach external database files
* Dump database files on disk
* Ability to open hidden files or bundles under OSX
* New table editor with full support for altering exiting tables
* New improved GUI
* New Chart panel to easily visualize your data (Line chart, Bar chart, Pie 
chart, Venn chart, Scatter, Radar, Map and QR Code)

Minimum requirements:
* MacOS X 10.4 or higher
* Windows 2000/NT/XP/Vista/7

A SQLiteManager single license is $49 USD (a $20 discount coupon code is 
available for SQLiteManager 2.x registered users). Company and multiplatform 
licenses are also available. For more information, please visit the SQLabs 
website at:
http://www.sqlabs.com/sqlitemanager.php
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/

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