SQL Server / VB 6 Question 4

[AndyLee100]

Hi All,

Can someone help me with the best way to do the following.

From time to time we need to add a new field into a SQL database to go with a new (VB 6) executable that we distribute to our customers.

However we do not have a dial in to this customer and they will not send the database to us for us to amend and they are a long drive away.

What is the best way for us to programtically alter the database to include the new fields, while obviously preserving the data.

The resulting application would be a single button click to add the new fields and it would be great if it could first check to see if any users are connected and disconnect them from the database.

Any help in pointing me in the right direction would be appreciated.

Thanks

andy

 

[gmmastros]

We have a similar problem in our app. Each version of our software usually has some sort of changes to the db structure. Typically, we add fields, views, and/or stored procedures.

To accomodate this, we have a table in our database named 'Config'. In this table, we have 2 fields, ConfigName and ConfigValue. This table stores info that doesn't really require it's own table. One record in the table stores DatabaseVersion. Every time our app is started, we check the 'DatabaseVersion' record from this table. If it's an old version, we go about updating the database structure.

You don't want to 'simply' go adding fields to tables because the field could already exist. So you must be careful when doing this. Here's a SQL Script that may help.

If Not Exists(Select * from INFORMATION_SCHEMA.COLUMNS Where Table_Name = 'MyTable')
  Begin
	Create Table MyTable(MyId Integer Identity(1,1), MyField1 Integer, MyField2 VarChar(10))
  End
Else
  Begin
	If Not Exists(Select * 
            from   INFORMATION_Schema.Columns 
            Where  TABLE_NAME = 'MyTable' 
                   and Column_Name = 'MyField1')
    Begin
      -- Field does not exist
      Alter Table MyTable Add MyField1 Integer
    End

    If Not Exists(Select * From INFORMATION_SCHEMA.Columns Where TABLE_NAME='MyTable' And COLUMN_NAME='MyField2')
      Alter Table MyTable Add MyField2 VarChar(10)

  End

The sql first checks to see if the table exists. If it does not exist, it will be created. If the table does exist, then each field is checked. If the field does not exist, it will be created for you.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Golom]

One of my apps routinely needs to make structural changes to databases to accommodate changed functionality. To that end I've written a module that reads a flat file that contains DDL (Data Definition Language) statements and executes them. Here's some of the code just to give you some ideas

[COLOR=green]
'----------------------------------------------------------
' Module    : modUpgradeTables
' Purpose   : This module performs upgrades to Tables if any are pending.
'----------------------------------------------------------[/color]

Option Explicit
Option Compare Text

Public mvarStopReading      As Boolean
Public nH_Updates           As Integer
Dim Echo                    As Boolean
Dim Process                 As Boolean

Public Sub UpGradeTables()
    Dim fso                 As FileSystemObject
    Dim nH                  As Integer
    Dim ActionLine          As String
    Dim Sections()          As String
    Dim ParmVal()           As String
    Dim DDLCmdMode          As Boolean
    Dim DDLCmdDB            As String
    Dim DDLCmdBuffer        As String
    Dim InFile              As String
    Dim OutFile             As String
    Dim STimer              As Double
    Dim nLines              As Long
    Dim ByPassMode          As Boolean
    Dim AddQueryMode        As Boolean
    Dim AddQueryName        As String
    Dim AddQueryDB          As String
    Dim AddQueryBuffer      As String

    STimer = Timer
    Set fso = New FileSystemObject

    Echo = True
    Process = True

    If fso.FileExists(StartPath & "\UpGradeTables.txt") Then

        fso.CopyFile StartPath & "\UpGradeTables.txt", StartPath & "\UpGradeTablesP.txt"
        fso.CopyFile StartPath & "\UpGradeTables.txt", StartPath & "\UpGradeTablesOld.txt"
        fso.DeleteFile StartPath & "\UpGradeTables.txt"

    End If

    InFile = StartPath & "\UpGradeTablesP.txt"
    OutFile = StartPath & "\LogUpdatesP.txt"

    If fso.FileExists(InFile) Then

        nH_Updates = FreeFile
        Open OutFile For Output As #nH_Updates
        Print #nH_Updates, "UPDATE FILES on " & Format(Now, "dddd, mmmm dd yyyy  hh:nnAMPM")
        Print #nH_Updates,
        nH = FreeFile
        Open InFile For Input As #nH

        Do Until EOF(nH)

            Line Input #nH, ActionLine
            nLines = nLines + 1
            If Echo Then Print #nH_Updates, PadR(nLines, 5) & ActionLine

            ' IGNORE BLANK LINES OR LINES STARTING WITH ";"
            If Len(Trim$(ActionLine)) > 0 And Left$(Trim$(ActionLine), 1) <> ";" Then
                
                ' DDLCmdMode = TRUE if we're inside an ACTION clause
                If DDLCmdMode Then

                    If InStr(1, ActionLine, "ENDDDLCOMMAND") > 0 Then
                        DDLCmdMode = False
                        If Process Then
                            ProcessDDL DDLCmdDB, DDLCmdBuffer
                        Else
                            If Echo Then Print #nH_Updates, "     *** NOT PROCESSED ***"
                        End If
                    Else
                        DDLCmdBuffer = DDLCmdBuffer & " " & ActionLine
                    End If

                ElseIf ... Other Clauses Deleted ...

                Else

                    If Len(Trim$(ActionLine)) > 0 And InStr(1, Trim$(ActionLine), ";") > 0 Then

                        Sections = Split(Trim$(ActionLine), ";")

                        ParmVal = Split(Sections(0), "=")

                        If UBound(ParmVal) = 1 Then

                            Select Case ParmVal(1)

                                
                                Case "DDLCOMMAND"

                                    ParmVal = Split(Sections(1), "=")
                                    If UBound(ParmVal) = 1 Then
                                        DDLCmdMode = True
                                        DDLCmdDB = ParmVal(1)
                                        DDLCmdBuffer = ""
                                    ElseIf UBound(ParmVal) = 0 Then
                                        ByPassMode = True
                                        If Echo Then Print #nH_Updates, "*** MISSING EQUAL SIGN ***"
                                    Else
                                        ByPassMode = True
                                        If Echo Then Print #nH_Updates, "*** TOO MANY EQUAL SIGNS ***"
                                    End If
                                    
                            ' .... CODE DELETED ...

                            End Select

                        End If

                    End If
                End If
            End If

        Loop

        Print #nH_Updates,
        Print #nH_Updates, "Processing Time: " & Format(Timer - STimer, "0.00") & " seconds"
        Close nH_Updates
        Close nH

        fso.DeleteFile InFile

    End If

    Set fso = Nothing

