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 (
    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
    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

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

    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:

    Create Table:

    Alter Table:

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


    @ThingsExpo Stories
    Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
    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...
    Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
    The IoT Will Grow: In what might be the most obvious prediction of the decade, the IoT will continue to expand next year, with more and more devices coming online every single day. What isn’t so obvious about this prediction: where that growth will occur. The retail, healthcare, and industrial/supply chain industries will likely see the greatest growth. Forrester Research has predicted the IoT will become “the backbone” of customer value as it continues to grow. It is no surprise that retail is ...
    Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...
    The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
    DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of bus...
    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...
    DXWorldEXPO LLC announced today that "Miami Blockchain Event by FinTechEXPO" has announced that its Call for Papers is now open. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Financial enterprises in New York City, London, Singapore, and other world financial capitals are embracing a new generation of smart, automated FinTech that eliminates many cumbersome, slow, and expe...
    Cloud Expo | DXWorld Expo have announced the conference tracks for Cloud Expo 2018. Cloud Expo will be held June 5-7, 2018, at the Javits Center in New York City, and November 6-8, 2018, at the Santa Clara Convention Center, Santa Clara, CA. Digital Transformation (DX) is a major focus with the introduction of DX Expo within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive ov...
    DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
    DXWorldEXPO | CloudEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
    DXWorldEXPO LLC announced today that ICOHOLDER named "Media Sponsor" of Miami Blockchain Event by FinTechEXPO. ICOHOLDER give you detailed information and help the community to invest in the trusty projects. Miami Blockchain Event by FinTechEXPO has opened its Call for Papers. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Miami Blockchain Event by FinTechEXPO also offers s...
    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.
    Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
    Digital Transformation and Disruption, Amazon Style - What You Can Learn. Chris Kocher is a co-founder of Grey Heron, a management and strategic marketing consulting firm. He has 25+ years in both strategic and hands-on operating experience helping executives and investors build revenues and shareholder value. He has consulted with over 130 companies on innovating with new business models, product strategies and monetization. Chris has held management positions at HP and Symantec in addition to ...
    Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
    Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities - ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups.
    The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...
    Predicting the future has never been more challenging - not because of the lack of data but because of the flood of ungoverned and risk laden information. Microsoft states that 2.5 exabytes of data are created every day. Expectations and reliance on data are being pushed to the limits, as demands around hybrid options continue to grow.