Handling of special characters in SQL in a macro

[nexturejohn]

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

[Ratslinger]

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) "

[nexturejohn]

thank you yet again Mr Ratslinger! The above was for a utility program I was writing to automatically generate all the necessary hyperlinks in LibreOffice for the hundreds of photographs in the hundreds of tables! Being as you have been instrumental in helping me get this program developed, I am now pleased to tell you that on Windows all now seems to be working. First indications are also that it ports quite easily to Lubuntu, I am in the process of testing it now, and hopefully all will be online soon. Thankyou once again for all your help.