Parsing Insert scripts into COlumns and values

Ragunathan Gunasekaran · Wed Mar 31, 2010 3:25 am

Hi ,

I have a very wierd requirement of using datastage to parse the MYSQL Insert Scripts into Column value pairs and then write it to an oracle database.

The requirement comes in the backdrop of business saying that the MYSQL database is a third patry database and they can provide only the scripts of transactions on a day to day basis.They are not likely in a position to give the database for connectivity to datastage.

Can someone give me a clue of how to convert the insert scripts into column value pairs in datastage...

ArndW · Post by **ArndW** » Wed Mar 31, 2010 3:31 am

Could you post a representative portion of one of the scripts?

Ragunathan Gunasekaran · Wed Mar 31, 2010 4:32 am

Hi ,
I am yet to get that script but i believe the script would be like..

insert into Table(Col1,Col2....) Values (Val1,Val2....)

I am trying to visualise the logic for just insert statements but what i was told is that the third party data provider would be giving me a complete MySql transaction dump for a day. This means it may also have Delete and upate statements in it ...

Any clues for translating insert statements as of now ..?

ETLJOB · Post by **ETLJOB** » Wed Mar 31, 2010 4:51 am

I would have a stored procedure in oracle to execute these statements and would be invoking it using a stored procedure stage in Datastage. SQL Statements can be passed in a single field to SP stage.

ArndW · Post by **ArndW** » Wed Mar 31, 2010 5:08 am

If the insert statements are indeed standard SQL then the approach might be to take the string, replace table/instance/column names as required, then pass that string to your Oracle stage and execute it directly. If it isn't standard SQL then it is not difficult to parse the string in a transform stage; but you would need to provide a specific example in order to get a specific answer.

chulett · Post by **chulett** » Wed Mar 31, 2010 7:41 am

Before we go too far afield, could you clarify exactly what it is you need to do with these "insert scripts"? You need to break them down and store them as scripts in Oracle or somehow turn them into Oracle DML and execute them?

Best to show us not only what your input would look like but also exactly what the output from that input should be. An example, in other words, so we ensure we're solving the right problem.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 31, 2010 3:52 pm

ETLJOB wrote:I would have a stored procedure in oracle to execute these statements and would be invoking it using a stored procedure stage in Datastage. SQL Statements can be passed in a single field to SP stage.

An Oracle stored procedure to execute statements intended to be used against MySQL? A novel approach indeed! Can you please elaborate?

kduke · Post by **kduke** » Thu Apr 01, 2010 9:20 am

I have done this in BASIC before. My suggestion is create a list of keywords. Throw these words away when found. I would then look for comment lines (begin with --) and comment pairs from /* to */ and throw those away. At this point I would change end of lines into a space. Now process one word at a time. You then need to look for your insert word in the statement up to (. I would lowercase all words to compare to keywords. I would keep original case except to do these comparisons. Your column names are between the (). Next word should be values with another (). The exception is when this is a select. You need to throw out exceptions like when the next word should be "values" and is not. Same for (). Change your code to deal with these exceptions. Should get you close. You could probably land your results in a flat file with column names as the first row.

By the way I have never done exactly this. I have always done this to extract the metadata like table and column names.