aspose file tools*
The moose likes JDBC and the fly likes Backup from Db on pressing a button Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Backup from Db on pressing a button" Watch "Backup from Db on pressing a button" New topic
Author

Backup from Db on pressing a button

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I have a requirement where if the user presses a button, backup (with data) from oracle (for a particular user) is taken out and saved as .sql
Is it possible?


kunal
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61421
    
  67

Yes.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks Bear Bibeault. It has been long time since i saw your reply. How can i do it? A simple way i can guess is to write the java code for copying(to a different location) the backup file (which is somewhere in oracle dir). But i doubt that it will work. What Do you suggest?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Lots of things can be done. However your question is quite vague and it is not easy to give the right answers.

The right question is: what do you need the .sql files for?

If you're serious about using them as a backup, then no - .sql file is never a backup. Oracle backups are managed using a tool called rman and it is not very easy, it is actually far beyond a scope of a post on any internet forum. There is plenty of Oracle documentation for that, actually.

Do you need to move data and table definitions between computers? Then you should definitely look at expdp and impdp - they are far faster and more reliable than SQL scripts.

SQL files can be easily created using SQL Developer. This allows you to include table creation scripts.

And lastly, if you really need to create them using your own Java program, it's all up to you. You obviously need to know the name of the table and names and types of its columns. You can either get this information dynamically using JDBC methods for accessing the database metadata, or you might already know the structure of the table by other means. If you need to include table creation scripts, you can use DBMS_METADATA package.

You'll then write individual INSERT statements into the file, using proper format to encode values (you need to properly escape string data, use to_date function for DATE values, and so on).

(In any case, you may have troubles storing BLOBs, CLOBs or LONGs into SQL files.)
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks Martin for your reply.

Here is the reason for backups

Actually, I have created a school management application, where if student is promoted (at the end of session), all his old payment details should be deleted (as new data will be saved)after taking out backup. So, why not taking out the backup all info of the user.

Here is the details . I am using status column. When student is admitted, status shows "Admitted", and when he leaves the school, i am changing his status as "Deleted", so in case of promotion, why not set the status as "Promoted".
But, suppose a student is promoted in 2012, all his old records (2011-2012) is set as promoted, his new payment details appears with status as "Admitted". SO far, its good. But, if he gets promotion in 2012 (2012-2013), again his old records will have status as "Promoted". SO, how will i identify whether the records belongs to 2011-2012 session or 2012-2013 session?? I can't afford to change the db structure now, as the project is almost completed. What i thought was to take out the backup, and in pdf format too and then delete the data.

Other solution(i guess a better one) which comes to my mind is to create a table dynamically before the promotion code runs. This table will contain the old payment records. (I will have to create the table and its columns via java code) Will it work??
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Just on a very general level: the records should have a date (perhaps just a year) associated with them, so that the database can hold data for many years simultaneously. If the amount of data ever becomes a problem (not very probable, as storage capacity generally grows so fast), Oracle contains mechanisms for archiving old data.

It looks like you've omitted the date column from your DB model, which is a serious flaw. The "backup" solution you're proposing would lead to a "backup" per user and year. Though it might appear to solve your immediate problems, did you think of who and how would manage these "backups"? What if a student disputes a payment several years after it was made? Who will comb through thousands of PDF files to get all the necessary details? And so on.

Creating a table dynamically is slightly better, since the table at least remains in the database, but after several years this will be a mess too. Generally, it is possible to do, though it might complicate or compromise the security of the application. And again, someone will have to manage them somehow. And all this just because of the missing DB column.

You really should rectify your DB model. I'm not sure just adding the date column will solve all your problems, though it seems to me it would solve the problem you've described. This is what you ought to do. Hacking up a workaround can help temporarily, but will hurt you in the long run. You should at least plan how to fix this in the future, if nothing else, and design the workaround to make the future fix easier.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks martin fro your reply. My DB has date column. To be more specific date of payment column. But that doesn't solves the problem. Anyways, since the delivery date is near, I will go for dynamically creating table. When the school session ends (march 2013), i will redesign the db.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Kunal Lakhani wrote:Thanks martin fro your reply. My DB has date column. To be more specific date of payment column. But that doesn't solves the problem....

