?
Solved

Coldfusion previously with Access but now will be in DB2

Posted on 2004-09-22
6
Medium Priority
?
164 Views
Last Modified: 2013-12-24
Hello

I developed this application with Coldfusion and Access.  My IT Dept wants to convert the backend database from Access to DB2.  If the data will now be in DB2, will I have to adjust my SQL that now works with Access.  How will this work, and what problems will i have along the way.  Please help

Thanks
0
Comment
Question by:mdbbound
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 640 total points
ID: 12128444
If DB2 is using the sql standard then you should not have to much trouble - if you do, it will mainly be when you use functions in Access, they might not be called the same in DB2...
And if you use any other non standard functionality from Access...
0
 

Author Comment

by:mdbbound
ID: 12129462
Thanks Tacobell777

My queries are Select, some with INNERJOIN, the most number of tables i join is three.
I also have some ADD, DELETE, UPDATE.  I used some simple Query of Queries in coldfusion.

Thanks
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12129641
Migrating a Microsoft Access 2000 Database to IBM DB2 Universal Database 7.2
http://www-106.ibm.com/developerworks/db2/library/techarticle/alazzawe/0112alazzawe2.html
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 640 total points
ID: 12129661
from that site
[quote]
Summary
On the Microsoft Windows platforms it is common to prototype Microsoft Visual Basic applications using Microsoft Access databases. When the prototype phase is completed, applications are then migrated to a relational database server, specifically IBM DB2 Universal Database V7.2.

There are a number of ways to export Access database tables to DB2. This article described two such methods: the Access Export Tool and the DB2 OLE DB Table UDF Assist Wizard. The UDF wizard has many advantages over the alternative. Some of these include the flexibility it offers in terms of remapping of column types, reordering of columns, selecting a subset of columns, providing more accurate default data types, and the ability to export tables, accommodate results of a query from possibly multiple-joined tables, as well as the ability to create a view to access the data directly from the OLE DB data source.
[\quote]

another way of migrating is using MsSQL Server ImportExport  Data.

note that there are SQL statements that works well in MsAccess but not on other languages.
like Custom defined functions used in SQL statements, the NZ() funtion will not work, etc.
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 720 total points
ID: 12134112
If you are talking about the CF code changing then things you need to watch out for:

If you did not use cfqueryparams then you should change to those as part of this process.  If you did then there will be little changes needed to escape your input variables in your queries.

If  you used Access specific functions like iif() then you will need to convert those to DB2 functions.


If you are looking for how to convert the database itself then see the above comments.
0
 

Author Comment

by:mdbbound
ID: 12138059
Thanks for all the helpful comments.  I'll split the points
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question