ODBC for QuickBase

Application Note for QuNect ODBC for QuickBase

VB.Net Console Application for Uploading Files from a Directory to a Quickbase Table

This console application takes five command line arguments.

• The first argument is the name of your DSN.
• The second argument is the directory of your files.
• The third argument is the DBID of your Quickbase table.
• The fourth argument is the FID of the file attachment field.
• Additional pair of arguments of a FID followed by a value will INSERT into additional fields in the Quickbase table.

Please note that this code will delete files that have been succesfully uploaded! An example command line setting the field with a FID of 6 to 'AutoUpload' appears below. Notice that text string values must be enclosed in single quotes.

		C:\QuNect>QuNectFileUpload.exe "QuickBase via QuNect 64 bit" "C:\Users\claude\Downloads" bc45c8xjn 9 6 "'Auto Uploaded'"

Please note that the code below code will delete files that have been succesfully uploaded!

Imports System.Data.Odbc
Imports System.IO
Imports System.Text.RegularExpressions
Module upload
    Private Class qdbVersion
        Public year As Integer
        Public major As Integer
        Public minor As Integer
    End Class
    Private qdbVer As qdbVersion = New qdbVersion
    Private dbid As String
    Private fid As String
    Private QuNectConnection As OdbcConnection
    Private fids As String
    Private fidValues As String
    Sub Main(ByVal arguments As String())
        If arguments.Length < 4 Then
            Console.WriteLine("Please supply at least four command line arguments:")
            Console.WriteLine("QuNectFileUpload ""DSN"" ""Path\To\Folder"" dbid fid")
            Exit Sub
        End If
        If arguments.Length Mod 2 = 1 Then
            Console.WriteLine("Please supply an even number of command line arguments.")
            Exit Sub
        End If
        dbid = arguments(2)
        fid = arguments(3)
        Dim isFID As Regex = New Regex("^[1-9][0-9]*$")
        Dim match As Match = isFID.Match(fid)
        If Not match.Success Then
            Console.WriteLine("{0} Is not a valid fid.", fid)
            Exit Sub
        End If
        fids = "fid" & fid
        For i As Integer = 4 To arguments.Length - 1 Step 2
            fids &= ", fid" & arguments(i)
            fidValues &= ", " & arguments(i + 1)
        Next
        QuNectConnection = getquNectConn("DSN=" & arguments(0))
        If QuNectConnection Is Nothing Then
            Exit Sub
        End If
        Dim fileFolder As String = arguments(1)
        If Directory.Exists(fileFolder) Then
            ' This path is a directory.
            processFiles(fileFolder)
        Else
            Console.WriteLine("{0} is not a valid directory.", fileFolder)
            Exit Sub
        End If
        QuNectConnection.Close()
    End Sub
    Private Function getquNectConn(connectionString As String) As OdbcConnection
        Dim quNectConn As OdbcConnection
        Try
            quNectConn = New OdbcConnection(connectionString)
            quNectConn.Open()
        Catch excpt As Exception
            If excpt.Message.StartsWith("Error [IM003]") Or excpt.Message.Contains("Data source name Not found") Then
                Console.WriteLine("Please install QuNect ODBC For Quickbase from http: //qunect.com/download/QuNect.exe and try again.")
            Else
                Console.WriteLine("Could not open ODBC connection with connection string '{0}' because {1}", connectionString, excpt.Message)
            End If
            Return Nothing
            Exit Function
        End Try
        Dim ver As String = quNectConn.ServerVersion
        Dim m As Match = Regex.Match(ver, "\d+\.(\d+)\.(\d+)\.(\d+)")
        qdbVer.year = CInt(m.Groups(1).Value)
        qdbVer.major = CInt(m.Groups(2).Value)
        qdbVer.minor = CInt(m.Groups(3).Value)
        If (qdbVer.major < 6) Or (qdbVer.major = 6 And qdbVer.minor < 98) Then
            Console.WriteLine("You are running the {0} version of QuNect ODBC for QuickBase. Please install the latest version from http://qunect.com/download/QuNect.exe", ver)
            quNectConn.Close()
            Return Nothing
            Exit Function
        End If
        Return quNectConn
    End Function
    Private Sub processFiles(fileFolder As String)
        Dim fileEntries As String() = Directory.GetFiles(fileFolder)
        ' Process the list of files found in the directory.
        Dim fileName As String
        For Each fileName In fileEntries
            processFile(fileName)
        Next fileName
    End Sub
    Private Function processFile(fileName As String) As Integer
        Dim strSQL As String = "INSERT INTO " & dbid & " (" & fids & ") VALUES ('" & fileName & "'" & fidValues & ")"
        Dim quNectCmd As OdbcCommand = New OdbcCommand(strSQL, QuNectConnection)
        Dim rowsCreated As Integer = 0
        Try
            rowsCreated = quNectCmd.ExecuteNonQuery()
        Catch excpt As Exception
            Console.WriteLine("Failed: {0} {1} because: {2}", fileName, strSQL, excpt.Message)
        Finally
            quNectCmd.Dispose()
        End Try
        If rowsCreated = 1 Then
            Console.WriteLine("Success: {0}", fileName)
            System.IO.File.Delete(fileName)
        End If
        Return rowsCreated
    End Function
End Module