Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Best approach for DML generation

 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good day,

Appreciated your sharing on the best way or plugin for DML/DDL generation,

Scenario:
if table1 have 2 records, then i'll need to generate 2 DML statement for insertion, so that i can pass over to another DB to run the query for insertion.

thanks
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database platform and programming languages are you using, and what do you want to plug your "plugin" into?

How are you planning to move the data between databases?

  • If you're on a Java stack, you might look at an ORM like Hibernate which will hide the SQL behind an object-oriented interface.
  • Or you can just use JDBC if you simply want to fire SQL strings at your database.
  • Or your databases might allow you to connect directly from one DB to the other and do everything in SQL, which is usually the most efficient way of doing things.

  • Maybe if you explain more about what you are trying to do, people might have a better idea how to advise you.
     
    Martin Vajsar
    Sheriff
    Posts: 3752
    62
    Chrome Netbeans IDE Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    There are tools that are able to generate the INSERT statements to populate an empty table with the contents of the selected table (assuming their structure is compatible), eg. Sql Developer (which I'm using myself), but any decent tool should be able to do it. The relevant commands is usually grouped with other EXPORT commands, which does make some sense, but it isn't the first place one might try to look for it.
     
    Nakataa Kokuyo
    Ranch Hand
    Posts: 189
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Martin and Chris for reply, Let me elaborate more on what i plan to do.

    first of all, I'm using java and oracle DB, what i really need is to build a component to allow user click on the export function from the client account via web UI.

    When export function triggered, the component will dive into db to seek for all records that related to that account from all specific tables,

    With this, component will start convert those records by generate sql, eventually the sql will probably hold in json or xml format(not sure what is the best approach here too), and send over via post method to another environment so that receiver side will continue the process of insertion.

    my question:
    1. Is there any plugin/tool/API can help me to generate sql?
    2. What are the best way to hold to generated SQL? json or xml? we need to worry on security concern and validation from receiver end like having .xsd to validate the receiving data format.
    3. What are the best approach to handle such scenario?

    Thanks for enlightenment !
     
    Martin Vajsar
    Sheriff
    Posts: 3752
    62
    Chrome Netbeans IDE Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Converting the data to SQL scripts to ship them to another database - all under the user's control - looks pretty dangerous to me. How are you going to verify that the SQL script hasn't been tampered with? Because if you don't verify, what you get would practically be SQL injection on steroids.

    I personally woulds try to devise some other format to transport the data. Serializing the Java representation of the data into XML is what would I think of first.
     
    Nakataa Kokuyo
    Ranch Hand
    Posts: 189
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Martin, what if we using json here? do you see the advantage of lightweight from json over xml?

    between, is there any API can help me to convert data into sql ?
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic