Android Question Change the type in a column (Sqlite Table)

[Espinosa4]

Hello everybody,

Can I change the type or size in a column? For example from Char(1) to Char(2) or Char(1) to Integer

Thank you very much in advance

 

[Erel]

SQLite data types: http://www.sqlite.org/datatype3.html

The column data type in SQLite is not really important. You can store any value you like.

 

[Espinosa4]

Thanks Erel for your reply, but the size?
Can I store two char in a field with size char(1)?

 

[eps]

Thanks Erel for your reply, but the size?
Can I store two char in a field with size char(1)?

Are you sure you've defined it as Char(1)? Typically they're set as Text and then you can enter as much or as little as you want (well within the DB constraints) I think within SQLite they're just set up as TEXT anyway, as the Affinity.

 

[Erel]

There is no such type as char(1) in SQLite. See the above link.

 

[Espinosa4]

Ummmm yes eps, I defined as char(1) I needed to store only a char but now I need to store two. I know I know but when you store a text field has more size than a Char(1) in the database. Am I right?

        sSql.ExecNonQuery("CREATE TABLE IF NOT EXISTS ConfLaboral(DesdeFecha Date,HastaFecha Date,TipoServicio Char(2))")

I undertand that I can store a text in a Char(1) field. In SQLite Debugger (Android APP) I can see that the type is Char(1)

Thanks to both for your replies

 

[Erel]

Not sure what can we answer...

There is no such type in SQLite. SQLite has converted it automatically to TEXT.

If you prefer not to follow the link I posted above then I can post a screenshot instead:

 

[Espinosa4]

Thank you Erel, now I understand that I can store text there.
Best regards.

 

[JOTHA]

Hello Erel, you said:

The column data type in SQLite is not really important.

But I have to change the column type, because I want to find out the maximum and the minimum value in a column, and the code doesn't give me the correct result if the column type is TEXT.

I attached the code, so you can test it:

#Region  Project Attributes
    #ApplicationLabel: MUSTER - MinMax
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region
'-------------------------------------------------------------------------------------------------------------------------
#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region
'-------------------------------------------------------------------------------------------------------------------------
Sub Process_Globals
Dim SQL1 As SQL
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub Globals
    Private ButtonMax As Button
    Private ButtonMin As Button
    Private LabelMax As Label
    Private LabelMin As Label
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub Activity_Create(FirstTime As Boolean)
Activity.LoadLayout("Panel1LayoutMinMax")
If FirstTime Then
SQL1.Initialize(File.DirDefaultExternal, "MinMax.sl3", True)
End If
Create_Table
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub Activity_Resume
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub Activity_Pause (UserClosed As Boolean)
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub Create_Table
SQL1.ExecNonQuery ("DROP TABLE IF EXISTS MinMaxTable")
SQL1.ExecNonQuery ("CREATE TABLE  IF NOT EXISTS MinMaxTable (Position TEXT, Numbers TEXT)")  '--> Change the ColumnType "Numbers TEXT" into "Numbers FLOAT"  --> CORRECT!
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('001', '123')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('002', '120')")  '--> Minimum Value if ColunmType "Numbers" is TEXT --> WRONG!
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('003', '999.9')")  '--> Maximum Value if ColunmType "Numbers" is TEXT --> WRONG!
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('004', '1234')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('005', '345')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('006', '678')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('007', '121')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('008', '99')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('009', '199')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('010', '299')")
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('011', '9.9')")  '-- Minimum Value if ColunmType "Numbers" is FLOAT --> CORRECT!
SQL1.ExecNonQuery ("INSERT INTO MinMaxTable VALUES ('012', '1999')")  '-- Maximum Value if ColunmType "Numbers" is FLOAT --> CORRECT!
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub ButtonMin_Click
    Dim ReturnValue As Float
    ReturnValue = SQL1.ExecQuerySingleResult("SELECT MIN (Numbers) AS MinValue FROM MinMaxTable")
    LabelMin.Text = ReturnValue
End Sub
'-------------------------------------------------------------------------------------------------------------------------
Sub ButtonMax_Click
    Dim ReturnValue As Float
    ReturnValue = SQL1.ExecQuerySingleResult("SELECT MAX (Numbers) AS MaxValue FROM MinMaxTable")
    LabelMax.Text = ReturnValue
End Sub
'-------------------------------------------------------------------------------------------------------------------------

So please can you tell me, how I can change the column type in an existing SQLite table by B4A (code)?

 

[Mahares]

Without changing the column type, did you try the CAST function:

ReturnValue = SQL1.ExecQuerySingleResult("SELECT MIN(CAST(Numbers AS REAL)) AS MinValue FROM MinMaxTable")

 

[JOTHA]

Hi Mahares,

I tried your solution, it is working!
Thank you very much!