This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jan 7, 2013 11:36 AM by asci RSS

JDBC PreparedStatement gives null after executing a valid SELECT statement

bdzevel Newbie
Currently Being Moderated
Hello,

I have the following code:
    public static String GetJobLogLocation(Connection connection, int jobId) throws SQLException
    {
        String sqlstr = "SELECT LOGFILE_NAME FROM apps.fnd_concurrent_requests WHERE REQUEST_ID = ?";
        PreparedStatement prest = connection.prepareStatement(sqlstr);
        prest.setInt(1, jobId);
        ResultSet rs1 = prest.executeQuery();
        String rv = null;
        if (rs1.next())
        {
            rv = rs1.getString(1);
            System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + rv);
        }
        rs1.close();
        prest.close();
        return rv;
    }
As you can see, this function executes a select statement. Before I continue, let me first say that executing the exact same statement in PL/SQL Developer (external program) yields correct/expected results. Also, running this on my Windows environment yields correct/expected results. I seem to be having this problem only on my Unix environment (SunOS).

Anyways, I get output as follows from this function (running against my Unix environment, of course):

+>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>null+

The thing is, it doesn't error out, which means that the ResultSet contains a String, but the string is returned to me as null, even though there is a real, valid result (which, in this case, is a path).

Does anyone have any idea what's going on? I've tried using ojdbc6.jar AND ojdbc14.jar, with the same results. I just don't get it...

Thanks.

Edited by: 964530 on Jan 4, 2013 2:31 PM

