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

**URL:** <https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444>\
**Category:** Toad Data Modeler\
**Created:** [May 9, 2017, 5:37pm UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444 "2017-05-09T17:37:48Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 9, 2017, 5:37pm UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/1 "2017-05-09T17:37:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Daril](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/daril/32/31_2.png) [@Daril](https://forums.toadworld.com/u/Daril)\
**Post date:** [May 10, 2017, 12:29am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/2 "2017-05-10T00:29:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 10, 2017, 1:08am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/3 "2017-05-10T01:08:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Daril](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/daril/32/31_2.png) [@Daril](https://forums.toadworld.com/u/Daril)\
**Post date:** [May 10, 2017, 1:58am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/4 "2017-05-10T01:58:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 10, 2017, 4:09am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/5 "2017-05-10T04:09:57Z")

</div>

Thanks Daril,

The existing package reads the same as your suggested edit:

```auto
  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

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 10, 2017, 4:19am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/6 "2017-05-10T04:19:49Z")

</div>

> [@](#):
>
> Thanks Daril,
> 
> The existing package reads the same as your suggested edit:
> 
> ```auto
> 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

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

---

<div class="post-metadata">

**Author:** ![Daril](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/daril/32/31_2.png) [@Daril](https://forums.toadworld.com/u/Daril)\
**Post date:** [May 10, 2017, 4:29am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/7 "2017-05-10T04:29:43Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 10, 2017, 4:34am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/8 "2017-05-10T04:34:17Z")

</div>

> [@](#):
>
> 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):
> 
> ```auto
> function QuotedNameMA(ObjName)
> {
> return "["+ObjName+"]"; }
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 10, 2017, 5:00am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/9 "2017-05-10T05:00:27Z")

</div>

> [@](#):
>
> > Daril
> 
> > 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):
> 
> > ```auto
> > function QuotedNameMA(ObjName)
> > {
> > return "["+ObjName+"]"; }
> > 
> > ```
> 
> 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.

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

---

<div class="post-metadata">

**Author:** ![Daril](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/daril/32/31_2.png) [@Daril](https://forums.toadworld.com/u/Daril)\
**Post date:** [May 10, 2017, 7:04am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/10 "2017-05-10T07:04:39Z")

</div>

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](https://global.discourse-cdn.com/quest/original/2X/d/d53e5583d62c7f1d5ee4e288994c3566cd6c4464.zip) (6.55 KB)

---

<div class="post-metadata">

**Author:** ![greg\_long](https://avatars.discourse-cdn.com/v4/letter/g/ba9def/32.png) [@greg\_long](https://forums.toadworld.com/u/greg_long)\
**Post date:** [May 15, 2017, 8:43pm UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/11 "2017-05-15T20:43:43Z")

</div>

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.

```auto

'=== 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

---

<div class="post-metadata">

**Author:** ![Daril](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/daril/32/31_2.png) [@Daril](https://forums.toadworld.com/u/Daril)\
**Post date:** [May 16, 2017, 1:43am UTC](https://forums.toadworld.com/t/can-i-use-bracket-for-all-field-names-in-sql-code-generation/39444/12 "2017-05-16T01:43:33Z")

</div>

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