• Welcome, Guest. Please login.
 
September 17, 2019, 10:51:20 am

News:

Welcome to the SQLitening support forums!


Any thoughts???

Started by Gary Stout, May 25, 2012, 02:43:46 am

Previous topic - Next topic

Gary Stout

May 25, 2012, 02:43:46 am Last Edit: May 25, 2012, 03:14:35 am by Gary Stout
I am still in the process of converting several programs from Tsunami to SQLitening. I am close to putting one of the programs into "normal" use, but in the converting/testing stage, I frequently will do a query and not all records are returned. I can do the same query in SQL Expert and all records are returned and I also found that I can "repair" the database in SQL Expert and my program will then retrieve all records. It makes no sense to me how SQL Expert will retrieve the record but the same query in my program doesn't, but works fine after a repair.
Is there anything that I could be doing wrong that would cause this behaviour?

Confused???
Gary

Update...It appears that newly added records are not being shown in SQL Expert queries either until "repairing" the file. I can view all data in the table with SQL Expert and the records are there (newly added at the bottom of the list), but when I do a query, the newly added records are not returned unless the file is repaired.
Gary Stout
gary@sce4u.com

Rolf Brandt

I have experienced a similar problem sometimes that might have similar a background than yours. The problem was related to the WHERE clause in a SELECT statement. It sometimes happened that
WHERE Status = 10
would show no results, but
WHERE Status = '10'
would.

After inserting a new row with SQLiteExpert it worked, all rows where shown.

I usually do not define any affinity to columns. The Status field contains values from either 1 to 100 or nothing. I wonder if SQLite might have problems with assigning the right affinity sometimes.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Rolf Brandt

You might check this page from the SQLite Web Site:
http://www.sqlite.org/datatype3.html

Look under
2.3 Column Affinity Behavior Example

Maybe the way you save new data causes the problem. I will try to check in my program too.


Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Gary Stout

Rolf,

Thanks for the link on affinity. That gives me a better idea of how sqlite handles things internally. I went into SQL Expert and took a look at the design of the table giving me problems and I have no affinity set for any of the 7 fields or columns. From what I read, it sounds like the BLOB affinity doesn't convert anything internally.....would that be recommended to use that or should I set the columns to TEXT (which is what they contain) or should affinity of NONE be sufficient???

Thanks again for your insight!
Gary
Gary Stout
gary@sce4u.com

Rolf Brandt

May 25, 2012, 10:56:07 am #4 Last Edit: May 25, 2012, 11:03:11 am by Rolf Brandt
I always use affinty=none and so far I never had any problems with it, except in a few rare case that I had described above. No matter what affinity you assign to a column it can hold any type of value - text or numeric - SQLite will use the correct affinity.

In certain cases it might be useful to set the affinity. Example: Gernam area phone codes always start with zero (0234), zip codes sometimes too. So here I would use an affinity=text. Otherwise SQLite will convert it to numerical and cut the leading zero off.

Here is where SQLiteExpert handles things different then SQLite. SQLiteExpert will always save data as text if no affinity is set.

Attached is a small database with fields set to different affinities. All received the same input - the value "0234". The first row was added with SQLiteExpert, the second one with an SQLitening tool. Notice the difference in the first field with no affinity.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Gary Stout

Thank Rolf! Your explanation is very helpful. I think I may have figured out what is going on, and although I haven't changed my code yet, I think I see what I need to do differently.

For test purposes, I just changed 2 of the column types from NONE to TEXT and that seemed to solve my issue. The data being saved is text but take a numeric value and appends a letter to beginning, in other words, data from a textbox might be 302248, so the data being saved is P302248. I am guessing that I need to enclose the entire string in single quotes when I save it to the sqlitening db. When I changed the field type to TEXT, additions and queries seemed to be working fine.

Am I on the right track???

Thanks again,
Gary
Gary Stout
gary@sce4u.com

Rolf Brandt

I think so. Maybe Fred still has an idea.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Gary Stout

Thanks..... I am going to try leaving all affinities at NONE and see if enclosing in single quotes solves the problem. I am making some changes now and will report back shortly after I do some testing.

Thanks again for pointing me to the affinity information.....I have a much better understanding now how sqlite does things internally.

Gary
Gary Stout
gary@sce4u.com

Gary Stout

It appears that making the changes in my previous post has solved the problem. I left all affinities as NONE and changed the information I was writing to the file.

I was creating the data to be written as "P" + trim$(textbox content) or as "P" + "302248". I found that if I do things the same, but enclose the entire string in single quotes, ie  "'" + "P" + "302248" + "'" that everything works fine.
As a side note, the original code also worked fine by just changing the affinity to TEXT in SQL Expert for the field causing problems.

Regardless, problem solved.....

Thanks again for pointing me in the right direction!

Gary
Gary Stout
gary@sce4u.com

Fred Meier

I also always use affinity of none.  I really like it.  I try to make sure than none numeric values are always enclosed in quotes.

I would like to know what you mean by 'repair', do you mean Vacuum?

Also see http://www.sqlitening.com/support/index.php?topic=3165.0 for more info.

Gary Stout

Quote from: Fred Meier on May 25, 2012, 01:17:37 pm
I would like to know what you mean by 'repair', do you mean Vacuum?


If you go into SQL Expert and click on the Database tab, down in the lower right corner you have options to Check, Vacuum, Re-Index or Repair.

Speaking of vacuum though...Is there a way with SQLitening to use the auto-vacuum feature of SQLite?

Thanks,
Gary
Gary Stout
gary@sce4u.com

Fred Meier

May 25, 2012, 03:26:32 pm #11 Last Edit: May 26, 2012, 01:54:58 pm by Fred Meier
OK, repair is a new feature added to SQLite Expert (pro version only), it is not a function of SQLite.  According to the SQLite Expert help file it will create a new empty database and then 'attempt' to copy all the records. I don't know the difference between this and the SQLite Vacuum.  Below is from the SQLite Expert help:
QuoteIt is recommended to choose the option to repair the database in a new file in order to preserve the old database. Click the Start button to start the repair process. SQLite Expert will create a new database and will try to copy as much data as possible in the new database. There is no guarantee that all the data will be recovered.
I use SQLite Expert and it is an excellent tool but be aware it does some things, all good I'm sure, that are not part of SQLite.


QuoteSpeaking of vacuum though...Is there a way with SQLitening to use the auto-vacuum feature of SQLite?

Yes, auto-vacuum, below is the SQLitening command:
slExe "Pragma auto-vacuum=FULL"
You will probable need to understand the other options which are NONE and INCREMENTAL.
http://www.sqlite.org/pragma.html#pragma_auto_vacuum


cj

SQLiteExpert  Pro 3 may fix a database corruption that vacuum may not like error 11 database malformed.
Would be nice to figure out exact steps SQLiteExpert performs that vacuum does not.

Bern Ertl

cj - have you tried contacting the folks who develop SQLite Expert and asking them directly?  I wonder if they are attempting to extract data from the (source) sqlite db directly (ie. not using sqlite, but parsing the file data directly with knowledge of the file format).