Re: [sqlite] Still getting Insertion failed because database is full. errors
Hi Dan, Responses inline: On 4/18/07, Dan Kennedy [EMAIL PROTECTED] wrote: At first I thought this had solved the problem, because all in house testing runs beautifully. However, as soon as the device is sent to the field, the error starts again. Unfortunately, it means that I have never been able to catch this in debug. I did, however, change the error reporting a little and got some more information. The SQLiteException I am not getting includes this information: Insertion failed because the database is full That message is from the wrapper. Yes, I talked to DRH yesterday on the phone and that was the initial conclusion, that this error is somehow related to the wrapper (in this case System.Data.SQLite .NET Managed Provider). I have posted a report in the BUG section of their Forums. In the meantime, DRH suggested I try to catch the error and recreate the Connection, which is first on my list for this morning. I'll keep the list posted. database or disk is full And the above is from sqlite3. The corresponding return code is SQLITE_FULL. Search source file os_win.c for where SQLITE_FULL errors can be generated - there's only a couple of places. Odds on it's one of them. Looks like on windows, any error writing or seeking a file is reported as SQLITE_FULL. at System.Data.SQLite.SQLite3.Reset() at System.Data.SQLite.SQLite3.Step() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader.ctor() at System.Data.SQLite.SQLiteCommand.ExecuteReader() at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader() at ... etc etc So is this really a SELECT? Probably good to check that. No question, it is absolutely a select. If it is a SELECT, why would it be filling up the database? Is it rolling back a journal file and running out of space like that? Or the user rolling back the journal file doesn't have permission to write the database file and SQLite is reporting the error incorrectly. As far as I know, there are no journal files. The user has full authority to the entire system including the SQLite database. Check for a journal file on the device after the error. Also run the SQLite integrity-check on the database. Dan. OK, I'll have to find out about that... Thanks, -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database is full. errors
Hi Christian, This is really interesting. What is the official definition of suspension? On handheld devices, the device suspends itself every minute or so to save battery life. All the user does is press the power button and the application is back. Is that the level of suspension you are talking about? If so, how does the application handle that? How do you test for AboutToSuspend or NotYetRemounted? Is the removable media dismounted every time the device sleeps like that? Any specifics you could give me would be great. If this is the case, then I'm going to seriously consider moving the database to the internal memory. Thanks, -- Joel Cochran On 4/18/07, Christian Schwarz [EMAIL PROTECTED] wrote: Hello Joel! We were faced with similar problems in the field, too. Those were more general ones with PCMCIA/CF/SD cards. The reason was that the mobile devices (different device types with Windows CE 4.1 and 5.0) doesn't handle the access to removable media gracefully when the device is going to suspend and resuming from suspend. We had to learn (the hard way) that it's an application's task to block *any* file (reading and writing) access before the device is going to suspend until the time the device resumed from suspend *and* the removable media has been successfully remounted. The remount process can take up to 5-10 seconds! If your application doesn't handle those cases well you'll run into problems. Typically, you will see that kind of problems only in the field and not when doing in-house tests... Greetings, Christian - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting Insertion failed because database isfull. errors
OK, now I am confused... On 4/18/07, Samuel R. Neff [EMAIL PROTECTED] wrote: One thing to note is that the SQLite.NET wrapper by default issues all transactions as BEGIN IMMEDIATE so if you're running the SELECT within a transaction it will be within the context of an exclusive transaction (that's what BEGIN IMMEDIATE means, right?). I plead ignorance: I don't actually know that much about Transactions. I had asked the list before, and it seems correct to ask again: should I be using Transactions for SELECT statements? They make sense for things that change the database, but what would be the reason when reading? I am currently NOT running the SELECTs inside Transactions. You can override this by using the wrapper-specific BeginTransaction(deferred) override but it is not accessible if using the DbFactory standard interface. I completely agree with Dan that there is no way the wrapper is generating this error message, however behavior in the wrapper such as the above could be contributing to it. By Wrapper are we talking about the same thing? DRH said that it IS the wrapper causing the problem, if by the wrapper we mean the .NET Managed Provider... if not, then I am just confused... HTH, Sam Thanks Sam, -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database isfull. errors
OK, then I won't be worrying about Transactions for SELECT statements, it doesn't really apply to our application. And some additionaly confirmation that Christian seems to have been right on key: according to the problems reported at the System.Data.SQLite forums, the problem is most likely due to the retaining of an ope Connection against the database residing on removable media. When the system returns, the pointer to the media is not guaranteed to work again. In other words, every time the system shuts down, there is the potential for losing database connectivity. The recommended solution is to move the database to internal memory and use the CF card as a backup device. This also confirms why I can't replicate the problem in DEBUG: the device never sleeps and the connection is never lost. I'll keep the list posted. Joel On 4/18/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Joel Cochran [EMAIL PROTECTED] wrote: should I be using Transactions for SELECT statements? The only reason to use a transaction around SELECT statements is if you want to make sure that the database is not changed by another process in between consecutive SELECTs. It used to be the case that putting multiple SELECTs within a transaction would be slightly faster since doing so avoided a cache flush after each statement. But as of version 3.3.16 that cache flush is avoided regardless of whether or not transactions are used so performance should no longer be a factor. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database isfull. errors
OK, I understand. This was my initial instinct, that it had to be coming from the Database, which was why I contacted DRH. His reponse was basically that my symptoms didn't match a problem in SQLite, given the other information at hand, and he is correct. So really, it isn't SQLite's problem OR the wrappers problem: it is the way the device handles its handles regarding the removable media. I'm testing it now with moving the database to internal memory. Thanks, Joel On 4/18/07, Samuel R. Neff [EMAIL PROTECTED] wrote: By wrapper I think we are all talking about the managed provider, SQLite.NET. This wrapper is not generating the error message--it is being reported to the wrapper by SQLite core. It is possible that the wrapper is contributing to the problem, but it is not generating the error. The SQLite3.Reset() method in the wrapper calls sqlite_reset(), handles the schema and locked errors, and then just passes along other errors. There's nothing in the code that can actually generate the error you reported. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joel Cochran [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 9:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting Insertion failed because database isfull. errors OK, now I am confused... On 4/18/07, Samuel R. Neff [EMAIL PROTECTED] wrote: One thing to note is that the SQLite.NET wrapper by default issues all transactions as BEGIN IMMEDIATE so if you're running the SELECT within a transaction it will be within the context of an exclusive transaction (that's what BEGIN IMMEDIATE means, right?). I plead ignorance: I don't actually know that much about Transactions. I had asked the list before, and it seems correct to ask again: should I be using Transactions for SELECT statements? They make sense for things that change the database, but what would be the reason when reading? I am currently NOT running the SELECTs inside Transactions. You can override this by using the wrapper-specific BeginTransaction(deferred) override but it is not accessible if using the DbFactory standard interface. I completely agree with Dan that there is no way the wrapper is generating this error message, however behavior in the wrapper such as the above could be contributing to it. By Wrapper are we talking about the same thing? DRH said that it IS the wrapper causing the problem, if by the wrapper we mean the .NET Managed Provider... if not, then I am just confused... HTH, Sam Thanks Sam, -- Joel Cochran - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database isfull. errors
I was able to recreate this problem on the testing device (but NOT in DEBUG, of course). I pulled up the application, did some operations, and then let the device go to sleep. I then powered back up, and the first operation I tried to do threw the error. Joel On 4/18/07, Christian Schwarz [EMAIL PROTECTED] wrote: the database residing on removable media. When the system returns, the pointer to the media is not guaranteed to work again. In other words, The file handle remains perfectly valid when the media has not been removed or changed. Besides, I've observed that sometimes the media is not accessible at all after an application was trying to use one of the open file handles while the media is being remounted. Maybe that's just a sign for a poorly written driver and thus device dependent... Greetings, Christian - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database isfull. errors
On 4/18/07, Dan Kennedy [EMAIL PROTECTED] wrote:If the win32 SetFilePointer() function fails (used to position the pointer at a given file offset, which SQLite does as part of a SELECT) SQLite assumes the reason is that the disk is full and returns SQLITE_FULL. This is probably what's happening here - SetFilePointer() is saying the file-system is gone! and SQLite is misinterpreting it. Probably SQLite should return SQLITE_IOERR instead. True: the error message really through me from the beginning because you could easily surmise that the database was NOT full. So why can't you just handle this in the application? Open and close the database connection when an SQLITE_FULL occurs. If the device is really shutting down (not starting up) then the second attempt to open will fail. Or just try every couple of seconds from that point on. Dan. This was one of the suggestions we came up with, however there are dozens of places in lots of different classes and files that would need this code added. By comparison, moving the database to the internal memory store and backing it up on the Card took about 10 lines of code in one class. And others have suggested that it will perform faster from internal memory: if so, there's an added bonus. So far, my limited testing here in the office (disconnected, of course) has not produced any error. I'm going to the field tomorrow with our field tester to confirm the solution. For the first time in a long time I am confident that what we are doing is going to work. Thanks to everyone for their input and patience during this thread. I'll report back when I know more. -- Joel Cochran
Re: [sqlite] Still getting Insertion failed because database is full. errors
The saga continues... I was very excited by the idea that there was something wrong with the CF Card. The theory fits all the facts: it explains why the original database threw unspecified errors, it explains why now SQLite is throwing errors, it explains why I can't reproduce the problem in house or on my machine. It just seemed to explain everything, so yesterday I went out and bought a brand-spankin' new SanDisk CF card. I loaded it up with the database, installed it on my tester's machine, and this morning it went back out to the field for testing. Within minutes, he encountered the same error. Now I just don't believe the problem is with the card, so I feel that I am right back at square one. I'm really at my wits end and don't know what to do next. I am going to go ahead and install the database on the device memory instead of removable media, just to test it out, but I have no faith that it will change anything. When that fails, I will send the tester out with another device entirely, but again I expect the same results. I'm convinced now that the problem is with the application architecture, but I have no idea what to look at anymore. I've stared and fiddled with this code so much that I'm ready to throw in the towel. But since I'd like to keep my job that isn't an option. If I had hair, I'd pull it out. Any help at all would be appreciated. -- Joel Cochran On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Unless things have changed recently, the following should still be valid for Windows Mobile/Windows CE devices: Usually these devices do not power off, but stay in a standby state where the memory is always powered. Check if that's the case with your system and move as much as possible into RAM or a RAM disk, if that feature is provided by the windows mobile kernel built for your device. If that's not possible, I'd suggest replacing CF cards with micro drives - these are regular hard drives in a CF card format. I'm not up to date on storage space, but should be sufficient for your needs. To test the cards I'd put them in a card reader format it and fill it completely up with zeros. When a flash card erases a byte, it sets all bits to ones and upon write clears those, which need to be zero. So to test all bits you really need to zero out the entire card. This will also give the controller in the card a chance to remap bad sectors with spares. Finally you determine the file size of the card, when you receive the first write error. This is (approximately) the number of bytes the card can store (at that point in time) and falling. It seems some cards even return read errors, when they hit a defective sector upon read. Maybe the actual error code just gets lost/mangled on the way up and the actual error is just a simple read error ;) I've seen reports about this with some digital cameras, which would not even let people view the pictures taken a minute ago. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 23:44 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Still getting Insertion failed because database is full. errors You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I have something to look at. I will look into the CF problem next. The database itself gets generated on a PC and then transferred to the CF Card. During testing and development, this could have been 20-30 times a day, constantly erasing and recreating the existing DB. We have also sent large numbers of JPGs along with the database in the past (there are none now, but have been before). So these cards have been written over a lot, perhaps that is the problem. I think to test this, I will send the device back to the field with a brand new card and see if the problem persists. If the user can go several days of normal use without the problem, then I'll be convinced that it is the card. Out of curiosity I just checked the CF cards we've been using: on the development machine (which has NEVER shown the error) I have a SanDisk CF Card. On the Testing machine that is having the problem, there is a PNY Technologies CF Card. I wouldn't be surprised if the SanDisk card isn't simply better than the PNY card, so there is something else to consider. Once actual field use begins, the database will be replaced every week or so, along with a fair number of images (like 100-300 a week). The purpose of the application would have every record in the database being updated and some new ones created. And it would be that way week in and week out, essentially forever. We may eventually port it over to very small Tablet PCs, but right now it is all Windows Mobile 5. This is one of the reasons I went with SQLite, so that down the road I
Re: [sqlite] Still getting Insertion failed because database is full. errors
First, to answer John's question: the CF Card is a 1GB card, and the only thing on the card is the SQLite Database (currently 509KB), so I really don't think it is a space problem. Unless you mean something else by Flash memory? Michael, Yes, I am doing Selects, and using DataAdapters to fill DataTables (which are then used to populate instances of objects). Interesting question about the CF Card. I have 2 devices right now, one in house for development and one in the field for testing, so I suppose it could be the memory card's problem. But if it was bad sector space, wouldn't it happen on the same record every time? As it is, the problem occurs sporadically and unpredictably. I used the field device here in the office and opened over a hundred records with no error (hence my thinking I had fixed the problem). The field tester took the device to the field and the first record threw the error. And I'm still hung up on why it reports an insertion error when there is no insertion occurring (unless it is some sort of internal mechanism used by the database itself). For Mike's last question, and I hope this doesn't sound too newbie-ish, but do you run Select statements in a Transaction? The short ansewr is no, they are not. And actually, now that I think about it, I don't think any of the updates/inserts on the device itself are either. The software that creates the database runs on a PC, and I know all those are in transactions, but on the device I do not think they are. (This program was originally written for SqlServerCE and was recently converted to SQLite). Could that have anything to do with it? Could these database changes build up over time or something like that? I admit I am not very knowledgeable about Transactions. I will gladly go and put all the updates into transactions, but would I do the same for Select statements? Thanks for the responses, Joel On 4/13/07, Michael Ruck [EMAIL PROTECTED] wrote: Hi, Is this the only device seeing this error or are *all* devices seeing this error? Have you checked the CF card? May be its just the card, which is corrupt and you're hitting these bugs at points, where the file system is hitting a bad sector. Is this running in a transaction? Mike -Ursprüngliche Nachricht- Von: Joel Cochran [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 13. April 2007 17:46 An: [EMAIL PROTECTED] Betreff: [sqlite] Still getting Insertion failed because database is full. errors Hi folks, I had sent this message out a couple of weeks ago, and I am still searching for a solution. I looked at the application design and made a modest change: I now create a single SQLiteConnection and use it from start to finish of the application. Previously I was creating multiple connections (and disconnecting, disposing them and then running GC), but all that has been replaced with the single connection approach. At first I thought this had solved the problem, because all in house testing runs beautifully. However, as soon as the device is sent to the field, the error starts again. Unfortunately, it means that I have never been able to catch this in debug. I did, however, change the error reporting a little and got some more information. The SQLiteException I am not getting includes this information: Insertion failed because the database is full database or disk is full at System.Data.SQLite.SQLite3.Reset() at System.Data.SQLite.SQLite3.Step() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader.ctor() at System.Data.SQLite.SQLiteCommand.ExecuteReader() at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader() at ... etc etc I downloaded the C source and tried to read through it, but honestly I am not a C programmer and didn't get very far. Other than a possible bug, the only thing I can think of is that there is something fundamentally wrong with my architecture. What I can't get, is why the message has anything to do with inserting. While there are several actions in my product that Insert into the database, the error never occurs at those points. If I understood what was being inserted, perhaps I could figure out a soultion. If anyone can help, I'd greatly appreciate it. The original message is included below this one. TIA, Joel -- Original message -- Hi all, I have searched the web, the newsgroups, and the archives, but all I can find is one reference to someone having this trouble with Python and a bunch of references that list this as predefined error #13 according to http://www.sqlite.org/capi3.html. What I can't find is any help in determining why a program might receive this error. The database is only 203KB and has 6 tables (maybe 1,000 rows in all the tables combined) running off a 1GB CompactFlash card on a Windows Mobile 5 device with 256MB of onboard RAM: 50MB of that is dedicated to programs and the rest is storage. The only app running on the device is the one in question
Re: [sqlite] Still getting Insertion failed because database is full. errors
John, What do you mean by general processing? The database is on the CF card, the application accesses the database. Other than what application normally do (select, update, insert, etc.), I'm not sure what else to tell you. Or do you mean over the course of the lifetime of a CF card it can only be used so much? That might apply to this scenario, these cards have been written over continuously for the last 6 months. Joel On 4/13/07, John Stanton [EMAIL PROTECTED] wrote: Regular flash memory has a limited number of write cycles before it fails. Are you hitting this problem by using it for general processing?
Re: [sqlite] Still getting Insertion failed because database is full. errors
Thanks John and Dennis, At least now I have something to look at. I will look into the CF problem next. The database itself gets generated on a PC and then transferred to the CF Card. During testing and development, this could have been 20-30 times a day, constantly erasing and recreating the existing DB. We have also sent large numbers of JPGs along with the database in the past (there are none now, but have been before). So these cards have been written over a lot, perhaps that is the problem. I think to test this, I will send the device back to the field with a brand new card and see if the problem persists. If the user can go several days of normal use without the problem, then I'll be convinced that it is the card. Out of curiosity I just checked the CF cards we've been using: on the development machine (which has NEVER shown the error) I have a SanDisk CF Card. On the Testing machine that is having the problem, there is a PNY Technologies CF Card. I wouldn't be surprised if the SanDisk card isn't simply better than the PNY card, so there is something else to consider. Once actual field use begins, the database will be replaced every week or so, along with a fair number of images (like 100-300 a week). The purpose of the application would have every record in the database being updated and some new ones created. And it would be that way week in and week out, essentially forever. We may eventually port it over to very small Tablet PCs, but right now it is all Windows Mobile 5. This is one of the reasons I went with SQLite, so that down the road I wouldn;t have to reinvent the database piece of the software for a different platform. Given all this, I will definitely look into the link Dennis sent. The company is not going to be happy replacing CF cards all the time, so if that can extend the wear then it will be welcome. Thanks a lot, Joel On 4/13/07, Dennis Cote [EMAIL PROTECTED] wrote: Joel Cochran wrote: Or do you mean over the course of the lifetime of a CF card it can only be used so much? That might apply to this scenario, these cards have been written over continuously for the last 6 months. Joel, Yes, that is exactly the problem. You should look at using a flash file system such as http://sourceware.org/jffs2/ that uses wear leveling algorithms to spread the writes over all the flash devices blocks if you are writing often. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] SQL and SQLite pronounciation?
I prefer the ess cue ell version. And I can never remmeber that there is only one el, so I end up saying ess cue ell light even though I know its wrong. -- Joel Cochran On 4/4/07, Dennis Cote [EMAIL PROTECTED] wrote: Hi All, I have a simple question; how do you pronounce SQL and SQLite? I have heard some people pronounce SQL like sequel, others like squeal, and others like three words ess cue el. Which do you prefer? How about SQLite? Is it ess cue light or something else like sequel light? I prefer ess cue el and ess cue light myself. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Insertion failed because database is full.
Hi all, I have searched the web, the newsgroups, and the archives, but all I can find is one reference to someone having this trouble with Python and a bunch of references that list this as predefined error #13 according to http://www.sqlite.org/capi3.html. What I can't find is any help in determining why a program might receive this error. The database is only 203KB and has 6 tables (maybe 1,000 rows in all the tables combined) running off a 1GB CompactFlash card on a Windows Mobile 5 device with 256MB of onboard RAM: 50MB of that is dedicated to programs and the rest is storage. The only app running on the device is the one in question. The error occurs at seemingly random times and forces the user to end the application and start over. At this point I don't know much else: the user is in the field and I will have the device back late this afternoon for debugging. I was hoping to have a head start before I get the device, because as it is I have no idea what the cause could be. Any thoughts would be greatly appreciated. Thanks, -- Joel Cochran
[sqlite] Retrieve Database Metadata
Hi all, Is there a way (through SQL) to retrieve metadata about an SQLite database? Specifically I would like to find lists of tables, indexes, views, and table schema. Thanks, -- Joel Cochran
Re: [sqlite] Retrieve Database Metadata
Thank you both... I'll be looking at those... Joel On 3/29/07, Griggs, Donald [EMAIL PROTECTED] wrote: Hi Joel, Have you already looked at: http://www.sqlite.org/pragma.html#schema (as well as sqlite_master as mentioned earlier) -Original Message- From: Joel Cochran [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 1:55 PM To: sqlite-users@sqlite.org Subject: [sqlite] Retrieve Database Metadata Hi all, Is there a way (through SQL) to retrieve metadata about an SQLite database? Specifically I would like to find lists of tables, indexes, views, and table schema. Thanks, -- Joel Cochran - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Issue with trailing blanks
On 3/27/07, Robert L Cochran [EMAIL PROTECTED] wrote: Hi Joel, It's been a while since I've had to code DB2 and my memory might be faulty. But the SQL standard says char(n) has to be padded with trailing spaces, right? See this http://troels.arvin.dk/db/rdbms/ under the heading The CHAR type. Thanks Bob Cochran Hi Bob, There was never a question about WHY the blanks were there, just how they were treated in SELECT statement matching. And (not that this has anything to do with anything), on DB2 for AS/400, SQL is not typically used to create schema (or at least not has been in the past), but rather the AS/400 proprietary commands and DDS file structure. But character fields on the 400 are padded with trailing blanks, regardless of how they are created. Here is the difference: In the database, pretend I have a field called NAMEFIELD, defined as CHAR(15). In the database, it's value is 'MIKE ' SELECT * FROM MYFILE WHERE NAMEFIELD = 'MIKE' will return this row. That is NOT true for SQLite, which would require the full 15 characters, blanks and all, to return the row. That is all my original message was asking about. Thanks, -- Joel Cochran
Re: [sqlite] Issue with trailing blanks
Hi Bob, always nice to meet a fellow Cochran!
This is DB2/400 for V5R1 of OS/400, and yes it is stored in EBCDIC. I am
using the IBM supplied .Net Managed Provider to read the data into a C#
program, (which I believe automatically converts it to ASCII) and then using
the SQLite Managed Provider to insert the records into SQLite. The SQLite
version is the most recent (1.0.40?) and the platform is Windows. The data
file is then shipped to a Compact Framework application and is used on a
Windows Mobile device, so there is that platform as well.
Thanks,
--
Joel Cochran
On 3/26/07, Robert L Cochran [EMAIL PROTECTED] wrote:
What version if DB2 is this? Is your DB2 engine storing data in EBCDIC
format? What platform is the SQLite database on, and you didn't mention
the SQLite version.
If your DB2 data is EBCDIC did you convert it to ASCII before putting it
in SQLite? I want to point out the difference between an EBCDIC space
character ('X'40') and an ASCII space (X'20'). What translation table
you used if you did perform an EBCDIC to ASCII translation -- IBM offers
quite a few of them and sometimes you can get stuck with various
characters translating to X'C0' depending on the table used.
Of course, what I'm saying applies to Z/OS; it may be that AS/400 uses
ASCII by default. I'll try to check on that in the morning.
Thanks
Bob Cochran
Joel Cochran wrote:
Howdy all,
I am new to SQLite, so I hope this isn't too much of a newbie
question, but
I searched the Internet, the archives, and the help docs and could not
find
any mention of this.
I am populating an SQLite database from a legacy IBM AS/400 database.
The
400 stores all character fields with padded blanks. As a result, when I
export the data the blanks remain. Normally this is not a problem, but
I
noticed in SQLite when I do a select statement the MYFIELD = 'A' will
not
work if the data is in fact 'A ' (a trailing blank).
SQLite apparently does not ignore trailing blanks on character
matching like
other DBs do. Has anyone else experienced this, and if so what have you
done about it? I know I could rewrite the export routine to trim the
trailing blanks as they come out of the parent DB, but I was hoping
there
may be another solution. If nothing else, maybe I can find one of you
to
commiserate with!
Thanks,
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] CREATE INDEX performance
Did you create the index before or after populating the database? -- Joel Cochran On 3/27/07, Stephen Toney [EMAIL PROTECTED] wrote: Hi everyone, I'm trying to speed up index creation: CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int, value, nextword, sec, ipr, fldseq int); CREATE INDEX valuekey on keyword (value, key); The value field is a string, max 15 bytes. The key field is a string of fixed-width 10 bytes. It took only 7 minutes to fill this table with 5.7 million records, but it's taking 18 minutes to build the index. This is on a dual-core Windows XP Pro machine with 4GB memory. Any ideas on how to improve this? It will have to be done as part of a software installation, and I can't see users waiting that long. By comparison, building separate indexes on the two fields in the multi- column index took only 2-3 minutes. Why would it be so much longer for a multi-column index? Thanks for any ideas! -- Stephen Toney Systems Planning [EMAIL PROTECTED] http://www.systemsplanning.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Issue with trailing blanks
Yes, that's what I have decided to do: I am going to trim off the trailing blank on the way into SQLite. Thanks everyone, -- Joel Cochran On 3/27/07, John Stanton [EMAIL PROTECTED] wrote: Why not get a better database by stripping out the trailing spaces? Joel Cochran wrote: Hi Bob, always nice to meet a fellow Cochran! This is DB2/400 for V5R1 of OS/400, and yes it is stored in EBCDIC. I am using the IBM supplied .Net Managed Provider to read the data into a C# program, (which I believe automatically converts it to ASCII) and then using the SQLite Managed Provider to insert the records into SQLite. The SQLite version is the most recent (1.0.40?) and the platform is Windows. The data file is then shipped to a Compact Framework application and is used on a Windows Mobile device, so there is that platform as well. Thanks, - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Issue with trailing blanks
Howdy all, I am new to SQLite, so I hope this isn't too much of a newbie question, but I searched the Internet, the archives, and the help docs and could not find any mention of this. I am populating an SQLite database from a legacy IBM AS/400 database. The 400 stores all character fields with padded blanks. As a result, when I export the data the blanks remain. Normally this is not a problem, but I noticed in SQLite when I do a select statement the MYFIELD = 'A' will not work if the data is in fact 'A ' (a trailing blank). SQLite apparently does not ignore trailing blanks on character matching like other DBs do. Has anyone else experienced this, and if so what have you done about it? I know I could rewrite the export routine to trim the trailing blanks as they come out of the parent DB, but I was hoping there may be another solution. If nothing else, maybe I can find one of you to commiserate with! Thanks, -- Joel Cochran Stonewall Technologies, Inc.
Re: [sqlite] Issue with trailing blanks
Hi Puneet, I probably shouldn't have said that they 'ignore' the blanks, but they are capable of treating them as white space for text matching purposes. I can't speak for Oracle, but I'm pretty sure MySQL and SqlServer (and I know for sure DB2) all allow you to search on 'A' and they will return records for 'A ' or 'A ', etc. I think it is intuitive to treat trailing blanks as whitespace, but that might be because of my main experience with DB2/400. One difference, of course, is that these other databases allow you to define character fields with lengths, as opposed to just TEXT. I suppose that inherently means that TEXT is literal (meaning that it recognizes the blank as its ASCII character), where a CHAR(35) field would know, within the context of its defined length, how many trailing blanks it could ignore. I thought about the like idea, but 'AA' is potentially valid as well, so ultimately that will create its own problems. And in this particular case, this one is a key fields, so EQUAL matching is pretty necessary. I do think more and more that the solution for me is to trim the trailing blanks before INSERTing them into SQLite. Thanks for your input, -- Joel Cochran Stonewall Technologies, Inc. On 3/26/07, P Kishor [EMAIL PROTECTED] wrote: On 3/26/07, Joel Cochran [EMAIL PROTECTED] wrote: Howdy all, I am new to SQLite, so I hope this isn't too much of a newbie question, but I searched the Internet, the archives, and the help docs and could not find any mention of this. I am populating an SQLite database from a legacy IBM AS/400 database. The 400 stores all character fields with padded blanks. As a result, when I export the data the blanks remain. Normally this is not a problem, but I noticed in SQLite when I do a select statement the MYFIELD = 'A' will not work if the data is in fact 'A ' (a trailing blank). Try MYFIELD LIKE 'A%' SQLite apparently does not ignore trailing blanks on character matching like other DBs do. I am not sure they do. I am speaking from memory, but I am pretty sure that both SQL Server and Oracle don't just ignore blanks. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
