Solved

Coldfusion previously with Access but now will be in DB2

Posted on 2004-09-22
6
155 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
6 Comments
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 160 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 160 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 180 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now