Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

author
message
[Level:3]castle79

Post subject: Tutorial How to Use Java Stored Procedures In .NET (code C#)

registered: 05/09/2012

IP: *.88.171.235

views: 5

ADO.NET doesn't provide the same data types as JDBC particularly ResultSet.

Well, my english is not good, so I need much time to write some words. Therefore, I will write all the code and exact steps, so you only need to copy and paste.

Requirements

  • C# compiler, editor, Visual studio any version, express, etc.
  • Eclipse or any java compiler.
  • CUBRID 8.4.1
  • Understanding of C# development.

Little explanation

When you run the Java stored procedure in CUBRID Manager, you can use SELECT or CALL methods. For example, if you need to call rset() stored procedure which returns STRING, then you can use CALL rset() or SELECT rset().

If you read this manual, you can find examples of Java stored procedures where you can see one with ResultSet return value. Here is the problem: ADO.NET provider cannot handle it because the ResultSet is a Java type.

But if the Stored procedure returns STRING or other native type like INTEGER you can use SELECT rset() to get the value.

Solution

My solution is get a ResultSet value by converting it to an XML file.

Example in Java:

import java.sql.*;

import cubrid.jdbc.driver.*;

import java.sql.Connection;

import java.sql.Statement;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;


import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.transform.*;

import javax.xml.transform.stream.*;

import javax.xml.transform.dom.*;

import org.w3c.dom.Document;

import org.w3c.dom.Element;


public class JavaSP3 {

   public static String TResultSet(){

       ResultSet rs = null;

       Statement stmt = null;

       String sql;


       try {

           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

           Connection con = DriverManager.getConnection("jdbc:default:connection:");

           ((CUBRIDConnection)con).setCharset("euc_kr");


           DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

           DocumentBuilder builder =factory.newDocumentBuilder();

           Document doc = builder.newDocument();

           Element results = doc.createElement("Results");

           doc.appendChild(results);

                   

           sql = "select * from athlete";

           stmt=con.createStatement();

           rs = stmt.executeQuery(sql);

           ResultSetMetaData rsmd = rs.getMetaData();

           int colCount = rsmd.getColumnCount();

                     

           while (rs.next()) {

               Element row = doc.createElement("Row");

               results.appendChild(row);

               for (int ii = 1; ii <= colCount; ii++) {

                   String columnName = rsmd.getColumnName(ii);

                   Object value = rs.getObject(ii);

                   Element node = doc.createElement(columnName);

                   node.appendChild(doc.createTextNode(value.toString()));

                   row.appendChild(node);

               }

           }


                     

           String valor = getDocumentAsXml(doc);

           return valor;

                    

       }

       catch (Exception e)

       {

           e.printStackTrace();

       }

       

       return null;


   }


   public static String getDocumentAsXml(Document doc)

         throws TransformerConfigurationException, TransformerException {

       DOMSource domSource = new DOMSource(doc);

       TransformerFactory tf = TransformerFactory.newInstance();

       Transformer transformer = tf.newTransformer();

       //transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION,"yes");

       transformer.setOutputProperty(OutputKeys.METHOD, "xml");

       transformer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");

       transformer.setOutputProperty

          ("{http://xml.apache.org/xslt}indent-amount", "4");

       transformer.setOutputProperty(OutputKeys.INDENT, "yes");

    

       java.io.StringWriter sw = new java.io.StringWriter();

       StreamResult sr = new StreamResult(sw);

       transformer.transform(domSource, sr);

       return sw.toString();

    }


}
C# code:
using System.Data;

using CUBRID.Data.CUBRIDClient;

using System.Data.Common;

using System;


namespace ConsoleApplication1

{

   class Program

   {

       static void Main(string[] args)

       {


           string ConnectionString = "server=localhost;database=demodb;port=30000;user=dba;password=123456";

           DataTable dt = new DataTable();

           DataSet ds = new DataSet();

           CUBRIDConnection con = new CUBRIDConnection(ConnectionString);


           CUBRIDCommand com = new CUBRIDCommand();

           com.CommandType = CommandType.Text; //Important ADO.NET driver crash using call convention

           com.Connection = con;

           com.CommandText = "select rset();";

           CUBRIDParameter pan = new CUBRIDParameter();

           con.Open();

           DbDataReader reader = com.ExecuteReader();

           CustomAdapter da = new CustomAdapter();

           da.FillFromReader(dt, reader);

           con.Close();


           DataRow fila = dt.Rows[0];

           Console.WriteLine(fila[0].ToString());

           Console.ReadKey();           


       }

   }


  

   public class CustomAdapter : System.Data.Common.DbDataAdapter

   {

       public int FillFromReader(DataTable dataTable, IDataReader dataReader)

       {

           return this.Fill(dataTable, dataReader);

       }

       protected override System.Data.Common.RowUpdatedEventArgs CreateRowUpdatedEvent(DataRow a, IDbCommand b, StatementType c, System.Data.Common.DataTableMapping d)

       {

           return (System.Data.Common.RowUpdatedEventArgs)new EventArgs();

       }


       protected override System.Data.Common.RowUpdatingEventArgs CreateRowUpdatingEvent(DataRow a, IDbCommand b, StatementType c, System.Data.Common.DataTableMapping d)

       {

           return (System.Data.Common.RowUpdatingEventArgs)new EventArgs();

       }


       protected override void OnRowUpdated(System.Data.Common.RowUpdatedEventArgs value)

       {


 

       }

       protected override void OnRowUpdating(System.Data.Common.RowUpdatingEventArgs value)

       {


 

       }

   }


}

See these lines:

CUBRIDCommand
com = new CUBRIDCommand();
com.CommandType = CommandType.Text; //Important ADO.NET driver crash using call convention
com.Connection = con;
com.CommandText = "select rset();";

CommandType need be Text, the CUBRID engine will return you the result as STRING.

This will get you aN XML document with all the resultSet value. Of course the XML file will be very big, so you will need some compression.

You should also create this function in CUBRID:

CREATE FUNCTION "rset"() RETURN STRING

AS LANGUAGE JAVA

NAME 'JavaSP3.TResultSet() return java.lang.String'

And load the .class file generated in Eclipse using:

C:\CUBRID\bin\loadjava -y demodb JavaSP3.class

JavaSP3.class is the class generated for Eclipse, you can find it the bin directory after you compile the .java file.

Quote
[Level:8]CUBRID

# Post subject:Re: Tutorial How to Use Java Stored Procedures In .NET (code C#)

profile

registered: 03/28/2010

IP: *.91.139.66

Hello castle79!

Thank you for such a complete tutorial how to use Java stored procedure in C#. I have edited your tutorial to make it more clear for the users and added it to other CUBRID ADO.NET tutorials.

If you have other great tutorial, please share. We will be glad to add them to CUBRID tutorials. 

Quote
[Level:3]castle79

# Post subject:Re: Tutorial How to Use Java Stored Procedures In .NET (code C#)

profile

registered: 05/09/2012

IP: *.88.171.235

Many thanks! my english need some refresh.. hehe.

I am working hard using your database, since last week I am doing some test with it. my disadvantage is I never coding with java and have very troubles with the class libraries names, conventions etc.

I like CUBRID, I hope can extend more functions, usage and more...

Quote




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: