# handling of special characters in SQL in a macro

Hi, following on from a post(223950) I originally made on 5 Jan concerning special characters in SQL queries, I have a variation on that question. I am using LibreOffice 6.3.3.2 on Windows, and the database is Firebird 3.0. It has to do with the same SQL query, but this time I am trying to execute this request in a macro, and not as a Query

select rdb$relation_name from rdb$relations where rdb$view_blr is null and (rdb$system_flag is null or rdb$system_flag = 0); I have tried the following command in the macro strSQL = "SELECT ""rdb$relation_name"" FROM ""rdb$relations"" WHERE (""rdb$view_blr"" is null) AND (""rdb$system_flag"" is null or ""rdb$system_flag"" = 0) "


and also

strSQL = "SELECT ""rdb$relation_name"" FROM """ & "rdb$relations" & """ WHERE (""rdb$view_blr"" is null) AND (""rdb$system_flag"" is null or ""rdb$system_flag"" = 0) "  which translate into SELECT "rdb$relation_name" FROM "rdb$relations" WHERE ("rdb$view_blr" is null) AND ("rdb$system_flag" is null or "rdb$system_flag" = 0)

which seemed ok to me, but which causes a table unknown error.

Can you help, please? Full macro below:

Sub updateTables(oEvent As Object)
Dim oField As Object
Dim oForm As Object
Dim oSQL_Statement As Object
Dim strSQL As String
Dim oResult As Object

oField = oEvent.Source.Model
oForm = oField.Parent
oSQL_Statement = oForm.ActiveConnection.createStatement()
strSQL = "SELECT ""rdb$relation_name"" FROM ""rdb$relations"" WHERE (""rdb$view_blr"" is null) AND (""rdb$system_flag"" is null or ""rdb$system_flag"" = 0) " oResult = oSQL_Statement1.executeQuery(strSQL) End Sub  edit retag close merge delete ## 1 Answer Sort by » oldest newest most voted Hello, Replace this line: strSQL = "SELECT ""rdb$relation_name"" FROM ""rdb$relations"" WHERE (""rdb$view_blr"" is null) AND (""rdb$system_flag"" is null or ""rdb$system_flag"" = 0) "


with these:

sRelationName = "rdb$relation_name" sRelationTable = "rdb$relations"
sRelationView = "rdb$view_blr" sRelationFlag = "rdb$system_flag"
strSQL = "SELECT " & sRelationName & " FROM " & sRelationTable &_
" WHERE (" & sRelationView & " is null) AND (" &_
sRelationFlag & " is null or " & sRelationFlag & " = 0) "


or this also works:

strSQL = "SELECT rdb$relation_name FROM rdb$relations WHERE (rdb$view_blr is null) AND (rdb$system_flag is null or rdb\$system_flag = 0) "

more