Re: [sqlite] Deactivation of Manifest Typing
Simon de Hartog wrote: > SQLite has a feature called Manifest typing. As with many features of > software I run, I wonder whether this feature can be disabled. I prefer > to use static typing in my databases to prevent stored values not being > what my code (C++) expects them to be. So in short: is it possible to > use static typing instead of manifest typing in SQLite? If you access the database strictly through your software, this is trivially easy if you are using a strongly typed language like Pascal or C. Simply force the data to be of the appropriate type in your program and it will be stored as the appropriate type in your database. If you are allowing direct access to the database via SQL, you can probably assume that any user smart enough to write their own insert/update queries can probably also work out the correct data types. If not, you may wish to reconsider allowing direct SQL access. Clay ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best language match for SQLite?
For myself, I find C and C++ to be the best for use with SQLite (and the STL makes the set oriented nature of relational databases fit reasonably well). Mostly though that's because those are the languages I prefer to work in, rather than in inherent quality of the bindings. Like the man says, it's a TCL extension that escaped quarantine. Clay On Tue, 16 Sep 2008, Patrick wrote: > I am a beginner to intermediate Python Programmer. I can use SQLite with > it just fine but it is my understanding that relational database and > object oriented programming our not the perfect marriage. > > I was just wondering if anyone had an opinion on the most ideal language > to use with SQLite? > > I love Python but I LOVE SQLite, I would learn another language just to > use it better-Patrick > ___ > 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] Populating and scrolling the Listbox using query
Farzana wrote: > Dear All, > > We are working in eVC++ environment with SQLite database.We need to populate > the listbox with the values obtained by executing the query. > We were able to get the values of the query by using the API’s > sqlite3_prepare and sqlite3_step. > But we were able to populate and move the listbox in the downward direction > only and we couldn’t get the correct result when we click the upward > direction button of the listbox.Is there any API or functions available to > move the data in upward direction in the listbox? > > We happened to come across the functions of BTree such as > sqlite3BtreePrevious, sqlite3BtreeNext where we are suppose to use cursors. > Is it possible to execute the query using cursors and move the pointer in > the upward or downward direction? Farzana, Moving the list control has nothing to do with SQLite, that's a function of your interface code. I have a couple of solutions that I use for dealing with grids and list controls in general. The first is to maintain a shadow store behind the scenes with the data from my database records. In particular, I define a class to hold a record. My screen has a vector of these classes. So when the user makes changes to row 3 of the list control, behind the scenes I make those changes to item 3 of the vector. In fact I find this easiest to do by deriving a specialized list control that deals with this particular view on the data. Another approach is to record the primary key with each row of the list control. This works fine if you don't allow in-place editing, and the users will only be adding or deleting records (or their edits occur on a different screen). Clay ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Quick Review/Reference
Amit Uttamchandani wrote: >> I also purchased "Beginning Database Design - from Novice to >> Professional" by Clare Churcher, but found it to much of a beginners >> book for my needs. Let me recommend SQL For Smarties as an excellent starting point. The book is perfectly suitable for beginners, but can take you all the way to very advanced topics. Probably one of the best computer books I have purchased. This isn't an SQLite specific book, by the way. It's fairly generic, so what you learn here will work elsewhere. Clay Dowling http://www.lazarusid.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [C] Linker Error
Severin Müller wrote: > I tried to include sqlite3 in my current C Project. > > I downloaded the precompiled library sqlite-3.5.6.so and put it in my project. > Then, i downloaded the sqlite source and added sqlite3.h to my project. > > Now, when i try to compiler, i get the following error message: > > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to > `dlsym' > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to > `dlerror' > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to > `dlopen' > /home/fish-guts/workspace/Debug/lib/sqlite-3.5.6.so: undefined reference to > `dlclose' Rather than use the precompiled library, I recommend one of two options: 1. Download the latest version that is in your package manager and use that, if it is sufficiently recent. 2. Download the source and build it. It is a very easy to build package, and you would then have the option to build a static version if you wanted (the .a library) Clay -- CeaMuS, Simple Content Management http://www.ceamus.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I manually Lock a database?
Jerry Krinock wrote: > That's easy enough to detect with API from the OS, but a conflict can > still occur if they launch and start reading while I am in the middle > of writing something I don't want to stop. I need to lock the > database so that they get SQLITE_BUSY until I'm done. > > How can I manually lock the database using the C API? I can't find > any "lock" function. http://www.openbsd.org/cgi-bin/man.cgi?query=flock That only works if you're on a UNIX based OS, but it's the right trick to use there. Clay ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a IPC mechanism
Seems like it would work, but maybe overkill. What's stopping you from working out a fairly direct protocol to exchange data with? Sending key, type, val for all of your IPC seems reasonable. A mem-mapped file, a local socket or a network socket seem reasonable, depending on the structure of the system. Clay Joshua D. Boyd wrote: > I have a system that currently consists of 2 C programs and 3 python > programs. Currently the python programs transfer data between > themselves via pickles. The C programs transfer data between themselves > via streaming structs, and the C programs talk to one of the python > programs via a fairly ugly text over socket method. All of the programs > are threaded. > > Of the data being communicated, some of it must also be saved to disk, > and other pieces go away after a reset. All told there is only about 4 > k of stuff saved. > > I am wondering about using SQLite to communicate between the programs. > I'd use two databases. One on a flash disk for the data that needs to > be saved, and the other database would somehow be in a ram disk. Each > Db would have 1 table, and the fields would be key, type, val. Most > fields would only be written to by one or two sources, but would be read > from by nearly all processes. > > Is this a stupid use of SQLite? I can't quite seem to find anyone using > it like this. I am a little concerned about page locking as opposed to > row locking, but I think I can work around that. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] wxgrid fed with sqlite
I've fed editable wxGrids with several different data sources. The principle would be the same with SQLite as with any other. You get the most control by using the two-layer approach of deriving from wxGridTableBase. When editing moves to another row you write your updates/new record. Clay sqlfan wrote: > > has anyone fed an editable wxgrid with sqlite? > -- > View this message in context: > http://www.nabble.com/wxgrid-fed-with-sqlite-tp14790059p14790059.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
I have to say, this discussion has been very informative, although probably not in a way that would make mr Weick happy. I've certainly learned a lot about encirq that tells me what I need to know about doing business with them. Clay steveweick wrote: > > Good idea... I'll pass it along to the right folks. Meanwhile, if anyone > has > further questions or comments, please feel free to write me here (if they > think the group would be interested) or at [EMAIL PROTECTED] > > Steve > > I would like to recommend that Encriq create a forum or mailing list of > their own for those who are interesting in learning more. For me, what > might be an interesting product is quickly being overshadowed by this > thread. > > > -- > View this message in context: > http://www.nabble.com/DeviceSQL-tp14297970p14329799.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sample code
Tom Parke wrote: > Where can I find some sample C code for stepping thru a result set and > for binding variables to columns? I am just beginning to experiment > with Sqlite3 and I am having a hard time getting aquainted. http://www.lazarusid.com/sqlite3 >From a Linux Journal article several years ago, when SQLite 3 first came out. Clay -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite with C++
Andreas Volz wrote: > Am Sun, 25 Nov 2007 12:11:50 +0100 schrieb Andreas Volz: > >> Hello, >> >> I used this minimal example code to test my sqlite connection. This >> works without problems. But now I tried to move the 'db' variable into >> the private member section of my 'Cache' class to access it from >> various member functions. That's all. I would assume that this makes >> no difference. But my app crash at sqlite3_open() > > Seems as it was a strange memory bug here. Forget my question. Andreas, While there might be a strange memory bug going on, the bigger issue is that if you have a db member of the Cache class, you're declaring a local db variable that's masking the member. That could be leading to unexpected results later on if you're expecting to use it. Clay -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind. >(2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners >(4) http://sqlite.hwaci.com/v4/ CSS font specification only > > (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. > That leaves me with (4). > > I suppose we could go with (4) now and change it later To be honest I thought that they all looked pretty good. 1 was pretty nice and I rather liked 2 and 3. I don't know about IE6, but they looked fine in IE7. Clay -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] beginner
Nishit, Much as we believe in helping out new people, I think it is reasonable to expect that before you ask for helping in using the C interface to SQLite that you familiarize yourself with C. Looping and logic structures are something you should learn pretty early on, and if you haven't reached that point yet SQLite is going to be well beyond you. If you're truly this lost, let me recommend "The C Programming Language", which while basic, is also very good. Clay nishit sharma wrote: > thanks for this help. i have done this... > my another problem is that i have multiple columns in my database and on > the > basis of > two columns( has integer values) i have to read the database. > but i m unable to make a loop in C. > can u help me. > > regards > Nishit > > > On 9/3/07, Pavan <[EMAIL PROTECTED]> wrote: >> >> You should link the library when you compile .It should be gcc >> test.c-l >> library name should be your sqlite shared library. Just check in >> /usr/lib >> directory >> >> Thanks, >> Pavan. >> >> >> On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote: >> > >> > hey buddy can u tell me how to compile the C source code in which i >> have >> > used >> > sqlite3_open() like calls. >> > i m doing gcc test.c but it is giving me error of undefined reference. >> > i think i am missing some thing in compiling. >> > waiting for reply >> > >> > regards >> > Nishit >> > >> > >> > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote: >> > > >> > > thanks for telling the link. >> > > >> > > regards >> > > >> > > >> > > On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote: >> > > > >> > > > Hi Nishit, >> > > > >> > > > http://www.sqlite.org/quickstart.html >> > > > >> > > > This is a good link to start with. >> > > > >> > > > Thanks, >> > > > Pavan. >> > > > >> > > > >> > > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote: >> > > > > >> > > > > Hi, >> > > > > i m beginner to sqlite >> > > > > can anybody send me a link which can help me >> > > > > in building and maintining databse >> > > > > >> > > > >> > > > >> > > > >> > > > -- >> > > > ' >> > > > Always finish stronger than you start >> > > > * >> > > > >> > > >> > > >> > >> >> >> >> -- >> ' >> Always finish stronger than you start >> * >> > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Looking for a cryptographic library
There are two options: 1. D. Richard Hipp sells a version which encrypts the file on disk. This might be the fastest and easiest route. 2. OpenSSL offers as much encryption as you're likely to need if you're willing to deal with the paucity of documentation. Linux Journal had a couple of articles on using OpenSSL for encryption, although I don't know if they covered using OpenSSL from code. Clay [EMAIL PROTECTED] wrote: > Hi all: > > I'm writing an application that uses SQLite to store user's data, and need > a library to crypt some stuff, including passwords and data. The goal is > to crypt before insert and decript after extract tha data, so this last > can't be seen by others who gain access to the SQLite dataBase. The > application don't need military security level :-) > > I have been reading about Blowfish, but it seem that it encrypts data in > 8-byte blocks, and I suppose that it need pad the data to 8-byte round, > who might cause some headache. > > The ideal is some freeware library although commercial products can also > be considered. Of course the final product must be commercially > distributable without patent issues. > > Any advice in this matter would be grateful > > A.J.Millan > > > > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] WxDevCpp and Sqlite 3.4.1
If you want to use a DLL then you should download the DLL and the source package. Create an import library against the DLL with dlltool and put the resulting libsqlite.a into your libs folder. Copy sqlite3.h from the source distribution into your includes folder. If you'd rather be statically linked you'll need to compile the source from the preprocessed windows .zip source distribution into a static library. Not hard to do, just compile all the .c files except the source for the client (notable by containing the function main) into a static library. Perform the same file copying operations as you would for the DLL. Clay Massimiliano Marini wrote: > Hi all, > > Under linux I've no problems with g++ I've compiled sqlite 3.4.1 and > created > a makefile with the parameters > (include,library) where I compile the main.cpp and all is right. > > I want to compile the same file main.cpp under WxDevCpp but I don't know > how > setup sqlite 3.4.1. > > Under linux I've the library libsqlite3.so (generated by the compilation > of > sqlite) but under windows > I don't know how to setup WxDevCpp with sqlite 3.4.1. > > main.cpp is simple (shell) program that create the db file and populate it > with a simple insert. > > Any help, links, suggests are appreciated. > > -- > Massimiliano Marini - http://www.linuxtime.it/massimilianomarini/ > "It's easier to invent the future than to predict it." -- Alan Kay > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Security Problem C/C++
Check the result code of sqlite3_open and use the error message from
sqlite3_errmsg as the message in your exception. Then you'll know what's
wrong.
Clay
Severin Müller wrote:
> Hi
>
>
>
> Im trying to use SQLite3 for my File, but Im having trouble to use the
> library properly.
>
>
>
> I Have to following Code:
>
>
>
> void Nickserv::write_nickname(std::string nick,std::string
> pass,std::string
> email,User user)
>
> {
>
> #ifdef _WIN32
>
> const char *filename = "db\\Nickserv.db";
>
> #else
>
> const char *filename = "db/Nickserv.db";
>
> #endif
>
> sqlite3 *db;
>
> sqlite3_open(filename,);
>
> }
>
>
>
> When i run the Program, I get get the following Error:
>
>
>
> Unhandled exception
so its some kind of a segmentation fault.
>
>
>
> And my Debugger is tracing the error in a file called gs_support.
>
>
>
>
>
> Now, I really cant figure out, what the Problem is. Are certain
> Architectures not supported? Im Using Win32 on a Pentium 4.
>
>
>
> Thanks for your help in Advance.
>
>
>
>
>
> Greetings
>
>
>
>
> Severin Mueller
>
> Switzerland
>
>
>
>
--
Lazarus Registration
http://www.lazarusid.com/registration.shtml
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [Delphi] Escaping quote?
John Elrick wrote: >> A much better solution than QuotedStr is to use queries with parameters. >> If you're going to be running the query multiple times it also gives you >> a >> speed boost. >> > > True, however, that assumes you will be running the query multiple times > in a row, which I haven't experienced in our particular project. Even if you aren't running the query multiple times, the parametric query is a good idea. It avoids any possibility of SQL injection, due either to malicious users or programming mistakes. Clay -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Delphi] Escaping quote?
John Elrick wrote:
> // Input := 'Let's meet at the pub tonight!';
> MyFormat := 'insert into stuff (title) values (%s)';
> SQL := Format(MyFormat, QuotedStr(Input));
>
> try
> ASQLite3DB1.Database := db;
> ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
> ASQLite3DB1.Open;
>
> ASQLite3DB1.SQLite3_ExecSQL(SQL);
> ASQLite3DB1.Close;
> except
> ShowMessage('Bad');
> end;
A much better solution than QuotedStr is to use queries with parameters.
If you're going to be running the query multiple times it also gives you a
speed boost.
Clay
--
Simple Content Management
http://www.ceamus.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Recommend server for Windows?
Gilles Ganault wrote: > We'd really like to stick to SQLite because it's very easy to set up, and > most of our customers don't have anyone technical around to help them set > up a DBMS server. I'm going to recommend PostgreSQL. It's very easy to install from your application's installer and quite simple to administer. The supporting utilities are also of excellent quality, so that it's pretty simple to set up a shortcut that would let your customers do a backup. Clay Dowling -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why do you use SQLite? Comments for an article needed
Tim Anderson wrote: > I'm writing an article about SQLite and I'd love to get some comments > from users about why you use it. Performance? Features? Reliability? > Cost? Is the open source aspect important? Anything else? For that > matter, anything you really don't like about SQLite? > > You can email me at tim(at)itwriting.com or comment here if you prefer - > but to use your quote I'd need at least a full name, what you do and the > company you work for I'm using SQLite as an intermediate database in a shrinkwrap product. As part of a major version upgrade we're moving data from a Paradox database with little to no data normalization into a SQL Server database normalized to Boyce-Cod normal form. SQLite is used to keep track of primary keys in the new database to be used later in the process for maintaining data relations. SQLite was chosen for the following reasons: 1. Easy deployment. 2. Zero configuration. 3. Easy API to access from Delphi. 4. Licensing costs 5. Speed. Speed was the primary deciding factor, because these lookups must be fast. Clay Dowling -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Announcement: mod_auth_sqlite3
For all of you who have been wanting to use SQLite3 to manage authentication on your Apache web servers, I have just released mod_auth_sqlite3. You may download it from http://www.lazarusid.com/download/modauthsqlite3-1.0.tar.gz The module includes both a command line and a web utility for managing the password database. There will be enhacements to the web interface over the next month or so, but it is sufficient to manage users currently. Clay -- CeaMuS http://www.ceamus.com Simple Content Management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon example
Cesar Rodas wrote: > Here is Lemon tutorial. Shows how to make a calculator with a feature of > use Parents "()" in math expression. > http://www.cesarodas.com/2007/03/creating-basic-calculator-with-lemon.html. > > The author disclaims the copyright of the calculator. There's a problem with that URL. Could you check it, or check your server? I'd love to read the example, but my browser steadfastly refuses to resolve an address for cesarodas.com Clay Dowling -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite c++ interfaces
Pavan wrote: > Hi, > > I was googling for c++ interfaces for sqlite and found sqlitemm provides. > But, i am unable to download the code. Can some one pls point me to link > from where i can download the c++ interfaces for sqlite. You can try my wrapper at http://www.lazarusid.com/download/sqdataset.tar.gz It's testing, working, and generally makes my life easier. Clay -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Abuse of the SQLite website
[EMAIL PROTECTED] wrote: > But lately, there have been so many problems coming from > win98 and moz4 that I'm thinking of banning all traffic > that self-identifies as such in the User-Agent string of > the HTTP header. > > Thoughts anyone? Are there less drastic measures that might > be taken to prevent this kind of abuse? It seems unlikely that any legitimate client would be visiting with such characteristics. With SQLite being primarily of interest to developers, and win98 being not the most pleasant platform to develop on, I would think that the number of legitimate requests from these machines would be vanishingly small. Clay Dowling -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Limit statement size?
You have any way to normalize that original source table? It's full of extents, which is the first sign of badness in a table design and assured of complicating your life. Clay RB Smissaert wrote: > Yes, I agree it looks messy, but I used to do this in steps and after advice > I think from Igor Tandenik I lumped it all together and run it in one go, > which is a lot faster. > > RBS > > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: 28 January 2007 18:49 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > Wow! Talk about obfuscated code! I didn't even try to dig deeper than > a quick scan, but could this abomination be broken into multiple update > queries? On the surface it looks like each "group" is unique. If so, > wouldn't a transaction with multiple update statements be much more > efficient and a much lighter load on resources on a step by step basis? > I damn well know it would be much more pleasing to the eye! :-) > > Fred > >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Sunday, January 28, 2007 12:07 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Limit statement size? >> >> >> Is there any limit on the size of the SQL statements in SQLite? >> Didn't think this would come into play, but have now come across this >> query and wonder if this needs considering: >> >> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE >> FROM GROUP_2 T >> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT >> NUMERIC_VALUE FROM >> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT >> ENTRY_ID FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT >> READ_CODE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT >> TERM_TEXT FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT >> START_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT >> ADDED_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT >> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = >> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> READ_CODE_E5 = >> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> TERM_TEXT_E5 = >> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> START_DATE_E5 = >> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> ADDED_DATE_E5 = >> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE >> PATIENT_ID = >> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE >> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE >> FROM GROUP_7 T >> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID >> FROM GROUP_8 T >> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE >> FROM GROUP_8 T >> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT >> FROM GROUP_8 T >> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE >> FROM GROUP_8 T >> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE >> FROM GROUP_8 T >> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT >> NUMERIC_VALUE FROM >> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT >> ENTRY_ID FROM >> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9
Re: [sqlite] Using sqlite.exe
Sorry I didn't chime in before, but I would strongly recommend using Mike Cariotoglou's mksqlite import library, which is available in the contrib section of the sqlite website. It contains a direct import of the SQLite library into Delphi. I'm using it for a couple of projects right now and it's ideal. The API works identically to the C api, which is quite easy to follow. Mike has also written a Delphi object wrapper which could be of use, although I haven't investigated that. Easy enough to put your own object bindings around the library if you don't like what Mike has done. Contact me off list if you need some help learning the native API. I have a nice example that you can find at Linux Journal by searching on SQLite. Clay Dowling Michael Hooker wrote: > I have sent Ralf a long reply directly. > > No criticism was intended, and eventually I expect to be as impressed by > DiSQLite as I am by SQLiteSpy. It's just that I can't make it work yet and > don't have the time for the steep learning curve. > > Michael Hooker > > -Original Message- > From: Ralf Junker [mailto:[EMAIL PROTECTED] > Sent: 31 December 2006 10:12 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Using sqlite.exe > > > Hello Michael Hooker, > >> I shall try using a Delphi wrapper later on, DiSQLite3 will probably be the >> one I will choose, but so far I'm struggling to understand the examples >> because the author has chosen to use a maze of separate units and an >> unfamiliar set of external third party components to illustrate what it > does >> instead of just showing in a straightforward manner how to get data into a >> standard Delphi record structure, stringlist, string array or whatever. > > As the author of DISQLite3: The library now contains 17 example projects for > using SQLite with Delphi, even including full text search (FTS1 and FTS2). I > designed them to serve two purposes for both beginners and advanced users: > > * Explain basic and advanced usage of DISQLite3. > * Show the power of SQLite with semi real world applications. > > I agree that the examples use two sets of 3rd party components. I felt they > are necessary to overcome some of Delphi's limitations, most notably the > missing Unicode controls. Both packages (TNT Unicode Controls and > VirtualTrees) are freeware, highly recognized for their outstanding quality > and widely distributed. > > Thanks for letting me know that some aspect of DISQLite3 are apparently > still missing from the demos. Regarding record structures, TStringList and > string arrays: I did not cover these because of their potentially huge > memory requirements. Instead, I demonstrated an intelligent buffering > mechanism. > > However, given the need for it, I will be glad cover these in the demos as > well. What exactly do you want to achieve, and what kind of example project > are you looking for? > >> The documentation is very comprehensive but starts half-way through the >> film, as far as I'm concerned. > > Did you read the chapter labeled "Overview"? Again, if you let me know what > you are looking for I will be glad to add the missing information. > > Ralf > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.409 / Virus Database: 268.16.0/610 - Release Date: 30/12/2006 > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.409 / Virus Database: 268.16.0/610 - Release Date: 30/12/2006 > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- CeaMuS http://www.ceamus.com Simple Content Management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a database from inside a program
Open the database as per normal with sqlite3_open(). Then issue the SQL
commands necessary to create your schema. I have a nice little utility I
wrote which will take an SQLite schema dump and convert it to an array of
C strings that you can issue in sequence (and thanks to Microsoft for the
technique).
I've included the utility which converts an SQL export to C code. I'll
let you work out the details for the rest.
Clay Dowling
Rob Richardson said:
> Greetings!
>
>
>
> I need to be able to create a database with a known schema from inside a
> program. I used sqlite3.exe's .schema command to export the SQL needed
> to create the schema for my new database. I can create the database by
> the following steps:
>
>
>
> C:\: sqlite3 newdatabase.db
>
> Sqlite3> .read schemafile.txt
>
>
>
> At that point, my database is ready to go.
>
>
>
> But, sqlite3 is supposed to be able to accept commands on its command
> line. For example, the schema file was generated using this command:
>
>
>
> C:\: sqlite3 existingdatabase.db .schema > schemafile.txt
>
>
>
> So, I thought I could just issue the following command from inside my
> program:
>
>
>
> Sqlite3 newdatabase.db .read schemafile.txt
>
>
>
> But, when I issue this command from the DOS prompt, it gives me an
> error message saying that there is no command named "read". (Note the
> lack of a leading period.) If this won't work from a DOS prompt, I'm
> sure it won't work from my program. So, what is the recommended way to
> create a new database and its schema from inside a program? In case it
> matters, I'll be using Visual C# 2005 and the SQLite.net.dll file from
> SourceForge.
>
>
>
> Thank you very much!
>
>
>
> Rob Richardson
>
> RAD-CON INC.
>
>
--
Simple Content Management
http://www.ceamus.com/* This file (c) Copyright 2004, 2005, 2006 Lazarus Internet Development
*
* Permission is given to use this source code for personal or
* non-profit use free of charge, so long as this copyright is
* maintained. You may use this source code for commercial use
* so long as you have obtained a license from Lazarus Internet
* Development. A license may be purchased by writing to
* Clay Dowling <[EMAIL PROTECTED]>
*
* If you modify this source code and distribute it you must
* indicate such in this header, and provide all support for
* your modified version.
*
* $Id: sqlmodule.c 142 2006-05-28 17:43:28Z clay $
*/
/* Create a C module which will return an array of strings, one for each
* of the SQL statements in the passed file name
*
* This program runs on the SQL dumps/scripts produced by most SQL databases.
* It's only requirement is that each statement ends with a semicolon (';').
* This rules out SQL Server, which terminates a statement with GO on a line
* by itself. It should be a pretty easy adaptation though if such a feature
* is needed.
*
* Comments and empty lines are ignored.
* Lines with text are added to the array with a newline appended and
* any single or double quotes escaped.
* Lines which have a semi-colon cause a new array entry to be created
*/
#include
#include
#include
#include
#define LINE_SIZE 2048
void markcomment(char*);
const char* readline(FILE*);
void escapeline(char*);
void trim_trailing_space(char*);
int main(int argc, char** argv) {
FILE* in;
FILE* out;
char* filename;
char* base;
char* outfile;
const char* line;
if (argc == 1) {
fprintf(stderr, "usage: %s file.sql\n", argv[0]);
return EXIT_FAILURE;
}
in = fopen(argv[1], "r");
if (!in) {
perror(argv[1]);
return EXIT_FAILURE;
}
filename = (char*)calloc(1, strlen(argv[1]) + 1);
strcpy(filename, argv[1]);
base = strrchr(filename, '.');
if (base) *base = '\0';
outfile = (char*)calloc(1, strlen(filename) + 6);
snprintf(outfile, strlen(filename) + 6, "mod%s.c", filename);
out = fopen(outfile, "w");
if (!out) {
perror(outfile);
return EXIT_FAILURE;
}
fprintf(out, "/* auto-generated SQL module */\n\n");
fprintf(out, "char** getsql() {\n");
fprintf(out, " static char *sql[] = {\n");
while((line = readline(in))) {
if (strlen(line) > 0) {
fprintf(out, " \"%s\"", line);
if (strchr(line, ';')) fprintf(out, ",\n");
fprintf(out, "\n");
}
}
fprintf(out, " 0};\n\n");
fprintf(out, " return sql;\n");
fprintf(out, "}\n");
fclose(in);
fclose(out);
return EXIT_SUCCESS;
}
const char* readline(FILE* in) {
static char line[LINE_SIZE];
memset(line, 0, LINE_SIZE);
if (fgets(line, LINE_SIZE/2, in)) {
markcomment(line);
trim_trailing_space(line);
escapeline(line);
Re: [sqlite] Change in behavior from 2.x to 3.x
sqlite3_column_name would be favorite, assuming that the DBD provider uses the prepared statements API (which it should be doing). Clay Dowling Joe Casadonte said: > On Wed, 27 Dec 2006, Clay Dowling wrote: > >> If you use the SQLite API rather than shelling to the SQLite command >> line utility you may get more satisfactory results. The API between >> 2.x and 3.x is quite different, but the column headers are readily >> available. > > I actually need this capability in the DBD::SQLite Perl module; I was > using the command line tool as an example. What can I look for in the > API, to point the DBD::SQLite maintainer at? Thanks for the help! > > -- > Regards, > > > joe > Joe Casadonte > [EMAIL PROTECTED] > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
If you use the SQLite API rather than shelling to the SQLite command line utility you may get more satisfactory results. The API between 2.x and 3.x is quite different, but the column headers are readily available. Clay Dowling Joe Casadonte said: > > When querying multiple tables I was relying on SQLite to return the > column names with the table name/designator prepended to it. The > following works in 2.x but not in 3.x: > > > SQLite version 2.8.17 > Enter ".help" for instructions > sqlite> .header on > sqlite> SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE > E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, > E.EditionID; > E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|P.Name > 130|126|Roads & Boats|46||Splotter Spellen > > > SQLite version 3.3.3 > Enter ".help" for instructions > sqlite> .header on > sqlite> SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE > E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, > E.EditionID; > EditionID|GameID|Name|PublisherID|Own|Name > 130|126|Roads & Boats|46||Splotter Spellen > > > This is reflected in my Perl program, where my scripts are now broken > after upgrading to a new version of SQLite, as I am looking for data > in E.Name and P.Name, and finding neither (in fact, I have no value > for Name returned at all). Is there any way to get the old behavior > back? Is there some other work-around? > > -- > Regards, > > > joe > Joe Casadonte > [EMAIL PROTECTED] > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] C++ SQLite
RB Smissaert said: > You might be right, but with the Win32 API you have loads of nice > documents/programs (I like the API guide from KPD) that help you out. > All I have to do is copy their declares straight to VB and look at the > data > types I have to provide. Is the same available for the SQLite API? > http://www.sqlite.org/capi3ref.html That's the page you want. It declares everything you need. In the amount of time that you've spent asking for an import library on this list you could have written all of the imports. The sqlite3* and sqlite3_stmt* types are just pointers, so you can declare them as such. Everything else is a fundamental type. Now you're good to go. Combine that with my article in a previous message about how to use the API and you're on the same footing as the C programmers who use the library. Clay Dowling > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: 28 November 2006 18:43 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] C++ SQLite > > If you can use the Win32 API you can use the Sqlite API. Just because > they can be called from C programs does not make them "all to do with C". > > RB Smissaert wrote: >> Will have a look, but I was looking for a text geared towards VB. I take > it >> the documentation that comes with SQLite is all to do with C. >> In fact I already have a wrapper that seems to work well, the one from >> TerraInformatica, but maybe there was more control if I could write my > own. >> >> RBS >> >> -Original Message- >> From: Clay Dowling [mailto:[EMAIL PROTECTED] >> Sent: 28 November 2006 18:19 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] C++ SQLite >> >> >> [EMAIL PROTECTED] said: >> >>>Thanks. Would you know any VB source code that wraps all the possible >>>declares in a class? Or do you know where to find the documentation >>>to make all the possible declares in VB/VBA? >>> >>>RBS >> >> >> The SQLite documentation will give you everything you need to write the >> wrapper. The sqlite.h file in the source bundle would also be a great >> help. You may also be able to find a wrapper already written linked on >> the SQLite web site. >> >> Clay Dowling >> >> >>> >>>>Yes. It's a regular windows DLL, so it will behave like all other >>>>Windows >>>>DLLs. >>>> >>>>Clay Dowling >>>> >>>>[EMAIL PROTECTED] said: >>>> >>>>>Can I call the SQLite API (as in the dll SQLite.dll) directly from >>>>>VB or do I need the wrapper? So, could it work from VB with declares >>>>>as I use for the Windows API? >>>>> >>>>>RBS >>>>> >>>>> >>>>>>sebcity wrote: >>>>>> >>>>>>>How would one go about using c++ (Visual Studio.NET) to call and >>>>>>>display >>>>>>>SQLite tables. C++ wrappers? >>>>>> >>>>>>You should be able to call the Sqlite3 API directly. >>>>>> >>>>>> >> >> > >> - >> >>>>>>To unsubscribe, send email to [EMAIL PROTECTED] >>>>>> >> >> > >> - >> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> >> > >> - >> >>>>>To unsubscribe, send email to [EMAIL PROTECTED] >>>>> >> >> > >> - >> >>>>> >>>> >>>>-- >>>>Simple Content Management >>>>http://www.ceamus.com >>>> >>>> >>>> >> >> > >> - >> >>>>To unsubscribe, send email to [EMAIL PROTECTED] >>>> >> >> > >> - >> >>>> >>>> >>> >>> >>> >>> >> > >> - >> >>>To unsubscribe, send email to [EMAIL PROTECTED] >>> >> >> > >> - >> >>> >> >> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C++ SQLite
Yes. It's a regular windows DLL, so it will behave like all other Windows DLLs. Clay Dowling [EMAIL PROTECTED] said: > Can I call the SQLite API (as in the dll SQLite.dll) directly from > VB or do I need the wrapper? So, could it work from VB with declares > as I use for the Windows API? > > RBS > >> sebcity wrote: >>> How would one go about using c++ (Visual Studio.NET) to call and >>> display >>> SQLite tables. C++ wrappers? >> You should be able to call the Sqlite3 API directly. >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C++ SQLite
sebcity said: > > Thanks, Could you supply an example? > http://www.linuxjournal.com/article/7803 Clay -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C++ SQLite
sebcity wrote: > How would one go about using c++ (Visual Studio.NET) to call and display > SQLite tables. C++ wrappers? You could just use the API directly. Myself, I've put a wrapper around it, but there's nothing saying that you have to. Clay -- CeaMuS http://www.ceamus.com Simple Content Management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Locked
Lloyd, You need at some point to call the sqlite3_finalize function. I don't use wxSqlite, but if it's not handled by the destructor of the recordset then there should be a method, possibly called finalize or close, that needs to be called. I recommend consulting the documentation for wxSQLite since most of the wxWidgets components are pretty well documented. Clay Dowling Lloyd said: > I have tracked down the problem and found th following. > > I have a select statement which stores the result set in a pointer > variable. If the result set of select statement is empty, the program > executes, otherwise it gives a Database Locked error. > > How can I solve this problem ? > > Thanks again, > Lloyd. > > > > On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote: >> Hi, >> I have opened a database, created tables and inserted data to it in a >> single transaction. Then I have closed the data base. Then I reopened >> the database and tried to update the table entries. But it throws an >> exception called Database Locked. Where can be the mistake? I am using >> wxSqlite3 wrapper class. All these are performed from a single program. >> >> Thanks, >> Lloyd. >> >> >> __ >> Scanned and protected by Email scanner >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - > > > __ > Scanned and protected by Email scanner > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] so many warnings on vc6 with warning level 4
First, I use the pre-built DLL from the SQLite web site, and then use my compiler's library import utility to generate the appropriate import lib. That saves me all the warnings. Second, examine the actual warnings, not just the count. You'll find that a large number of them are warnings about deprecated library methods such as strcpy. The fact that Microsoft has chosen to deprecate standard library functions is a Microsoft issue rather than an SQLite issue. It's true that a lot of these functions are dangerous in inexperienced and careless hands. It's also true that they're very safe and have been used successfully for 30 years by programmers who understand their limitations and use them appropriately. Examine the warnings, look at the code around them, see if there is any merit to the warnings or if it's more of a chicken little situation. If you can't check all 600, spot check several which you find at random. And remember that there's nothing wrong with strcpy and strcat, as long as the destination buffer is large enough for the string being received. Look for the allocation of that buffer and see how it's come about and how the size is checked. Clay Dowling Gunnar Roth said: > Hello, > i want to use sqlite3 in a sub-project at work. > when compiling with warning level 4 with vc6 ( internal philosophy ) , i > get over 480 warnings. With warning level 3 there are still 119 left. > This leads to problems to convince the project lead of the quality of > sqlite3 ( which i do not doubt, as i have used it for my private project > since some years). > Is there any chance this warnings will be fixed sometime in the future? > How are others dealing with this problem at work? > > regards, > gunnar > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and McAfee Anti-Virus
[EMAIL PROTECTED] said: > Now, I just have to figure out how to get Mcafee to upgrade > to the latest version of SQLite and recompile I think you need to contact this fellow: David Juche Development Manager McAfee Master Installer/Download Manager I don't have an email address, but you can probably look it up, or even call the company switchboard and ask for him. If you tell him who you and what you do he's probably going to listen. Clay Dowling -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and McAfee Anti-Virus
Actually, just by following the links on your web site I was able to find a forum post from a McAfee employee suggesting that they were going to change the naming of the temp files, or were at least considering it. Check the last message in the forum posting that you have linked from the wiki page about the McAfeeBug. One of the chief problems that you're running into is that people in general can't or won't troubleshoot problems to determine a root cause (I'm certainly guilty of this). Additionally, the McAfee phone support people don't seem to be aware that their program is generating these files, probably because any poor soul stuck on phone support is fairly new to the company and the product. Phone support in any company has a high turnover. Clay Dowling [EMAIL PROTECTED] said: > I need advice from the community. The problem > is seen here: > > http://www.sqlite.org/cvstrac/tktview?tn=2049 > http://www.sqlite.org/cvstrac/tktview?tn=1989 > http://www.sqlite.org/cvstrac/tktview?tn=1841 > http://www.sqlite.org/cvstrac/wiki?p=McafeeProblem > > It appears that McAfee Anti-Virus uses SQLite internally, > and it leaves lots of files in C:/TEMP that contain > SQLite in their names. This annoys many windows users. > They get on Google and search around for "sqlite" and > find me. Then they send me private email or call me at > my office or on my cellphone to complain. Many refuse > to believe that I have nothing to do with the problem > and I am accused of spreading a virus or malware. > > My efforts to contact Mcafee about this problem have > been unfruitful. > > Does anybody have an suggestions on how I might deal > with this? Does anybody know how I can get in touch > with an engineer at Mcafee so that we can at least > change the names of the files in future releases? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] any data access and retrieval engine?
Sarah wrote: > Hi,all > > First of all, I want to thank all the guys on this mailing list for their > warm help. > > After 1 more month of work, I finally make SQLite work on my embedded > environment. SQLite is really great! Many thanks,Drh. > > But, due to the extremely heavy hardware constraints, I have to give up > SQLite finally. > > So I'm trying to find a much simpler data access and retrieval engine. > > Could anyone give me some help on this issue?(some guidance on how to make a > DARE or is there any open-source one available?) > > thanks in advance. The Berkeley DB engine and it's related engines might be suitable for your situation. They don't give relational access, but they do give fast key=>value retreival and that might be suitable. The SleepyCat DB engine from SleepyCat Software is probably the best, but for a commercial application the licensing fees mean that you have to be well funded and expect a good return on the product. Clay Dowling -- CeaMuS, Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] New Email notification program
I've added another email notification program to the growing number of available programs. You can pick it up from http://www.lazarusid.com/download/cvstrac-notify-0.5.tar.gz Currently the program is fairly similar to the Python script provided on the Wiki. I don't have Python on my machine though, and I have a strong bias in favor of C. The plan for this program, when it reaches 1.0, is to make it more sensitive to the type of change that has been made, so that a different message is received for changes of ownership, status, and remarks. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can anyone recommend some ISAM db to me?
Sarah wrote: > Hi, all > After trying SQLite on my embedded platform, I feel that it's a little too > complicated and time-consuming to my platform, especially the parsing. > So, could someone recommend several ISAM ones to me?(I'm a newbie of > database*^_^*) I can recommend Berkeley DB from Sleepycat Software. It's very high quality, however it's not cheap to license for a commercially sold application, and it doesn't pretend to have any relational features. If you're just looking for a way to read key-value pairs from a database, I have a nice little library I could share with you which sits on top of an SQLite database. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Anyone can help me?
I think first it would be helpful if you had a working pagerank algorithm. Data storage is not really what makes Google's pagerank and search engines so impressive, it's the searching and categorization capabilities. Clay Cesar David Rodas Maldonado said: > Some one what to help? > > On 9/26/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote: >> >> I am developing a hobby that is how to calculate something like the >> PageRank with SQLite for HDD Storage. >> >> Thanks to all. >> > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite under linux
./configure, especially since I build on more platforms than are supported by default. OpenBSD and FreeBSD are in my mix. Clay Martin Jenkins said: > Clay Dowling wrote: >> I don't have tcl installed on my systems and I've never had a problem >> doing the default build. I do skip "make test" because I don't have tcl >> installed, and because I don't modify my copies of SQLite. > > Do you use Makefile.linux or go the ./configure route? > > Martin > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite under linux
I don't have tcl installed on my systems and I've never had a problem doing the default build. I do skip "make test" because I don't have tcl installed, and because I don't modify my copies of SQLite. Clay Martin Jenkins said: > Clay Dowling wrote: >> What I strongly recommend doing, rather than trying to cobble together >> your own solution, is to download the source and go through the build >> and >> install -exactly- as decribed in the document. > > Good point, but I don't think he has (or wants) Tcl, so the build isn't > going to work. Also, there seems to be a make install missing from those > instructions so I'm not convinced it would work even if he did have Tcl. > > Martin > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite vs MS Access
What level of access is needed from outside of the application? There are nice GUIs available for SQLite that people could use. There is in theory ODBC access as well, although my experience with ODBC and SQlite was not especially happy. Clay Allan, Mark said: > Hi, > > After successfully using SQLite on an embedded device, we are now thinking > of using SQLite in a PC application. > > This would be used in place of an MS Access database on a local/network > disk. I believe that SQLite should be quicker for both transactions and > queries than Access. The one draw back that comes to mind maybe > portability (i.e. accessing data outside of the application), although the > data would be portable across machines (PC, Mac, Unix, etc) should we ever > need it to be in the future. > > Is there any webpage, or does anyone have any information comparing the > benefits of the two. I can only find comparisons between MySQL and > PostgreSQL. This information would aid us greatly in deciding whether to > use SQLite or stick with Access. > > Any help/advice will be gratefully received. > > Mark > > > DISCLAIMER: > This information and any attachments contained in this email message is > intended only for the use of the individual or entity to which it is > addressed and may contain information that is privileged, confidential, > and exempt from disclosure under applicable law. If the reader of this > message is not the intended recipient, or the employee or agent > responsible for delivering the message to the intended recipient, you are > hereby notified that any dissemination, distribution, forwarding, or > copying of this communication is strictly prohibited. If you have > received this communication in error, please notify the sender immediately > by return email, and delete the original message immediately. > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] problem with auto increment of ROWID
It's great to design your application to be future proof and all, but I
think Dr. Hipp has a point: that failure point probably isn't in your
lifetime, or the lifetime of 32 bit computing.
Do you have a particular reason for needing this behavior, other than your
own desires? If choosing non-sequential rowid values is fine for some
point down the road in the unforseeable future, why isn't it fine for now?
Clay Dowling
Dixon Hutchinson said:
> But "AUTOINCREMENT" has slightly different behavior that what I desire.
> The difference is what happens when the ROWID reaches the "largest
> possible integer". If AUTOINCREMENT is specified, then the next insert
> after "largest possible integer" is reach will fail, regardless of the
> availability of empty rows that resulted from deletes. The behavior I
> desire is that after "largest possible integer" is reach, "/the database
> engine starts picking candidate ROWIDs at random until it finds one that
> is not previously used/".
>
> Mario Frasca wrote:
>> Dixon Hutchinson wrote:
>>
>>>H:\b>sqlite3.exe t.dat
>>>SQLite version 3.3.7
>>>Enter ".help" for instructions
>>>sqlite> CREATE TABLE abc
>>> ...> (
>>> ...> c TEXT,
>>> ...> p INTEGER,
>>> ...> t TEXT,
>>> ...> masked INTEGER PRIMARY KEY,
>>> ...> UNIQUE(p,c)
>>> ...> );
>>> [...]
>>>
>>> Notice I still have elements 1,2 and 3 in the end where I want to
>>> have elements 1, 2 and 4.
>>
>> which is the reason why sqlite has autoincrement...
>>
>> [EMAIL PROTECTED]:~$ /usr/bin/sqlite3
>> SQLite version 3.3.4
>> Enter ".help" for instructions
>> sqlite> CREATE TABLE abc ( c TEXT,p INTEGER, t TEXT, masked INTEGER
>> PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
>> sqlite> SELECT * FROM abc;
>> t1|24|t2|1
>> t3|25|t4|2
>> t5|26|t6|3
>> sqlite> DELETE FROM abc WHERE ROWID='3';
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
>> sqlite> SELECT * FROM abc;
>> t1|24|t2|1
>> t3|25|t4|2
>> t5|26|t8|4
>> sqlite>
>>
>> works also if you write "rowid" instead of "masked"
>>
>> -
>>
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>
>
--
Simple Content Management
http://www.ceamus.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] problem with auto increment of ROWID
ROWID is a reserved word. Each row has one and you don't need to specify
it. In fact you probably shouldn't, since it seems to be causing you
problems.
If you want the column to be explicitly declared in your table, call it
something else like id. If you don't want to do that just use the keyword
ROWID in your queries, without declaring it as a column, and everything
wil be fine.
Clay
Dixon Hutchinson said:
> I am having a problem with default behavior of ROWID not
> auto-incrementing.
> I use the following to create my table:
>
> CREATE TABLE abc
> {
> c TEXT,
> p INTEGER,
> t TEXT,
> ROWID INTEGER PRIMARY KEY,
> UNIQUE(p,c)
> );
>
> When I insert into the table using just c, p and t, I see that ROWID is
> assigned auto-incrementing values, as I would expect. If I delete an
> entry (and in my test it was the last entry made), and then reinsert the
> same c and p with a different t, I notice that ROWID gets the value of
> the row I just deleted. I was hoping to get the behavior specified at
> http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is
> not used. I have other elements in the table, but I left them out for
> this illustration. I want to be able to do relatively quick lookups of
> data by either p, c, or ROWID. But I need ROWID to auto increment until
> the largest 64-bit integer value is used and then follow the random
> search algorithm described in the previously mention web page. I
> suspect my problem is that specifying "UNIQUE(p,c)" has obviated the
> table's need for a unique ROWID.
>
> I'm looking for a recommendation on how to specify the table such that
> ROWID gets the desired behavior and I can still do quick lookups by
> ROWID, p, or c.
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
--
Simple Content Management
http://www.ceamus.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] running a script?
John Salerno said: > I'm asking about an actual file, though, not just a single query. I've > tried something like what you suggest with the file path, but it > doesn't work. > sqlite database.db < script.sql > > > On 8/23/06, Scott Baker <[EMAIL PROTECTED]> wrote: >> echo "SELECT * FROM Table" | sqlite database.bin >> >> John Salerno wrote: >> > Hi everyone. Can someone tell me the proper syntax for running a sql >> > script when starting up sqlite from the command line interface? >> > >> > Thanks, >> > John >> > >> > - >> > >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > - >> > >> > >> > >> > >> >> -- >> Scott Baker - RHCE >> Canby Telcom System Administrator >> 503.266.8253 >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] libsqlite3.so
That's the right file. It might be helpful to know what platform you're building on, and if that platform supports shared libraries. Shared libraries are usually built by default on platforms that support them. As for the source to sqlite3_get_table, it will be visible in your source. You will probably have to go through the configure process first, because a lot of the source files aren't built until after the configure process. Clay Dowling Laura Longo said: > Hi all, > I would like to view the source code of libsqlite3.so because of a problem > with sqlite3_get_table function... I have a question: in the file > sqlite-3.3.7.tar.gz that I find at http://www.sqlite.org/download.html, > are > there also the source code of the library? If the answer is no, where can > I > find them? Instead, if the answer is yes, what is the option of the > 'configure' command to build the shared library? (I've tried with > './configure --enable-shared' but without any result... I only build the > binary sqlite3) > > Laura > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Building on SCO Open Server
Installing gcc is an excellent idea, but I would not be entirely surprised to learn that the actual failure is in sed. SCO and awk/sed do not have a happy history, with incompatible changes having been introduced at times. Clay Steve Baldwin said: > Hi, > > I'm trying to build SQLite 3.3.7 on SCO Open Server 5.0.5. So far, I've > installed gnu make (which was reasonably painless) to get around the > limitations of the default SCO make. We don't have tcl installed, so I > ran > configure with the -disable-tcl option. I didn't get any warnings from > configure. > > When I try the build, I get this ... > > $ make > sed -e s/--VERS--/3.3.7/ ../src/sqlite.h.in | \ > sed -e s/--VERSION-NUMBER--/3003007/ >sqlite3.h > cc -g -belf -o lemon ../tool/lemon.c > cp ../tool/lempar.c . > cp ../src/parse.y . > ./lemon -DSQLITE_OMIT_CURSOR parse.y > mv parse.h parse.h.temp > awk -f ../addopcodes.awk parse.h.temp >parse.h > cat parse.h ../src/vdbe.c | nawk -f ../mkopcodeh.awk >opcodes.h > ./libtool --mode=compile cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. > -I../src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 > -DSQLITE_OMIT_CURSOR -c ../src/alter.c > mkdir .libs > cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../src -DNDEBUG > -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c > ../src/alter.c -Kpic -DPIC -o .libs/alter.o > "./sqlite3.h", line 88: error: invalid type combination > "./sqlite3.h", line 89: error: invalid type combination > make: *** [alter.lo] Error 1 > > Here are the lines the compiler is barfing on ... > > [EMAIL PROTECTED] W]$ head -89 sqlite3.h | tail -2 > typedef long long int sqlite_int64; > typedef unsigned long long int sqlite_uint64; > > Is this doable with the standard SCO C compiler or am I banging my head > against a brick wall? > > Thanks for any suggestions. > > Steve > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] linking C program
Michael Alperovitch wrote: Hi, I am new SQLite user. How I can link the simple C Program with Sqlite static or shared library. I tried to link to libsqlite3.a and it cannot find sqlite3_open(), sqllite3_close(), sqllite3_exec() functions. I got the same result linking with libsqlite3.so shared library. Locate your sqlite library path. To the compiler command line for the link phase add the following: -L/path/to/lib -lsqlite3 That should do it just fine. If it doesn't, it's possible that you have stored the library outside of your system's known library folders. If they are in the library folders, try rerunning ldconfig, which should then make them visible when running the program. Clay Dowling -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert delay!
Cesar David Rodas Maldonado wrote: I was thinking a lot in the next month in how can I do the delay insert and I found something for do that, is basic because I don't have a lot of knowledge, I'm just start the University. OK, I need with SQLite to select all the time as possible, and delay the Insert, I don't care the time that took insert something. These is my needs with SQLite. Is something Interesting on my idea? If there is one I will share my idea. If you're trying to save your inserts until your app has some idle time, the best technique I can come up with is to cache those inserts in your application. When your activity monitoring thread detects that there is no other database activity it can fire off a process to perform the inserts. That keeps your app very responsive to user queries. If you find that you need a higher degree of concurrency you might consider PostgreSQL. But you'll probably be okay with SQLite as long as your app can find the time to run the inserts often enough. Clay -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] outputting select in cpp file?
Clay Dowling wrote: > 1. Use the sqlite3 interface, not sqlite 2. > > 2. Store a 0 or a 1 in the field. Then you can get the value with > sqlite3_column_int > > There's a more complicated answer as well, but I wasn't able to get the > code assembled before it was time to turn in last night. > > Clay Dowling My proposed "more complicated answer" can be downloaded from http://www.lazarusid.com/download/dbconfig.zip It's shockingly complete, with documentation and unit testing even. If my boss ever finds out that I'm doing stuff like that there's gonna be trouble Clay -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] outputting select in cpp file?
Keiichi McGuire wrote: > This sounds like a very simple problem, but I cannot figure it out! > > Basically what I have is this settings table that has a boolean data > type, and I want to check it via a cpp program. > > sqlite_exec(db,"select flag from setting",0,0,); > > and I want to be able to return a 0, or a 1 according to what I put into > the flag entry. 1. Use the sqlite3 interface, not sqlite 2. 2. Store a 0 or a 1 in the field. Then you can get the value with sqlite3_column_int There's a more complicated answer as well, but I wasn't able to get the code assembled before it was time to turn in last night. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] A littel question...
Cesar David Rodas Maldonado said: > Hello to everybody > > If I have a table with 100.000 unique words I am wondering if SQLite > select > if faster an cheaper (RAM, Processor, etc), or If i have to help SQLite > using a Hash function, and what could be that Hash function? If you're going to the trouble of building your own hash table, why bother with SQLite? The Hash table will provide faster access to the data, assuming that you load the entire list of words into RAM, but will be more memory intensive than using SQLite. This is assuming that the SQLite database lives on disk. If the SQLite database lives in memory, it's still going to take less RAM (btrees are almost always more compact than hash tables), but the speed will depend on the efficiency of your hash implementation. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] What wrapper for SQLite?
I've been using SQLite from Delphi. I found it easiest to use the native API, and put my own wrappers around it when necessary. In general I've kept it pretty simple, because I'm using SQLite as an intermediate database to manage data conversions. For the main data store of an application you'll want something a little more sophisticated. Clay Dowling Joe DiMaggio said: > Hello, > > I need a server-less SQL engine. Unless someone recommends a better tool > (embedded FireBird? Something else?), I'm thinking of using SQLite. > > Problem is, several wrappers are listed in the SQLite wiki. Can you > recommend one, ideally under active development, easy to use, etc. > > - Aducom open-source Delphi/BCC SQLite components > http://www.aducom.com > > - ZeosLib > http://sourceforge.net/projects/zeoslib/ > > - DISQLite3 > http://www.yunqa.de/delphi/sqlite3/ > > - LibSQL > http://libsql.dubaron.com/ > > - Simple Sqlite 3.0 for Delphi > http://www.itwriting.com/sqlitesimple.php > > Thank you. > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 19/06/2006 > > > -- Simple Content Management http://www.ceamus.com
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
Bogus³aw Brandys said: > In fact that is as I fairy know how it's implemented in Firebird Classic > Server (where each server process has separate lock manager I suppose) > This classic server processes are spawn by xinetd deamon. > I see sqlite in very similar manner : sqlite library is attached to each > spawned process which uses it. You've just proposed changing SQLite from an embedded database to a server database. The fact that it would be a self-launching server doesn't really change that. It completely kills its value to me. If I wanted a server process running I'd use PostgreSQL and get the associated benefits to boot. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] SQLite incompatible with NFS?
Halim Issa wrote: Hi, I have been trying to get an application that utilizes SQLite to run on our system, but according to the application developers it fails because SQLite is incompatible with NFS and thus cannot work on NFS-mounted volumes. Attempts at locating this bug in the SQLite database has been futile, so I wondered if someone could kindly point me towards the bug ID of this problem? The application in question is KDE's digiKam, and the bugs are described in Yes this is a main sqlite problem. Look here : http://bugs.kde.org/show_bug.cgi?id=116252 http://article.gmane.org/gmane.comp.kde.digikam.devel/2166/ I'm a bit puzzled by this, as all our clients are diskless and thus rely on NFS for both /home and other volumes, and we run multiple other SQLite-based applications without trouble. Any insight regarding the status and progress of this bug would be greatly appreciated! That will depend entirely on your OS vendor's plans to upgrade NFS and file locking. This is an old and known problem with NFS, and is not a problem with SQLite. SQLite relies on filesystem locking to control database access during writes, and many (most?) network filesystems don't have very fine-grained locking. You may get away with using SQLite just fine on a networked file system, but don't be shocked if the database gets corrupted during writes. It's mentioned in the documentation, and again, isn't an SQLite bug. Clay -- CeaMuS, Simple Content Management http://www.ceamus.com
Re: [sqlite] question about php_pdo_sqlite
yuyen wrote: > Ok, finally I found the problem is that the PHP and Apache are installed > in local drive and I put the sqlite database file on a remote file > server which is a Linux / Samba. I can use sqlite3.exe to open that > remote database with a mapped remote drive. And Insert, Update and > Delete have no problem with it. While with Apache and PHP pdo, if the > database is concurrently opened by sqlite3.exe, it will report "can't > not open database". Close sqlite3.exe, then PHP pdo can open it, but it > open as read-only. If I put the database file in local dirve, then there > is no any problem at all. Even I can use sqlite3.exe concurrently with > PHP pdo. Does Apache has different thinking about remote server? SQLite has issues with remote servers. It's entirely dependent upon the network file system's file locking mechanism, which tends to be something of a blunt instrument. It's also somewhat unpredictable. You'll be best off to keep the file on a local drive. If you need access from multiple servers a database engine with a network server daemon is going to be a better choice. PostgreSQL comes highly recommended, as do several others. Clay -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] Queries against NULL data
The behavior you are seeing is what is expected. NULL only every matches the IS NULL criteria. It doesn't pass equality, inequality, greater than or less than tests. The best option is to define the field as NOT NULL. NULLs cause all manner of trouble, and the best option is usually to avoid dealing with them. Clay Dowling Kareem Badr said: > I read the section on the SQLite website regarding the inconsistencies > of how NULL values are handled in various databases. I didn't see > anything that mentioned what I am running into, though. > > In a nutshell, the following queries do not return rows when test_field > is NULL. It seems counter-intuitive to me. > > SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' > SELECT * FROM my_table WHERE test_field != 'test value' > > I can understand the NOT LIKE query not returning rows with NULL more > than I can understand the != query not returning any rows. NULL does not > equal 'test value'. > > Is there an easy way around this, other than adding "OR test_field IS > NULL" to my queries? The example above is a lot simpler than the case I > need to handle in production, so I would like to avoid having to modify > every query to handle NULL values in a special case. > -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Re: getting the field names
Michele Simionato said: > I forgot to say that I would like a solution working from sqlite3, the > console, not from the > programming interface. For instance, I may want to pipe the SQL code to > sqlite3 from a CGI script, or just make some interactive exploration. You don't want this. You may think you want this, but shelling out to sqlite3 to perform queries for another program is a sub-optimal solution and opens you up to major performance and security problems. I have code at http://www.ceamus.com/objbuilder/ which can show you how to get your table structure. Download the package, extract, and look for sqliteschema.c. The example code is written in C, but the interface should be similar enough to whatever interface library you're using that you can get the information you want. Also, sqliteschema.c all by itself will spew the data you're looking for into an XML file. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access
[EMAIL PROTECTED] wrote: "Fred Williams" <[EMAIL PROTECTED]> wrote: How about some form of automated(?) sequence where: New subscriber submits subscription request. System sends "query" message to subscriber address. New subscriber sends "confirmation" message within reasonable time period. List access granted on receipt of confirmation. At least the damn spammer would have to do a little work to be a bottom feeder. Too bad there must be an endless number of idiots out there (P T Barnum postulation) or the problem would self regulate. Such a system is already in place. Please recall when you signed up that you got a confirmation message that you had to reply to before you were added to the mailing list. And yet somehow, the spammer still managed to get signed up using a "paypal.com" address. How did they do that? [EMAIL PROTECTED] at least does have an auto-responder. The message contains the text of any message sent to it, appended to the bottom of the email. That would serve to automatically validate the check email. You might want to consider reworking the check mechanism, using the capture mechanism that a lot of web forums and blogs use. Users must type in text presented on a web page in the form of a graphic. That will put a stop to the automated signup pretty quickly. Clay -- CeaMuS, Simple Content Management http://www.ceamus.com
Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access
[EMAIL PROTECTED] wrote: In order to be able to send messages to this mailing list, the spammer above had to subscribe. To subscribe means that he had to respond to an email that was sent to the subscription address. Since his email address does not exist, I'm wondering how he managed to pull this off. Any ideas? I have unsubscribed every account from "paypal.com" and "ebay.com". All such accounts were of the form "[EMAIL PROTECTED]" or "[EMAIL PROTECTED]", etc. There were 7 such accounts. I would start by putting a filter in front of the list that won't accept anything with @paypal.com or @ebay.com, not just at signup time but when the messages are received. As to how he pulled it off, there's probably an auto-responder on those addresses. Clay Dowling -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Make it faster
Anne, Try wrapping your inserts in a transaction. Individual inserts are quite fast, but transactions are not. Therefor anything that you can do to reduce the number of transactions will boost the speed. My preference is to wrap a transaction around every 200 or 300 inserts, depending on the size of the record. Clay Dowling [EMAIL PROTECTED] wrote: Hi to all Today I inserted as a Test 100 Records in my DB. I repeat therefor 100 times the same "Insert into...". The Job starts and I wait, and wait, and wait. I say, Programm hangs or died, because I do a Mistake in Code. But it isn't. The Job needs really so much time. Trace-Message shows me, what he just do. I look and search over a few hours in the Docu and older Mails and search with Keywords like "Faster", "Performance", "Disable Journal" and so on. Some Results was successful. After I perform the "PRAGMA synchronous=OFF" it works much faster. If I see it right, now I cannot Rollback, because Journal is disabled. Is it so? And is it recommendable? Or is it a "Never to this..."? ;-) Is there perhaps any other way to make it faster (...with Customizing some parameters)? In the moment I'm just a little bit confused... which adjustment has which effect? Greetings, Anne -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Possible bug with non-standard characters in column names
[EMAIL PROTECTED] said: > CREATE TABLE test1 (loader_id INTEGER PRIMARY KEY, loader_status > VARCHAR(255)); > ALTER TABLE test1 ADD name VARCHAR(255); > ALTER TABLE test1 ADD straße VARCHAR(255); > ALTER TABLE test1 ADD plz VARCHAR(255); > > Raises an SQL error: malformed database schema - near "plz": syntax error > > The suspected bug is that the ALTER TABLE that adds straße should raise > the error, not the following statement? I'm guessing that "straße" was indeed what ticked off the engine, but it took until "plz" for the parser to realize the problem. Have you tried using the 16 bit character functions instead? The 8 bit functions could be choking on the essen. Clay Dowling -- Simple Content Management http://www.ceamus.com
RE: [sqlite] XML to SQLite upload
Rajan, Vivek K said: > Thanks for answers. > > Sorry, I was not clear in my previous email. I do know the structure of > the for the XML - I was wondering if there was a direct upload > capability, once I know the structure of the XML. I think that the problem is a little too generic for an existing tool. However writing a tool for your specific situation should be fairly easy. If you're comfortable with C I strongly recommend looking at libxml2 (http://www.xmlsoft.org), which makes XML parsing very easy. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] How to create a database without sqlite?
Dan Baker wrote: > I been reading this list for several days, but just started using it today. > > I'm using it as an embedded database (on Windows), and want to be able to > create the initial database from within my app -- meaning, I don't want to > ship a database file. > > What is the recommended method for creating a new database file from within > my app? (I looked for an SQL statement, but failed to find something like > "CREATE DATABASE mydb") > > I'm thinking of the following solutions: > 1) Create a database with a small known table in it via sqlite.exe, and > storing that file in my app (as a resource), and then copying that file to > disk to "create a new database". (Then, I could drop the known small table). > > 2) Ship the sqlite.exe app, and launch it with the appropriate cmd line > parameters to create the database. > > SQLite looks fantastic! Nemanja's recommendation is definitely the best. Microsoft also has some good resources on doing an initial database installation and creation if you search on msdn.microsoft.com (install and SQL Server). While they were speaking in the context of SQL Server, I found that what they said applied very well to SQLite. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] UI framework for sqlite
David, What you're looking for is an editable data grid. They're common components and fairly easy to find. They're also typically a pain in the tail. If you're comfortable with C++ you might look at wxWidgets and their built-in datagrid. Clay David Bicking said: > This is probably off-topic for this list, so let me apologize in advance > if it is. I don't have a probably in using sqlite itslef, but in rather > how to use a UI to present the info to the user. I am not neccesarily > asking for how to advice here, but rather pointers on where I can find > such advice. > > I have written applications that will grab a record from an sqlite > table, and insert the values in pre-existing text boxes, which the user > can then change, finally clicking a save button, which sends the changed > data back to sqlite. > > I have also written applications that will select multiple records, and > dump that data as a printed report on paper. > > What I don't know how to do (without specialized tools) is to grab > multiple records and present them in editable fashion to the user, > keeping track of changes so thy can be written back. I am looking for > something like a datasheet view or continuous form on MS Access. > > I have been advised to just place enough edits boxes for five or so > records, then fill in the first five, then give the user "Next Five" and > "Previous Five" buttons to click. But I would rather a solution in which > the user can scroll down to see all the records, (within reason). > > I also want to do this with the least overhead and the most portable way > possible. My target audience is like my brother in laws business: they > have a few non-networked PC's with a mixture of versions of windows. My > second audience is my own home network of PC's running windows and > linux. > > This, I believe, leaves out any tool that requires KDE or Gnome or > anything big and bloated. While I consider GTK to be big and bloated, I > guess that is the upper limit that I want to consider. > > My language of choice is Basic, but do program in C, and have programmed > in C++. > > So I am looking for links to tutorials or how-tos, or even the right > string of text to use on google to point me in the right direction. > > Any advice is appreciated. > Thanks, > David > > -- Simple Content Management http://www.ceamus.com
[sqlite] SQLite & Palm!
Just got news about SQLite being available on the next generation of PalmOS systems (which is Linux based). This is fantastic news from the perspective of somebody looking to start Palm development. The concept of a Palm device that I can ssh to is also somewhat amusing. So has ACCESS or PalmSource been good enough to purchase a support contract? Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Database created in linux cant be read in windows
ryan bel brillo wrote: > Is there a compatibility problem with database created in linux and > windows? I remember this problem on occurs on sqlite version 2.6.2 or > earlier.. Or is there a problem in the way I create the database? How are you getting the file from Linux to Windows? If you used FTP it's possible that you forgot to configure your FTP client for a binary transfer. In that case certain characters would have been added. If you didn't transfer the files via FTP then I really can't say, and would suspect a corrupt file. One thing you might try though is reading the file on Windows with the sqlite3 command line program. That would be the authoritative source. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] Using semiclon!
Roger said: > The reason i need a semicolon is because, i am creating some queries for > my reports and there are instances i concatenate some fields which i > then seperate via a semicolon, but they occur in one line e.g. for the > above query > > Smith, John ; South Africa Roger, Your best bet would be to use the sqlite prepared statement interface. You can bind whatever you would like into the parameters and everything will be happy. Alternatively you can scan your strings for the ';' character and replace it with '\;'. This method isn't as nice, but if the prepared statement interface isn't available it's a good alternative. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Create Username and password for db
mohsen ahmadian wrote: > hello all > how to Create User name and Password for access to db on connection > to security db > thank > Mohsen Ahmadian > There are no users or passwords for SQLite. File system security is all that's available to you. Your application can provide additional security, but the database file itself can't. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
Re: [sqlite] Using SQLite on networked drive
Deepak Kaul said: > I was tasked to use sqlite on a NFS mount on MacOSX 10.4. I had > numerous problems with it because MacOSX does not implement locks > properly. It would run for about 15 minutes or so and then I would > start receiving Database Malformed errors. I had to come up with a > different solution. > > I came up with a scheme where only one process would handle updating the > database directly. All other processes locally or remotely would update > the database through a file hand shaking protocol. This is pretty ingenious, but I really think that in a situation like this you'd be better off looking at something like PostgreSQL, that can handle multiple network clients. SQLite is a great tool, but it's not the only tool. Use the right tool for the job, and a file-based database is rarely the right tool when network access is needed. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] How to secure SQLITE
Hakan VELIOGLU said: > Hi, > > I have a server that users are accessing it with ssh and publishing their > web > sites. What I want is a database support with less effor. So, > SQLite is a very good option for me to decrease the management tasks for a > database support. However, I searched the SQLites's web site for security > tasks but I couldn't find any sugestion. > > What I need is some suggestions like don't give the 777 permission to a > database > file. :) Of course this is very simple, but I am a newbee in SQLite so I > need > your experiments. What are you trying to accomplish with your database, and your database security? That said, unless you buy the encrypting version of the database your only real options for securing a database file are through filesystem permissions. Set the filesystem permissions appropriately and things should be okay. If the database needs to be accessible from a web app the best solution is to create the database outside of the web server's document root in a folder that only the web server and the site owner have permissions to read, write or execute. That's folder permissions 770 with the owner or the group being the web server's owner or group, and the group or the owner being the user. >From there keeping miscreants out of the file becomes the domain of the web application accessing that file. The app writer can give as much or as little access as desired. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] performance statistics
[EMAIL PROTECTED] said: > 1. My anticipated bottleneck under postgres is that the DB-writing app. > must parse incoming bursts of data and store in the DB. The machine > sending this data is seeing a delay in processing. Debugging has shown > that the INSERTS (on the order of a few thousand) is where most of the > time > is wasted. Jason, You might be better performance simply by wrapping the insert into a transaction, or wrapping a transaction around a few hundred inserts at a time. A transaction is a very expensive operation, and unless you group your inserts into transactions of several inserts, you pay the transaction price for each single insert. That has a devastating impact on performance no matter what database you're using, so long as it's ACID compliant. SQLite is a wonderful tool and absolutely saving my bacon on a current project, but you can save yourself the trouble of rewriting your database access by making a slight modification to your code. This assumes, of course, that you aren't already using transactions. Clay Dowling -- Simple Content Management http://www.ceamus.com
[sqlite] sqlite3_last_insert_rowid in transactions
I'm using SQLite in a performance-intensive data conversion process and it's come about that it would be very useful to get the insert_rowid of the last record inserted while I'm still in the middle of a transaction of servert hundred inserts(the primary key will become a foreign key to a child table). Can I get a reliable rowid from this call while I'm in the middle of a transaction, without interrupting the transaction? The large transactions are too important to sacrifice just to get this information, but it would make the programming a lot easier to have this data. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Writing wrappers?
David Gewirtz said: > > I've just started to explore SQLite and I've noticed a whole pile of > wrappers for various environments. The environment I'm using (the open > source Frontier Kernel) doesn't have any wrappers, so I'd like to write > one. > Can someone post some pointers to resources on how to get started writing > wrappers? As somebody who has written his own wrapper, the best advice I can give is to see what the needs of your application are first. Your wrapper class effectively becomes the SQLite client program, so make sure that it addresses all of the needs of SQLite such as statement finalization and parameter population. Make sure that the wrapper itself addresses the needs of your program and makes it so that you don't need to make sqlite3_* calls from your code. It's definitely a good idea to be comfortable with writing a couple of C programs that use SQLite first before trying to write a wrapper. The standard C interface to SQLite is pretty good already, and once you become comfortable with it you'll be able to see what you want to do with your wrapper. Clay -- Simple Content Management http://www.ceamus.com
Re: Re: [sqlite] SQLITE with MINGW
jam_lraep said: > I have only downloaded, unzipped SQLite-3.3.3.tar.gz and launched the > followings commands: > ./configure > make && make install > > if there is another way to compile the library I don't know it... There's probably a slicker way to do this, but as a crude hack you can search the generated Makefile for a variable CFLAGS. Any options you put there will be used to compile the program. Somebody who understands autotools can probably show you how to do it in a more elegant way. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] SQLITE with MINGW
jam_lraep said: > Hi sqlite-users-help, > I have compiled SQLITE with MINGW and the resultant library libsqlite3.a > results very great (about 8MB), it probably contains the symbols for the > debugger. How can I eliminate them? > Do I have to modify the makefile? Thanks to all. Use the strip command. That removes a lot of fluff but keeps the functionality. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Can sqlite support
Jiao said: > Hi,all > NOW I want to use sqlite in an embedded device.In my design ,a web cgi > in a single process is used to update configuration,and I expect my main > application in another process can be noticed whenever its configuration > is changed, how to use sqlite to achieve this goal, can I use trigger > and how to use it? Create a user-defined function (see the documentation) which sends the notification. Call that function from the trigger. Your notification method will be up to you and entirely dependent on your application. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Compatibility between different versions of SQLite
Thomas Levesque wrote: > I'm new to SQLite, and I noticed a problem of compatibility between > different versions of SQLite : it seems that a database created with a > version of SQLite cannot be used with another version. It seems logical that > downward compatibility isn't ensured, but I expected that a database created > with SQLite 2.x should be readable by SQLite 3.x. Am I doing something > wrong, or is it really impossible to do that ? It can be a problem, because > if a program uses SQLite as its storage medium, it can never use a newer > version of SQLite. The solution is for your program to perform the upgrade. Presumably at the same time that you're upgrading the database engine you'll be upgrading other features of your software, and a format change was probably necessary anyway. Different versions of the API can happily co-exist in the same program, so there's no problem that way. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
[sqlite] Update to Object Builder announcement
I light of Jay's comments about being unclear what Object Builder does, I've added an additional page to cover that very concept at http://www.ceamus.com/objbuilder/what.html Anyone with more questions, feel free to hit me with them, on list or off. And definitely feel free to use this code in your own application. That's why I've released it. Clay Dowling -- Simple Content Management http://www.ceamus.com
[sqlite] Useful SQLite tool?
I think that I've previously mentioned my Object Builder code on this list. If I haven't, you might want to take a look at http://www.ceamus.com/objbuilder/ Object Builder is a tool to automate the creation of Active Record classes for reading and writing data from databases. The included examples are specific to SQLite 3 using C++. I've also included a more thorough example in PHP that generates a significantly large portion of the application automatically. Hopefully these examples will be useful to people reading this list. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] question about performance
Xavier Noria said: > I have a simple schema and a sql loader that fills a table with > initial values: > >delete from foo; >insert into foo ...; >insert into foo ...; >... about 50 inserts ... > > To my surprise, the execution of these inserts took a few seconds > (SQLite is 3.3.3). However, if I wrapped the entire loader in a > transaction: > >begin transaction; >delete from foo; >insert into foo ...; >insert into foo ...; >... about 50 inserts ... >commit transaction; > > then it was immediate. Why? When you didn't wrap it in a transaction, you really had about 50 little transactions. That gets expensive in a hurry. So when you're doing bulk inserts remember to make transactions. When I had to insert a 800k records into a database I found that is changed insertion time from hours to a few minutes. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Fwd: Help regarding insertion into database
pavan savoy said:
> -- Forwarded message --
> From: pavan savoy <[EMAIL PROTECTED]>
> Date: Feb 8, 2006 5:22 PM
> Subject: Help regarding insertion into database
> To: [EMAIL PROTECTED]
>
> Hi,
>
> Inside C program I have a structure and want to store it into a
> database, isnt there any datatype where I could just mention this many
> number of bytes starting from this address...
>
> specifically I want to store a struct s { int a; char b[16]}; directly
> without having to create table tab(a integer, b varchar2);
>
> cant I just have create table tab(data binary);
> insert into tab();
> insert into tab(); . etc..
It's certainly possible to cast the object as a void* and insert it as a
blob. That's really somewhat less than desirable though. You might be
happier if you check out http://www.ceamus.com/objbuilder/ which will
create data objects in the Active Record pattern. Currently it generates
C++ objects, but it's a fairly simple matter to convert it to generating C
functions and data structures.
Clay
--
Simple Content Management
http://www.ceamus.com
Re: [sqlite] More benchmarks
Jim C. Nasby said: > Finally, and luckily this applies to SQLite as well so this doesn't get > too off topic :), PLEASE seek help/advice BEFORE spending a bunch of > money on a big server! All too often I see people who spend a load of $$ > on equipment they didn't need or won't be able to utilize because they > didn't do enough research before hand. Granted, I'm biased since I make > money on consulting, but the amount of money I've seen people spend on > needless hardware would often buy a pretty good chunk of my time. Fear not, I'm pretty feircely conservative (i.e. cheap) when it comes to acquiring hardware and software. This comes of having a small budget. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] More benchmarks
Jim C. Nasby said: > Well, that certainly won't help things... at a minimum, on your machine, > you should change the following: > shared_buffers=1 > effective_cache_size=10 > > The following should also help: > work_mem=1 > vacuum_cost_delay=50 > autovacuum=on > autovacuum_vacuum_scale_factor=0.2 Jim, I just want to say thanks for providing these tuning parameters. I not currently using your product, but I hope to in the near future for a larger scale version of my own product. Performance tuning is something of an arcane art from my perspective, so getting any help on it is highly desirable. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] bulk processes with dll or obj versions?
Randall wrote: Hi, I have a DOS wrapper running sqlite3.exe at present. Do either the dll version or obj wrapper versions accept ".import" or ".output" or equivalent as a bulk process to and from csv, or only line by record? If so, what are the commands? [noting that "copy" is removed from version 3 on?..] (or where can i find them?...) These features aren't built directly into the library, but implemented in the client. However it's very easy to implement those in code. If you'd like I can supply C source to parse the CSV text. Clay Dowling -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Sqlite powered Web Sites
Clint Bailey said: > Can anyone point me to web sites that are powered by Sqlite? I'm curious > as to how they function as compared to a MySQL, or brand M$ powered site. http://www.ceamus.com Of course, you aren't going to see the guts of its SQLite access from this perspective. But as a regular user of my own product, I assure you that the performance is excellent. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Fwd: Does SQLite provide XML support
pavan savoy said: > #db2xml test.db test.xml > > which converts test.db database to XML format. I dont really care abt DTD, > stuff and all. I just want it to be converted. Thats it ... It really depends on what you want to find in that XML file. I already have a tool at http://www.ceamus.com/objbuilder/ which will render an XML file describing the schema of the database. It would be a pretty straight forward matter to generate an XML file with the contents as well, but I don't know about such a beast. For filthy lucre I would be more than happy to make such a tool. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Non standard database design (max no if tables?)
Michael, Your first-draft design is going to be easier to manage and more scalable. Stick with that, rather than the curious notion of having one table per keyword. Speaking as a frequent maintenance programmer, that's the kind of design that would force me to hunt somebody down. It probably will give very good performance, but it's unlikely that your current list of keywords will remain the final list of keywords. The real solution of course it to try it both ways and see which is faster. My suspicion is that you won't find a gigantic performance improvement by having a separate table for each keyword. You will have a giant maintenance headache though. The money you spend on programming time could have been more profitably invested in better hardware. Even a dirt cheap programmer costs about $500 for a day of work once you factor in taxes and minimal benefits. A week of that and you've paid for a decent server. A month of extra programming over the life of the project and you've paid for a very good server, with fast processor and disks that could have netted you the improved performance without the extra coding. It's a cold way of looking at it, but I've had to deal with too many designs that saved a little time and gained a little speed up front, but that cost a lot of money down the road in longer development times. Clay Dowling Michael Sizaki wrote: > Hi, > > I'm thinking about using a non standard design for my database. > There are up to 1 million records in the database. Each record > has an integer UID. There is another table with keywords for > each of the records. Records typically have 0-15 keywords associated. > The number of keywords is small 100-1000. Currently I have > > CREATE TABLE data ( > dataId INTEGER PRIMARY KEY AUTOINCREMENT, > ... > ); > CREATE TABLE words ( > wordId INTEGER PRIMARY KEY AUTOINCREMENT, > word TEXT PRIMARY KEY ON CONFLICT IGNORE > ); > > CREATE TABLE keywords ( > wordId INTEGER, > dataId INTEGER, > PRIMARY KEY (wordId, dataId) ON CONFLICT IGNORE > ); > CREATE INDEX keywordsDataIdIndex ON keywords (dataId); > > This creates in 2 big indexes for keywords... > > When I display data items, I also display all associated keywords. > Therefore I created a cat function, that creates a comma separated > list of items: > > SELECT *,(SELECT cat(category) FROM keywords WHERE keywords.dataId = > data.dataId) FROM data ... > > I just wonder, if I should not forget about database normalization > and create one table per keyword: > > CREATE TABLE data ( > dataId INTEGER PRIMARY KEY AUTOINCREMENT, > keywords TEXT, -- a comma separated list of word ids > ... > ); > CREATE TABLE words ( > wordId INTEGER PRIMARY KEY AUTOINCREMENT, > wordTEXT PRIMARY KEY ON CONFLICT IGNORE > ); > > -- for each word ID there's a table > CREATE TABLE keywords_0 ( > dataIdINTEGER PRIMARY KEY, > ); > > Is there a limit in the number of tables? > > If I choose keyword 1, 7 and 42 I would > > SELECT * FROM keywords_1 UNION SELECT * FROM keywords_7' UNION SELECT * > FROM keywords_42' > > I expect the database to become significantly smaller... > > Has anybody tried something like this? > > Michael
Re: [sqlite] Does anyone know how I would uninstall sqlite?
twoeyedhuman wrote: > I have the latest version of sqlite3 on my debian box and I'd like to > uninstall it because I keep getting this error through bash: > > sqlite3: error while loading shared libraries: libsqlite3.so.0: cannot open > shared obje ct file: No such file or directory > > I'm actually running xandros linux and I just want to find where all the > files were installed so I can delete them manually and install sqlite through > a debian package. Is this possible? First, find out where your system has installed these libraries. By default libsqlite3.so installs in /usr/local/lib, but Linux distributions don't like to put much of anything there. sqlite3 itself installs to /usr/local/bin by default, but again don't assume that's where it's gotten to. The last file you'll need to delete will be sqlite3.h, which by default goes to /usr/local/include. A typical Linux distribution trick is to move these from /usr/local to /usr. Also be aware that if you didn't install the development package, you might not have sqlite3.h, but hunt it out and be sure. If it's installed in /usr/include and you don't delete it, you're going to be in for a rough ride when you try to built SQLite based software. Clay Dowling
[sqlite] Ann: Object Builder
To make it easier to build and maintain my CeaMuS product I wrote an automatic database object building tool. While it's intended to work with any programming language and database engine, CeaMuS was written using SQLite and C++. Object Builder follows in that tradition, with prebuilt Windows binaries to generate C++ code for manipulating SQLite 3 databases. Hopefully some of you will find it useful. The code and more information is available at http://www.ceamus.com/objbuilder/ Clay Dowling -- CeaMuS, Simple Content Management http://www.ceamus.com
Re: [sqlite] Quick question about locking
JD Smith wrote: An old associate of mine wrote a quick and dirty C++ SQLite wrapper when he started using SQLite. He passed me a copy of it to test out a few years ago and I've been using it and SQLite when I need database access. Somehow, up until now, I never ran across this particular problem... I perform a query and then, while iterating through the result set, do updates to the database based upon some of the things I see in that query. It is saying that the database is locked and thus kicks me back. Is it normal that a query locks the database for writing? Below I will include some of the pertinent code... I'm just a bit confused. JD, This is perfectly normal. See http://www.sqlite.org/lockingv3.html for an explanation that might clear things up for you. Although it's not ideal, you have a couple of solutions. First, you could do your calculations and updates entirely in SQL, although that's potentially a major pain in the tail. The alternative is that you store you updates outside of the database until you have completed your read. Once you have gone through the data to figure out what updates are necessary, you then perform the updates based on your cached data. Clay Dowling -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Database design and SQLite
michael munson wrote: > Greetings, > I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I > have run into a bit of a wall while attempting to design a database for a C++ > program I am attempting to write. > > The needs of the database are to: represent an object oriented hierarchy of > any number of objects, where each object may have custom properties of > several different datatypes and permission bits. > > The table fields that I have so far are a primary integer key (ID), text > representing the object's name, (NAME), and two fields "OWNER" and "PARENT" > which are integers representing other objects in this table. > > The problem I am running into is the object hierarchy and custom properties. > For example: If object #1 is a parent of object #2, and #1 has a property > named "location" then #2 should also have that property (although the value > and permissions may be different from the parent). > > I'm trying to see if I can do this some way I do not currently know how, > because the only thing I can think of is some delimited BLOB and recalculate > all the parent properties whenever the parent is changed which I imagine may > significently slow down my database. The first thing to ask yourself here is whether or not it makes sense to use a database. Certainly that makes sense if you need concurrent read and write access, but if you only need to write from one source at a time an XML file sounds more like what you need. It supports your need for infinite hierarchy, and if you work without a DTD it allows setting of any properties that you would like. If you don't like the general bloat of an XML file you can make use of a library like libxml2 (http://www.xmlsoft.org) which natively and transparently supports compressed XML files. If you're absolutely sold on the need that this be in a database, buy a copy of Joe Selko's _SQL for Smarties_. It covers these hierarchical structures in great detail. Even after implementing this kind of structure before I wouldn't try it again without consulting Selko's book. Clay Dowling
Re: [sqlite] enum in SQLite
Kurt Welgehausen said: >> SQLite doesn't support enums natively. You could emulate it using >> triggers, although it would be somewhat hidden and definitely a pain in >> the tucus to use. > > It's not really so hard. > > create table MainTbl ( > ... > EnumCol SomeType references EnumVals, > ...); > > create table EnumVals (val SomeType); > > Now you have to enforce the foreign key with a trigger. > > create trigger EnumTrg before insert on MainTbl for each row > when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin >select raise(rollback, 'foreign-key violation: MainTbl.EnumCol'); > end; That's a lot more elegant than what I had envisioned, which was a static list of values. Don't forget though that you'll also need to write an update trigger, since it's pretty easy to write "UPDATE MainTbl SET EnumCol='bogus'" Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] enum in SQLite
Rajan, Vivek K said: > I would like to know the following: > > *Does SQLite support enum like MySQL? If yes, how to use it > > *If not, is there another way to model enumeration in SQLite? > And/or any plans going forward to support enums natively in SQLite? SQLite doesn't support enums natively. You could emulate it using triggers, although it would be somewhat hidden and definitely a pain in the tucus to use. If your database is only going to be accessed by applications that you control, it's really easy to emulate enumerations in your own code, by restricting the possible values that can be used for a field in code. In the languages that I'm familiar with (C, C++, Delphi, PHP) this is best accomplished by making a data access class for each table and putting the logic in that class. In fact my preference is to encode the value as a language enum where that's possible (I'm not sure if PHP handles enums). Clay Dowling -- Simple Content Management http://www.ceamus.com
RE: [sqlite] Final Year Project/Dissertation help required!!!!
John, There's a package often found in the discount aisles of computer stores called My Database. My father (who doesn't pretend to know anything about databases or programming) uses it for his business and personal uses. The tool looked absolutely brilliant from the perspective of making it easy for a non-database person to store their data in a logical way. You might want to examine that project as well. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Final Year Project/Dissertation help required!!!!
john.newby wrote: > Why users use SQLite instead of other SQL databases? > Would a GUI detract users from using SQLite? I use SQLite as an embedded database for my content management system CeaMuS (http://www.ceamus.com). I use it because it allows me to distribute an application that takes full advantage of a relational database, but doesn't require any setup for the user. It lets my app be completely dependency free. A GUI is completely irrelevant to me. I need it in the way that a fish needs a unicycle. That said, I'm not Joe User. I do know of users who are not programmers who like the concept of SQLite. For them a GUI would be a most advantageous thing. You should keep in mind though that in this case you suffer from Prior Art. Others have already put GUIs on SQLite. At U.S. institutions at least students are required to do some completely original work for the dissertation. It would seem then that you have another hurdle to cross: you application can't be just a GUI, it has to enable features that no other GUI has allowed for users of SQLite. Maybe an access-like interface that makes it easy to design forms and reports? I haven't done a full literature search on this matter so I can't really say that this would be breaking new ground. Clay Dowling
Re: [sqlite] CGI
[EMAIL PROTECTED] said: > Hi all, > > Where can i find a framework (or example) to incorporate > SQLITE in a CGI (written in C)? The URL in my sig is a decent sized CGI app written with C++ and SQLite. The previous recommendation of cgic is a good starting point. From there I go on to add http://www.lazarusid.com/libtemplate.shtml and http://www.lazarusid.com/download/sqdataset.tar.gz This last is strictly speaking a C++ library, but I made it to simplify my interaction with SQLite a bit. If libtemplate doesn't quite appeal to you, I can also strongly recommend a combination of libxml2 and libxslt to generate your output. I've used both solutions to generate output for high-volume web apps. Clay Dowling -- Simple Content Management http://www.ceamus.com
RE: [sqlite] [OTAnn] Feedback
Brad DerManouelian said: > I was looking for something aggregateder. I've been scared to read email > lists until now because of how unsafe it is. Just gotta figure out how I > upgrade the internet to 2.01. I hope it comes with AJAX. If you get the Web 2.01 upgrade pack, I think they throw AJAX into your mashup for free. That way you can get into these mysterious new on line communities. Clay Bad management consultant. Sit in the corner. -- Simple Content Management http://www.ceamus.com
