I’m in the process of converting a front-end Microsoft Access application to Xojo. At this stage I’m sticking with the the Access tables but in time I can’t see why I couldn’t use SQLite tables.
I’ve read ahead regarding SQLite and I’ve noticed that it is not possible to alter fields (see here).
Not that altering fields after data has been entered is a daily occurrence but how would this be done where SQLite is involved, what is the best work-around?
I had this issue a while ago and I simply used some third party DB browser to change a field name.
Because it was not a common application usage (and the fact it only happened twice) it was acceptable for me.
I am guessing though you want to do this programatically?
From memory you can change the name of and add a field in SQLite and not much else but the only time I use it is for storing attributes with desktop apps so I am certainly not able to help there.
Mike Bailey has a great example project “SQLite Schema Editor”
The SQLiteSchemaEditor class simplifies the process of making changes to your database schema in code. This class can handle changes such as dropping and changing columns, that are not built into the SQLite engine.
My problem is that I don’t have access (pardon the pun) to all of the backend access databases and it would have to be performed programmatically. Presently, I have one nagging text field that requires the text length to be increased and this will only be achieved during an upgrade routine. For the backend databases I have access to I would perform this function myself but then there are other backend databases in which I don’t have permissions (and can’t get access permissions - sensitive material) and these files might not be opened for a week or two or three, so only an automatic upgrade process will do.
As I said, I’m reading ahead here as I will whilst still in Access mode perform the field change, though there is a chance that not all backend databases will be so modified prior to a change to SQLite database.
Sqlite doesn’t impose length limits. You can store 1000 chars in a char(10) field.
Not sure if this a good thing or not though it shouldn’t pose a problem for my field that needs lengthening.
Hi,
It happens to me to have to add one Column to a Record of a SQLite db. I used Export as Text, generate a brand new db with the changes applyed in the Create Table Method, and Import from Text".
It does not take so much times to do the whole.
If the things have to be done on a regular basis or many times a year…
[quote=15008:@Steven Taylor]My problem is that I don’t have access (pardon the pun) to all of the backend access databases and it would have to be performed programmatically. Presently, I have one nagging text field that requires the text length to be increased and this will only be achieved during an upgrade routine. For the backend databases I have access to I would perform this function myself but then there are other backend databases in which I don’t have permissions (and can’t get access permissions - sensitive material) and these files might not be opened for a week or two or three, so only an automatic upgrade process will do.
As I said, I’m reading ahead here as I will whilst still in Access mode perform the field change, though there is a chance that not all backend databases will be so modified prior to a change to SQLite database.[/quote]
SQLite works very differently than nearly any other database. For starters, as Tim mentioned above, field length is not used. In fact, SQLite really only has four types: INTEGER, TEXT, REAL and BLOB. And even that is not accurate because SQLite doesn’t actually have types. You can put text in an INTEGER column without any complaints: http://www.sqlite.org/datatype3.html
And as you’ve found, you cannot alter column names on an existing table. The standard way around this problem is like this:
You can use Valentina Studio to resolve this task very easy.
VStudio makes a copy of table as rquired, then copy records if they exists.
So virtually all this looks like table was altered.