[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I populate a DB2 database using ColdFusion.

Posted on 2004-11-08
12
Medium Priority
?
200 Views
Last Modified: 2013-12-24
Hello,

I developed a CF application using MS Access as backend database.  Now, our DBAs build the database in DB2 so we could migrate from Access to DB2.  The DB2 database for now, only contains the tables.  The DBA told me that I am supposed to be the one to populate the tables by using coldfusion.

Let say I have 3 tables in  MS Access that currently have records of 60, 300, 600.  I want to move all of these records at the same time to the tables in DB2, Should I do a select first and then do an insert?  Please guide me.   I already have the datasource in DB2.

Thanks
0
Comment
Question by:mdbbound
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:kkhipple
ID: 12525882
1.  create another source in MS Access
2.  Create the necassary tables in MS Access (use the CREATE TABLE syntax)
3.  Like you said, use select statement to query the db2 table and put that into the MS Access
4.  Then using that data, do an insert into the MS Access

that is basically what you need to do and your right, you do have the idea.
0
 

Author Comment

by:mdbbound
ID: 12526231
no wait, the data is coming from MS Access and going to IBM/DB2.  The tables in IBM/DB2 are already done but has no data.  Now my job, is to move the records from Access tables to the tables in IBM/DB2.

How do i do that using coldfusion. I mean do I have to do a SELECT then INSERT.  Please, if there is anyone out there, I need help.
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 1500 total points
ID: 12526255
1st ensure that no one else is using the DB2 system

2nd set up a datasource in CF admin to the DB2 system

3) set up tables in DB2 that mirror the tables in access

4) Determine any relationships between tables in acces  (such as if one is orders and another order details)

5) Move over any tables that are not related to the others using a simple SELECT and INSERT process

6) Select each record that does have multiple relations in other tables and write an insert to create those as new records in DB2.
This needs to be done with a loop over one record at a time to ensure that keys match back up since they may end up different in your DB2 if they were autonumber columns...
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mdbbound
ID: 12540695
Thanks Mrichmon.  I'm still working on this
0
 

Author Comment

by:mdbbound
ID: 12570320
Hi Mrichmon,

I don't know what to do, is it possible to show me an example of the solution please.

The tables/structures are already setup in DB2.  I just have to put the data that is still sitting in MSAccess.

For Example: the Appt table has
AppointID(autono)
CoID(FK)
CrID(FK)
AppointDate
TermEnd

<cfquery name="fromMSData" datasource="MSdatasource">
SELECT * from Appt
ORDER BY ApptID
</cfquery>

<cfquery datasource="ComStat">
      INSERT INTO tAppointment(
            ApptID,
            CoID,
            CrID
            AppointDate,
            TermEnd)
      VALUES #Trim(ApptID)#,
         #Trim(CoID)#,
         '#Trim(CrID)#',
         #CreateODBCDate(AppointDate)#,
         #CreateODBCDate(TermEnd)#)
      </cfquery>

Thanks
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12571153
Basically you have it.  Just wrap the query inside a cfloop to go through all of the rows in the query pulled from access.


THe other thing as I mentioned is that if you have foreign keys or other relationships - make sure you join those tables, pull the complete records , and reinsert since the UID fields may change if they are autonumbers in access...
0
 

Author Comment

by:mdbbound
ID: 12596042
Hi Mrichmon,

What does it mean when they say "set up a native datasource"?  Would you please give an example?

Thanks
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12596681
When who says that?  Who is the "they" and what database are we talking about the access one or the db2 one?
0
 

Author Comment

by:mdbbound
ID: 12598107
Hello Mrichmon,

My IT Dept requires me to use a native datasource, well i have been reading i think they mean to put it in the Application.cfm.  But how many datasources can you include in the Application.cfm?

Thanks.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12598399
I am not sure what they mean by that....
0
 

Author Comment

by:mdbbound
ID: 12631793
Hello Mrichmon,

I got it!  I created a native DB2 data source (in CFAdmin).  I do have a problem connecting though and sadly I can't find a solution.  Sometimes it connects but 80% it is not connected and it is very hard to test my queries.  The database is in linux.  Based on reading other problems posted over the Internet, it is a CFMX issue and there is no solution from macromedia.  

If you know anything, please help me.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12645693
Sorry I don't.....
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
Suggested Courses

872 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