Edited by: EJP on 7/01/2013 17:48: added {noformat}
{noformat} tags and removed your bizarre bold formatting. Please do this properly in future.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 1. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    Are you sure the Id you're looking for is present in your database? How do you put it there?

    bye
    TPD
  • 2. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    EJP Guru
    Currently Being Moderated
    If it wasn't there, rs.next() would have returned false and nothing would have been printed. The evidence is rather that the row retrieved has a null column.
  • 3. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    MrBabakishiyev Newbie
    Currently Being Moderated
    Hello.
    Are you sure 1st column datatype is varchar (in java String) or like something?
    You used :
       rv = rs1.getString(1);
    String getString(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language

    http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html

    P.S when write a piece of code between
     .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 4. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    Kayaman Guru
    Currently Being Moderated
    Mr Babakishiyev wrote:
    Hello.
    Are you sure 1st column datatype is varchar (in java String) or like something?
    An error would occur if the column type was something else (such as an integer).

    You shouldn't be answering questions at all. Most if not all of your answers have been useless at best, and misleading at worst.
  • 6. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    gimbal2 Guru
    Currently Being Moderated
    Kayaman wrote:
    You shouldn't be answering questions at all. Most if not all of your answers have been useless at best, and misleading at worst.
    A bit too harsh. Lets be fair - we all start out as greenhorns and start answering questions with that "lets discuss and all learn!" mentality. You begin by giving many (partially) invalid answers, over time the regulars hammer that into a deep understanding through countless corrections.

    So I wouldn't say "don't answer questions", I'd say "answer with more care". If you really -know- the answer feel free to give it, if you're only guessing then perhaps simply wait for someone else to answer and then compare notes to perhaps learn from it.
  • 8. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    asci Newbie
    Currently Being Moderated
    1) Sorry I was unaware there were "code" tags, there aren't any helpers for them (like the bold, italics, and underline buttons that I see above)

    2) Yes, there -should- definitely be an entry in there. (Obviously?) I'm working with Oracle EBS in this case, so I run a concurrent request and I get back an ID with which I can reference this request at a later date (in order to, say, find the log location). After my code fails to grab the log location, I can run a SQL query on my own using the ID that I get back from my code. Running the same SQL query in PL/SQL Developer (external program to run SQL queries) results in a valid log/output file location. The field is NOT null OR empty, and both the column and the row SHOULD exist.

    2.5) My thinking was that this might be an issue with timing. Although I don't have this issue with my .NET implementation, which obviously uses a slightly different model for PL/SQL queries, maybe the Java implementation of queries is so much faster that i try to read the log location before it's written to the DB. However, if this were the case, it seems that rs1.next() would be false and that code would never be reached.

    3) I have literally dozens of these prepared statement executeQuery() calls and the only ones that seem to be a problem are the log file and output file locations. The code is nearly identical in all cases, differentiated almost exclusively (except for very few exceptions) by the SQL statement that's being run.

    P.S.> I don't know exactly what the data type of this field is but it IS some kind of String (it's a file path). I'd have to do some research to find out how to find the data type of a column in a table.


    EDIT: Just realized, I signed in with my company's 'generic' oracle forums accoun, but this is the same person as the OP. Sorry for the confusion.

    Edited by: asci on Jan 7, 2013 7:34 AM
  • 9. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    asci wrote:
    2) Running the same SQL query in PL/SQL Developer (external program to run SQL queries) results in a valid log/output file location. The field is NOT null OR empty, and both the column and the row SHOULD exist.
    Just for completeness:
    Are you sure to connect to the same database you verified the data on?
    bye
    TPD
  • 10. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    asci Newbie
    Currently Being Moderated
    Yes.

    It must be, as we only have one EBS system. Even if it were a different EBS DB, then the job ID wouldn't work to reference that data (Well, there's a CHANCE it could work, but it's improbable and it's nearly impossible for it to work consistently as I see it does).

    Regardless, as I set up all of the tnsnames.ora files (which set up what connection descriptors will connect to what DBs) on all of the systems in question, I am certain that I am referencing the same (correct) DB in each case.
  • 11. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    Just groping in the dark:
    Is there any entry in <tt>apps.fnd_concurrent_requests</tt> where <tt>LOGFILE_NAME</tt> is NULL?
    Can you proove that <tt>jobId</tt> holds the expected <tt>REQUEST_ID</tt>?

    bye
    TPD
  • 12. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    asci Newbie
    Currently Being Moderated
    Yes, there are several (15) entries in the table where LOGFILE_NAME is null. Running my code once again, I get back request ID 393666, but this req. ID is NOT in this list of rows with null LOGFILE_NAME:

    381361
    383173
    383174
    388190
    388212
    389557
    389558
    393586
    393587
    393588
    393602
    393621
    393664
    393665
    393667

    (I copied and pasted these from pl/sql developer)

    Also, yes, the variable must contain the correct REQUEST_ID. I get back a request ID from the Oracle EBS PL/SQL API (I'm not "looking" for the request ID or anything like that, it's given to me by oracle - hopefully this distinction makes sense).

    This isn't the EBS board but here's my PL/SQL that runs a request:
    DECLARE
      p_id INTEGER;
      u_id INTEGER;
      a_id INTEGER;
      r_id INTEGER;
      Not_Submitted EXCEPTION;
    BEGIN
      SELECT user_id INTO u_id FROM apps.fnd_user WHERE user_name = 'SYSADMIN';
      SELECT application_id INTO a_id FROM apps.fnd_application WHERE application_short_name = 'FND';
      SELECT responsibility_id INTO r_id FROM apps.fnd_application fa, apps.fnd_responsibility_tl fr WHERE fa.application_short_name = 'FND' AND fa.application_id = fr.application_id AND fr.responsibility_name = 'Application Developer';
      apps.fnd_global.apps_initialize(u_id, r_id, a_id);
      p_id := apps.fnd_request.submit_request('FND','FNDMDGEN',NULL,NULL,FALSE, argument2 => 'SQLGL', argument1 => 'US', argument3 => 'DB_TO_RUNTIME');
      IF p_id = 0 THEN
        RAISE Not_Submitted;
      END IF;
      ? := p_id;
      COMMIT;
    EXCEPTION
      WHEN Not_Submitted THEN
        ? := apps.fnd_message.get();
    END;
    This code is kinda hard to follow, but essentially p_id is the "job ID" or "request ID" (I guess "p_id" is supposed to stand for process ID). "? := p_id;" sets my output variable and allows me to get back the ID. The second output variable is an error message, in the case that p_id comes out to be "0" which means an error occurred. Obviously, what I do with this SQL block now that it's constructed is I feed it into a CallableStatement and register the output variables.

    Edited by: asci on Jan 7, 2013 8:48 AM

    EDIT: Again, running the SELECT LOGFILE_NAME... query in PL/SQL developer using the request ID that I get back from this CallableStatement produces a valid, non-null result. In fact, even running the same prepared statement from Java on a separate (Windows) machine results in the correct behavior. I see this weird "null" behavior when I run this code on a unix machine, but when I run it on my development platform (Windows 7), it runs fine.

    Edited by: asci on Jan 7, 2013 8:52 AM
  • 13. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    could you please add the parameter in the output string? Your requested ID is right in the middle of 2 "null"-Entries, just want bo be sure at this point...

    bye
    TPD
  • 14. Re: JDBC PreparedStatement gives null after executing a valid SELECT statement
    asci Newbie
    Currently Being Moderated
    what do you mean "add the parameter to the output string"
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points