Change Field Data Type in SQLite DB and Use with changes
-
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