Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Reconfiguring Remote Databases via SQL

Reconfiguring Remote Databases via SQL

Here's a dilemma faced by nearly every developer of Access databases who's got the database stored on a remote CF server. How do you reconfigure the database if you want to add, change or remove columns, tables or indexes? If it's remote, you can't very well use the Access interface to do so.

So you've probably bitten the bullet and resorted to simply downloading the database, changing it locally, then uploading it back to the server. It's a brute force method, and it does indeed work, but at a price - especially compared to a much faster, safer and generally more productive approach.

This month we look at how to perform such database configurations remotely through SQL. You'll still use CFQUERY (though there are some issues to be aware of), but you'll use different SQL commands that may be new to you.

What's Wrong with the Brute Force Approach?
If you reconfigure your remote database by way of the download/ edit/upload approach rather than by using SQL commands to perform those actions, you'll encounter several drawbacks:

  • You're wasting precious time with the lengthy download/ upload.
  • You're possibly risking the integrity of your database (if you don't keep users from updating it during the lengthy download/ edit/upload process).
  • You're increasing downtime for your visitors (if you do arrange to keep them out of the database during that process).

    Of course, this brute force approach is limited to file system databases in which the changed database can be downloaded/uploaded, which typically means we're referring to Access. There are plenty of arguments against the long-term viability of running a production application in Access, but it's what many are forced or choose to use.

    Users of databases like SQL Server, Sybase and Oracle have probably been using SQL for a long time, since the brute force approach simply wasn't available to them. And those enterprise-class DBMSs also typically offer tools to administer a remote database with a properly configured network connection to the remote server.

    But what if you don't have such a tool, or can't get that "proper network connection" arranged? Then this SQL approach may be important, and new to you, too. Finally, note that SQL Server 7 databases are now based on files (rather than "devices") that can be downloaded/uploaded in a similar fashion, so novice SQL Server developers may be applying the brute force approach as well.

    About the SQL Approach
    The SQL approach to reconfiguring a remote database involves a few steps, and doing it the first couple of times may indeed seem to take you longer than the brute force approach only because you have to learn a few new things and set up some procedures the first time around. Once you've become familiar with the SQL approach, however, you'll find it definitely faster than brute force (or it will be valuable to you if you simply didn't know how to remotely administer your enterprise class databases via SQL).

    First decide what you want to do (add/change/remove columns/indexes/tables, etc.), then determine the SQL DDL (Data Definition Language) statement to use and determine the appropriate parameters for the statements (such as data types available when adding columns, which may vary depending on the database management system). We'll discuss the available actions and corresponding SQL DDL statements later. There are even tools to help build this DDL.

    (The SQL statements you're more familiar with, such as SELECT, INSERT and UPDATE, are referred to as DML - Data Manipulation Language. They're both still SQL, and are executed the same way in CF. They're just classified separately.)

    You'd be wise to make your next step a test of the set of DDL statements on a local copy of the database - which needn't necessarily have the latest data from the remote version. You just want to make sure you haven't made a mistake in your SQL. If it works locally, it'll almost certainly work remotely - though not necessarily. If the database drivers on your local and remote system aren't the same, the code could respond differently.

    Finally, before attempting to execute the tested set of SQL remotely, it would probably be wise to create a backup of the database on the remote server. While that may seem to negate the time savings of the brute force approach and open up some of the integrity issues referred to before, keep in mind that making a copy on the remote server itself (via operating system file management commands or CFFILE) will be very fast, or at least orders of magnitude faster than downloading/uploading the file even over a fast Internet connection.

    I lay these steps out now, before focusing on the all-important SQL DDL statements, because I want you to appreciate all the steps in the approach before getting overly excited about it. The SQL is pretty easy to use, but it's also easy to make a mistake. Approaching this process in a cavalier manner would be dangerous to your data, your users and possibly your job! Whenever you're dealing with reconfiguring your databases, you must exercise supreme caution.

    It's not really that what we're discussing here is any more dangerous than making the edits via the brute force approach. You can make a mistake in either case. But since you're issuing commands rather than using a graphical user interface, there's a little more potential for making a mistake. As always, forewarned is forearmed.

    Creating a Table via DDL
    Okay, so you're ready and willing to try reconfiguring your database entirely via SQL. Let's jump right in. Say you need to create a new table for your database. SQL has been designed from its inception to be very English-like, so the syntax is indeed straightforward. You'd want to use CREATE TABLE.

    More specifically, you'd describe the names and descriptions (data type, size, etc.) of each of the columns in the table. For instance, you might enter the following command:

    CREATE TABLE Tasks (
    TaskId COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
    Name TEXT(255) ,
    Description MEMO NULL,
    RequestedBy SHORT NULL CONSTRAINT FK_RequestedBy
    REFERENCES Employees (EmployeeID),
    Priority TEXT(10) ,
    EstTimeToComplete TEXT(50) NULL
    Completed BIT NULL
    )

    Don't let some of the options throw you. It's really pretty simple. This creates a table called Tasks with several columns. The first, TaskID, is defined as a COUNTER field. That's the ODBC term for what's called an AutoNumber field in Access and an Identity Column in SQL Server. You don't use Auto-Number when using DDL to configure or reconfigure columns for an Access table. You have to use the proper SQL "data type." Notice the other types of columns created, including TEXT, SHORT, MEMO and BIT.

    Table 1 shows several of the available data types as defined in the Access "Jet Engine" SQL reference. Some of them will work in SQL Server and other DBMSs as well. See the documentation for your specific database for more details. (If any aspect of this DDL fails in a database other than Access - toward which this article is primarily focused - see your database documentation to find the equivalent statements.)

    Note too that the TaskId column was defined as the primary key. You can do that via DDL as well. We even defined the Requested_By column as a foreign key with a CONSTRAINT clause defining a relationship to the Employees table, specifically the EmployeeId column. Finally, note that you can also indicate if a column is allowed to take on NULL values by adding the NULL keyword.

    Entering the Commands via CFQUERY
    Put those SQL commands in a CFQUERY just as you would Select commands. Just be aware that, as with SELECT statements, if you need to enter multiple statements, you need to enter them in separate CFQUERYs.

    There are two potential traps here:

    1. The Datasource definition in the CF Administrator may be set to prevent any SQL statements other than SELECT, INSERT, UPDATE, DELETE and/or stored procedures. This is an option available under the "CF Settings" button for a given data source in the Administrator. Be sure that all five checkboxes are turned off.

    2. Be sure that you have the permissions to enter these commands. Sadly, in most Access databases there's no security defined to prevent this, but if there is, or if you're using an enterprise-class database, you may need to specify an authorized user ID in either the CFQUERY (via the USERNAME and PASSWORD attributes) or in the Datasource definition in the Administrator. Again, see the "CF Settings" button there.

    Performing Additional Reconfiguration via DDL
    You can do all sorts of other things to reconfigure your database remotely.

    Let's say you want to add an index to an existing table on your remote database. It's as simple as:

    CREATE INDEX Completed ON Tasks
    (Completed ASC)

    Or to add a relationship definition:

    ALTER TABLE Employees
    ADD CONSTRAINT FK_DeptID
    FOREIGN KEY (DeptID) REFERENCES Departments (DeptID)

    Note that when defining relationships in both the ALTER TABLE and CREATE TABLE, we can't indicate cascading deletes or updates (in Access, at least). We also can't modify relationships. We must drop and re-create them. That's easy, too. It's our first look at the DROP statement:

    ALTER TABLE Employees
    DROP CONSTRAINT FK_DeptID

    You can also drop columns, but first let's see how to add them with:

    ALTER TABLE Tasks
    ADD Authorization_level SHORT NULL

    Dropping is just as easy: ALTER TABLE Tasks
    DROP Authorization_level

    (Although SQL supports a RE-NAME and MODIFY clause for the ALTER TABLE statement, in Access - the Jet Engine - once it's created you can't alter the data type of a field or change its name. The only way to convert an existing field from one data type to another is to add a new field, run an update query to populate the field with values from the original field, then drop the original field.)

    You can also delete or drop an entire table with:

    DROP TABLE Tasks

    This is a dangerous command, of course. Use it with caution! (One potential source of confusion: notice that you don't drop columns or indexes via a DROP statement. Instead, you drop them by way of a DROP clause within the ALTER TABLE statement, a subtle but important difference.)

    Some Closing Thoughts
    As useful as this SQL DDL approach is, you still have to create the commands by hand. Or so it would seem. There are ways to get it created for you. If you have access to Erwin, it can generate DDL. There's also a downloadable tool for Access 97 that can generate DDL from an existing database. You could make changes on a local copy (again, don't worry about the data being updated as long as the database definition matches your remote file) and output the DDL to run against the remote database.

    One other potential gotcha: if any of your column or table names are reserved words in SQL, you can surround them in square brackets. That protects you from SQL errors if you happen to use reserved words for table or column names. Of course, you may omit the brackets and let the error occur during this process to catch you making the mistake. But sometimes the failure in the SQL won't be so obvious. The choice is yours.

    For More Information
    You can find that list of Access (or, technically, "Jet Engine") data types as well as the DDL statements in two places. If you have Access on your workstation, look in the Help, in the Table of Contents. At the very bottom (in both Access 97 and 2000) is a "book" or section in the list called "Microsoft SQL Jet Reference." See both the Overview and Data Definition Language sections.

    These books are also available online at Microsoft, in their MSDN library. Visit the following link to see the data types, http://msdn.micro-soft.com/library/sdkdoc/daosdk/dajsql05_1yr7.htm. From there, use the "Show TOC" link. Look for the specific DDL statements to be covered (strangely enough) under "Microsoft Jet Database Engine SQL Reserved Words," which appears a few sections down in the Table of Contents.

    Both the Access Help file and Web site also offer examples.

    In addition, there's an interesting discussion of some of the limitations of doing SQL in the Jet Engine at http://msdn.microsoft.com/library/psdk/dasdk/odbc50qb.htm. Finally, the Web site also offers an online book, the "MS Jet DB Engine Programmer's Guide." And Chapter 3 of that book, available at http://msdn.microsoft.com/library/books/dnjet/c3_body.htm, offers an even better written discussion of DDL topics. Be sure to open the TOC and then the subsections of the chapter to see those that are headed "...by Using SQL DDL," such as "Creating and Deleting Indexes by Using SQL DDL." It covers creating and deleting tables, columns and relationships as well.

  • More Stories By Charlie Arehart

    A veteran ColdFusion developer since 1997, Charlie Arehart is a long-time contributor to the community and a recognized Adobe Community Expert. He's a certified Advanced CF Developer and Instructor for CF 4/5/6/7 and served as tech editor of CFDJ until 2003. Now an independent contractor (carehart.org) living in Alpharetta, GA, Charlie provides high-level troubleshooting/tuning assistance and training/mentoring for CF teams. He helps run the Online ColdFusion Meetup (coldfusionmeetup.com, an online CF user group), is a contributor to the CF8 WACK books by Ben Forta, and is frequently invited to speak at developer conferences and user groups worldwide.

    Comments (1) View Comments

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    Most Recent Comments
    charles arehart 03/06/02 09:55:00 AM EST

    Folks, this article from July 2000 has a few links to Microsoft that, sadly, no longer work. Thanks to the heads up by Carina Cojeen on the Toronto CFUG list for bringing this to my attention.

    She's offered a couple of links that may help and that do work as of March 2002:

    DDL/SQL for Access:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q180841

    Create Table:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office9...

    Alter Table:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office9...

    And I found the following listing of various Access resources on the MS site:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k...

    IoT & Smart Cities Stories
    We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
    Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
    As IoT continues to increase momentum, so does the associated risk. Secure Device Lifecycle Management (DLM) is ranked as one of the most important technology areas of IoT. Driving this trend is the realization that secure support for IoT devices provides companies the ability to deliver high-quality, reliable, secure offerings faster, create new revenue streams, and reduce support costs, all while building a competitive advantage in their markets. In this session, we will use customer use cases...
    Machine learning has taken residence at our cities' cores and now we can finally have "smart cities." Cities are a collection of buildings made to provide the structure and safety necessary for people to function, create and survive. Buildings are a pool of ever-changing performance data from large automated systems such as heating and cooling to the people that live and work within them. Through machine learning, buildings can optimize performance, reduce costs, and improve occupant comfort by ...
    René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
    With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
    Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
    Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
    Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science" is responsible for guiding the technology strategy within Hitachi Vantara for IoT and Analytics. Bill brings a balanced business-technology approach that focuses on business outcomes to drive data, analytics and technology decisions that underpin an organization's digital transformation strategy.
    DXWorldEXPO LLC, the producer of the world's most influential technology conferences and trade shows has announced the 22nd International CloudEXPO | DXWorldEXPO "Early Bird Registration" is now open. Register for Full Conference "Gold Pass" ▸ Here (Expo Hall ▸ Here)