You may need two date columns then: date of payment and the school year being paid for.

Or not. Of course, I don't know what you do need. Just on the most general level: you need your database to be able to store historical as well as current data.
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 8008
    
  22

Kunal Lakhani wrote:I can't afford to change the db structure now, as the project is almost completed...

Oh dear. Where have I heard that one before? Oh yeah, I remember - a lot of failed projects.

There's a lovely old maxim from the 70's that's worth remembering: There's somehow always time and money enough to do it WRONG.

If your database structure is wrong, it needs to be changed; and your project ISN'T "complete" until it is.

What you can do is any of the following:
  • 1. Bite the bullet and do the database change NOW, before you write another line of code.
  • 2. Design the process that needs the change now (including the database change(s) as a pre-requisite), and shelve it until you have the time to do it properly.
  • 3. Implement a BETA version of the process with the existing db, and schedule both the database upgrade and a proper implementation of the process for some future date. But whatever you do, don't take that ".beta" off the project, and don't regard it as complete, until it IS done.

  • Personally, I like option 2; and if this is for school and I was your prof, I'd dock very few points if you could show me the design documentation and a decent implementation strategy.

    If this is for work, you may be forced by office politics to go with either (1) or (3) - you seem to have chosen the latter - but be warned that it's usually the most expensive and least reliable option.

    Winston


    Isn't it funny how there's always time and money enough to do it WRONG?
    Articles by Winston can be found here
    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    Thanks Martin and Winston Gutkowski for your reply.
    Winston Gutkowski, i will implement your suggestions. But,now it can be done only after the school session ends, i.e march 2013.
    I thing which i have realized is that, i am very weak in db designing & i am looking forward to improve my skills on it before the next project starts.
    So, Ranchers, please give your valuable suggestions to improve my skills. Also, do recommend any books/sites, as i need it (Its little difficult for me as i am a commerce graduate)
    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    I may get 10-15 days extension, for dispatching the project. So, i may opt for re-designing the whole db. So, i need some help. First of all, how to improve my skills in db designing. Give your suggestions. (keeping in mind, that, i am a very bad db designer)
    Winston Gutkowski
    Bartender

    Joined: Mar 17, 2011
    Posts: 8008
        
      22

    Kunal Lakhani wrote:Give your suggestions. (keeping in mind, that, i am a very bad db designer)

    Then I'd suggest finding someone who isn't.

    Alternatively, get a good book on the subject. Unfortunately, I started modelling more than 25 years ago, so my suggestions are likely to be way out of date (or possibly even out of print). Mind you, the basics haven't changed much since then, so anything by Ted Codd, Chris Date or Ray Boyce, who are the granddaddies of RDB theory, is probably worth reading - although two of them are now dead.

    The main things to understand are the rules of normal form, and for what you want, I doubt if you need much more than 3rd normal form (3NF) and possibly only even 2NF.

    Winston
    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    Thanks for your reply Winston Gutkowski
    Wendy Gibbons
    Bartender

    Joined: Oct 21, 2008
    Posts: 1107

    In my experience every quick dodgy fix spawns 2 more problems that then also require a quick dodgy fix each.

    You see how it gets very expensive very quickly.
    Paul Clapham
    Bartender

    Joined: Oct 14, 2005
    Posts: 18656
        
        8

    Another way of putting that: There is always plenty of time/money to do it wrong.
    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    I have heard a number of times "expensive". Expensive as per ?? Money? or memory?
    Wendy Gibbons
    Bartender

    Joined: Oct 21, 2008
    Posts: 1107

    Using the old adage time is money, it may take less time today, but when you add up all the time spent on the other fixes.

    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    Thanks for your reply Wendy Gibbons, Paul Clapham
    Wendy Gibbons
    Bartender

    Joined: Oct 21, 2008
    Posts: 1107

    Kunal Lakhani wrote:I have heard a number of times "expensive". Expensive as per ?? Money? or memory?


    I don't think i have thought about memory usage for a dozen years, you can always get more memory.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Backup from Db on pressing a button