Solved

Coldfusion previously with Access but now will be in DB2

Posted on 2004-09-22
6
158 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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
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…
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…
The purpose of this video is to demonstrate how to Import and export files in 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 : Click on Too…

910 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

22 Experts available now in Live!

Get 1:1 Help Now