Introduction

ApexSQL Code Introduction

ApexSQL is proud to present ApexSQL Code - a new version of a remarkable product from a family of ApexSQL Tools. The new version has a great, improved internal engine, better scripting language support, more templates and better performance.

ApexSQL Code is a Rapid Application Development (RAD) Code Generation Tool for interacting with Microsoft SQL Server to create code patterns in a variety of languages.

The key feature of ApexSQL Code is Template based code generation that allow users to configure custom code patterns automated with the scripting language of their choice, VBScript or JScript.

ApexSQL supports a large library of pre-written templates in a variety of languages (included in installation and available on web site) and will continue to add to this library over time. See Template page for more details.

Sincerely,

ApexSQL Code development team.

© 2006 ApexSQL LLC. All Rights Reserved.


System/Hardware Requirements

System Requirements

Software Prerequisites:

ADO 2.7 or later
Microsoft SQL Server version 7 or later
Microsoft .NET Framework 1.1 or later is required for all products. You can download it Here.

Click here for more information on prerequisites.

Compatible Operating Systems:

Windows NT 4.0 SP3+
Windows 2000
Windows XP
Windows 2003

Processor:

Personal Computer with an Intel Pentium 233 MHz or faster processor (Pentium III recommended)

RAM:

256 MB RAM (512 MB suggested)

Hard Disk Drive Space:

30 MB Free Disk Space *

Copyright © 2005 ApexSQL LLC. All Rights Reserved.


Copyright and Disclaimer

This document, including sample examples herein, is provided for informational purposes only and ApexSQL LLC makes no warranties, either express or implied, in this document. Information in this document, including samples, URL, and other Internet Web site references, is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user.

The primary purpose of a sample is to illustrate a concept, or a reasonable use of a particular statement or clause. Most samples do not include all of the code that would normally be found in a full production system, as a lot of the usual data validation and error handling is removed to focus the sample on a particular concept or statement. Technical support is not available for these samples or for the provided source code.

Unless otherwise noted, the example organizations, products, people, and events depicted herein are fictitious and no association with any real organization, product, person, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of ApexSQL LLC

The names of actual organizations and products mentioned herein may be the trademarks of their respective owners.

Copyright © 2005 ApexSQL LLC. All Rights Reserved.


Logging In

Logging into a SQL Database

This login form will allow you to access any SQL Server database on your client and network

Perform the following steps to log on to SQL Server.

  1. In the Server list, select a server from the available options. Click the ellipsis next to the Server list to show a list of available servers.

  2. Select a security (authentication) mode.

  • Windows Authentication: Windows Authentication mode allows you to connect through a Microsoft Windows user account.

  • SQL Server Authentication: SQL Server Authentication mode allows you to type your credentials. When you connect with a specified user name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have an account set, then the authentication fails and you will receive an error message.

  3. If you have selected the SQL Server Authentication mode, then type the user name in the User box.

  4. If you have selected the SQL Server Authentication mode, then type the password in the Password box.

  5. Click Connect to connect to the database and open ApexSQL Code. Click Cancel to quit the ApexSQL Code program.

Copyright © 2006 ApexSQL LLC. All Rights Reserved.


Load Options

Error on Loading some properties

Selecting this option will notify the user of all errors that occurred (usually when obtaining result columns from stored procedures). This is normally caused by Temp Tables included in a stored procedure which interfere with ApexSQL Code's ability to interrogate it's Result Columns

If there is an error loading a procedures Result Column set the ? icon will be displayed as is shown below.


options_VB

Options : VB/ADO : VB Variables

Variable Typing (Null Handling)

See Null Strategies

VB Variable Prefixes

This section allows the user to customize the prefixes/naming conventions of common variables.

This mapping will populate Key or Field Prefix. To edit, double click or click and hold, then enter in the desired prefix.

"Restore defaults" will overwrite all changes to the original variables ApexSQL Code uses by default.


options_Config

Options : Configuration

This section allows for General customization of generated code

Startup

Load last opened files

This setting will direct the application to remember the last opened templates and automatically open them again when the app. is next started.

Database Loading

Show System Databases

Toggling this setting will Show|Hide system databases in the database picklist of login form

Show Warning if some object properties can't be obtained

Toggling this setting will Show|Hide the error report dialog for object property errors. Click Here for more information on this dialog.

Selection Handling

For large schemas with multiple object selections, ApexSQL Code offers the convenient mechanism for storing selection profiles and 3 different means to manage them.

Auto

Profiles will automatically be saved to file when application is closed or a new database is selected. Profiles, if found, will automatically be retrieved and the grid selections populated

Prompt

In this case, the application will alert the user with a prompt to both Save a "dirty" profile or retrieve on that has been found.

Manual

In this mode, the application takes no action. Profiles must be Saved and Retrieved Manually, via the File Menu or Toolbar

Template Directory

Default Directory

Specifies the default location of the Template directory. ApexSQL Code will populate the template explorer from this directory.

Remember Directory from Last Template Opened

When this option is set to True, ApexSQL Code will reset the value of the default directory each time a template is opened, based on the directory of the last template opened.

Maximum Recent Files in Memory

Determines the number of Templates stored in Recent Template section in File Menu


options_FileExt

Options : File Extensions

Use this section to associate a default file extension with the supported language. This will default the associated file extension when outputting results to file.

"Restore defaults" will overwrite all changes to the original variables ApexSQL Code uses by default.


Prefixes

Options : Prefixes

This section will supply the valaue for "PrefixNet" as in the following example


options_DatatypeMapping

Options : .NET : Datatype Mapping

ApexSQL Code offers the ability to map SQL Server datatypes to their .NET/CLR equivalents


options_Editor

Options : Editor

The Editor tabs offers several options to customize the Editor

Show Tabs at Top

Changes the position of the Tabs for "Template", "Script Source" and "Results" from the bottom (default) to the top as shown below.

Enable Intelliprompt

Allows the Intelliprompt feature to be turned off

Enable Auto Replace

Toggles Auto Replace Functionality On|Off


options_FileOutput

Options : FileOutput

File Output / Writing is controlled via this Option Module

File Operations

Use Template Setting: Tells the application to use the settings for file output contained in the template properties itself

Write each Schema Object to a Separate File (Multi-File): Will create a new file for each checked schema object

Append all output to a Single File (Single-File): Will create a new file at the beginning of the batch and then append all subsequent code generated against additional schema objects to that file.

Suppress File Writing: Suppresses all file writing by ApexSQL Code entirely. This is used when File I/O is done internally in the template by using the File System Object

Overwrite Existing Files

This option will suppress any warning dialog that would alert user to existing file of the same name, and will automatically overwrite it.

Show File Output Dialog

Will toggle the state Shown|Hidden of the File Output dialog. This dialog is shown at the point of code generation to allow the user to intervene and alter the file output process immediately before file creation.

Output File Name

This setting uses a Name template consisting of several name tags to construct the file name for the generated code file. The tags include

{Server}, {Database}, {Owner}, {Date}, {Time}

{Type} for example Table, View, Procedure

{Name} name of the processed schema object


Creating Queries

Generating Code

Code generation in ApexSQL Code is very easy. Simply select the SQL object(s) that you want to create code patterns from on the left hand side of the main form. These objects can be either Tables, Views or Stored Procedures based on the toggle button which is selected.

Select Schema Objects

The schema explorer is a powerful browsers of database objects that can be selected for Code Generation. One or more objects can be selected for Code Generation.

Select a Template

The next step is to select a Template. Templates can be opened via the Toolbar or File Menu

or then can be accessed via the Template Explorer

Ready, Set ... Generate

Via the Schema Explorer context menu, File or Toolbar "Generate Code for Checked Objects" can be executed. F5 will also do the same thing

As the templates are processed, they appear in the grid control on the right of the form. Double-Clicking on the template line item will display the derived code.


Sprocs

Proc Specific Processing

ApexSQL Code adds an additional attribute for Stored Procedures. It distinguishes between procedures that return a recordset and those that don't. This information can be used when creating templates, by leveraging the object model property IsRecordset

This property will return a true or false depending on whether the procedure returns a recordset (true) or not (false).

By looking at the column "Recordset", you can see which procedures return a recordset and which don't. A ? symbolizes unknown status for recordset. Most likely the procedure contains a Temp table and an error was returned when it's resultset status was interrogated by ApexSQL Code.


interface_Schema_Explorer

Schema Explorer

The ApexSQL Code Schema explorer offers powerful, and feature rich object organization and browsing.

Object Types

Currently 3 object types are shown : Tables, Views and Procedures. Each object type has a unique set of properties that can be displayed in the grid itself

Sub-Objects

By expanding the grid for an object, the collections of it's sub-objects can be displayed, such as Return Value, Result Columns and Parameters of stored procedures.

Checked|Unchecked

All Objects and Sub-Objects can be Checked (IsChecked property = True|False) and this property can be interrogated in a template and processed accordingly.

Object Property Page

Read only Properties for Top Level Objects (Tables, Views and Procedures) are displayed in the Property Page in the schema explorer. Properties exist for Subobjects as well.

Editing Properties

All properties are Read-Only with the exception of Alias which is editable. Alias' can be changed to Top Level Objects and Sub-Objects. Editable properties are shown in Bold

Schema Explorer Toolbar

Icon

Caption

Action

Expand All | Expands all of the Top Level Nodes in the Grid (Tables, Views, Procedures)
Collapse All | Collapses all of the Nodes in the grid.
Group | Toggles Grouping of objects by Top Level Object Type (Tables, Views, Procedures)
Show Child Objects | Toggles the Shown|Hidden state of Sub Objects (i.e. Parameters for Procedures) are shown
Show Filter | Toggles the Shown|Hidden state of whether Column Level Filter is displayed or not

Choose Columns

Shows Column chooser dialog

Show Properties

Toggles the Shown|Hidden state of the Object Property Page


interface_TemplateExplorer

Template Explorer

The Template Explorer is a convenient way to visually browse your template collection.

It is predicated on a Folder location that can be set via the Template Directory option setting in the Options form. The template explorer will create a tree structure based on the folder structure of the top level directory mapped in the Options form.

