Forum Stats

  • 3,827,386 Users
  • 2,260,768 Discussions
  • 7,897,223 Comments

Discussions

JDBC PreparedStatement gives null after executing a valid SELECT statement

bdzevel
bdzevel Member Posts: 45
edited Jan 7, 2013 2:36PM in Java Programming
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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
Tagged:

Best Answer

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    Answer ✓
    asci wrote:
    I'm glad this didn't turn out to be a stupid question. At least I feel a little vindicated due to that fact.
    There are no stupid questions... ;o)

    So what's left is: your select is to early. Can you attach an after insert, update each row trigger to that table that loggs the insert /update timestamp and the content of the LOGFIILE_NAME field?

    bye
    TPD
«13

Answers

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    Are you sure the Id you're looking for is present in your database? How do you put it there?

    bye
    TPD
  • EJP
    EJP Member Posts: 32,920 Gold Crown
    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.
  • 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
     .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • Kayaman
    Kayaman Member Posts: 3,844 Silver Trophy
    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.
  • ok, I understood
  • gimbal2
    gimbal2 Member Posts: 11,949 Gold Trophy
    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.
  • Kayaman
    Kayaman Member Posts: 3,844 Silver Trophy
  • asci
    asci Member Posts: 74
    edited Jan 7, 2013 10:35AM
    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
  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    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
  • asci
    asci Member Posts: 74
    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.
This discussion has been closed.