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)

    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.


    @ThingsExpo Stories
    SYS-CON Events announced today that Technologic Systems Inc., an embedded systems solutions company, will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Technologic Systems is an embedded systems company with headquarters in Fountain Hills, Arizona. They have been in business for 32 years, helping more than 8,000 OEM customers and building over a hundred COTS products that have never been discontinued. Technologic Systems’ pr...
    SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 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 ...
    The taxi industry never saw Uber coming. Startups are a threat to incumbents like never before, and a major enabler for startups is that they are instantly “cloud ready.” If innovation moves at the pace of IT, then your company is in trouble. Why? Because your data center will not keep up with frenetic pace AWS, Microsoft and Google are rolling out new capabilities In his session at 20th Cloud Expo, Don Browning, VP of Cloud Architecture at Turner, will posit that disruption is inevitable for c...
    SYS-CON Events announced today that Cloudistics, an on-premises cloud computing company, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Cloudistics delivers a complete public cloud experience with composable on-premises infrastructures to medium and large enterprises. Its software-defined technology natively converges network, storage, compute, virtualization, and management into a ...
    Keeping pace with advancements in software delivery processes and tooling is taxing even for the most proficient organizations. Point tools, platforms, open source and the increasing adoption of private and public cloud services requires strong engineering rigor - all in the face of developer demands to use the tools of choice. As Agile has settled in as a mainstream practice, now DevOps has emerged as the next wave to improve software delivery speed and output. To make DevOps work, organization...
    SYS-CON Events announced today that Loom Systems will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Founded in 2015, Loom Systems delivers an advanced AI solution to predict and prevent problems in the digital business. Loom stands alone in the industry as an AI analysis platform requiring no prior math knowledge from operators, leveraging the existing staff to succeed in the digital era. With offices in S...
    The explosion of new web/cloud/IoT-based applications and the data they generate are transforming our world right before our eyes. In this rush to adopt these new technologies, organizations are often ignoring fundamental questions concerning who owns the data and failing to ask for permission to conduct invasive surveillance of their customers. Organizations that are not transparent about how their systems gather data telemetry without offering shared data ownership risk product rejection, regu...
    SYS-CON Events announced today that Interoute, owner-operator of one of Europe's largest networks and a global cloud services platform, has been named “Bronze Sponsor” of SYS-CON's 20th Cloud Expo, which will take place on June 6-8, 2017 at the Javits Center in New York, New York. Interoute is the owner-operator of one of Europe's largest networks and a global cloud services platform which encompasses 12 data centers, 14 virtual data centers and 31 colocation centers, with connections to 195 add...
    SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
    SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buyers...
    SYS-CON Events announced today that T-Mobile will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. As America's Un-carrier, T-Mobile US, Inc., is redefining the way consumers and businesses buy wireless services through leading product and service innovation. The Company's advanced nationwide 4G LTE network delivers outstanding wireless experiences to 67.4 million customers who are unwilling to compromise on ...
    SYS-CON Events announced today that Infranics will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Since 2000, Infranics has developed SysMaster Suite, which is required for the stable and efficient management of ICT infrastructure. The ICT management solution developed and provided by Infranics continues to add intelligence to the ICT infrastructure through the IMC (Infra Management Cycle) based on mathemat...
    SYS-CON Events announced today that SD Times | BZ Media has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. BZ Media LLC is a high-tech media company that produces technical conferences and expositions, and publishes a magazine, newsletters and websites in the software development, SharePoint, mobile development and commercial UAV markets.
    SYS-CON Events announced today that Telecom Reseller has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
    In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
    "I think that everyone recognizes that for IoT to really realize its full potential and value that it is about creating ecosystems and marketplaces and that no single vendor is able to support what is required," explained Esmeralda Swartz, VP, Marketing Enterprise and Cloud at Ericsson, in this SYS-CON.tv interview at @ThingsExpo, held June 7-9, 2016, at the Javits Center in New York City, NY.
    SYS-CON Events announced today that HTBase will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. HTBase (Gartner 2016 Cool Vendor) delivers a Composable IT infrastructure solution architected for agility and increased efficiency. It turns compute, storage, and fabric into fluid pools of resources that are easily composed and re-composed to meet each application’s needs. With HTBase, companies can quickly prov...
    Web Real-Time Communication APIs have quickly revolutionized what browsers are capable of. In addition to video and audio streams, we can now bi-directionally send arbitrary data over WebRTC's PeerConnection Data Channels. With the advent of Progressive Web Apps and new hardware APIs such as WebBluetooh and WebUSB, we can finally enable users to stitch together the Internet of Things directly from their browsers while communicating privately and securely in a decentralized way.
    DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
    What sort of WebRTC based applications can we expect to see over the next year and beyond? One way to predict development trends is to see what sorts of applications startups are building. In his session at @ThingsExpo, Arin Sime, founder of WebRTC.ventures, will discuss the current and likely future trends in WebRTC application development based on real requests for custom applications from real customers, as well as other public sources of information,