Results 1 to 11 of 11

Thread: [RESOLVED] Insert, delete, and rename the fields of a SqliteDB table.

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Resolved [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.

  2. #2

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    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.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Maybe CREATE TABLE {new} AS {subquery} or something similar.

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Quote Originally Posted by dilettante View Post
    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.

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Insert, delete, and rename the fields of a SqliteDB table.


  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Yes, thank you wqweto. I modified InsertFieldToSqliteTable according to your tips.
    Last edited by dreammanor; May 19th, 2018 at 09:43 AM.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    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.

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Quote Originally Posted by dreammanor View Post
    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

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    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.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Quote Originally Posted by dreammanor View Post
    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

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Insert, delete, and rename the fields of a SqliteDB table.

    Quote Originally Posted by Schmidt View Post
    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
  •  



Click Here to Expand Forum to Full Width