run a sql query through JDBC .....

[dmjpro]

if i run a query like (select sysdate from dual) then it runs easily on oracle client .. like SQL plus or Navigator ......

but if i run this through JDBC then it falshes an error .... missing mandatory paramter

plz help me .... thanxxxx

[sicarie]

if i run a query like (select sysdate from dual) then it runs easily on oracle client .. like SQL plus or Navigator ......

but if i run this through JDBC then it falshes an error .... missing mandatory paramter

plz help me .... thanxxxx

Can you post the line that has your query in it, and copy and paste the exact error message?

[dmjpro]

ok kkkk ... here is my code

import java.sql.*;
 
class QueryTest
{
  public static void main(String args[]) throws Exception
  {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection l_con = DriverManager.getConnection("jdbc:oracle:thin:@172.20.0.207:1522:BCCLPROD","coalnetapps","coalnetapps");
      Statement l_stmt = l_con.createStatement();
      ResultSet l_rs = l_stmt.executeQuery("(select sysdate from dual)");
      while(l_rs.next()) System.out.println("Debasis: " + l_rs.getString(1));
      System.out.println("Debasis Yahoooooo .....");
  }
}

and the error message is ....

java.sql.SQLException: ORA-01009: missing mandatory parameter

now explain plz .......
thanxxxx

[sicarie]

ok kkkk ... here is my code

import java.sql.*;
 
class QueryTest
{
  public static void main(String args[]) throws Exception
  {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection l_con = DriverManager.getConnection("jdbc:oracle:thin:@172.20.0.207:1522:BCCLPROD","coalnetapps","coalnetapps");
      Statement l_stmt = l_con.createStatement();
      ResultSet l_rs = l_stmt.executeQuery("(select sysdate from dual)");
      while(l_rs.next()) System.out.println("Debasis: " + l_rs.getString(1));
      System.out.println("Debasis Yahoooooo .....");
  }
}

and the error message is ....

java.sql.SQLException: ORA-01009: missing mandatory parameter

now explain plz .......
thanxxxx

You're missing a try-catch, i know a lot of compilers will complain about that...

[nathj]

Ok, I'm pretty new to this whole thing but I tend to use a class specifically for database connections in conjunction with a simple properties file. Both are shown below:

 
public class DBConnector 
{
 
    private String className;
    private Properties jdbcConnectProps = new Properties();
    private String connectionString;
 
    public Connection getJDBCConnection(String dbUserId, String dbPassword)
    {
        Connection conn = null;
        try
        {
            Class.forName(this.className);
            conn = DriverManager.getConnection(this.connectionString, dbUserId, dbPassword);
        }
        catch(Exception e)
        {
            // TODO: ND - use of ESF Exceptions relating to databases - may need to generate a new Exception class
        }
 
        return conn;
    }
 
    public void loadProperties(String propsLocation)
    {
        try
        {
            this.jdbcConnectProps.load(new FileInputStream(propsLocation));
        }
        catch(Exception e)
        {
            // TODO: ND - use ESFIOException as this means no props file found
        }
    }
 
    public void setClassName(String key)
    {
        try
        {
            this.className = jdbcConnectProps.getProperty(key);
        }
        catch(Exception e)
        {
            // TODO: ND - use ESFRetrievalException as this means we could not get the properties
        }
 
    }
 
    public void setConnectionString(String connectString)
    {
        this.connectionString = connectString;
    }
 
    public String getConnectionString()
    {
        return this.connectionString;
    }
 
    public String getClassName()
    {
        return className;
    }
 
    // @Override
    // public Connection getBlackboardJDBCConnection() {
    // // TODO Auto-generated method stub
    // return null;
    // }
    //
    //
    // @Override
    // public Connection getCMISJDBCConnection() {
    // // TODO Auto-generated method stub
    // return null;
    // }
}
 

The properties file is as follows:

#Class names for different databases
jdbc.sqlserver.classname=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jbdc.oracle.classname=
#jdbc.mysql.classname=
 

What this enables me to do is the following:

 
package org.esf.test;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import org.esf.utils.DBConnector;
 
public class GetUserQueries
{
    private final DBConnector dbc = new DBConnector();
    private ResultSet usersByDSK = null;
 
 
    public static void main(String[] args)
    {
        GetUserQueries guq = new GetUserQueries();
        guq.run();
    }
 
    public GetUserQueries()
    {
        dbc.loadProperties("E:/full/path/to/properties/jdbcConnection.properties");
        dbc.setClassName("jdbc.sqlserver.classname");
        dbc.setConnectionString("jdbc:sqlserver://machine:port;databaseName=mydatabase");
    }
 
    public void run()
    {
        // get a rs
        usersByDSK = getUserNamesByDSK(1);
        // print rs
        System.out.println(toString());
    }
 
    public ResultSet getUserNamesByDSK(int dsk)
    {
        Statement queryStmt;
        ResultSet queryResult = null;
        try
        {
            queryStmt = dbc.getJDBCConnection("username", "password").createStatement();
            queryResult = queryStmt.executeQuery("Select user_id from users where data_src_pk1 = " + dsk);
        }
        catch(SQLException sqlEx)
        {
            System.out.println("Oh Poo - can't query");
        }
        return queryResult;
    }
 
    public String toString()
    {
        StringBuilder sb = new StringBuilder();
 
        try
        {
            while(usersByDSK.next())
            {
                // retrieve and print the values for the current row
 
                sb.append("User : " + usersByDSK.getString("user_id") + "\n");
            }
        }
        catch(SQLException e)
        {
            System.out.println("Oh Poo - results issues");
            //TODO: ND Handle this exception properly
        }
 
        return sb.toString();
    }
}
 

This is a very specific checking system to ensure I got the results I expected.

Hopefully that's clear enough. I know this is for SQL Server but the same principles hold true for Oracle - I believe.

Anyway, hope that helps and if not ask again and I'll try to help some more.

nathj