Invantive UniversalSQL Server provides real-time connectivity using the TDS-protocol of Microsoft SQL Server to over 80 (cloud)platforms such as Freshdesk, Freshservice, ActiveCampaign, Salesforce, Twinfield and others.
All SQL-statements are executed as Invantive UniversalSQL. Many SQL Server client programs will run without any changes. However, SQL Server programs can also use SQL-statements that are not compatible with Invantive UniversalSQL. A built-in preprocessor covers many use cases, but not all.
This topic explains how to extend the pre-processor of Invantive UniversalSQL Server by your own logic. The topic is based on the first steps to get Sumatra 2022 working on an Invantive database.
Add database
After instalaltion of Sumatra 2022, the web manager is started and a database is added:
Specification of new credentials fails with itgensql005: Unsupported function 'HAS_PERMS_BY_NAME' found... as shown below:
The SQL statement involved can be retrieved from Elastic, Invantive Trace or the monitor. Using Monitor, the SQL statement was quickly found:
to be:
select name from sys.databases where HAS_PERMS_BY_NAME('[' + replace(name,']',']]') + ']', 'DATABASE', 'SELECT') = 1
In the appsettings.json of Invantive UniversalSQL Server a section is introduced to extend the preprocessor as follows:
, "QueryRewriteRules":
[ { "Code": "rle001"
, "SortingOrder": "A001"
, "RegexPattern": "select name from sys\\.databases where HAS_PERMS_BY_NAME\\('\\[' \\+ replace\\(name,'\\]','\\]\\]'\\) \\+ '\\]', 'DATABASE', 'SELECT'\\) = 1"
, "Replacement": "select 'master'"
, "NextCodeOnMatch": "EXIT"
, "NextCodeOnNoMatch": null
}
, { "Code": "EXIT"
, "SortingOrder": "EXIT"
, "RegexPattern": "xyzxyzxyz"
, "Replacement": "xyzxyzxyz"
, "NextCodeOnMatch": null
, "NextCodeOnNoMatch": null
, "ExitSuccess": true
}
]
When the SQL statement matches rule rle001, the query is rewritten to return just master using an Invantive UniversalSQL statement. After that, execution continues on the EXIT rewrite rule (which could have been left away in this specific situation).
After restarting the connection now succeeds with Sumatra assuming there is solely a master database:
Next Challenge
When continueing the creation of the database down the road, the following error occurs:
which is caused by a query with a trailing carriage return:
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ExactLicense' and TABLE_SCHEMA = 'dbo' union all select case when count(*) = 3 then 1 else 0 end from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('Uren', 'WeekStaat', 'weburen_config') and TABLE_SCHEMA = 'sumatra' union all select case when count(*) = 4 then 1 else 0 end from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('bedryf', 'gbkmut', 'requesttasks', 'GLMaps') and TABLE_SCHEMA = 'dbo'\n
and expected outcome on a non-Exact Globe environment of three rows, each with 0.
Final Result
After several iterations, the result is:
, "QueryRewriteRules":
[ { "Code": "rle001"
, "SortingOrder": "A001"
, "RegexPattern": "select name from sys\\.databases where HAS_PERMS_BY_NAME\\('\\[' \\+ replace\\(name,'\\]','\\]\\]'\\) \\+ '\\]', 'DATABASE', 'SELECT'\\) = 1"
, "Replacement": "select 'master'"
, "NextCodeOnMatch": "EXIT"
, "NextCodeOnNoMatch": null
}
, { "Code": "rle002"
, "SortingOrder": "A002"
, "RegexPattern": "select count\\(\\*\\) from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME = 'ExactLicense' and TABLE_SCHEMA = 'dbo' union all select case when count\\(\\*\\) = 3 then 1 else 0 end from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME in \\('Uren', 'WeekStaat', 'weburen_config'\\) and TABLE_SCHEMA = 'sumatra' union all select case when count\\(\\*\\) = 4 then 1 else 0 end from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME in \\('bedryf', 'gbkmut', 'requesttasks', 'GLMaps'\\) and TABLE_SCHEMA = 'dbo'\n"
, "Replacement": "select 0 union all select 0 union all select 0"
, "NextCodeOnMatch": "EXIT"
, "NextCodeOnNoMatch": null
}
, { "Code": "rle003"
, "SortingOrder": "A003"
, "RegexPattern": "select count\\(\\*\\) from fn_my_permissions\\(null,'DATABASE'\\) where permission_name = 'SELECT'"
, "Replacement": "select 1"
, "NextCodeOnMatch": "EXIT"
, "NextCodeOnNoMatch": null
, "ExitError": false
, "ExitErrorMessage": null
}
, { "Code": "EXIT"
, "SortingOrder": "EXIT"
, "RegexPattern": "xyzxyzxyz"
, "Replacement": "xyzxyzxyz"
, "NextCodeOnMatch": null
, "NextCodeOnNoMatch": null
, "ExitSuccess": true
}
]