SQLite 3 issues with DB Insert or select

[Kimera]

Hello all,

I looked around and did not see a post about this so I am posting it. If it has been posted, sorry.

Create a DB with SQLlite 3
Add a table
CREATE TABLE T1 (c1 TEXT(10),c2 TEXT(10),c3 TEXT(10));

The Table gets created, then insert a record..
INSERT INTO T1 VALUES('C1TEXT1','C2TEXT1','C3TEXT1');

And the record gets added. You can even do a Select on it in any fashion from what I can tell. This is how it should work. This should also work...

INSERT INTO T1 (c1,c2,c3) VALUES('C1TEXT1','C2TEXT1','C3TEXT1');
INSERT INTO T1 (c1,c2,c3) VALUES('C1TEXT1','C2TEXT1','');

You can run any type of select on them you want from what I can see

but with this...
INSERT INTO T1 (c1,c2) VALUES('C1TEXT1','C2TEXT1');
You are so messed over...
SELECT * FROM T1 *** FAILS (crash the app)***
SELECT * FROM T1 WHERE ROWID > 0 *** FAILS (crash the app)***
SELECT * FROM T1 WHERE C3 > "" *** WORKS ***

Infact it fail with any select that would include the new insert where you state the column names you want in the new record, unless you state them all and make sure you state the value as '' if its blank, such as in the 3rd example shown. With tables that have 20+ columns it becomes a pain to manage them as you want.

Now you have to write a function that orders the statement based on all the columns in the table, set the values in another string and make the Insert statement always insert a value for every column each time. You realy only need to know the number of columns and the order they are in but you now don't have to state the column names. What this intended?

This means I have to rewrite the DBE I have almost finished unless I am missing something. Can anyone give me some help on this as it just does not work the way I have worked with SQL over the last 15 years.

Also I had some other questions or observations I would love some help with if anyone has the time...
* It appears the column size is not important on any column as the DB will grow as needed and shrink when ask.

* It appears that, other than blob, TEXT and INT are about the only things you need as a TEXT field will save both, as long as it's quoted, and a INT field will convert the number to a float, in fact, if quoted, text goes in to an INT column.

* It appears that it does not like table or column names with spaces in them.

* As discussed, with an insert you have to state each column value or never run a select that would include that cell data. Once the insert has been done you can just update cell data based on just that colomn and not have issues with the select if a field is blank.

Thanks, sorry so long. :blush:
K

[Kimera]

New Update...

I have run some other tests on SQLite DB, outside of MMF. I wrote a web based app that creates a DB, creates a new table, creates a record where I don't define one of the fields and it craps out on the web side as well when I do selects that include that record. It appears that the information to read table names is messed up for that record.

So, while still good information, this issue has nothing to do with the SQLite object itself but rather the SQLite DB format and I want to make that clear, the SQLite object is a great object and I think byo did a great job!!!!

[Kimera]

I figured it all out and the problem is ME!!!! :blush:

sorry!

Though I have used sql for years I have always used a GUI to create/edit my tables and as soon as I opened my GUI and started looking at the table creation I realized I missed something big, Default Values, and that was my issue. On Column creation you can state default values in a GUI and I had not seen that option in any examples of statements until I went looking for it.

Once again, sorry, my bad! :blush::whistle:

The more I learn, the more I learn I need to learn!

K

[byo]

Hi, Kimera.

I'm sorry to not have replied before. I can't believe I missed that. I should learn to use the search function more often. :/

If you ever need any help please post and I'll try my best to reply as fast as I can. These last few weeks were very busy for me having to work 16 hours a day on a deadline.

I'll post a small update to the extension as soon as I have some time available. Thank you for using the extension and for all the kind comments.

Sincerely,

Andre Guerreiro (byo)

[Kimera]

Thx, found your other post in the sql MMFDBE. We will keep that thread going and let this one fade away as it points out how stupid I was over the weekend. :WHISTLE: LOL

Thank,
K

[Bornemix]

Lol, I too had that problem