Re: [sqlite] how can we solve IF EXIST in SQLite
Sorry, I don't see EXISTS in SQLite documentation.
On 8/20/09, Asif Lodhi wrote:
> Hi,
>
> Perhaps you can do this in TWO SQL STATEMENTS - see below. However,
> this way you inefficiently check the existence twice:
>
> On 6/2/09, robinsmathew wrote:
>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>> prod_batch_code=1000)
>> UPDATE stock_tab
>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>> WHERE
>> oduct_batch_code=1000 )
>> WHERE prod_batch_code=1000
>> ELSE
>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date) values (20009, 1003, 200,
>> DATETIME('NOW') );
>
> UPDATE stock_tab
> SET stock_qty=stock_qty+(SELECT purchase_qty
>FROMpurchase_tab
>WHERE prod_batch_code=1000)
> WHERE prod_batch_code=1000
> AND EXISTS (SELECT prod_batch_code
> FROMstock_tab
> WHERE prod_batch_code=1000);
> INSERT INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date)
> VALUES (20009, 1003, 200, DATETIME('NOW') )
> WHERE NOT EXISTS (SELECT prod_batch_code
> FROMstock_tab
> WHERE prod_batch_code=1000);
>
> -Asif
> PS: List users, is there any way we can cache the result of the EXISTS
> clause above in order to avoid having to execute it twice using the
> above approach?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi,
Perhaps you can do this in TWO SQL STATEMENTS - see below. However,
this way you inefficiently check the existence twice:
On 6/2/09, robinsmathew wrote:
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000)
> UPDATE stock_tab
> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
> oduct_batch_code=1000 )
> WHERE prod_batch_code=1000
> ELSE
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,
> DATETIME('NOW') );
UPDATE stock_tab
SET stock_qty=stock_qty+(SELECT purchase_qty
FROMpurchase_tab
WHERE prod_batch_code=1000)
WHERE prod_batch_code=1000
AND EXISTS (SELECT prod_batch_code
FROMstock_tab
WHERE prod_batch_code=1000);
INSERT INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date)
VALUES (20009, 1003, 200, DATETIME('NOW') )
WHERE NOT EXISTS (SELECT prod_batch_code
FROMstock_tab
WHERE prod_batch_code=1000);
-Asif
PS: List users, is there any way we can cache the result of the EXISTS
clause above in order to avoid having to execute it twice using the
above approach?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Group By, having clause?
Hi Christophe, On 12/9/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > the difference between a normal "where"-clause and the "Group by", and > "having" I used www.sqlcourse.com and sqlcourse2.com probably in the year 2000. They had very good tutorials and also let your actually practice the SQL online! -Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
Hi All, On 10/10/08, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > On Fri, Oct 10, 2008 at 12:59:31PM -0400, Igor Tandetnik scratched on the > wall: >> Shaun R. <[EMAIL PROTECTED]> wrote: > It is true that on most systems "char" is signed, but it is worth > remembering that this is not universally true. This is why "char" and > "signed char" are actually different types in C, unlike "int" and > "signed int", which are assumed to be the exact same type. I do not know for sure about C but "char", "signed char" and "unsigned char" are three different data types in C++ and the SAFEST portable way that is supposed to work across all compilers and platforms is to use simply "char" - no "signed char" or "unsigned char". -Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System function with Sqlite
Hi Chris, On 8/15/08, Chris Brown <[EMAIL PROTECTED]> wrote: > > Can you offer any further help/suggestions? > AFAIK, how the system() passes the command-string to the command-processor is implementation defined. I would suggest that you also pass the shell (e.g. /bin/sh) to system() in addition to the command string. Which shell are you using? - Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 6, Issue 90
Hi Dick, On 6/27/08, Richard W. Kulp <[EMAIL PROTECTED]> wrote: > how to replace the > AspNetSql providers with the SQLite providers. I have copied everything into > App_Code as instructed and changed the Web.Config file but when trying to > use the WebAdmin tool, the only providers available are the AspNet ones. You are using AspNetSql providers - this is the problem. These providers are ONLY meant for connecting to the SQL Server data sources - nothing else. For connecting to Sqlite, you'll have to use ADO.NET provider for SQLite and use general (NOT SQL Server specific providers) code for data access. -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hi Ken, On 6/6/08, Ken <[EMAIL PROTECTED]> wrote: > Some numbers can be represented exactly using the > floating point type. . Here is a reference from "The C++ Programming Language, 3rd Edition" by Bjarne Stroustrup, Page 835, section - C.6.2.6: int i = float ( 1234567890); left i with the value 1234567936 on a machine, where both ints and floats are represented using 32 bits. Clearly, it is best to avoid using potentially value-destroying implicit conversions. . - -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hi Christophe, On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > .. > AND (latitude_DDS BETWEEN 44.261771 and 44.424779) You might want to check if you can somehow store this data *without* the decimal point (with the point implied - counting six digits from right to left) and use *integer* mathematical operators to do the maths. > someone suggested to divide up the tables - something which led me to > the idea to create different views for each class_dds value: > create view Level1 as Select * from cities where class_dds=1 > .. > So i could do select statements like: > > select * from Level1 > Union > select * from Level2 > ... Perhaps he meant "partitioning" when he said "dividing" and he was coming from an Oracle background or something ??? This is because UNIONs are ALWAYS slow unless you are using a higher end RDBMS such as Oracle where you can take special measures to speed up union using the Oracle provided facilities. -- Best regards, Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resetting a Primary Key
Hi Vincent, On 11/21/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > .. is giving me "primary key must be unique" errors. > > How can I reset the sequence for a primary key? The table only has > about 15000 records in it and I've never seen this happen before.. Though I haven't used sqlite but apparently you must have stored NULL once into the table and were storing it a second time which led to this error because this violated the primary key constraint. You could have COUNTed NULLs instead on ItemID using different values for other relevant columns to use GROUP BY to check the data yourself and see what was wrong. -- Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] BLOB data performance?
Hi, On 11/15/07, Andreas Volz <[EMAIL PROTECTED]> wrote: > Am Tue, 13 Nov 2007 12:46:11 -0800 (PST) schrieb Ken: > > > I think your blob file performance may greatly depend upon the file > > system that it used and the workload. > > > > I found this article: > > > > http://oss.sgi.com/projects/xfs/papers/filesystem-perf-tm.pdf > > Very interesting document. But I couldn't know and rely on what > filesystem the user has. There should be another way to ensure a good > file access speed. Interestingly, Microsoft's SourceSafe (at least VS-6.0) apparently uses file system while SVN uses Berkeley DB as I read once. Both products have a common application but use different storage mechanism. That boils down to what algorithm you are using for your particular problem. Just my two cents. -- Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
Hi Darren, On 11/3/07, Darren Duncan <[EMAIL PROTECTED]> wrote: > At 9:42 AM -0400 11/2/07, Samuel R. Neff wrote: > I think the real problem here is "virtual" is a broad enough term ... I immediately think about MySQL when you use terms like "federated" and "data engine". With the word "federated", anyone with a MySQL background can immediately "see" what the virtual table is all about - if that's what it is, that is. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unicode Capabilities in Simple Terms
Hi, I've been just glancing over the Unicode related posts. However, I would like to know how it's possible for me to insert text in different languages in an Sqlite database. Can you fixate the "type" of the language for any particular database? I would be thankful if any of you guys could explain to me the procedure in a step-by-step manner. -- Thanks in advance, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite db portability
Hi Shilpa, On 8/28/07, Shilpa Sheoran <[EMAIL PROTECTED]> wrote: > Eg. I create sqlite db file say "mysqlitedb.db" and now I have MySQL > installed. Can it access "mysqlitedb.db" IIRC, I did read about "Linked Servers" in MS-SQL-Server documentation where, if your SQL Server is being used in an NT domain setting then you can create "Linked Servers" - which means that you can "link-in" different databases having ADO wrappers. MS-Access is NOT a database server but I think I did read that you could link in MS-Access databases to your SQL Server process. Though I am NOT sure but I think someone did mention ADO wrappers pertaining to Sqlite and I would suggest that you give that a shot. -- Best, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Replicating table data from sqlite to ms sql server
Hi maitong, On 7/16/07, maitong uy <[EMAIL PROTECTED]> wrote: The scenario would be the sqlite database is managed using CGI C, resides in Linux environment, and accessed through the web. Then the sql server would be replicating whatever changes would occur in the sqlite database (both sqlite and sql server have the same tables). This will also happen vice versa wherein any change in sql server will be replicated in the sqlite. Sql server is managed using ASP and resides in windows server 2003. Assuming you have both the servers in one room/location/place, A listener daemon on Linux to listen for the replication-specific messages from the SQL Server machine and a listener service on Windows to listen for the replication messages from Sqlite. You might want to use the "Java Service Wrapper" to develop a single listener software that does the kind of replication you are talking about on both the servers (Linux as well as Windows) using JDBC and install it as a service (on Linux as well as on Windows). You will then have to write separate pieces of code on Linux & Windows to communicate with the service hosted on the respective OS using the data to be replicated as parameter which in turn will communicate with the corresponding service on the other OS (send the data to another machine/OS for replication) and the corresponding listener service on that machine will take the data and update the local database. You can also try to write the database update code transparently - it will be a good exercise. You might also want to use some kind of a design pattern to isolate the database updating code in a separate Java object (based on whether you want your Java code to update an Sqlite database or an SQL Server one) so that you can select run the appropriate (single piece of) code dynamically at run time on both the machines. You might also want to look at "Microsoft Windows Services for Unix" - may be that's what you need. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sharing an in-memory database between applications
Hi Rob, On 7/13/07, Rob Richardson <[EMAIL PROTECTED]> wrote: Greetings! But I'm wondering if I can use an in-memory database to improve this dramatically. The data is collected by a Windows service that collects data and adds it to the database once a minute. If the service would also store the data into an in-memory database, and the graphing application could somehow read the same database, I ought to be able to get unbelievable speed. Is this feasible? If so, how would I set it up? You might want to have a look at the "Times Ten" database solution which is now owned by Oracle. You might want to use it to build the kind of a "fast" in-between cache that you have mentioned in your. -- HTH Asif
Re: [sqlite] Replicating table data from sqlite to ms sql server
Hi maitong, On 7/11/07, maitong uy <[EMAIL PROTECTED]> wrote: I see...any idea as to how exactly? I really am out of ideas regarding this... :( I think I did see some UnixODBC files on a Linux environment (Fedora Core 6, to be exact) and if you have the same kind of UnixODBC files on your system then you can use that to access the SQL Server database on the SQL Server machine. When it comes to SQLServer-to-Sqlite communication, it should be noted that Sqlite is an embedded database solution and you will have to write and some kind of a listener on your Linux server so that a windows program can communicate with it. You can use a CORBA environment that exists on both Linux and Windows environment and use that to update both of your databases. You can use Java for this task as well using its JNI interface. These are a few possibilities that have immediately popped up in my mind when I read your post. -- HTH Asif
Re: [sqlite] Recommend server for Windows?
Hi Gilles, On 6/20/07, Gilles Ganault <[EMAIL PROTECTED]> wrote: At 16:49 19/06/2007 -0700, Medi Montaseri wrote: The context is that, until now, our apps were almost used on stand-alone hosts with only a few customers hosting the (small) SQLite database file on a shared drive on the LAN, so performance was just fine. Now, we have a customer whose DB file is about 50MB... and using a 10Mbps LAN, and it takes about 8 seconds for an INSERT. So we have to find a solution ASAP, with minimal changes to our app, at least until we get around to rewriting the DB part so that it uses a location-independent connector. I would suggest that you develop a small application-specific Sqlite server (specific to your application requirements - "specialized"), put it on a machine and do ALL communication with it using straight TCP-IP because Sqlite is an "embedded" database and I think your are having problems because you're sharing it on a LAN. I think embedding your database in an application-specific server and making your client applications communicate with that server through TCP/IP (instead of sharing on a LAN) would do the job. -- Best regards, Asif Instead of putting an Sqlite database on a shared drive on a LAN, why don't you - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Hi Christian, On 6/19/07, Christian Smith <[EMAIL PROTECTED]> wrote: SQLite is not optimised for large datasets. .. Consider using larger pages than the default 1024 bytes to limit the number of pages SQLite must track. .. Thank you for replying. I think performance can be tested only by actual testing on live data. I'll code my application accordingly so that I can replace Sqlite specfic code with something else in case I run into major problems. -- Thanks again, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Hi Kees, Thanks for replying. On 6/17/07, Kees Nuyt <[EMAIL PROTECTED]> wrote: >... thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but sqlite is used in many end-user products, ranging from operating systems .. Basically, I intend to use sqlite's data capacity as well - I mean 2^41 bytes - for reasonably sized databases. Well, not as much as 2^41 but somewhere around 2^32 to 2^36 bytes. I would like to know if the "crash-recovery" feature will still work and the high-performance mentioned will be valid even if I have this kind of a data volume. And yes, I am talking about highly normalized database schemas with number of tables exceeding 80. Please reply assuming I tend to come up optimized db & query designs - keeping in view general rules for database/query optimizations. -- Thanks again and best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Recovery After Crash
Hi Everybody, I have just joined this mailing list as Sqlite looks like a good software solution to my needs. What I need right now is RE-assurance of "crash-recovery" that is mentioned on your front page. So, I would be thankful if you experts would give me an "accurate" and fair picture of the crash-recovery aspects of SQLite - without any hype. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
