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

    @ThingsExpo Stories
    Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution and join Akvelon expert and IoT industry leader, Sergey Grebnov, in his session at @ThingsExpo, for an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
    Because IoT devices are deployed in mission-critical environments more than ever before, it’s increasingly imperative they be truly smart. IoT sensors simply stockpiling data isn’t useful. IoT must be artificially and naturally intelligent in order to provide more value In his session at @ThingsExpo, John Crupi, Vice President and Engineering System Architect at Greenwave Systems, will discuss how IoT artificial intelligence (AI) can be carried out via edge analytics and machine learning techn...
    From 2013, NTT Communications has been providing cPaaS service, SkyWay. Its customer’s expectations for leveraging WebRTC technology are not only typical real-time communication use cases such as Web conference, remote education, but also IoT use cases such as remote camera monitoring, smart-glass, and robotic. Because of this, NTT Communications has numerous IoT business use-cases that its customers are developing on top of PaaS. WebRTC will lead IoT businesses to be more innovative and address...
    WebRTC is the future of browser-to-browser communications, and continues to make inroads into the traditional, difficult, plug-in web communications world. The 6th WebRTC Summit continues our tradition of delivering the latest and greatest presentations within the world of WebRTC. Topics include voice calling, video chat, P2P file sharing, and use cases that have already leveraged the power and convenience of WebRTC.
    SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
    SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
    In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, discussed the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insights from the vast t...
    The question before companies today is not whether to become intelligent, it’s a question of how and how fast. The key is to adopt and deploy an intelligent application strategy while simultaneously preparing to scale that intelligence. In her session at 21st Cloud Expo, Sangeeta Chakraborty, Chief Customer Officer at Ayasdi, will provide a tactical framework to become a truly intelligent enterprise, including how to identify the right applications for AI, how to build a Center of Excellence to ...
    SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
    Recently, IoT seems emerging as a solution vehicle for data analytics on real-world scenarios from setting a room temperature setting to predicting a component failure of an aircraft. Compared with developing an application or deploying a cloud service, is an IoT solution unique? If so, how? How does a typical IoT solution architecture consist? And what are the essential components and how are they relevant to each other? How does the security play out? What are the best practices in formulating...
    In his session at @ThingsExpo, Arvind Radhakrishnen discussed how IoT offers new business models in banking and financial services organizations with the capability to revolutionize products, payments, channels, business processes and asset management built on strong architectural foundation. The following topics were covered: How IoT stands to impact various business parameters including customer experience, cost and risk management within BFS organizations.
    Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...
    SYS-CON Events announced today that Elastifile will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Elastifile Cloud File System (ECFS) is software-defined data infrastructure designed for seamless and efficient management of dynamic workloads across heterogeneous environments. Elastifile provides the architecture needed to optimize your hybrid cloud environment, by facilitating efficient...
    There is only one world-class Cloud event on earth, and that is Cloud Expo – which returns to Silicon Valley for the 21st Cloud Expo at the Santa Clara Convention Center, October 31 - November 2, 2017. Every Global 2000 enterprise in the world is now integrating cloud computing in some form into its IT development and operations. Midsize and small businesses are also migrating to the cloud in increasing numbers. Companies are each developing their unique mix of cloud technologies and service...
    SYS-CON Events announced today that Golden Gate University will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Since 1901, non-profit Golden Gate University (GGU) has been helping adults achieve their professional goals by providing high quality, practice-based undergraduate and graduate educational programs in law, taxation, business and related professions. Many of its courses are taug...
    SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
    SYS-CON Events announced today that DXWorldExpo has been named “Global Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Digital Transformation is the key issue driving the global enterprise IT business. Digital Transformation is most prominent among Global 2000 enterprises and government institutions.
    21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
    Recently, WebRTC has a lot of eyes from market. The use cases of WebRTC are expanding - video chat, online education, online health care etc. Not only for human-to-human communication, but also IoT use cases such as machine to human use cases can be seen recently. One of the typical use-case is remote camera monitoring. With WebRTC, people can have interoperability and flexibility for deploying monitoring service. However, the benefit of WebRTC for IoT is not only its convenience and interopera...
    When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...