-
May 19th, 2018, 07:12 AM
#1
Thread Starter
PowerPoster
[RESOLVED] Insert, delete, and rename the fields of a SqliteDB table.
I need to insert, delete, and rename the fields of a sqlite data table. My methods are as follows
CreateNewTable:
The sqlite data table Test has the following fields:
ID, Field1, Field2, Field3, Field4
Code:
Option Explicit
Private MemDB As cMemDB
Private Sub CreateNewTable()
Dim i As Long
Set MemDB = New_c.MemDB 'one can instantiate and hold this also in a global variable
'this block is not much more typing, than a VB6-UDT-definition
With MemDB.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "Field1 Text"
.Add "Field2 Text"
.Add "Field3 Text"
.Add "Field4 Text"
End With
MemDB.CreateTable "Test"
End Sub
InsertFieldToSqliteTable: (Insert the Field5 to the table Test)
Code:
Private Sub InsertFieldToSqliteTable()
Dim sSQL As String
sSQL = "Alter Table Test Add Column Field5"
MemDB.Exec sSQL
sSQL = "Create Table Temp As Select ID, Field1,Field5, Field2, Field3, Field4 From Test Where 1=1"
MemDB.Exec sSQL
MemDB.Exec "Drop Table Test"
MemDB.Exec "Alter Table Temp Rename to Test"
MsgBox "Successfully inserted Fielde5 before Field2"
End Sub
RemoveFieldFromSqliteTable: (Remove the Field3 from the table Test)
Code:
Private Sub RemoveFieldFromSqliteTable()
Dim sSQL As String
sSQL = "Create Table Temp As Select ID, Field1, Field2, Field4 From Test Where 1=1"
MemDB.Exec sSQL
MemDB.Exec "Drop Table Test"
MemDB.Exec "Alter Table Temp Rename to Test"
MsgBox "Fielde3 was successfully removed !"
End Sub
RenameSqliteTableFieldName (Rename the Field4 to the Field5)
Code:
Private Sub RenameSqliteTableFieldName()
Dim sSQL As String
sSQL = "Create Table Temp As Select ID, Field1, Field2, Field3, Field4 As Field5 From Test Where 1=1"
MemDB.Exec sSQL
MemDB.Exec "Drop Table Test"
MemDB.Exec "Alter Table Temp Rename to Test"
MsgBox "Renamed Fielde4 to Field5 successfully"
End Sub
I wonder if there are any easier ways to implement the above operations? Thanks.
Last edited by dreammanor; May 19th, 2018 at 09:19 AM.
-
May 19th, 2018, 07:38 AM
#2
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
Sorry, it seems that SqliteDB does not support "Select ... Into ...". Perhaps I need to import all the records in the table Test into the table Temp one by one using "Do While ... Loop"
Edit:
According to the dilettante and wqweto's prompts, I removed "Select ... Into..." and now the test program is working. My question is whether there are any simpler ways to achieve the operations on the #1 floor.
Last edited by dreammanor; May 19th, 2018 at 09:13 AM.
-
May 19th, 2018, 08:09 AM
#3
Re: Insert, delete, and rename the fields of a SqliteDB table.
Maybe CREATE TABLE {new} AS {subquery} or something similar.
-
May 19th, 2018, 08:31 AM
#4
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
Originally Posted by dilettante
Maybe CREATE TABLE {new} AS {subquery} or something similar.
Yes, you are right, thank you very much, dilettante. I modified RemoveFieldFromSqliteTable and RenameSqliteTableFieldName according to your tips, but InsertFieldToSqliteTable still has no good solution.
Last edited by dreammanor; May 19th, 2018 at 09:43 AM.
-
May 19th, 2018, 08:51 AM
#5
Re: Insert, delete, and rename the fields of a SqliteDB table.
-
May 19th, 2018, 08:58 AM
#6
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
Originally Posted by wqweto
Yes, thank you wqweto. I modified InsertFieldToSqliteTable according to your tips.
Last edited by dreammanor; May 19th, 2018 at 09:43 AM.
-
May 19th, 2018, 09:05 AM
#7
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
According to the dilettante and wqweto's prompts, I removed "Select ... Into..." and now the test program is working. My question is whether there are any simpler ways to achieve the operations on the #1 floor.
-
May 19th, 2018, 09:14 AM
#8
Re: Insert, delete, and rename the fields of a SqliteDB table.
Originally Posted by dreammanor
Sorry, it seems that SqliteDB does not support "Select ... Into ...".
It does support something similar via either:
- "Insert Into ... Select ..."
or if it goes along with a (temp) table-creation then:
- "Create Table myTempTable As Select ..."
Here is an example which shows how to add, rename and remove a field:
Code:
Option Explicit
Private MemDB As cMemDB
Private Sub Form_Click()
Set MemDB = New_c.MemDB 'one can instantiate and hold this also in a global variable
CreateNewTable "Test": PrintTblDef "Test"
InsertFieldInto "Test", "Field5 Text": PrintTblDef "Test"
RenameFieldOn "Test", "Field3", "FX": PrintTblDef "Test"
RemoveFieldFrom "Test", "FX": PrintTblDef "Test"
End Sub
Private Sub CreateNewTable(TableName$)
With MemDB.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "Field1 Text"
.Add "Field2 Text"
.Add "Field3 Text"
.Add "Field4 Text"
End With
MemDB.CreateTable TableName
End Sub
Private Sub InsertFieldInto(TableName$, FieldDef$)
MemDB.Exec "Alter Table [" & TableName & "] Add Column " & FieldDef
MemDB.Cnn.DataBases(1).ReScanSchemaInfo
End Sub
Private Sub RenameFieldOn(TableName$, OldField$, NewField$)
If Left$(OldField, 1) <> "[" Then OldField = "[" & OldField & "]"
If Left$(NewField, 1) <> "[" Then NewField = "[" & NewField & "]"
MemDB.Exec "Create Table tmpR As Select " & _
Replace(GetFieldList(TableName), OldField, OldField & " " & NewField, , , 1) & " From " & TableName
MemDB.Exec "Drop Table [" & TableName & "]"
MemDB.Exec "Alter Table tmpR Rename to [" & TableName & "]"
MemDB.Cnn.DataBases(1).ReScanSchemaInfo
End Sub
Private Sub RemoveFieldFrom(TableName$, FieldName$)
If Left$(FieldName, 1) <> "[" Then FieldName = "[" & FieldName & "]"
MemDB.Exec "Create Table tmpR As Select " & _
Replace(GetFieldList(TableName), "," & FieldName, "", , , 1) & " From " & TableName
MemDB.Exec "Drop Table [" & TableName & "]"
MemDB.Exec "Alter Table tmpR Rename to [" & TableName & "]"
MemDB.Cnn.DataBases(1).ReScanSchemaInfo
End Sub
Private Function GetFieldList(TableName$) As String
Dim Rs As cRecordset, F As cField, L As cArrayList
Set Rs = MemDB.GetTable(TableName, "1=0")
Set L = New_c.ArrayList(vbString)
For Each F In Rs.Fields: L.Add F.Name: Next
GetFieldList = "[" & L.Join("],[") & "]"
End Function
Private Sub PrintTblDef(TableName$)
Debug.Print MemDB.Cnn.DataBases(1).Tables(TableName).SQLForCreate
End Sub
HTH
Olaf
-
May 19th, 2018, 09:32 AM
#9
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
Wonderful, this is exactly what I want. Thank you very much, Olaf.
One thing needs to be explained:
Your InsertFieldInto inserts the new field into the last column of the table. My InsertFieldToSqliteTable can insert new fields before a existed column.
-
May 19th, 2018, 09:47 AM
#10
Re: Insert, delete, and rename the fields of a SqliteDB table.
Originally Posted by dreammanor
One thing needs to be explained:
Your InsertFieldInto inserts the new field into the last column of the table. My InsertFieldToSqliteTable can insert new fields before a existed column.
Yep, my function-signature should perhaps be renamed as:
Code:
Private Sub AddFieldTo(TableName$, FieldDef$)
MemDB.Exec "Alter Table [" & TableName & "] Add Column " & FieldDef
MemDB.Cnn.DataBases(1).ReScanSchemaInfo
End Sub
And sure, it adds (a complete Field-Def, including the Field-Type BTW) always to the end of the existing Field-List -
but it is a relative "un-expensive" operation (doesn't do any Data-Copying).
What you currently have, does not support a Field-Type-Definition for your new inserted Field.
If you really *have to* ensure a certain Field-order in your Table-Fields, then I'd suggest to write a dedicated
Routine for it (e.g. named ReOrderFieldsOn(TableName$, NewFieldList$) - then called after an AddFieldTo, if you want)...
Olaf
-
May 19th, 2018, 08:40 PM
#11
Thread Starter
PowerPoster
Re: Insert, delete, and rename the fields of a SqliteDB table.
Originally Posted by Schmidt
What you currently have, does not support a Field-Type-Definition for your new inserted Field.
If you really *have to* ensure a certain Field-order in your Table-Fields, then I'd suggest to write a dedicated
Routine for it (e.g. named ReOrderFieldsOn(TableName$, NewFieldList$) - then called after an AddFieldTo, if you want)...
Very good advice.
I plan to use RC5.RecordSet(MemDB.GetTable) as the data storage container for my new grid control, so I need to insert another columns (of the grid control or a MemDB.Table) before a existed column (of the grid control or a MemDB.Table).
In addition, because the cell data types in a grid column may not be the same (for example, it may be number, string, or picture), the Sqlite Field-Type must be variant type.
I don't know if it's a good idea to use RC5.RecordSet(MemDB.GetTable) instead of the RC5.Collection as a data storage container for my new Grid control.
Last edited by dreammanor; May 19th, 2018 at 11:43 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|