Properties are shown for the selected template in the Template Property Page. The Shown|Hidden Status of the Property page can be toggled via the Property Page Toolbar button. For a full description of all template properties see Template Properties.


Current_Template

The "Current Template" panel exists to edit the currently selected template's properties.

This panel makes editing templates very easy. Users can directly type into the row like when editing Author. In larger text fields, an ellipse is provided that will open a small text input control to accept data - this applies to Legal and Description. For Output File Path and Output File Name, file/path dialogs are provided. A custom form can be accessed to add includes. All other properties display a picklist.

For more information about these properties, step through the template wizard which offers an easy tour through all template attributes.


interface_GeneratedFiles

Generated Files

Generated files can be browsed and opened via the Generated Files panel


Templates

Template Overview

Template based code generation is what ApexSQL Code is all about. ApexSQL Code reads templates written in either VBScript or JScript and processes selected database objects against them. The result is a code pattern in any language used to work with SQL Server.

Template + Database Object (i.e. Table) + ApexSQL Code Application = Ready to Use Code

What are Templates?

Templates are *.ptpx files (xml) that contain user defined text mixed with calls to ApexSQL Code Object Model using VBScript or JScript Constructs, Loops and functions. ApexSQL Code can read .ptpx files and VBScript/JScript statements they contain to generate code across multiple sql objects in a database. Depending upon the Scripting Language selected, ApexSQL Code uses the Script Debugger for parsing and displaying errors if any in the Templates.

ApexSQL Code leverages off the full capabilities of Microsoft Script Control, Microsoft VBScript and JScript and Microsoft Script debugger. To realize the full potential of these powerful scripting components, see http://www.msdn.microsoft.com/scripting. Also, a complete VBScript and JScript reference is included in this help file.

What is VBScript?

Microsoft Visual Basic Scripting Edition, brings active scripting to a wide variety of environments, including Web client scripting (ASP) in Microsoft Internet Explorer and Web server scripting in Microsoft Internet Information Services and software applications like ApexSQL Code. See VBScript Reference for more info.

What is JScript?

JScript is the Microsoft implementation of the ECMA 262 language specification (ECMAScript Edition 3). JScript is an interpreted, object-based scripting language. Although it has fewer capabilities than full-fledged object-oriented languages like C++, JScript is more than sufficiently powerful for its intended purposes. See JScript Reference for more info.

What is ApexSQL Code Object Model?

The ApexSQL Code object model exposes all of the properties of and internal component that has been populated with METADATA from the current database. This object model allows developers to pick and choose what information they want from the database to access to create their code pattern. Many properties offer value added features like direct conversion of a sql object attribute into an ADO keyword, for example. As the component resides internally in ApexSQL Code, it works seamlessly with the GUI interface to determine what SQL Objects have been selected for processing.

You can work with this object model with templates and program it using VBScript or JScript. The script combined with the object model meta data, when run against a selected sql object, will create the final code patterns.

How do I create a template?

Well, you may not need to. 1000's of lines of code in scores of templates exist already encompassing most of the major programming languages like C#, VB.NET, VB and ASP.

The can be found in the Template Explorer

.

But if you wish to create a new template you can click the New Template button on the toolbar or the file men.

Any of these actions will open up the template editor and allow you to begin writing a template from scratch. Note: you can also modify existing templates to suit your needs and save them with a new name.

Is there an easier way to create templates?

ApexSQL Code includes a sophisticated Template Wizard that will walk you through the process of creating a template step by step.


Managing Templates

Creating and Editing Templates

ApexSQL Code contains an integrated environment for editing *.ptpx Template files.

Note: Some pre-written templates have been included with the ApexSQL Code installation and can be found in the installation directory (i.e. [Installation Drive]:\Program Files\ApexSQL\ApexSQL Code\). You can also do a search on *.ptpx files to find these templates. These templates can also be browsed via the Template Explorer.

Note: To get the template to format properly for Color, make sure that users have selected the appropriate language color formatting for the type of Template file you are opening. If it is a Template to create ASP code, select ASP under Language in the property page. Language select will affect 3 things - color formatting, case normalization and File Extension of the output files.

See also VBScript Language Reference and JScript Language Reference.

Opening Templates and Include files

Templates can be opened via the Template Explorer or clicking the Open Template toolbar button.

Include Files

ApexSQL Code supports include files that help power base templates by abstracting complex functions, etc See Include Files for more information.

Template Parsing

ApexSQL Code offers a basic parsing engine mainly to look for Un-Opened and Un-Closed Template tags. Some hints and warnings are also included for cases where backward compatibility of object model may have been broken and ApexSQL Code finds a legacy property. See Template Parsing for more information.

Processing template script with Microsoft Script debugger

When executing a template, a variety of errors may be generated depending on the scripting language used. Malformed VBScript / JScript will be parsed via Microsoft Scripting engine allowing template designers to leverage off powerful script parsing as script developers in other tools like ASP, Internet Explorer or Windows itself. See the following links to get more information on these errors.

    • VBScript Run Time Errors

    • VBScript Syntax Errors

    • JScript Run Time Errors

    • JScript Syntax Errors

Processing Templates

Process the templates by hitting F5 or ...

From the toolbar

From the menubar

Editing Templates

See also Template Editor for more information on creating and editing Templates.


Include Files

Include files extend a template developers ability to leverage an existing code library. They work in the same way as Include files in ASP in that they provide access to the contents of the include file as if it were a part of the file that it is included in.

Why Should I use Include Files?

Include files are helpful to re-use code across other multiple templates. If you use a file full of generic functions and include them in a template, all of these functions will be directly accessible to your template.

This has a beneficial side effect of reducing the size and complexity of your templates. Also, maintenance is made easier as a bug in a particular function need only be fixed once if it exists in an include file as opposed to having to fix the bug in every single template where it may have been implemented.

Finally, include files are a great place to store constants so they don't clutter up your templates and can be accessible to all of your templates.

Tech Note: See Delphi_Basic_Fun_Table.ptpx for an excellent example of how a developer made extensive use of an include file (VCL Header.ptpx) to map SQL Datatypes to Delphi Datatypes during run time.

Adding Includes to a Template

To add an include file to a template via the template editor, hit the ellipse to the right of the includes row. See the following graphic. Note: For this template, 3 include files exist already.

File Options

2 Options exist when adding include files.

1) User Template Path (Default): This will tell ApexSQL Code to look for the include file in the path and directory of the current template being processed. This assumes there has been no change in CurDir and CurDrive from the template script.

2) Use full file path: This accepts the hard-coded file path. ApexSQL Code will look for the include file in this exact location.

Relative Paths

An include file path is relative to the template that references it. If the include file exists in the same directory as the referencing template, no file path is required.

Location of Includes in body of template

It doesn't matter where in the code to include the file as long as it exists in a template. It can occur near the top of the template file or at the very bottom. The include will be detected and added to the top of the Script Source Pane, followed by the code from the template itself.

Editing include Files with template editor

Include Files can be opened like other Templates. In the File Dialog select one of the non-Template file options to see existing Include Files.

Include File Types

Include files can be any text files of any type and file extension, but it is recommended to use *.ptpx for ApexSQL Code template, *.vbs for VBScript, *.js for JScript. The dialog will offer users the default file choice of .ptpx with a second choice of either .vbs or .js depending on the current setting for scripting language.

Viewing Include File Results

The contents of the include file(s) will be displayed in the script source pane at the top of the editor and in the order in which the files were included.


Template_Properties

Templates support a variety of editable properties.

File

