I moved this thread out since the "Executing the SQL statements" thread was becoming too cluttered.
Here is a cut and paste from the original thread:
----8<----
I need one more favor. My xml will be in the following format.
<SqlCode>
<SQL_CODE>ADDCVD_LIST_001</SQL_CODE>
<SQL_STMT>SELECT nim_adcvd_mftr.case_ref_no ,
nim_adcvd_mftr.actual_case_no ,
nim_adcvd_mftr.case_no ,
nim_adcvd_mftr.shipper_id ,
nim_adcvd_mftr.manuf_name ,
nim_adcvd_mftr.mid ,
nim_adcvd_mftr.deposit_rate1 ,
nim_adcvd_mftr.deposit_rate2 ,
nim_adcvd_mftr.deposit_rate3 ,
nim_adcvd_mftr.eff_entry_date ,
nim_adcvd_mftr.expiry_date ,
nim_adcvd_mftr.eff_export_date
FROM nim_adcvd_mftr
WHERE NIM_ADCVD_MFTR.CASE_STATUS NOT IN ('R','E','T') </SQL_STMT>
<MAIN_TABLE>nim_adcvd_mftr</MAIN_TABLE>
<UPDATEABLE_COLUMNS/>
<KEY_COLUMNS>nim_adcvd_mftr.case_ref_no,nim_adcvd_mftr.actual_case_no,nim_adcvd_mftr.case_no</KEY_COLUMNS>
<TIMESTAMP_COLUMN/>
</SqlCode>
My main sql code is in between the <SQL_STMT> tags.
Is there any way to extract the sql and run using the SQL unit.
I am not sure, whether this is standard framework.
I will explain , what I am trying to achieve through the SQLUnit.
Our application framework uses a property file called sys_sql.xml, which contains all the base SQL's the application uses.
During the regression testing lots of issues were due to the mismatch between the sys_sql.xml and database.( we use an sql called alter.sql, which upgrades the databse to the current version).If any field is missing in one of the tables, mismatch occurs and leads to a crash.
So, my intention is to extract the sql from the sys_sql.xml and run fom the SQLunit.
----8<----
I was thinking about this some more, and I think apache digester would be a better tool to use to make this data driven, so even if its not a standard format, the transformer could be written in a general enough way so it can be used with a new rules file for other XML data formats.
It should be very simple, I will try to whip up something to do this in the next few days and update the thread.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am almost done with this, I finally decided to use XSLT for scripting the transformation, and used the org.jdom.transform.* classes in JDOM and javax.xml.transform.* classes in java 1.4 to build a custom transformer (all the custom stuff it does is set the DOCTYPE).
I havent checked it in yet, but the code is complete, need to complete the docs, I will check it in tonight. You should see it over the weekend or next week.
I also have a XSL file that converts your XML to SQLUnit XML, which I will also check in.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just checked in the oode for the transformation tool, it should be availble in the public cvs servers sometime tomorrow.
Here is the code that you need to get it to work.
Checking in etc/transform_test_input.xml;
/cvsroot/sqlunit/sqlunit/etc/transform_test_input.xml,v <-- transform_test_input.xml
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/etc/transform_test_transform.xsl,v
done
Checking in etc/transform_test_transform.xsl;
/cvsroot/sqlunit/sqlunit/etc/transform_test_transform.xsl,v <-- transform_test_transform.xsl
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/tools/TransformTool.java,v
done
Checking in src/net/sourceforge/sqlunit/tools/TransformTool.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/tools/TransformTool.java,v <-- TransformTool.java
initial revision: 1.1
done
Checking in build.xml;
/cvsroot/sqlunit/sqlunit/build.xml,v <-- build.xml
new revision: 1.104; previous revision: 1.103
done
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.93; previous revision: 1.92
done
There is a new ant task, so you can call this with the following parameters:
ant transform -Dtransform.input=etc/transform_test_input.xml -Dtransform.transform=etc/transform_test_transform.xsl -Dtransform.output=/tmp/sqlunitfile.xml
where the xml file in etc/ is the file you specified in this thread, the xsl file is the stylesheet that does the conversion and the tmp/ file is the sqlunit file that is generated. Feel free to tweak the xsl file to make any changes that you need.
I found this page useful to learn xslt (and yes, I started learning it as I was writing this code, in fact that was one of the drivers that made me decide that I would do this :-)). Not sure if you already know it, if not this may be useful. http://www.w3schools.com/xsl/
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Meghanath,
I moved this thread out since the "Executing the SQL statements" thread was becoming too cluttered.
Here is a cut and paste from the original thread:
----8<----
I need one more favor. My xml will be in the following format.
<SqlCode>
<SQL_CODE>ADDCVD_LIST_001</SQL_CODE>
<SQL_STMT>SELECT nim_adcvd_mftr.case_ref_no ,
nim_adcvd_mftr.actual_case_no ,
nim_adcvd_mftr.case_no ,
nim_adcvd_mftr.shipper_id ,
nim_adcvd_mftr.manuf_name ,
nim_adcvd_mftr.mid ,
nim_adcvd_mftr.deposit_rate1 ,
nim_adcvd_mftr.deposit_rate2 ,
nim_adcvd_mftr.deposit_rate3 ,
nim_adcvd_mftr.eff_entry_date ,
nim_adcvd_mftr.expiry_date ,
nim_adcvd_mftr.eff_export_date
FROM nim_adcvd_mftr
WHERE NIM_ADCVD_MFTR.CASE_STATUS NOT IN ('R','E','T') </SQL_STMT>
<MAIN_TABLE>nim_adcvd_mftr</MAIN_TABLE>
<UPDATEABLE_COLUMNS/>
<KEY_COLUMNS>nim_adcvd_mftr.case_ref_no,nim_adcvd_mftr.actual_case_no,nim_adcvd_mftr.case_no</KEY_COLUMNS>
<TIMESTAMP_COLUMN/>
</SqlCode>
My main sql code is in between the <SQL_STMT> tags.
Is there any way to extract the sql and run using the SQL unit.
I am not sure, whether this is standard framework.
I will explain , what I am trying to achieve through the SQLUnit.
Our application framework uses a property file called sys_sql.xml, which contains all the base SQL's the application uses.
During the regression testing lots of issues were due to the mismatch between the sys_sql.xml and database.( we use an sql called alter.sql, which upgrades the databse to the current version).If any field is missing in one of the tables, mismatch occurs and leads to a crash.
So, my intention is to extract the sql from the sys_sql.xml and run fom the SQLunit.
----8<----
I was thinking about this some more, and I think apache digester would be a better tool to use to make this data driven, so even if its not a standard format, the transformer could be written in a general enough way so it can be used with a new rules file for other XML data formats.
It should be very simple, I will try to whip up something to do this in the next few days and update the thread.
-sujit
Hi Meghanath,
I am almost done with this, I finally decided to use XSLT for scripting the transformation, and used the org.jdom.transform.* classes in JDOM and javax.xml.transform.* classes in java 1.4 to build a custom transformer (all the custom stuff it does is set the DOCTYPE).
I havent checked it in yet, but the code is complete, need to complete the docs, I will check it in tonight. You should see it over the weekend or next week.
I also have a XSL file that converts your XML to SQLUnit XML, which I will also check in.
-sujit
Hi Meghanath,
I just checked in the oode for the transformation tool, it should be availble in the public cvs servers sometime tomorrow.
Here is the code that you need to get it to work.
Checking in etc/transform_test_input.xml;
/cvsroot/sqlunit/sqlunit/etc/transform_test_input.xml,v <-- transform_test_input.xml
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/etc/transform_test_transform.xsl,v
done
Checking in etc/transform_test_transform.xsl;
/cvsroot/sqlunit/sqlunit/etc/transform_test_transform.xsl,v <-- transform_test_transform.xsl
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/tools/TransformTool.java,v
done
Checking in src/net/sourceforge/sqlunit/tools/TransformTool.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/tools/TransformTool.java,v <-- TransformTool.java
initial revision: 1.1
done
Checking in build.xml;
/cvsroot/sqlunit/sqlunit/build.xml,v <-- build.xml
new revision: 1.104; previous revision: 1.103
done
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.93; previous revision: 1.92
done
There is a new ant task, so you can call this with the following parameters:
ant transform -Dtransform.input=etc/transform_test_input.xml -Dtransform.transform=etc/transform_test_transform.xsl -Dtransform.output=/tmp/sqlunitfile.xml
where the xml file in etc/ is the file you specified in this thread, the xsl file is the stylesheet that does the conversion and the tmp/ file is the sqlunit file that is generated. Feel free to tweak the xsl file to make any changes that you need.
I found this page useful to learn xslt (and yes, I started learning it as I was writing this code, in fact that was one of the drivers that made me decide that I would do this :-)). Not sure if you already know it, if not this may be useful.
http://www.w3schools.com/xsl/
-sujit