如何在VBA中为PostgreSQL转义数据库表和列名称?

huangapple go评论67阅读模式
英文:

How do I escape database table and column names for PostgreSQL in VBA?

问题

我尝试将Access数据库迁移到PostgreSQL数据库,许多表名或列名中包含空格或数字,例如,表名:“test history”和“123 people”,列名:“test history”。由于这些空格和数字,我一直在遇到SQL语法错误。

只是想知道是否有办法将表名和列名的输出传输到一个字符串。

英文:

I'm trying to migrate an Access database to a PostgreSQL DB, and lots of table names or column names have space or number, for instance, table name: "test history" and "123 people", and column name: "test history". I'm keeping getting SQL syntax errors because of these spaces and numbers.

 'create tables in PostgreSQL database
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
        Dim strCreateTable As String
        strCreateTable = "CREATE TABLE " &  tdf.Name  & " ("
        For Each fld In tdf.fields
            strCreateTable = strCreateTable &  fld.Name  & " " & GetPostgreSQLDataType(fld.Type) & ","
        Next fld
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1) & ")"
        'MsgBox strCreateTable
        cnn.Execute strCreateTable
    End If
Next tdf

Just wondering if there is a way to transfer the output of table name and column name to a string.

答案1

得分: 0

I'm trying to migrate an Access database to a PostgreSQL DB.

Good (I'm of the opinion MS Access needs to die a dignified death instead of circling the drain for another 20 years...).

And lots of table names or column names have space or number, for instance, table name: "test history" and "123 people," and column name: "test history." I'm keeping getting SQL syntax errors because of these spaces and numbers.

PostgreSQL follows ANSI/ISO SQL, so it uses double-quotes " to delimit object-identifiers, whereas Access (aka JET Red and ACE) uses square-brackets [].

So it's just a matter of ensuring object-identifiers in your SQL strings are delimited with " within the VBA Strings - which isn't that bad as VBA uses "" within a string as an escape-sequence for double-quotes.

So this should work for you (I've also added some additional whitespace and NULL/NOT NULL handling too):

Sub GenerateAndExecuteCreateTableStatements()

    ' Create tables in PostgreSQL database
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
        
            Dim createStmt As String
            createStmt = GetCreateTableSql(tdf)

            ' Consider using Debug.Print instead of MsgBox to dump variables:
            Debug.Print createStmt
            cnn.Execute createStmt

        End If
    Next tdf

End Sub

Function GetCreateTableSql(tbl As DAO.TableDef)

    Dim createStmt As String
    createStmt = "CREATE TABLE """ &  tbl.Name  & """ ("
    
    Dim fld As DAO.Field
    Dim i As Integer
    i = 0
    For Each fld In tbl.Fields

        Dim column As String
        column = vbTab & """" & fld.Name & """" & " " & GetPostgreSQLDataType(fld.Type) & Iif(fld.Required, " NOT NULL", " NULL")

        If i > 0 Then
            column = "," & vbCrLf & column
        End If
        i = i + 1

        createStmt = createStmt & column
     Next
     
    ' Add closing parenthesis:
    createStmt = createStmt & vbCrLf & ")"
    
    ' Return it:
    GetCreateTableSql = createStmt

End Function
英文:

> I'm trying to migrate an Access database to a PostgreSQL DB

Good <sub>(I'm of the opinion MS Access needs to die a dignified death instead of circling the drain for another 20 years...)</sub>

> and lots of table names or column names have space or number, for instance, table name: "test history" and "123 people", and column name: "test history". I'm keeping getting SQL syntax errors because of these spaces and numbers.

PostgreSQL follows ANSI/ISO SQL so it uses double-quotes &quot; to delimit object-identifiers, whereas Access (aka JET Red and ACE) uses square-brackets [].

So it's just a matter of ensuring object-identifiers in your SQL strings are delimited with &quot; within the VBA Strings - which isn't that bad as VBA uses &quot;&quot; within a string as an escape-sequence for double-quotes.

So this should work for you (I've also added some additional whitespace and NULL/NOT NULL` handling too):

Sub GenerateAndExecuteCreateTableStatements()

    &#39; Create tables in PostgreSQL database
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) &lt;&gt; &quot;MSys&quot; Then
        
            Dim createStmt As String
            createStmt = GetCreateTableSql(tdf)

            &#39; Consider using Debug.Print instead of MsgBox to dump variables:
            Debug.Print createStmt
            cnn.Execute createStmt

        End If
    Next tdf

End Sub

Function GetCreateTableSql(tbl As DAO.TableDef)

    Dim createStmt As String
    createStmt = &quot;CREATE TABLE &quot;&quot;&quot; &amp;  tbl.Name  &amp; &quot;&quot;&quot; (&quot;
    
    Dim fld As DAO.Field
    Dim i As Integer
    i = 0
    For Each fld In tbl.Fields

        Dim column As String
        column = vbTab &amp; &quot;&quot;&quot;&quot; &amp; fld.Name &amp; &quot;&quot;&quot;&quot; &amp; &quot; &quot; &amp; GetPostgreSQLDataType(fld.Type) &amp; Iif(fld.Required, &quot; NOT NULL&quot;, &quot; NULL&quot;)

        If i &gt; 0 Then
            column = &quot;,&quot; &amp; vbCrLf &amp; column
        End If
        i = i + 1

        createStmt = createStmt &amp; column
     Next
     
    &#39; Add closing parenthesis:
    createStmt = createStmt &amp; vbCrLf &amp; &quot;)&quot;

    &#39; Return it:
    GetCreateTableSql = createStmt

End Function

huangapple
  • 本文由 发表于 2023年3月7日 07:58:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/75656892.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定