File Output Mode (Append (Combined File)|Output (Individual File)|Suppress File Writing

Determines if the Template is written to a new file (per database object) or is appended to a single file that includes processed code for all selected schema objects.

File Suppress (True|False)

Tells the application to process the File Output of the template or simply execute the template (assuming File I/O is done programmatically file File System object calls in the template itself)

OutputFileName (String)

A template based text string that will determine the name of the file created in the process of Code Generation. The file name template uses a variety of tags to dynamically create the file name during processing including

{Server}, {Database}, {Owner}, {Type}, {Name}, {Date} and {Time}

The file extension is determined by the output language chosen. It can be configured in the File Extensions tab in the Options dialog.

OutputFilePath (String)

Sets the folder which the template is written out to

Info

This section allows for editing of Information based template properties including Author, Description, Legal and Name.

These properties correspond to template properties

objPB

Author

Description

Legal

Name

Languages

Output Language

Determines the language of the generated script which affects syntax highlighting of the Results panel as well as File Extension of generated files.

Script Language

Offers a choice of scripting languages, VBScript and JScript, used in the creation of templates

Misc

Includes

See Includes Page for more information.

Object Type

Associates a template with a particular Object Type (i.e. Table, View, Procedure). If the currently selected Schema Object isn't a supported type the following warning will be shown

Settings

Custom Properties

The Custom Properties feature is a power means to extend the power and extensibility of templates. Basically they are a means to add additional design time properties to the template. These design time properties can then be used within the property.

Properties have several built in attributes including Default, Description, Name and Type

Custom Properties can be used in the template like this

xml profiles of Custom Properties can be Saved (exported) and Loaded (imported) and Defaults values can be reset via the following context menu

`

For more information and training on using custom properties in ApexSQL Code see the Training section on the ApexSQL Website.


temp_Template

Template Editor : Template Pane

Template Pane

The template pane is used to actually create and edit templates. It supports syntax highlighting and Intellisense to be able to easily interrogate and manipulate the ApexSQL Code object model.

This editor will support all features common to most text editors like Cut and Paste, Line Numbers, etc.


temp_Source

Template Editor : Source Pane

The script source is used for debugging purposes to show the exact line of failure if an error occurs.

In this example, an error has occurred because the user had tried to process a ApexSQL Code Object Model Method that doesn't exist, Prixnt. The correct property is Print. The developer will get a message box indicating the error description and line number of the error.

If more details about the error are required, the Source pane indicates the actual source code run against the Microsoft VBScript compiler and highlights the exact line of failure.

Text in the Source Pane is Read Only

Copyright © 2006 ApexSQL LLC. All Rights Reserved.


temp_Results

Template Editor : Results Pane

When the template is processed using either Run -> Generated Code Preview a results file is generated. This is the output of the template and is the code that will be utilized by the end user.

Text in the Results Pane is Read Only

Copyright © 2006 ApexSQL LLC. All Rights Reserved.


Template Parsing

Parsing Templates for basic non Script errors

Currently, Template parsing exists for missing script tags {% and %} and a variety of syntax problems related to upgrades and unsupported / legacy properties.

Templates are parsed using the menu bar command, Run -> Parse

... or the Parse toolbar button. Or hit the shortcut key F6.

If it is determined that there is a missing script tag - either opening or closing, the template parser will detect it and raise a warning to the user.

Also, the code will be dumped into the Script Source window to help the user determine the location of the missing script tag.

In the following case ....

I will remove the first script tag so the code looks like this ...

and will parse the file. The result is the following warning:

The code in the Script source window will look like this ...

The parser will display all of the template code up to the closing script tag (that has no corresponding open script tag) in order to show the exact nature of the problem. From this code, it is easy to see that the opening script tag is missing and the problem can be fixed by adding it.

Now look at another case. In this scenario, the closing script tag is missing.

the user will get the following warning message ...

and the following code will get dumped into the Script Source pane ...

In this case, the template parser has displayed all of the code up to the script tag that is unclosed. Once again, it is easy to see and fix the problem.

Over time, as other common errors are reported, more functionality may be added to the template parser. They will be added to the success message to inform the user what has been parsed.


Template Editor Options

There are a variety of configurable options for Templates

Line Numbers

Adds line numbers to the left side of the script editor.

Left Margin

Adds a blank space on the left side of the script editor which is useful if a Find -> Mark All operation is done. In the following case, a search for Dim was done and all the instances that were found were marked. Only if the left margin is checked would the markers be visible.

Advanced Options

Via File / Advanced a menu of Template configuration options is available covering Case Correction, and other common editor options


WizardIntro

Template Wizard Introduction

The template creation wizard included with ApexSQL Code is a great way to create new templates. It will walk you through each step in the template creation process in a systematic and easy to digest manner. At each step, you can read basic instructions and access the help topic for that step.

After the final step, you will have created all of the template properties required. The wizard will close and the template editor will be opened with all of the properties set via the wizard process.

The only part not completed is the template itself. You can then write the template and when you hit Save, a complete, ready to use template will have been created.

To access the Template Wizard, hit this button on the Outlook bar or use menu bar Template -> Template Wizard.


WizardStep1

Step 1 - Template Name and Description

This first step will set the basic properties of the template including Name, Author, Legal info and Description.

The only required property is Name - all of the others are optional.


WizardStep2

Step 2 - Language

You will be asked to select the language of the generated code in step 2.

Language determines syntax coloring, case normalization and also the file extension of the outputted file. As you can see, ApexSQL Code supports a variety of Languages.

If you do not want to have language output or have a custom language that you don't want to be misformatted, select Documentation which will display the code in Black text with no Case Normalization.


WizardStep3

Step 3 - Script Language

ApexSQL Code offers two script languages - Microsoft VBScript and Microsoft JScript. This selection will determine how the Microsoft Script control will parse, debug and execute the template.


WizardStep4

Step 4 - Output File Path and Name

Both of these properties are optional and in most cases will be left blank. The output file object is a great way to direct files to a designated file directory or a hard-coded file name. In most cases, if a file name is specified, then the Append option will be set to true. Otherwise, if multiple files are processed, each will overwrite the previous, since they are all written to the same file name. See Step 5 (the next step) for options on file writing.

If Output File Path is designated, then the output file path dialog will be suppressed in batch mode. More than one SQL object is processed..

If Output File Name is designated, then the Output File Name dialog will be suppressed if only one SQL object is processed.

If Both Output File Path and Output File Name are selected, then regardless of the mode, multiple or single SQL objects selected, no file dialogs will be displayed. It would be redundant, since the exact file path and location is specified already.


WizardStep5

Step 5 - File Writing

ApexSQL Code offers flexibility in how to output files. You can indicate that files are create new (Output File) or files are appended to an existing file (Append File), if one exists with the same name already.

In some cases, users may not want ApexSQL Code to perform any file operations, since the File System Object will be used from within the template itself. If this is the case, you can eliminate unnecessary file dialogs and speed template processing by checking "suppress All File Operations"


WizardStep6

Step 6 - Includes

Include files are a powerful feature of ApexSQL Code templates and work just like ASP. You can have code libraries stored in separate files that can be accessed by all of your templates.

Adding Include files is very easy. Simply click "Add or Edit Includes", and the Include dialog will be displayed. See Include Files for a more complete description of Include functionality.


WizardStep8

Step 7 - Finished

At the completion of the wizard, the final step is displayed. If Finish is clicked, the Template Editor will be displayed. This can be used to add the final piece - the template script itself.


Working with Files

Working With the FileSystemObject

There are two major categories of file manipulation:

  • Creating, adding, or removing data, and reading files.

  • Moving, copying, and deleting files.

Creating Files
There are three ways to create an empty text file (sometimes referred to as a "text stream").

The first way is to use the CreateTextFile method. The following example demonstrates how to create a text file using this method in VBScript:

Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile("c:\testfile.txt", True)

To use this method in JScript, use this code:

var fso, f1;
fso = new ActiveXObject("Scripting.FileSystemObject");
f1 = fso.CreateTextFile("c:\\testfile.txt", true);
View this sample code to see how the CreateTextFile method is used in FileSystemObject.

The second way to create a text file is to use the OpenTextFile method of the FileSystemObject object with the ForWriting flag set. In VBScript, the code looks like this example:

Dim fso, ts
Const ForWriting = 2
Set fso = CreateObject("Scripting. FileSystemObject")
Set ts = fso.OpenTextFile("c:\test.txt", ForWriting, True)

To create a text file using this method in JScript, use this code:

var fso, ts;
var ForWriting= 2;
fso = new ActiveXObject("Scripting.FileSystemObject");
ts = fso.OpenTextFile("c:\\test.txt", ForWriting, true);

A third way to create a text file is to use the OpenAsTextStream method with the ForWriting flag set. For this method, use the following code in VBScript:

Dim fso, f1, ts
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile ("c:\test1.txt")
Set f1 = fso.GetFile("c:\test1.txt")
Set ts = f1.OpenAsTextStream(ForWriting, True)

In JScript, use the code in the following example:

var fso, f1, ts;
var ForWriting = 2;
fso = new ActiveXObject("Scripting.FileSystemObject");
fso.CreateTextFile ("c:\\test1.txt");
f1 = fso.GetFile("c:\\test1.txt");
ts = f1.OpenAsTextStream(ForWriting, true);
Adding Data to the File

Once the text file is created, add data to the file using the following three steps:

  1. Open the text file.

  2. Write the data.

  3. Close the file.

To open an existing file, use either the OpenTextFile method of the FileSystemObject object or the OpenAsTextStream method of the File object.

To write data to the open text file, use the Write, WriteLine, or WriteBlankLines methods of the TextStream object, according to the tasks outlined in the following table.

Task

Method

Write data to an open text file without a trailing newline character.

Write

Write data to an open text file with a trailing newline character.

WriteLine

Write one or more blank lines to an open text file.

WriteBlankLines

View this sample code to see how the Write, WriteLine, and WriteBlankLines methods are used in FileSystemObject.

To close an open file, use the Close method of the TextStream object.

View this sample code to see how the Close method is used in FileSystemObject.

Note The newline character contains a character or characters (depending on the operating system) to advance the cursor to the beginning of the next line (carriage return/line feed). Be aware that the end of some strings may already have such nonprinting characters.

The following VBScript example demonstrates how to open a file, use all three write methods to add data to the file, and then close the file:

Sub CreateFile()
  Dim fso, tf
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set tf = fso.CreateTextFile("c:\testfile.txt", True)
  ' Write a line with a newline character.
  tf.WriteLine("Testing 1, 2, 3.")
  ' Write three newline characters to the file.       tf.WriteBlankLines(3)
  ' Write a line.
  tf.Write ("This is a test.")
  tf.Close
End Sub

This example demonstrates how to use the three methods in JScript:

function CreateFile()
{
  var fso, tf;
  fso = new ActiveXObject("Scripting.FileSystemObject");
  tf = fso.CreateTextFile("c:\\testfile.txt", true);
  // Write a line with a newline character.
  tf.WriteLine("Testing 1, 2, 3.") ;
  // Write three newline characters to the file.       tf.WriteBlankLines(3) ;
  // Write a line.
  tf.Write ("This is a test.");
  tf.Close();
}
Reading Files

To read data from a text file, use the Read, ReadLine, or ReadAll method of the TextStream object. The following table describes which method to use for various tasks.

Task

Method

Read a specified number of characters from a file.

Read

Read an entire line (up to, but not including, the newline character).

ReadLine

Read the entire contents of a text file.

ReadAll

View this sample code to see how the ReadAll and ReadLine methods are used in FileSystemObject.

If you use the Read or ReadLine method and want to skip to a particular portion of data, use the Skip or SkipLine method. The resulting text of the read methods is stored in a string which can be displayed in a control, parsed by string functions (such as Left, Right, and Mid), concatenated, and so forth.

The following VBScript example demonstrates how to open a file, write to it, and then read from it:

Sub ReadFiles
  Dim fso, f1, ts, s
  Const ForReading = 1
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f1 = fso.CreateTextFile("c:\testfile.txt", True)
  ' Write a line.
  Response.Write "Writing file <br>"
  f1.WriteLine "Hello World"
  f1.WriteBlankLines(1)
  f1.Close
  ' Read the contents of the file.
  Response.Write "Reading file <br>"
  Set ts = fso.OpenTextFile("c:\testfile.txt", ForReading)
  s = ts.ReadLine
  Response.Write "File contents = '" & s & "'"
  ts.Close
End Sub

This code demonstrates the same thing in JScript:

function ReadFiles()
{
  var fso, f1, ts, s;
  var ForReading = 1;
  fso = new ActiveXObject("Scripting.FileSystemObject");
  f1 = fso.CreateTextFile("c:\\testfile.txt", true);
  // Write a line.
  Response.Write("Writing file <br>");
  f1.WriteLine("Hello World");
  f1.WriteBlankLines(1);
  f1.Close();
  // Read the contents of the file.
  Response.Write("Reading file <br>");
  ts = fso.OpenTextFile("c:\\testfile.txt", ForReading);
  s = ts.ReadLine();
  Response.Write("File contents = '" + s + "'");
  ts.Close();
}
Moving, Copying, and Deleting Files

The FSO object model has two methods each for moving, copying, and deleting files, as described in the following table.

Task

Method

Move a file

File.Move or FileSystemObject.MoveFile

Copy a file

File.Copy or FileSystemObject.CopyFile

Delete a file

File.Delete or FileSystemObject.DeleteFile

View this sample code to see two ways to delete a file in FileSystemObject.

The following VBScript example creates a text file in the root directory of drive C, writes some information to it, moves it to a directory called \tmp, makes a copy of it in a directory called \temp, then deletes the copies from both directories.

To run the following example, create directories named \tmp and \temp in the root directory of drive C:

Sub ManipFiles
  Dim fso, f1, f2, s
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f1 = fso.CreateTextFile("c:\testfile.txt", True)
  Response.Write "Writing file <br>"
  ' Write a line.
  f1.Write ("This is a test.")
  ' Close the file to writing.
  f1.Close
  Response.Write "Moving file to c:\tmp <br>"
  ' Get a handle to the file in root of C:\.
  Set f2 = fso.GetFile("c:\testfile.txt")
  ' Move the file to \tmp directory.
  f2.Move ("c:\tmp\testfile.txt")
  Response.Write "Copying file to c:\temp <br>"
  ' Copy the file to \temp.
  f2.Copy ("c:\temp\testfile.txt")
  Response.Write "Deleting files <br>"
  ' Get handles to files' current location.
  Set f2 = fso.GetFile("c:\tmp\testfile.txt")
  Set f3 = fso.GetFile("c:\temp\testfile.txt")
  ' Delete the files.
  f2.Delete
  f3.Delete
  Response.Write "All done!"
End Sub

This code shows the same thing in JScript:

function ManipFiles()
{
  var fso, f1, f2, s;
  fso = new ActiveXObject("Scripting.FileSystemObject");
  f1 = fso.CreateTextFile("c:\\testfile.txt", true);
  Response.Write("Writing file <br>");
  // Write a line.
  f1.Write("This is a test.");
  // Close the file to writing.
  f1.Close();
  Response.Write("Moving file to c:\\tmp <br>");
  // Get a handle to the file in root of C:\.
  f2 = fso.GetFile("c:\\testfile.txt");
  // Move the file to \tmp directory.
  f2.Move ("c:\\tmp\\testfile.txt");
  Response.Write("Copying file to c:\\temp <br>");
  // Copy the file to \temp.
  f2.Copy ("c:\\temp\\testfile.txt");
  Response.Write("Deleting files <br>");
  // Get handles to files' current location.
  f2 = fso.GetFile("c:\\tmp\\testfile.txt");
  f3 = fso.GetFile("c:\\temp\\testfile.txt");
  // Delete the files.
  f2.Delete();
  f3.Delete();
  Response.Write("All done!");
}

©2000 Microsoft Corporation. All rights reserved.


Programming the FileSystemObject

To program with the FileSystemObject (FSO) object model:

  • Use the CreateObject method to create a FileSystemObject object.

  • Use the appropriate method on the newly created object.

  • Access the object's properties.

The FSO object model is contained in the Scripting type library, which is located in the Scrrun.dll file. Therefore, you must have Scrrun.dll in the appropriate system directory on your Web server to use the FSO object model.

Creating a FileSystemObject Object

First, create a FileSystemObject object by using the CreateObject method. In VBScript, use the following code to create an instance of the FileSystemObject:

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
This sample code demonstrates how to create an instance of the FileSystemObject.

In JScript, use this code to do the same thing:

var fso;
fso = new ActiveXObject("Scripting.FileSystemObject");

In both of these examples, Scripting is the name of the type library and FileSystemObject is the name of the object that you want to create. You can create only one instance of the FileSystemObject object, regardless of how many times you try to create another.

Using the Appropriate Method
Second, use the appropriate method of the FileSystemObject object. For example, to create a new object, use either CreateTextFile or CreateFolder (the FSO object model doesn't support the creation or deletion of drives).

To delete objects, use the DeleteFile and DeleteFolder methods of the FileSystemObject object, or the Delete method of the File and Folder objects. You can also copy and move files and folders, by using the appropriate methods.

Note Some functionality in the FileSystemObject object model is redundant. For example, you can copy a file using either the CopyFile method of the FileSystemObject object, or you can use the Copy method of the File object. The methods work the same; both exist to offer programming flexibility.

Accessing Existing Drives, Files, and Folders

To gain access to an existing drive, file, or folder, use the appropriate "get" method of the FileSystemObject object:

  • GetDrive

  • GetFolder

  • GetFile

To gain access to an existing file in VBScript:

Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.GetFile("c:\test.txt")

To do the same thing in JScript, use the following code:

var fso, f1;
fso = new ActiveXObject("Scripting.FileSystemObject");
f1 = fso.GetFile("c:\\test.txt");

Do not use the "get" methods for newly created objects, since the "create" functions already return a handle to that object. For example, if you create a new folder using the CreateFolder method, don't use the GetFolder method to access its properties, such as Name, Path, Size, and so forth. Just set a variable to the CreateFolder function to gain a handle to the newly created folder, then access its properties, methods, and events. To do this in VBScript, use the following code:

Sub CreateFolder Dim fso, fldr Set fso = CreateObject("Scripting.FileSystemObject")
  Set fldr = fso.CreateFolder("C:\MyTest")
  Response.Write "Created folder: " & fldr.NameEnd Sub

To set a variable to the CreateFolder function in JScript, use this syntax:

function CreateFolder()
{
  var fso, fldr;
  fso = new ActiveXObject("Scripting.FileSystemObject");
  fldr = fso.CreateFolder("C:\\MyTest");
  Response.Write("Created folder: " + fldr.Name);
}
Accessing the Object's Properties
Once you have a handle to an object, you can access its properties. For example, to get the name of a particular folder, first create an instance of the object, then get a handle to it with the appropriate method (in this case, the GetFolder method, since the folder already exists).

Use this code to get a handle to the GetFolder method in VBScript:

Set fldr = fso.GetFolder("c:\")

To do the same thing in JScript, use the following code:

var fldr = fso.GetFolder("c:\\");

Now that you have a handle to a Folder object, you can check its Name property. Use the following code to check this in VBScript:

Response.Write "Folder name is: " & fldr.Name

To check a Name property in JScript, use this syntax:

Response.Write("Folder name is: " + fldr.Name);

To find out the last time a file was modified, use the following VBScript syntax:

Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
' Get a File object to query.
Set f1 = fso.GetFile("c:\detlog.txt")  ' Print information.
Response.Write "File last modified: " & f1.DateLastModified

To find out the same thing in JScript, use this code:

var fso, f1;
fso = new ActiveXObject("Scripting.FileSystemObject");
// Get a File object to query.
f1 = fso.GetFile("c:\\detlog.txt");  // Print information.
Response.Write("File last modified: " + f1.DateLastModified);

©2000 Microsoft Corporation. All rights reserved.


OpenTextFile Method

Description

Opens a specified file and returns a TextStream object that can be used to read from, write to, or append to the file.

Syntax
object.OpenTextFile(filename[, iomode[, create[, format]]])

The OpenTextFile method has these parts:

Part

Description

object

Required. Object is always the name of a FileSystemObject.

filename

Required. String expression that identifies the file to open.

iomode

Optional. Can be one of three constants: ForReading, ForWriting, or ForAppending.

create

Optional. Boolean value that indicates whether a new file can be created if the specified filename doesn't exist. The value is True if a new file is created, False if it isn't created. If omitted, a new file isn't created.

format

Optional. One of three Tristate values used to indicate the format of the opened file. If omitted, the file is opened as ASCII.

Settings

The iomode argument can have any of the following settings:

Constant

Value

Description

ForReading

1

Open a file for reading only. You can't write to this file.

ForWriting

2

Open a file for writing.

ForAppending

8

Open a file and write to the end of the file.

The format argument can have any of the following settings:

Value

Description

TristateTrue

Open the file as Unicode.

TristateFalse

Open the file as ASCII.

TristateUseDefault

Open the file using the system default.

Remarks

The following code illustrates the use of the OpenTextFile method to open a file for appending text:

var fs, a, ForAppending;
ForAppending = 8;
fs = new ActiveXObject("Scripting.FileSystemObject");
a = fs.OpenTextFile("c:\\testfile.txt", ForAppending, false);
...
a.Close();

©2000 Microsoft Corporation. All rights reserved.


General Usage

Console Application Name

Note : ApexSQL Diff is used as our example file

To use ApexSQL Software via the commandline interface you may simple enter in the product name. For example apexsqldiff.

You may also explicitly call the console application with it's explicit filename [productname].com

Calling to [productname].exe is reserved for the GUI application and will instantiate this instead of initiate the console application. If arguments are included it will throw a warning dialog similar to this

Format of Switches

  1. Switches are case insensitive, i.e. /S: = /s:.
  2. Switches are preceded with one forward slash (/), i.e. /db:pubs.
  3. Switches are followed with a colon (:), i.e. /db:pubs.
  4. There are two types of all switches - abbreviated/alias (1-3 chars) and Full - with full name of switch. i.e. /p and /project
  5. Default values for omitted parameters are used in most cases. Default values are specified in the following format in /help switch (“<default>” if omitted), i.e. (“(local)” if omitted) as illustrated in the following example.

Passing Values

  1. Values should be passed to switches in the following format (see examples).

apexsqldiff.com /switch:value

apexsqldiff.com /switch:"value with spaces"

  1. Use Double quotes for values that contain spaces.

  2. Do not put a space between the colon and the value

Structure of Switches

  1. Switches can be placed in arbitrary order.
  2. Switches and parameters are delimited with a white space from each other.

Connection Switches

  1. If server is not specified “(local)” is assumed.
  2. If user is not specified trusted connection is assumed.
  3. If no server specified output “No server was specified so assuming “(local)” server.
  4. If no security specified output “No security parameter was specified so assuming trusted connection”.

File IO

  1. All applications have a /force switch which will override existing file of same name. Otherwise this should produce an error.
  2. All input (profiles, projects) and output (scripts, reports) files are stored and taken from/into the current directory unless explicit output directory has been specified as a part of file name/path.
  3. All application/error logs/dumps are written to the current directory.

Special Keys

  1. It is possible to abort execution with Ctrl+C and Ctrl+Break key combinations.

Output Redirection

To spool output to file vs the console you can use either > (for file output) or >> (for file append)

For Example :

Return Codes

All ApexSQL applications should have support for common return codes. Individual application support of these codes may vary until implementation has been completed in all applications

0 – Success

1 – General error

2 – Illegal argument combination/duplication. Some arguments may not appear in command line at the same time. Some arguments may not be duplicated in command line. Some required arguments are missed in command line.

3 – Invalid value. The value supplied for an argument is invalid.

4 – IO error. Some error occurred during IO operation. Also returned if an application attempts to write to existent file without a user having specified a /force option.

5 – Insufficient permissions. Action cannot be completed because a user doesn’t have required permissions.

To obtain a return code note the following example

@C:\ApexSQL\ApexSQLDiff\ApexSQLDiff.com /s1:ABN\TEST2001 /s2:ABN\TEST2000 /d1:pubs /d2:pubs70 /v /f

@echo ApexSQLDiff exit code is %errorlevel%

Common Switches

Help

All console enabled ApexSQL applications support the Help Switch which can be called using either switch /help or /?

The following is an example of Help output

Verbose Switch

All console supported apps. support the Verbose switch which will spool out meaningful messages, warnings, etc during processing. With this switch specified application outputs full operation information to a console. Otherwise it operates in silent mode without any output produced (except some specific switches like /help and /test specified). The following is an example of the generation of a synchronization script with verbose switch on.

Test Switch

All console supported apps. support the Test switch which is a useful feature to validate your CLI input prior to processing. This will simply parse switch input and if successful, will list all successfully parsed switches. Application error handling takes precedence over the Test output - so if there is a incorrect switch value then the standard error messages will be returned.


cli_examples

Command Line Examples

Some examples of the command line input are as follows:

Generating the table description files for every table from database Northwind, using template 'Example_Tables.ptpx'. Files are stored in current directory. The file name format is 'Table_table_name' :

ApexSQLCode.com /d:Northwind /tf:".\Templates\Examples\Example_Tables.ptpx" /fn:"Table_{name}"

Generating the description files for views, specified in MyViews.apr selection file, from Northwind, using template 'Example_View.ptpx' and verbose option. Files are stored in 'd:\Docs\views' directory:

ApexSQLCode.com /d:Northwind /tf:".\Templates\Examples\Example_View.ptpx" /v /ot:v /sf:MyViews.apr /od:"d:\Docs\views"

Generating the description file for all views from Northwind, using template 'Example_View.ptpx'. Output writes into the single file - 'd:\Docs\Northwind\View.txt':

ApexSQLCode.com /d:Northwind /tf:".\Templates\Examples\Example_View.ptpx" /ot:v /fo:SingleFile /od:"d:\Docs" /fn:"{database}\{type}"

Generating a 'Insert' stored procedure for each table from Northwind database, using template 'SQL_Basic_Sproc_Table.ptpx' and storing files in 'd:\Docs\Procs'. The file name format is 'InsertIntotable_name' :

ApexSQLCode.com /d:Northwind /tf:".\Templates\SQL\SQL_Basic_Sproc_Table.ptpx" /op:i /od:"d:\Docs\Procs" /fn:"InsertInto{name}"


cli_Connection2

Connection Options Switches

For more information on each switch, click the respective link.

  • /server

  • /login

  • /password

  • /database

/server | Alias: | /s
 | Arguments: | /server :server_name[\instance_name]
 | Required: | -
 | Default: | (local)
 | Description: | Specifies the server and instance name to connect to
 | Example: | ApexSQLCode.com /s:(local)\VSNET ...

Top

/login | Alias: | /u
 | Arguments: | /login :user_login
 | Required: | -
 | Default: | -
 | Description: | Specifies login when you use SQL Server authentication. You must use this option together with /password option. Omitted means: the trusted connection
 | Example: | ApexSQLCode.com ... /u:sa ...

Top

/password | Alias: | /p
 | Arguments: | /password :password
 | Required: | -
 | Default: | -
 | Description: | Specifies login's password when you use SQL Server authentication. You must use this option together with /login option.
 | Example: | ApexSQLCode.com ... /p:password ...

Top

/database | Alias: | /d
 | Arguments: | /database :database
 | Required: | yes
 | Default: | -
 | Description: | Specifies the name of the database to connect to.
 | Example: | ApexSQLCode.com ... /d:pubs ...

Top

© 2006 ApexSQL LLC. All Rights Reserved.


cli_Output_Options

File Output Switches

For more information on each switch, click the respective link.

  • /outputdir

  • /filename

  • /file_operations

  • /force

/outputdir | Alias: | /od
 | Arguments: | /outputdir :dirname
 | Required: | -
 | Default: | The 'Output File Path' of template (if it's not empty), Current directory otherwise.
 | Description: | Specifies the output directory for generated files.
 | Example: | ApexSQLCode ... /od:C:\temp...

Top

/filename | Alias: | /fn
 | Arguments: | /fn :file_name_template
 | Required: | -
 | Default: | OutputFileName of processed template is used (if it's not empty), a default file name template otherwise.
 | Description: | Specifies the template of generated file name.
 | Example: | ApexSQLCode ... /fn:"The{name}Class" ...

Top

/file_operations | Alias: | /fo
 | Arguments: | /fo :[SingleFile|MultiFile|Suppress]
 | Required: | -
 | Default: | A file operations of processed template are used.
 | Description: | Overrides the file operations of processed template. Arguments can not be combined.
 | Example: | ApexSQLCode ... /fo:SingleFile ...

Top

/force | Alias: | /f
 | Arguments: | -
 | Required: | -
 | Default: | -
 | Description: | Specifies override existing file of same name.
 | Example: | ApexSQLCode.com ... /f ...

Top

© 2006 ApexSQL LLC. All Rights Reserved.


cli_Other_Options

Other Options Switches

For more information on each switch, click the respective link.

  • /help

  • /test

  • /verbose

/help | Alias: | /?
 | Arguments: | -
 | Required: | -
 | Default: | -
 | Description: | Prints help messages.
 | Example: | ApexSQLCode.com ... /? ...

Top

/test | Alias: | /t
 | Arguments: | -
 | Required: | -
 | Default: | -
 | Description: | Output parsed options without taking any action.
 | Example: | ApexSQLCode.com ... /t ...

Top

/verbose | Alias: | /v
 | Arguments: | -
 | Required: | -
 | Default: | -
 | Description: | Print process messages.
 | Example: | ApexSQLCode.com ... /v ...

Top

© 2006 ApexSQL LLC. All Rights Reserved.


cli_Output_Options

Architecture Switches

For more information on each switch, click the respective link.

  • /templatefile

  • /prop_values

  • /selectionfile

  • /objectstype

  • /operations

/templatefile | Alias: | /tf
 | Arguments: | /templatefile :filename
 | Required: | yes
 | Default: | -
 | Description: | Specifies the template file to process.
 | Example: | ApexSQLCode /templatefile:CSharp_Class.ptpx ...

Top

/prop_values | Alias: | /pv
 | Arguments: | /pv :filename
 | Required: | -
 | Default: | Every custom property is set to default value.
 | Description: | Custom properties values <file> to process.
 | Example: | ApexSQLCode ... /pv:values.xml ...

Top

/selectionfile | Alias: | /sf
 | Arguments: | /selectionfile :filename
 | Required: | -
 | Default: | All objects are selected
 | Description: | Specifies selection profile file. Profile contains a list of selected SQL objects from a database, that will be processed. Omitted means: all objects are selected.
 | Example: | ApexSQLCode ... /sf:pubs.apr ...

Top

/objectstype | Alias: | /ot
 | Arguments: | /objectstype :[t|v|p]
 | Required: | -
 | Default: | Tables.
 | Description: | Specifies the type of SQL objects to be processed: t - Tables, v - Views, p - Procedures. Arguments can not be combined.
 | Example: | ApexSQLCode ... /ot:t ...

Top

/operations | Alias: | /op
 | Arguments: | /op :[u|i|d|s|e]
 | Required: | -
 | Default: | All operations.
 | Description: | Specifies the requesting operations. Arguments can be combined.
 | Example: | ApexSQLCode ... /op:uids ...

© 2006 ApexSQL LLC. All Rights Reserved.


support and reporting bugs

Support, Updates and Reporting Bugs

Facilitating the reporting of errors is important to ensure that if the ApexSQL software you have purchased or are evaluating does have bugs, they are promptly fixed.

Please check the version of the software you are using before posting a support question. Your issue may have already been addressed in a newer version. You can check for and automatically obtain new versions of the software as they are released directly from the application itself via the products automatic update facility.

Also please see the troubleshooting section before reporting bugs. We will attempt to post and describe all known issues in this section.

Free Support

Support is available free at the ApexSQL support forum and all support queries will be responded to during the week (Monday to Friday) usually within 24 hours.

Run-Time Errors

Any program run-time errors that occur will display a run-time error dialog (see below) with useful diagnostics that will allow us to to better duplicate and fix the error. Simply hit "Email" and the dialog will email all error diagnostic information to ApexSQL. Alternatively you can copy or save the contents to file and mail error data to bugs@apexsql.com directly. Error data sent to support@apexsql.com and posted on support forum are also acceptable

Contacting Support

If you do not have an actual error, you can still gain access to support easily. Simply post the error on the ApexSQL support forum or email support@apexsql.com

Logic Errors / Design Defect

To report a logic error or design defect, describe the nature of the error and the context that it occurs in a post to the ApexSQL support forum.


_FAQs

I have a question on VBScript or JScript?

Please see this comprehensive online reference http://www.msdn.microsoft.com/scripting

How do I get the Intellisense to show?

Simply type objPB. and you can start the object model from the top and begin to walk down it

What are the code delimiters?

Currently {% and %} although we plan to change to more standard delimiters and also allow users to customize them to whatever they want soon.

What do these check boxes do?

Use these controls to set the values for these ApexSQL Code object model properties:

These properties don't "do anything" on their own but you can use them in a template to make a programmatic decision. For example you can write one template to create Insert, Update, Delete and Select Procedures but embed checks for "if objPB.Operations.DoDelete = True then ..." to see if the user wants to output the Delete. He/she might want to only create Insert procedures. In this way you can avoid writing 4 separate templates each for a different proc type.

Since this is such a common occurrence it was decided to feature these controls prominently on the interface.

Can I output to One file instead of multiple files like when building a large SQL Script?

Yes - set the FileOutput Mode property in the Template Property Page to Append/Single File and it will write all template output to one file for all the Checked objects.

How can I control how the created files are named?

You can set default file naming templates in the Options form using tags like {Server} and {Type}. For example {Type}_{Owner}_{Name} will result in a code generated off the stored procedure byroyalty being called "procedure_dbo_byroyalty.sql"

If you append to a single file tags like {Type} and {Database} might be more appropriate for example {Database}_{Type}s would render to "Northwind_Procedures.sql"

How do I set the File Extension of my generated files?

The file extension is determined by the mapping in Options / File Extension and associated with the Language selected for the template. So if C# is chosen then the file extension will be .cs or whatever the file extension for C# is changed to in this Option tab. This is customizable by the user.

How do I change an Alias for an Object?

The Alias is the only Editable property in the Schema Browser Property Page. Just select and overtype the alias to change it.

Do you have any examples of how to write templates?

A variety of example templates are included in the installation. Every template that begins with "Example_" is an example template that illustrates a variety of objects, collections and concepts. Look for these templates in the App Directory, Templates/Examples folder.

But there are many other templates as well that show real world code generation examples in ASP, C#, SQL and other languages.

The following templates offer good tutorials

Template Name | Purpose
Example.ptp | General purpose example template file
Example_Procs.ptp | Illustrates Stored Procedure Object Model
Example_JS.ptp | Shows how to write templates using JScript
Example_FileSystemObject.ptp | Demonstrates how to use the FileSystemObject

Does ApexSQL Code have any built in templates?

Yes, the ApexSQL Code installation includes thousands of lines of pre-written templates that cover Visual Basic, VB.NET, C#, SQL, Delphi and ASP. These are installed in the installation of the application.

What languages does ApexSQL Code support?

ApexSQL Code supports ANY language used with SQL server, non languages like Documentation (just text) and SQL itself.

ApexSQL Code has built in support for .NET Data Type mapping.

For additional languages and include file can be created to map the language datatypes to SQL server datatypes. An excellent example of this is technique is VCL.inc - a template included in the ApexSQL Code installation

Language syntax definitions exist for the following languages:

Visual Basic
ASP / VBScript
Java / Javascript
Delphi
C#
SQL
Perl
ANSI SQL
XML
VB.NET
Documentation (all black text)

What Database products does ApexSQL Code support?

ApexSQL Code supports Microsoft SQL Server 7.0, 2000 and 2005

I would like to create a new template - where do I begin?

A good place to start is to take an existing template and modify itself somewhat to suit your needs. You can also use the Template Wizard to walk through the steps of template creation.

I have looked through all of the templates but did not find one that suited my needs exactly - what can I do?

Write it yourself! ApexSQL Code is built to allow users to create their own templates vs being an out of the box canned code generation tool. The power of ApexSQL Code is providing a value added environment for to allow develpers to create their own templates as easily as possible.

Wow - these templates look complex - how can I learn to write these myself?

Actually ApexSQL Code templates are easy to write for a couple of reasons:

Intellisense: Intellisense Built into the ApexSQL Code Template editor itself allows you to walk down the object model without referring to the documentation. You can select from a choice of properties that ApexSQL Code serves up to you automatically.

Powerful error handling: ApexSQL Code will handle all Microsoft Script Engine Run-Time errors and display the EXACT line of code where the bad line of code exists. If an error exists you can easily isolate it, fix it and re-run the template.

Popular Scripting Languages: Templates can be written in one of the two most popular scripting languages, Microsoft VBScript or JScript. ApexSQL Code is the only code generation tool in the world that supports JScript.

Good Support: If you have problem or questions you can post them on the ApexSQL Support Forum and get timely responses and free support.

How can I get more control over how output files are written?

Use the FileSystemObject to do your file processing instead of ApexSQL Code. Set the "Suppress File Writing" property in Template Property Page to True and this will tell ApexSQL Code to suppress any File I/O operations allowing you to assume complete control.

Use Example_FileSystemObject.ptpx as an example of how to do this.

Also - see this Article for some neat File I/O tricks

Does ApexSQL Code offer support for multiple Include files?

Yes, ApexSQL Code supports unlimited Include files

Does ApexSQL Code object model have any Read-Write Properties?

Yes, currently you can change the Output File Path and File name. Also the Language and Scripting Language Properties are Read-Write.

Refer to the objPB object model for read - write and read only properties.

Can I instantiateCOM objects on my computer from ApexSQL Code templates?

Yes - via the built in scripting engine in ApexSQL Code any COM object can be instantiated and programmed via ApexSQL Code templates. The FileSystemObject is a good example.


Formatting

Include Functions for Formatting Output

There are four helper functions that allow better and easier formatting of generated code. Those functions are:

1. Sub InitTab(nTabLength)

2. Sub IncTabDepth

3. Sub DecTabDepth

4. Function Tab()

All four functions can be found in “utils.inc”.

Usage

These four functions are used in accord. First, before using any other function, one must call InitTab function that will initialize tab sub-system. Passed argument is the number of space that each tab will represent (2, 4 or any other positive number). Example:

{$

InitTab 6 ‘ initialize tab sub-system with 6 spaces for each tab

$}

IncTabDepth and DecTabDepth should be used in pairs. That is: each IncTabDepth should have a corresponding DecTabDepth. IncTabDepth should be used in places where, if you were manually typing the code, you would press TAB key. On the other hand, DecTabDepth should be used in those places where you would move “delete” the previous tab i.e. in those places where code returns to the previous tab depth. Example (from CSharp_Class.ptp, declaration of Find method):

public bool Find(PK pk)

{{% IncTabDepth %}

return Find({% IncTabDepth

For j = 1 To objTable.Keys.Count

Set objKey = objTable.Keys(j)

objpb.print tab() & "pk." & objKey.Name

If j <> objTable.Keys.Count Then

objpb.print ","

End If

objpb.print vbCrLf

Next

%}

);{% DecTabDepth %}

}{% DecTabDepth %}

You may have noticed that in the example above Tab function is also used. Tab function is the key to better formatting. It returns the TabLength times TabDepth spaces that substitute the tabs a code would have typed manually. It is used in places where we are printing generated code. If the example above was executed, a generated code might look like this:

public bool Find(PK pk)

{

return Find(

pk.uid

);

}

Notice that the “pk.uid” code is printed with correct tab depth. It is because when printed, Tab function was called before the actual code:

objpb.print tab() & "pk." & objKey.Name

Using this technique, all generated code can be better formatted for reading by human eye.


Output_Hack

Contributed by Brandon McPherson of Apexml

OK, here's my groovy little secret for pounding out multiple files easily in ApexSQL Code.

Because ApexSQL Code's 'Script Source' tab in the template editor shows you the code that's being generated, and because your template code gets generated inline with the PB output script, you may not have realized that you can directly manipulate the variables in the output code which ApexSQL Code generates.

For example, imagine the following scenario : You want to build one template that prints multi-line output to 3 different files. Normally, this would be done by creating multiple templates, or creating a big string concatenation block of code, and outputting it to an FSO object.

My friend, it does not have to be this way! :)

If you look at the 'Script Source' after generating the code for just about any template, you'll see a few things that are common each time:

Function ReturnThis()

Dim strVar

... then your script code, and your concatenated template results, followed by ...

ReturnThis = strVar

End Function

Since strVar contains the output which will be 'piped' to a file, you can manipulate this variable, and grab its contents anywhere in your script

Here's some text that would be generated in the template ("Stage One")

{%

Dim fsosql, MyFileSQL

Set fsosql = CreateObject("Scripting.FileSystemObject")

Set MyFileSQL = fsosql.CreateTextFile("c:\" & oTable.Name & "_StageOneOutput.sql", True)

' Since the 'Script Source' tab shows that all the output thus far is sitting in a

' string variable, we can pump this variable out to whatever object we want.

' Although the 'WriteLine' method is normally for one line of output, the variable

' can contain a multi-line string with pages of output (like strVar).

MyFileSQL.WriteLine(strVar)

MyFileSQL.Close

' Since we may want to wipe the buffer for the next phase, we'll erase

' the output already generated, so that we can continue.

strVar = ""

%}

And now that we're on to "Stage Two", we're starting from scratch with a

new output (which would be this code here.

You can also perform another hack which will give function and subroutine

support to your templates as well.

At the bottom of the template, add the following:

{%

objPB.Print MyFunction("This is a test.")

ReturnThis = strVar

End Function

Function MyFunction(sArgument)

MyFunction = "Yee haw! We can call this back to the template." & vbCrLf & sArgument

End Function

Function DeadFunction()

' Placeholder to prevent attempting to assign a value to the

' main output function used by ApexSQL Code.

Dim ReturnThis

%}

These two little tricks add a lot of power to an already powerful development tool.


Ordering Information

ApexSQL Software Logo

ApexSQL accepts credit card orders, orders by phone, fax and mail, checks, and wire transfers.

Price List

Licenses Tier | Unit Price
No Support & Upgrades | $399
1 yr Support & Upgrades | $479
2 yr Support & Upgrades | $543
3 yr Support & Upgrades | $579

Prices, licensing and policies are subject to change without notice. Please check the ApexSQL website Purchase page for latest price information.

See the Sales FAQs for a comprehensive list of commonly asked questions about the sales and ordering process.

Secure Web Order

Purchase this software on-line here.

You will be offered an opportunity to make a purchase online through secure Web server using your credit card or execute a fax, mail, or purchase order.

Phone, Fax, Mail, and Purchase Orders

We also offer payment via Purchase Order, direct credit card, wire transfer etc. Please visit the Sales FAQs Web page or Purchase Page for the most recent information on these payment methods.


Sales FAQs

Apex SQL Sales and Ordering FAQs

Please see the current version of ApexSQL Sales FAQs on the ApexSQL Website.


Activation Guide

Please see the current version of Activation Guide on the ApexSQL Website.


Contact ApexSQL

Contact Information

Address: | ApexSQL LLC 50101 Governors Dr., Suite 200 Chapel Hill, NC 27517 U.S.A.
Free Support: | Product Support Forums
Sales & Service: | Phone: International: Fax: | 866-665-5500 (U.S. and Canada) +1 919-968-8444 +1 919-869-1617
Email Contacts: | Sales: Service: | sales@apexsql.com service@apexsql.com

© 2006 ApexSQL LLC. All Rights Reserved.


Important Links

Please Note the following Important Links

Our site is constantly improving and evolving so please refer to ApexSQL Website for updated information

ApexSQL Purchase Page

ApexSQL Activation Guide

ApexSQL Sales FAQs

ApexSQL Support Forum

Current Price List

ApexSQL Contact Page

© 2006 ApexSQL LLC. All Rights Reserved.


Aligning Output

Include Functions for formatting Output

There are four helper functions that allow better and easier formatting of generated code. Those functions are:

1. Sub InitTab(nTabLength)

2. Sub IncTabDepth

3. Sub DecTabDepth

4. Function Tab()

All four functions can be found in “utils.inc”.

Usage

These four functions are used in accord. First, before using any other function, one must call InitTab function that will initialize tab sub-system. Passed argument is the number of space that each tab will represent (2, 4 or any other positive number). Example:

{$

InitTab 6 ‘ initialize tab sub-system with 6 spaces for each tab

$}

IncTabDepth and DecTabDepth should be used in pairs. That is: each IncTabDepth should have a corresponding DecTabDepth. IncTabDepth should be used in places where, if you were manually typing the code, you would press TAB key. On the other hand, DecTabDepth should be used in those places where you would move “delete” the previous tab i.e. in those places where code returns to the previous tab depth. Example (from CSharp_Class.ptp, declaration of Find method):

public bool Find(PK pk)

{{% IncTabDepth %}

return Find({% IncTabDepth %}

{%

For j = 1 To objTable.Keys.Count

Set objKey = objTable.Keys(j)

objpb.print tab() & "pk." & objKey.Name

If j <> objTable.Keys.Count Then

objpb.print ","

End If

objpb.print vbCrLf

Next

%}

);{% DecTabDepth %}

}{% DecTabDepth %}

You may have noticed that in the example above Tab function is also used. Tab function is the key to better formatting. It returns the TabLength times TabDepth spaces that substitute the tabs a code would have typed manually. It is used in places where we are printing generated code. If the example above was executed, a generated code might look like this:

public bool Find(PK pk)

{

return Find(

pk.uid

);

}

Notice that the “pk.uid” code is printed with correct tab depth. It is because when printed, Tab function was called before the actual code:

objpb.print tab() & "pk." & objKey.Name

Using this technique, all generated code can be better formatted for reading by human eye.


Ammendments

Amendments

This page lists changes to ApexSQL Code that may not be reflected in the Object Model Documentation and Examples.

CUSTOM_LANGUAGE_DATATYPE is removed

TemplateObject Properties are now all read only

Added KeyType property to Key object

Added Type to Key object to correspond to ForeignKey, Primary Key etc. Here are constants

SQLDMOKey_Foreign
 = 3
SQLDMOKey_Primary = 1
SQLDMOKey_Unique = 2
SQLDMOKey_Unknown = 0

New Method for Table, Procedure and View - Load - Loads the object's METADATA

New Property for Table, Procedure and View - IsLoaded - Boolean - Is the METADATA loaded?

See the following for an example

With  objpb.Database
For n = 1 to .Tables.Count
if .Tables(n).Isloaded = False Then
   .Tables(n).Load
End If

objPB.Print .Tables(n).Fields.Count & vbcrlf
Next
End With

Copyright © 2006 ApexSQL LLC. All Rights Reserved.


Code Trouble Shooting

Why does ApexSQL Code include the .NumericScale and .Precision values for Numeric and Decimal datatypes in the ADO code it generates?

.Parameters.Append .CreateParameter("ACCOUNT_ID", adNumeric, adParamInput, 14, dblACCOUNT_ID)

.Parameters("ACCOUNT_ID").NumericScale = 0

.Parameters("ACCOUNT_ID").Precision = 12

Failure to explicitly set these values here will result in "Invalid character value for cast specification" when the command that contains a Numeric or Decimal is executed. The reason for this is the value will be Numeric(0,0) or Decimal (0,0) which can contain no valid value.

Error 3421 Method 'CreateParameter' of object '_Command' failed.

This is the error you will get every time there is anything wrong with the line of code that appends a parameter to the ADO command object, no matter what the error is. Most common causes are that the parameter value is larger than the database will allow. In this example the parameter value is 11 characters but the length of the database field is only 1.

objcmd.Parameters.Append objcmd.CreateParameter("pCompanyName", adVarWChar, adParamInput, 10, strCompanyName)

? len(strCompanyName)

12

Another common problem is an invalid datatype is passed, like a string being passed to an parameter of type integer. For example:

objcmd.Parameters.Append objcmd.CreateParameter("pCompanyCode", adInteger, adParamInput, 10, intCompanyCode)

? intCompanyCode

AFDXY

Why do the ADO constants (i.e. adWChar) in the append parameter code created by ApexSQL Code differ from what ADO itself tells me are the correct parameters when I do a cmd.refresh and then Debug.print cmd.parameters(0).type?

ADO provides incorrect values in some cases for different datatypes. For example, ADO indicates that the sql datatype nvarchar should map to the ADO constant adVarWChar but this will result in an error. The correct datatype is adWChar. Numerous other examples of errors like this exist in the ADO mapping schema and several undocumented workarounds are required to get all of the SQL datatypes to map correctly. The datatype mapping for ApexSQL Code was derived from the original ADO mapping but then altered when mistakes were found to correct values.

You can change these mappings yourself via Options.


Creating Your Own Objects

To create instances of your own objects, you must first define a constructor function for them. A constructor function creates a new object, giving it properties and, if appropriate, methods. For instance, the following example defines a constructor function for pasta objects. Notice the use of the this keyword, which refers to the current object.

// pasta is a constructor that takes four parameters.
function pasta(grain, width, shape, hasEgg)
{
    // What grain is it made of?
    this.grain = grain;
    // How wide is it? (number)
    this.width = width;        // What is the cross-section? (string)
    this.shape = shape;      // Does it have egg yolk as a binder? (boolean)
    this.hasEgg = hasEgg;  }

Once you define an object constructor, you create instances of it with the new operator.

var spaghetti = new pasta("wheat", 0.2, "circle", true);
var linguine = new pasta("wheat", 0.3, "oval", true);

You can add properties to one instance of an object to change that instance, but those properties do not become part of the definition of other objects made with the same constructor, and do not show up in other instances unless you specifically add them. If you want the extra properties to show up in all instances of the object, you must add them to the constructor function, or to the constructor's prototype object (prototypes are discussed in the Advanced documentation).

// Additional properties for spaghetti.
spaghetti.color = "pale straw";
spaghetti.drycook = 7;
spaghetti.freshcook = 0.5;
var chowFun = new pasta("rice", 3, "flat", false);
// Neither the chowFun object, nor any of the other existing
// pasta objects have the three new properties that were added
// to the spaghetti object.
// Adding the 'foodgroup' property to the pasta prototyp object
// makes it available to all instances of pasta objects,
// including those that have already been created.
pasta.prototype.foodgroup = "carbohydrates"
// now spaghetti.foodgroup, chowFun.foodgroup, etc. all
// contain the value "carbohydrates"

Including Methods in the Definition

It is possible to include methods (functions) in the definition of an object. One way to do this is to add include a property in the constructor function that refers to a function defined elsewhere. For instance, the following example expands on the pasta constructor function defined above to include a toString method that will be called if you display the value of the object.

// pasta is a constructor that takes four parameters.
// The first part is the same as above
function pasta(grain, width, shape, hasEgg)
{
    // What grain is it made of?
    this.grain = grain;
    // How wide is it? (number)
    this.width = width;        // What is the cross-section? (string)
    this.shape = shape;      // Does it have egg yolk as a binder? (boolean)
    this.hasEgg = hasEgg;     // Here we add the toString method (which is defined below).
    // Note that we don't put the parentheses after the name of
    // the function; this is not a function call, but a
    // reference to the function itself.
    this.toString = pastaToString;
}
// The actual function to display the contents of a pasta object.
function pastaToString()
{
    // return the properties of the object
    return "Grain: " + this.grain + "\n" +
        "Width: " + this.width + "\n" +
        "Shape: " + this.shape + "\n" +
        "Egg?: " + Boolean(this.hasEgg);
}
var spaghetti = new pasta("wheat", 0.2, "circle", true);
// This will call toString() and display the properties
// of the spaghetti object (required Internet Explorer).
window.alert(spaghetti);
©2000 Microsoft Corporation. All rights reserved.


FAQs

Advanced FAQs


Object Model Architecture

See PB_ObjectModel.chm, which exists as a separate help file, for a description of the object model


Operator Precedence

Operator precedence is a set of rules in JScript. It controls the order in which operations are performed when an expression is evaluated. Operations with a higher precedence are performed before those with a lower one. For example, multiplication is performed before addition.

The following table lists the JScript operators, ordered from highest to lowest precedence. Operators with the same precedence are evaluated left to right.

Operator | Description
. [] () | Field access, array indexing, function calls, and expression grouping
++ -- - ~ ! delete new typeof void | Unary operators, return data type, object creation, undefined values
* / % | Multiplication, division, modulo division
+ - + | Addition, subtraction, string concatenation
<< >> >>> | Bit shifting
< <= > >= instanceof | Less than, less than or equal, greater than, greater than or equal, instanceof
== != === !== | Equality, inequality, strict equality, and strict inequality
& | Bitwise AND
^ | Bitwise XOR
| | Bitwise OR
&& | Logical AND
|| | Logical OR
?: | Conditional
= OP= | Assignment, assignment with operation
, | Multiple evaluation

Parentheses are used to alter the order of evaluation determined by operator precedence. This means an expression within parentheses is fully evaluated before it’s value is used in the remainder of the expression.

For example:

z = 78 * (96 + 3 + 45)

There are five operators in this expression: =, *, (), +, and another +. According to the rules of operator precedence, they are evaluated in the following order: (), +, +, *, =.

  1. Evaluation of the expression within the parentheses occurs first. Within the parentheses, there are two addition operators. Since the addition operators both have the same precedence, they are evaluated from left to right. 96 and 3 are added together first, then 45 is added to this total, resulting in a value of 144.
  2. Multiplication occurs next. 78 is multiplied by 144, resulting in a value of 11232.
  3. Assignment occurs last. 11232 is assigned to z.
©2000 Microsoft Corporation. All rights reserved.


Setting Options

Options

VB

Variable Typing (Null Handling)

See Null Strategies

ADO Datatypes

This section is used to customize the ADO Constants mapped to SQL Server Datatypes.

To edit a ADO constant, Double-Click the constant and enter the new value or click and hold.

"Restore Defaults" will restore all the ADO constants to their default values.

ADO Parameters

This allows the user to set the ADO recordset parameters which are found in the ApexSQL Code object model under ADO.

VB Variable Prefixes

This page allows the user to customize the prefixes/naming conventions of common variables.

This mapping will populate Key or Field Prefix.

To edit, double click or click and hold, then enter in the desired prefix.

"Restore defaults" will overwrite all changes to the original variables ApexSQL Code uses by default.

File Extensions

Scripting Language

Use this section to associate a default file extension with the supported language. This will default the associated file extension when outputting results to file.

Copyright © 1999-2004 Apex SQL Tools c/o Lockwood Technical Svc Inc, All Rights Reserved


options_ADODatatypes

Options : ADO Datatypes

This section is used to customize the ADO Constants mapped to SQL Server Datatypes.

To edit a ADO constant, Double-Click the constant and enter the new value or click and hold. "Restore Defaults" will restore all the ADO constants to their default values.

These values will map to the following syntax in the Object Model


options_ADOParams

Options : VB/ADO : ADO Parameters

This section is used to customize the ADO Constants mapped to SQL Server Datatypes.

This will affect the following values in the object model


options_VBVarPrefs

Options : VB Variable Prefixes

This page allows the user to customize the prefixes/naming conventions of common variables.

This mapping will populate Key or Field Prefix. To edit, double click or click and hold, then enter in the desired prefix.

"Restore defaults" will overwrite all changes to the original variables ApexSQL Code uses by default.


Overview

Type topic text here.


Viewing & Managing SQL Scripts

Viewing Results

All code is generated out to ApexSQL Code code editor. From this interface, code can be edited, Saved and/or Saved As to a new location. Syntax formatting, case normalization, etc. are currently supported for a variety of languages, including Visual Basic, VB.NET, ASP, SQL, C#, Perl, Java and Delphi.


Strategies

Null Strategies

Nulls can be complicated to handle in an application. If the user doesn't type in a value for a field on a form that corresponds to a NULLABLE int field in the database, how do you handle it in the VB proc call function? If you type the VB function parameter as an Integer, it won't accept the NULL. You will have to convert it to a pseudo NULL (i.e. -999) and then convert it back to a NULL at the database level or just let the -999 get into the database. Nulls in Dates and string fields can cause similar problems.

Handling Nulls can be accomplished in a variety of ways, three of which will be addressed here. For these examples, the customers table from Northwind.mdb will be used.

Always use Variants: The easiest way to handle NULLs is to treat all of your VB parameters for the proc call functions (and properties in your classes) as variants. Variants are slower but this difference may be imperceptible to your users, especially since these data access variables are populated once and are not generally involved in loops or other processes intensive operations. Some VB authors have advocated using variants in ALL cases. You can set ApexSQL Code to use only variants in Options under the VB section. Select Type Everything As Variants.

Sometimes use Variants: If you don't want to use only Variants but also don't want to get involved with the complexity of managing NULLs, ApexSQL Code Enterprise introduced a new feature with version 1.12 that automatically makes NULLABLE fields (in the Database) variants. This solution seems to strike a happy medium. It assumes that if a field is required, the client application will pass down a non NULL value. If the database field is NULLABLE, any NULL value that comes down from the client can easily pass down through the tiers in the application as a NULL. In the Options section, select Type Only Nullable Fields As Variants.

Never use Variants: The final option will strongly type all variables, regardless of whether they are NULLABLE in the database or not. In the Options section, select Strongly Type All Variables. This requires some additional work though to handle a situation where a NULL is passed down to a strongly type Visual Basic variable.

The most obvious solution is to convert the empty control/empty string into a NULL when passing the parameter to the VB function. The function call would look something like this:

Exec_qry_upd_Customers txtCustomerID, txtCompanyName, NullIt(txtContactName), NullIt(txtContactTitle), NullIt(txtAddress), NullIt(txtCity), NullIt(txtRegion), NullIt(txtPostalCode), NullIt(txtCountry), NullIt(txtPhone), NullIt(txtFax)

If the option is to explicitly declare VB parameters, then the NULL conversions will have to be processed WITHIN the function, usually in the line of code where the parameter is appended to the ADO command object or passed to the DAO querydef. In the following example, the IIF function is used to wrap the parameter and convert it from an empty string to a NULL, if necessary.

objcmd.Parameters.Append objcmd.CreateParameter("pCompanyName", adVarWChar, adParamInput, 40, IIf(strCompanyName="", NULL, strCompanyName))

or...

objcmd.Parameters.Append objcmd.CreateParameter("pCompanyName", adVarWChar, adParamInput, 40, IIf(len(strCompanyName)=0, NULL, strCompanyName))

Numerics and Dates may have a value of 0 that is the numeric/date equivalent of an empty string. The IIF will have to be modified for these types of variables.

Depending on the design of your database, NULLs may in fact have a key (i.e. 999, -1, 0) that would need to be switched in line when appending the parameter. Sample code to handle this is as follows:

objcmd.Parameters.Append objcmd.CreateParameter("pCompanyId", adInteger, adParamInput, 10, IIf(ISNULL(intCompanyID), 999, intCompanyID))

* Note: this is applicable to Visual Basic code only, Not ASP.


temp_Include

Template Editor : Include Pane

Just like in ASP, you can specify and include file in templates. The main template will reference any functions, variables, etc. that exist in the include file. In this way, you can re-use some of your modular code.

Include files can be edited and saved concurrently with work being done of the template itself.

See Delphi_Basic_Fun_Table.ptpx for an excellent example of how a developer made extensive use of an include file (VCL Header.ptpx) to map SQL Datatypes to Delphi Datatypes during run time.

Include files can be .inc, .vbs or .js files. Based on the scripting language selected by the user, the editor will default to .inc and the file extension for the current scripting language.


temp_PropertyPage

Property Page

The ApexSQL Code property page, included in Template Pane, makes editing templates very easy. Users can directly type into the row like when editing Author. In larger text fields, an ellipse is provided that will open a small text input control to accept data - this applies to Legal and Description. For Output File Path and Output File Name, file/path dialogs are provided. A custom form can be accessed to add includes. All other properties display a picklist.

For more information about these properties, step through the template wizard which offers an easy tour through all template attributes.


Template-Editor


TemplateEditor

Type topic text here.


WizardStep7

Step 7 - Pre-Processing Script

Pre-Processing code can be added to a template right from the wizard. Syntax highlighting and full intellisense is supported.

See Pre-Processing Code for a more detailed explanation of the use and benefits of Pre-Processing code.


cli_index2

Command Line Interface Switch Index

ApexSQL Code has a powerful command line interface that supports a variety of option switches.

Click the link for the respective switch group documentation.

Connection Options
Help Option
Other Options
Output Option

© 2006 ApexSQL LLC. All Rights Reserved.


Changing the Way Topics Appear

You can easily change the size and position of the ApexSQL window and the Navigation and Topic panes.

  • To resize the navigation or topic pane, point to the divider between the two panes. When the pointer changes to a double-headed arrow, drag the divider right or left.

  • To shrink or enlarge ApexSQL Edit online help, point to any corner of the ApexSQL Edit online help window. When the pointer changes to a double-headed arrow, drag the corner.

  • To change the height or width of ApexSQL Edit online help, point to the top, bottom, left, or right edge of the ApexSQL Edit online help window. When the pointer changes to a double-headed arrow, drag the edge.

  • To reposition ApexSQL Edit online help on your screen, click the title bar and drag the ApexSQL Edit online help window to a new position.

Copyright © 2005 ApexSQL LLC. All Rights Reserved.


Documentation Conventions

The following conventions are used in ApexSQL online help file to distinguish elements of text.

  1. UPPERCASE - Transact-SQL keywords and SQL elements.

  2. Initial Capitals - Paths and file names.

  3. Commands and button names are indicated with bold font and title case, as follows: On the File menu, click Open.

  4. Dialog box names are indicated with bold font and title case, as follows: On the Options dialog box, click Apply.

  5. Drop-down text - In some sections, drop-down text is provided for the various parameters. Click on the drop-down text once to view the image or text for the particular parameter. Click on the drop-down text again to make the image or text unavailable. Click here to see an example of a drop-down text.

Copyright © 2005 ApexSQL LLC. All Rights Reserved.