End Sub

And here are two of the several routines that may be called to run different commands.

Private Function GetTheDatabase(dbname As String) As DAO.Database
    Dim db  As DAO.Database
    Dim fso As New FileSystemObject
    
    Set db = Nothing
    On Error GoTo NoDB
    
    With DAO.DBEngine(0)
        Select Case dbname
            Case "Trans":   Set db = .OpenDatabase(Data_Path & "Trans.mdb")
            Case "Archive": Set db = .OpenDatabase(Data_Path & "Archive.mdb")
            Case "Master":  Set db = .OpenDatabase(Data_Path & "ChapsMast.mdb")
            Case "Control": Set db = .OpenDatabase(Data_Path & "ChapsCtrl.mdb")
            Case "CurSale": Set db = .OpenDatabase(Data_Path & "CurSale.mdb")
            Case "Core":    Set db = .OpenDatabase(Core_Path & "ChapsCore.mdb")
            Case "BackUp":  Set db = .OpenDatabase(Data_Path & "BackUp.mdb")
            Case "Tills":   Set db = .OpenDatabase(Data_Path & "Tills.mdb")
            Case Else
                If Not fso.FileExists(Data_Path & dbname) Then GoTo NoDB
                Set db = .OpenDatabase(Data_Path & dbname)
        End Select
    End With
    Set GetTheDatabase = db
    GoTo CloseObjects
    Exit Function

NoDB:

    If Echo Then Print #nH_Updates, Space(5) & "Database '" & dbname & "' does not exist."
    Set GetTheDatabase = Nothing
    
CloseObjects:
    Set fso = Nothing
    Set db = Nothing

End Function


Private Sub ProcessDDL(CreateDB As String, DDLCmdBuffer As String)
    Dim db          As DAO.Database
    Dim SubCmd()    As String
    Dim n           As Integer
    
    Set db = GetTheDatabase(CreateDB)

    If Not db Is Nothing Then
        SubCmd = Split(DDLCmdBuffer, ":")
        On Error Resume Next
        
        For n = 0 To UBound(SubCmd)
        
            Err.Clear
            If Len(Trim$(SubCmd(n))) > 0 Then db.Execute SubCmd(n)
    
            If Echo Then
                If Err.Number <> 0 Then
                    Print #nH_Updates, Space(5) & Err.Number & " - " & Err.Description
    
                Else
                    Print #nH_Updates, Space(5) & db.RecordsAffected & " RECORDS AFFECTED"
    
                End If
            End If
        Next n
    End If

    Set db = Nothing

End Sub

The input file may look like this

Action=DDLCOMMAND; DataBase=Master
ALTER TABLE RecLine
ADD COLUMN [InStock] Long;
Action=ENDDDLCOMMAND


Action=DDLCOMMAND; DataBase=Master
ALTER TABLE StockTakeHead
ADD COLUMN [StockTakeType] Text(2);
Action=ENDDDLCOMMAND

This code is used for an Access database but the modifications required for SQL Server should be minor.

 

[AndyLee100]

Thank you for both of your suggestions and the time you took on them.

This has definately given me ideas on the way forward.

Both of you have a star

Andy

 

[tsdragon]

Both very useful! Thanks!


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.