Ask Your Question

handling of special characters in SQL in a macro

asked 2020-02-06 11:40:05 +0200

nexturejohn gravatar image

updated 2020-08-03 01:40:25 +0200

Alex Kemp gravatar image

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 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. image description

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-02-06 17:53:27 +0200

Ratslinger gravatar image

updated 2020-02-06 19:40:44 +0200


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) "
edit flag offensive delete link more


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.

nexturejohn gravatar imagenexturejohn ( 2020-02-07 00:28:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-06 11:40:05 +0200

Seen: 56 times

Last updated: Feb 06 '20