Change Field Data Type in SQLite DB and Use with changes
-
I have a SQLite DB attached to a VB.Net app. I have about two years of data I would like to not loose. So with DB Browser I can change the two fields from TEXT to INTEGER. Then make necessary changes in the code to reflect the changes in the DB. Below are the variables that are declared in a Data Module used for searching
Public gvYear As String Public gvFromMonth As Integer Public gvToMonth As IntegerOnly change here gvYear will become an Integer Here is the code that created the original DB
Public Sub makeTxData()
'create table TxDataTable String for cmd Dim create\_table As String = String.Empty create\_table = "CREATE TABLE IF NOT EXISTS TxData( TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, txSortDate TEXT, txYear TEXT, txType TEXT, txAmount TEXT, txCKNum TEXT, txDesc TEXT, txBalance TEXT, txSearchMonth TEXT)" Dim dbTable As String = "TxDataTable"Changes here txYear and txSearchMonth will be INTEGERS txSearchMonth int that reflects month of year Steps for Process Copy DB and Paste DB in another folder Make Changes to Code that creates the DB Make Changes in the DB manually with DB Browser Uninstall the app and make new exe file with Inno Setup with new GUID Create the new DB and delete the DB that is created then Paste the OLD DB in the new app version I am sure I am overlooking something here so I guess the question is Will this work ? Is there a better way to accomplish this ? Because this is a Check Book app I hate to loose the data. What are the risks of this happening ?
Your "Steps for Process" isn't clear on every single step, and frankly, seems like you don't understand exactly what is going on at each step, especially the last one. I'm sorry to say it, but your entire script is screaming "data loss!" Also, there is no reason to start your column names with some abbreviation of their type, called "Hungarian Notation."
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak
-
My suggestion would be to write a converter. Create a new database with a similar table but all the data items stored as the correct types: INT for numbers, TEXT for strigs, and you could even use the DATE type for dates (See Date And Time Functions[^]). The converter application would then read all the records of the old database, convert their content to the correct data types, and write the new records to a new database. You then run your modified app against the new database and reconcile the details against the old one.
-
After discovering I can not change the DB schema with DB Browser Your suggestion of writing a converter is the only option Thanks for the link about Date & Time Function Wonder if anyone has written a Open Source SQLite DB Converter time to search
-
I have a SQLite DB attached to a VB.Net app. I have about two years of data I would like to not loose. So with DB Browser I can change the two fields from TEXT to INTEGER. Then make necessary changes in the code to reflect the changes in the DB. Below are the variables that are declared in a Data Module used for searching
Public gvYear As String Public gvFromMonth As Integer Public gvToMonth As IntegerOnly change here gvYear will become an Integer Here is the code that created the original DB
Public Sub makeTxData()
'create table TxDataTable String for cmd Dim create\_table As String = String.Empty create\_table = "CREATE TABLE IF NOT EXISTS TxData( TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, txSortDate TEXT, txYear TEXT, txType TEXT, txAmount TEXT, txCKNum TEXT, txDesc TEXT, txBalance TEXT, txSearchMonth TEXT)" Dim dbTable As String = "TxDataTable"Changes here txYear and txSearchMonth will be INTEGERS txSearchMonth int that reflects month of year Steps for Process Copy DB and Paste DB in another folder Make Changes to Code that creates the DB Make Changes in the DB manually with DB Browser Uninstall the app and make new exe file with Inno Setup with new GUID Create the new DB and delete the DB that is created then Paste the OLD DB in the new app version I am sure I am overlooking something here so I guess the question is Will this work ? Is there a better way to accomplish this ? Because this is a Check Book app I hate to loose the data. What are the risks of this happening ?
Make Changes in the DB manually with DB Browser This was perhaps the simplest solution for me
I did not want to learn all the code to put these commands behind a button click procedure
DB Browse has a tab labeled Execute SQL so my first task was to create a new Column in the DB TableALTER TABLE TxData ADD NxData INTEGER
Where TxData is the TABLE name and NxData is a new column with INTEGER type data. I hope
Next I needed to write the values in the old column to the new columUPDATE TxData SET NxData = txSearchMonth
OK Now we still have a column txSearchMonth that is referenced in the SQLite searches in the application
Because this column was created as a column with TEXT it needs to be deleted
It was causing errors in my SQLite SearchesALTER TABLE TxData DROP COLUMN txSearchMonth
One last step I have not implemented that will save me from rewriting code in the searches is to RE-Name
the NxData column to txSearchMonth
Any one who wants to share a little code to use these commands with a button click feel free I am all EYE's -
It's only a few lines of code: - read next record - convert data types - write new record
-
Make Changes in the DB manually with DB Browser This was perhaps the simplest solution for me
I did not want to learn all the code to put these commands behind a button click procedure
DB Browse has a tab labeled Execute SQL so my first task was to create a new Column in the DB TableALTER TABLE TxData ADD NxData INTEGER
Where TxData is the TABLE name and NxData is a new column with INTEGER type data. I hope
Next I needed to write the values in the old column to the new columUPDATE TxData SET NxData = txSearchMonth
OK Now we still have a column txSearchMonth that is referenced in the SQLite searches in the application
Because this column was created as a column with TEXT it needs to be deleted
It was causing errors in my SQLite SearchesALTER TABLE TxData DROP COLUMN txSearchMonth
One last step I have not implemented that will save me from rewriting code in the searches is to RE-Name
the NxData column to txSearchMonth
Any one who wants to share a little code to use these commands with a button click feel free I am all EYE'sThat is one of the most dangerous processes I have ever seen. Modifying your live data base in place and even dropping columns is something I would never dream of. Also in the following statement:
UPDATE TxData SET NxData = txSearchMonth
You need to check that the
NxDatacolumn has actually been converted fromTEXTtoINTEGER, because SQLite is quite happy storing text in integer fields: see Datatypes In SQLite[^]. -
That is one of the most dangerous processes I have ever seen. Modifying your live data base in place and even dropping columns is something I would never dream of. Also in the following statement:
UPDATE TxData SET NxData = txSearchMonth
You need to check that the
NxDatacolumn has actually been converted fromTEXTtoINTEGER, because SQLite is quite happy storing text in integer fields: see Datatypes In SQLite[^].Richard I agree Modifying live data was way too scary. So I wrote a TEST app with data that did not matter Checking that NxData column is TEXT or INTEGER is on the agenda for today I am not sure how to do that. I guess select the value in the column and see if it is equal to a know INTEGER As for why I went down this route I was told because my TEXT value in the txSearchMonth was text the search I had written was failing. It was suggested I would need to rewrite the app. This was a DB design issue on my part that I am sure will not happen again! Your feedback is valued as I know you have a number of years as a professional developer It is too cold for my other hobby woodworking so back to playing programmer and shoveling my Arizona snow
-
Richard I agree Modifying live data was way too scary. So I wrote a TEST app with data that did not matter Checking that NxData column is TEXT or INTEGER is on the agenda for today I am not sure how to do that. I guess select the value in the column and see if it is equal to a know INTEGER As for why I went down this route I was told because my TEXT value in the txSearchMonth was text the search I had written was failing. It was suggested I would need to rewrite the app. This was a DB design issue on my part that I am sure will not happen again! Your feedback is valued as I know you have a number of years as a professional developer It is too cold for my other hobby woodworking so back to playing programmer and shoveling my Arizona snow
I did actuall test that earlier, and it seems to be OK. So if you enter "23" for a field that is declared as INTEGER type, SQLite will convert it to an integer. The only issue arises if the text is not a pure integer. The link I gave you above does explain how SQLite treats different data types, and is worth reading.
Choroid wrote:
shoveling my Arizona snow
I thought it was hot in the southern USA. I live 17 degrees further north and it's only cool here.
-
I have a SQLite DB attached to a VB.Net app. I have about two years of data I would like to not loose. So with DB Browser I can change the two fields from TEXT to INTEGER. Then make necessary changes in the code to reflect the changes in the DB. Below are the variables that are declared in a Data Module used for searching
Public gvYear As String Public gvFromMonth As Integer Public gvToMonth As IntegerOnly change here gvYear will become an Integer Here is the code that created the original DB
Public Sub makeTxData()
'create table TxDataTable String for cmd Dim create\_table As String = String.Empty create\_table = "CREATE TABLE IF NOT EXISTS TxData( TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, txSortDate TEXT, txYear TEXT, txType TEXT, txAmount TEXT, txCKNum TEXT, txDesc TEXT, txBalance TEXT, txSearchMonth TEXT)" Dim dbTable As String = "TxDataTable"Changes here txYear and txSearchMonth will be INTEGERS txSearchMonth int that reflects month of year Steps for Process Copy DB and Paste DB in another folder Make Changes to Code that creates the DB Make Changes in the DB manually with DB Browser Uninstall the app and make new exe file with Inno Setup with new GUID Create the new DB and delete the DB that is created then Paste the OLD DB in the new app version I am sure I am overlooking something here so I guess the question is Will this work ? Is there a better way to accomplish this ? Because this is a Check Book app I hate to loose the data. What are the risks of this happening ?
- Add a new column to your existing data base: Call it "Year"; make it an int. 2) Copy your "string" year to your int year. 3) Test with your new int year. 4) Delete the old column.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
I have a SQLite DB attached to a VB.Net app. I have about two years of data I would like to not loose. So with DB Browser I can change the two fields from TEXT to INTEGER. Then make necessary changes in the code to reflect the changes in the DB. Below are the variables that are declared in a Data Module used for searching
Public gvYear As String Public gvFromMonth As Integer Public gvToMonth As IntegerOnly change here gvYear will become an Integer Here is the code that created the original DB
Public Sub makeTxData()
'create table TxDataTable String for cmd Dim create\_table As String = String.Empty create\_table = "CREATE TABLE IF NOT EXISTS TxData( TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, txSortDate TEXT, txYear TEXT, txType TEXT, txAmount TEXT, txCKNum TEXT, txDesc TEXT, txBalance TEXT, txSearchMonth TEXT)" Dim dbTable As String = "TxDataTable"Changes here txYear and txSearchMonth will be INTEGERS txSearchMonth int that reflects month of year Steps for Process Copy DB and Paste DB in another folder Make Changes to Code that creates the DB Make Changes in the DB manually with DB Browser Uninstall the app and make new exe file with Inno Setup with new GUID Create the new DB and delete the DB that is created then Paste the OLD DB in the new app version I am sure I am overlooking something here so I guess the question is Will this work ? Is there a better way to accomplish this ? Because this is a Check Book app I hate to loose the data. What are the risks of this happening ?
Are you aware that SQLite does not statically type it's columns, so even if you declare a column as type
int, you can still insert any value into the column and SQLite will not validate for you? e.g.sqlite> create table test(i int, d date);
sqlite> insert into test(i,d) values("seven", "Hello");
sqlite> insert into test(i, d) values(7, "2023-01-01");
sqlite> select * from test;
seven|Hello
7|2023-01-01
sqlite>See [Datatypes In SQLite](https://www.sqlite.org/datatype3.html) Since that's the case, you might wish to write insert/update triggers so that invalid input is flagged before database insert/updates. That might cause significant performance degradation, though, so maybe strict data validation in the application and the data converter would be a better approach.
Keep Calm and Carry On
-
I did actuall test that earlier, and it seems to be OK. So if you enter "23" for a field that is declared as INTEGER type, SQLite will convert it to an integer. The only issue arises if the text is not a pure integer. The link I gave you above does explain how SQLite treats different data types, and is worth reading.
Choroid wrote:
shoveling my Arizona snow
I thought it was hot in the southern USA. I live 17 degrees further north and it's only cool here.
-
Are you aware that SQLite does not statically type it's columns, so even if you declare a column as type
int, you can still insert any value into the column and SQLite will not validate for you? e.g.sqlite> create table test(i int, d date);
sqlite> insert into test(i,d) values("seven", "Hello");
sqlite> insert into test(i, d) values(7, "2023-01-01");
sqlite> select * from test;
seven|Hello
7|2023-01-01
sqlite>See [Datatypes In SQLite](https://www.sqlite.org/datatype3.html) Since that's the case, you might wish to write insert/update triggers so that invalid input is flagged before database insert/updates. That might cause significant performance degradation, though, so maybe strict data validation in the application and the data converter would be a better approach.
Keep Calm and Carry On
Are you aware that SQLite does not statically type it's columns I was that is why I designed the DB with all the columns as TEXT The issue was the search variable txSearchMonth is not entered it is being read from the DB as TEXT Only the two variables gvFromMonth & gvToMonth are selected from a drop down combo box the txSearchMonth only contains 1 to 12 So because they were TEXT a search for 1 to 3 would bring 10 data along When I changed the txSearchMonth to INTEGER with these lines of code in DB Browser the search function as intended ALTER TABLE TxData ADD NxData INTEGER UPDATE TxData SET NxData = txSearchMonth ALTER TABLE TxData DROP COLUMN txSearchMonth ALTER TABLE TxData RENAME COLUMN NxData to txSearchMonth
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "
Thanks for the reply and advice
-
I used to travel to Colorado regularly and recall seeing snow at the top of Mount Evans in June.
WOW I would spend my summer in Idaho Springs, Colorado my friend owed the Clear Creek Pharmacy also would fly out from Ohio in the winter to ski YES snow in June I had a company car so started driving up Mt Evans the Chevy Impala not so good in 4 in of snow They renamed Mt. Evans to Mount Blue Sky whole other story Not so sure I like trying to changing History because it was offensive Learn from the offenses and don't repeat them guess I am not Woke ha ha
-
WOW I would spend my summer in Idaho Springs, Colorado my friend owed the Clear Creek Pharmacy also would fly out from Ohio in the winter to ski YES snow in June I had a company car so started driving up Mt Evans the Chevy Impala not so good in 4 in of snow They renamed Mt. Evans to Mount Blue Sky whole other story Not so sure I like trying to changing History because it was offensive Learn from the offenses and don't repeat them guess I am not Woke ha ha
I didn't get to Idaho Springs, but my two favourite towns were Old Colorado in Colorado Springs, and Estes Park. I worked in the UK, but corporate HQ was in Louisville, CO, so it was great to be sent out there at the company's cost; sometimes more than once in a year.
-
That is one of the most dangerous processes I have ever seen. Modifying your live data base in place and even dropping columns is something I would never dream of. Also in the following statement:
UPDATE TxData SET NxData = txSearchMonth
You need to check that the
NxDatacolumn has actually been converted fromTEXTtoINTEGER, because SQLite is quite happy storing text in integer fields: see Datatypes In SQLite[^].Richard MacCutchan wrote:
dangerous processes I have ever seen.
I was working at a company with about 300 employees. DBA seemed like a nice guy. One day I asked him where he was checking his SQL (stored procs) into source control. He had no idea what source control was. I then asked where he was doing his work day to day. On the production database...
-
Richard MacCutchan wrote:
dangerous processes I have ever seen.
I was working at a company with about 300 employees. DBA seemed like a nice guy. One day I asked him where he was checking his SQL (stored procs) into source control. He had no idea what source control was. I then asked where he was doing his work day to day. On the production database...
and that's where I walk my ass out the door.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles. Dave Kreskowiak