英文:
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 "
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论