Can I use bracket for all field names in SQL code generation

Toad Data Modeler
1

I have a client who insists on using some reserved words in their database that I’m rebuilding.

Instead of having the argument about best practice and losing their confidence I’d like to be able to add square brackets automatically around ALL entities/attributes when generating the SQL / ADO.

Is this possible?

(I note Toad will delimit entities/attribute values that have spaces so I assume it’s possible somehow).

Many thanks

Greg

2

Hi Greg,

what database do you use? Square brackets are default quotation for Microsoft SQL Server and in Toad Data Modeler is this option by default on. It is on Generator form [F9 from model] - Detail Settings tab - “Delimited Identifiers (Use Bracket)” checkbox.

Daril

3

Hi Daril, thanks for responding.

I noticed bracketing option exists for SQL Server.

I’m generating for MS Access that as you know uses ADO.

My client has some ‘reserved words’ as table attributes. When generating tables, these attributes are double quoted so ADO does not throw an error.

However, those reserved words are not quoted when generating Views (in SQL) when using ADO.

An example field name is “Property” - ie. a field the client uses to describe a household etc. “Property” is an ADO reserved word so ADO throws an error.

Might we include the option “Delimited Identifiers (Use Brackets)” when generating for MS Access versions too?

My temporary solution is to edit the SQL DDL - but that’s tedious. I don’t wish to debate reserved words with my client just yet.

All the best

Greg

4

Hi Greg,

this options is not available for MS SQL Access, but you can do simply modification of system package to always generate brackets. Process is bellow

  1. Turn On Expert Mode - Go to “Main Menu-Settings-Options-General” and set checkbox “Expert Mode”

  2. Open Package Explorer - Go to “Main Menu-Expert Mode-Customization-Package Explorer”

  3. In Package Explorer Find Package “Generations for Microsoft Access” and expand this package

  4. In this package is script “PERCodeGeneratorMA” double click on it open script source code

  5. In Script Source Code Find method “function QuotedNameMA(ObjName)”

  6. Modify method to

function QuotedNameMA(ObjName)
{
return “[”+ObjName+"]";
}

  1. Save Script and try generate now SQL Code.

Daril

5

Thanks Daril,

The existing package reads the same as your suggested edit:

  function QuotedNameMA(ObjName)
{
if (ObjName.search(/\s+/) != -1)
//return "["+ObjName+"]"; // Safe copy of original return "["+ObjName+"]";
  else
return ObjName;
}

I made a trivial edit, but cannot save the package since it’s held in the Toad install directory (“C:\Program…”) and Windows rejects user edits (without elevated rights to Administrator level).

Should I consider making a copy of the Package in an alternative folder and import it?

Cheers

Greg

6

No wait...when I attempted to save the changed script "Message explorer" responded with "Package 'Generations for Microsoft Access' can't be saved".

But when I closed the edited script I was asked and confirmed to save changes to the Package - the changes were saved.

Nonetheless, that script you've identified doesn't seem to achieve what we hope - I changed it to output "XX[", saved it and ran the Generation, but that change doesn't appear in any generated output.

Greg

7

It’s look like Script is Saved, but package is not saved to HDD. Maybe Package can be readonly. Could you change it? Unfortunatelly package must be in install dir location. You can switch off UAC or install TDM to another location out of Program File.

My sugessted change it without if condition (It Always return ObjName with brackets):

   function QuotedNameMA(ObjName)
{
return "["+ObjName+"]"; }
8

Thanks Daril, yes I can make those changes (I'll edit the package outside Toad - can do).

And thanks, I'll remove the test so "[" is always exported, then test it.

Greg.

9

Hmmm, I edited/saved the package outside Toad to always return [ and ] - all Ok.

I closed/re-opened Toad and checked the script is loaded and then ran the generation for Access 2010.

However, ObjName was not returned as "[" & ObjName & "]" etc.

I tested this by changing the Generation script to "XXXX[" & ObjName etc, but that combination of "XX..." does not appear anywhere in the generated SQL.

I appreciate your efforts to resolve this since every ADO property not bracketed in SQL views will generate the same error - ouch.

Alternatives?

Cheers

Greg

10

Hi Greg,

I tested it on simple model with one Entity and one View. Both have attribute with name “Property”. See attachment there are in section “queries” brackets.

’ Create queries
'================

Sub CreateQueries()

Dim cmd As ADODB.Command
'=== Create query View1 ======

Set cmd = New ADODB.Command

cmd.CommandText = "SELECT [Property] " _
+ "FROM [Entity1]; "

cat.Views.Append “View1”, cmd

End Sub

Could you provide example, where you miss brackets?

Daril
msaccess-example.zip (6.55 KB)

11

Thanks Daril

I’ve modified the QuotedNameMA function so it doesn’t test for spaces. I’ve confirmed it’s written to the same Package folder; but it just is not getting called. I’ve removed all previous Toad version so can’t be called from an earlier version.

I did add “XXX[” in the function return so I can track where QuotedNameMA was called, but nothing showed up. This suggests there’s something odd with where Toad is calling the package from?

Here’s the sample of created code.


'=== Create query qry007_WaterTanks ======
Set cmd = New ADODB.Command
cmd.CommandText	=	"SELECT InspectionDetails.FarmerName, InspectionDetails.PropertyName, WaterTanks.PhotoID, WaterTanks.Number, WaterTanks.WaterTanks, WaterTanks.Position, WaterTanks.[Diameter (m)], WaterTanks.[Height (m)], WaterTanks.Calc_volume, WaterTanks.[Volume (L)], WaterTanks.NumberofItems, WaterTanks.YearBuilt," _
+ " WaterTanks.Condition, WaterTanks.comments " _
+	"FROM InspectionDetails INNER JOIN WaterTanks ON InspectionDetails.InspectionRID = WaterTanks.InspectionRID; "
cat.Views.Append "qry007_WaterTanks", cmd

When I bracket the “WaterTanks.Position” text the Append also works fine.

When I remove the “WaterTanks.Position” text the ADO Append works fine (“Position” is a reserved word)

Since I can’t get the bracketing to work in QuotedNameMA function, perhaps it could be set to use brackets for all objects - which of course your suggested changes intends?

Really need to fix this for future models.

Thanks

Greg

12

Hi Greg,

it is strange. I would like to you check this:

  • If you have Windows with UAC, that it is not works of virtualstore. You can check if exists folder “c:\users<username>\Appdata\local\Virtual Store\Program Files”. If Contain any subfolder of it our package, than it can be problem. VirtualStore is Windows mechanisms for write to Program Files Folder. More you can find on internet.

  • Do you have default settings in Generator?

Toad Data Modeler Load packages only from two locations. One is for System Packages and it is located in INSTALLDIR\Packages\System and other is for User Packages( you can find exactly path in Settings-Options-Path-Advanced-Path to Advanced (need Expert Mode)).

Daril