Again on SQLite…

Getting Started
1

Here I am again with a SQLite question.
Once you have created a DB with its Table, can you modify an entity, i.e. changing it from text to integer or some other value?
DB Browser for SQLite lets you “browse” the DB but not modify it.
Is there some other app to be used or do you have to work any change thru Xojo code?

Thank you

2

You cant change a column type in SQLLite
In theory, you can ad a new column, update it using the old column, lose the old column and so on…

But the simplest method is to do the whole table:

  1. Create new_table with correct types
  2. Copy data from old_table to new_table, performing conversion and validation if need be (text from the old table may not actually contain numbers!)
  3. Drop old_table
  4. Rename new_table into old_table
3

there are lots of free sqlite editors around here that let you do this transparently
the xojo built-in editor is quite dated and sparse !

5

There is no need to change, if you want to use another datatype you “can” jut put that data in. Xojo uses the variant type to store and get the right value.

So in your database rowset you can get column(“mycolumn”).StringValue or .IntegerValue for example. Setting is done also using variants.

6

SQLite columns don’t really have types anyway. They have affinities. Read this:

1 Like
7

The CubeSQL people have an application called SQLiteManager that can edit an SQLite file, as well as a CubeSQL database-based SQLite file.

It allows you to change the data type of a field, and does all the table rebuilding for you. Here is the SQL it uses where I an changing one field from TEXT to INTEGER (alternatively just buy their application!):

PRAGMA foreign_keys = 0;

COMMIT;

BEGIN TRANSACTION;

ALTER TABLE "GeoLocation" RENAME TO sqlitemanager_temp_table_448220592407;

CREATE TABLE "GeoLocation" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "StartIPNumber" INTEGER, "EndIPNumber" INTEGER, "CountryCode" TEXT, "Country" TEXT, "State" TEXT, "City" TEXT, "Latitude" TEXT, "Longitude" TEXT, "PostCode" TEXT, "TimeZone" INTEGER);

INSERT INTO "GeoLocation" ("id","StartIPNumber","EndIPNumber","CountryCode","Country","State","City","Latitude","Longitude","PostCode","TimeZone") SELECT "id","StartIPNumber","EndIPNumber","CountryCode","Country","State","City","Latitude","Longitude","PostCode","TimeZone" FROM sqlitemanager_temp_table_448220592407;

-- Update the following indexes: 

-- EndIPNumberIndex

-- StartIPNumberIndex

DROP TABLE sqlitemanager_temp_table_448220592407;

COMMIT;

PRAGMA foreign_keys = 1;

COMMIT;
8

Or you can use the free sqlite3 command-line application from sqlite.org, which is already installed on your Mac or is a download for your Win/Lin machine. You’ll be typing in the same stuff.

9

Thanks everybody for your suggestions

1 Like
10

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.