pass null to int field of method

[lbrechler]

I have a method that stores several values in an Oracle database. The database will allow null values, but when I try to pass "null" in to the method instead of the "int" that it's expecting, I get the following error.

methodName(int, int, int) in pkg cannot be applied to (in, int, <null>)

Is there any way to get around this? I tried to pass a value to the method and then convert that to &quot;null&quot; before updating the db, but it doesn't like that either. That error is:

incompatible types
found : <null>
required: int

Any suggestions?

Thanks in advance,
~lbrechler

 

[flawless69]

Does this method that is tripping actually do the updating or does it just call another method that does the communication with the db? Because then you could pass in a value which is non-null such as a very low or high value and convert that to null in the db method. I guess my question is two-fold: first of all, are you using a pre-defined method and second is the method directly talking with the DB or is it simply an interface method? --Derek

&quot;Fear not the storm for this is where we grow strong.&quot;

 

[lbrechler]

The method actually talks directly to the database. I have actually tried passing a low number (-99) instead of the null value, and when I try to convert it to a null (&quot;if (myInt == -99) myInt = null;&quot it errors on &quot;incompatible types&quot; during compile.

Hope this makes sense...

~lbrechler

 

[flawless69]

There are two types of data in Java: primitive and reference. Null is a reference type and int is a primitive type. Null is actually the null pointer since objects are actually represented as pointers by Java. Thus you can't set an int variable to a null value. I'm not sure what your method is doing as far as the actual action of communicating with the DB but instead of assigning the null to a variable, maybe you should try just using a null value wherever you would update the DB. Like if there is SQL somewhere or something. This would enable you to continue using -99 as an indicator of null. --Derek

&quot;Fear not the storm for this is where we grow strong.&quot;

 

[daniel135]

How about just passing (int, int, Integer)?

If you are using a PreparedStatement, remember to use setNull(). (If not, what are you using?)

 

[lbrechler]

I'm a bit new at this, so at the risk of sounding ignorant -- how would I use a PreparedStatement? Are there specific benefits / risks that would factor into when to use one? I'm currently using:

c = DriverManager.getConnection(dbURL, dbUsername, dbPass);
s = c.createStatement();
String q = <my query>
s.executeUpdate(q);

 

[AdamRice32]

Try something like this:

String myQuery = "SELECT * FROM someTable WHERE someColumn = ?";

c = DriverManager.getConnection( dbURL, dbUsername, dbPass );
PreparedStatement ps = c.prepareStatement( myQuery );

if ( myInt == -99 )
{
    ps.setNull( 1, java.sql.Types.INTEGER );
}
else
{
    ps.setInt( 1, myInt );
}

ResultSet rs = ps.execute();

With an additional try-catch-finally framework, that should do the trick.

Hope this helps,
Adam Rice

 

[AdamRice32]

Sorry... I guess I forgot to address the risks/issues associated with prepared statements. Essentially, they are useful because the SQL is compiled only once and is then cached on the database server. Thus, if you are repeatedly using the same queries, they should provide a performance improvement.

However, I believe that the overhead associated with creating them in the first place is significantly higher than if you were to build your query up from scratch (i.e. using a StringBuffer to append each individual argument and then calling toString() before throwing the query into the execute() method).

Thus, if you are only executing a particular query once, you will save yourself some processing time by creating the query manually (i.e. using a normal java.sql.Statement). However, if you're going to be running the same query many times (even with different arguments), you're better off going with a PreparedStatement.

If anyone wants to add clarity, feel free.

Best,
Adam Rice

 

[AdamRice32]

Oh yeah, and that last line of code should read:

ResultSet rs = ps.executeQuery();

Sorry 'bout that.