java.sql.SQLException: invalid arguments in call

[Gustavo de Sá Carvalho Honorato]

Hi,

I'm getting the following error while trying to run migrations. I'm
using JRuby 1.5.2 and Rails 2.3.8. Any help is appreciated.

java.sql.SQLException: invalid arguments in call
oracle/jdbc/driver/DatabaseError.java:125:in `throwSqlException'
oracle/jdbc/driver/DatabaseError.java:162:in `throwSqlException'
oracle/jdbc/driver/DatabaseError.java:227:in `throwSqlException'
oracle/jdbc/driver/T4CConnection.java:206:in `logon'
oracle/jdbc/driver/PhysicalConnection.java:361:in `<init>'
oracle/jdbc/driver/T4CConnection.java:151:in `<init>'
oracle/jdbc/driver/T4CDriverExtension.java:32:in `getConnection'
oracle/jdbc/driver/OracleDriver.java:595:in `connect'
java/sql/DriverManager.java:582:in `getConnection'
java/sql/DriverManager.java:154:in `getConnection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_jdbc_connection.rb:97:in
`new_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_jdbc_connection.rb:46:in
`initialize'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_connection.rb:11:in
`new'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_connection.rb:11:in
`create'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_base_ext.rb:12:in
`oracle_enhanced_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in
`new_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in
`checkout_new_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in
`checkout'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in
`loop'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in
`checkout'
/home/gustavo/.rvm/rubies/jruby-1.5.2/lib/ruby/1.8/monitor.rb:191:in
`mon_synchronize'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in
`checkout'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in
`connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in
`retrieve_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in
`retrieve_connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in
`connection'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/migration.rb:440:in
`initialize'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/migration.rb:401:in
`new'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/migration.rb:401:in
`up'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/migration.rb:383:in
`migrate'
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/rails-2.3.8/lib/tasks/databases.rake:112
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:636:in
`call'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:636:in
`execute'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:631:in
`each'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:631:in
`execute'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:597:in
`invoke_with_call_chain'
/home/gustavo/.rvm/rubies/jruby-1.5.2/lib/ruby/1.8/monitor.rb:191:in
`mon_synchronize'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:590:in
`invoke_with_call_chain'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:583:in
`invoke'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2051:in
`invoke_task'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2029:in
`top_level'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2029:in
`each'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2029:in
`top_level'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2068:in
`standard_exception_handling'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2023:in
`top_level'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2001:in
`run'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:2068:in
`standard_exception_handling'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/lib/rake.rb:1998:in
`run'
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/bin/rake:31
/home/gustavo/.rvm/gems/jruby-1.5.2@global/gems/rake-0.8.7/bin/rake:19:in `load'
/home/gustavo/.rvm/rubies/jruby-1.5.2/bin/rake:19
/home/gustavo/.rvm/rubies/jruby-1.5.2/bin/rake:1:in `load'
-e:1

Thanks,
Gustavo Honorato

[Raimonds Simanovskis]

If you go to /home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-oracle_enhanced-adapter-1.3.1/lib/active_record/connection_adapters/oracle_enhanced_jdbc_connection.rb line 97 then you can find the code that is failing:

          properties = java.util.Properties.new

          properties.put("user", username)

          properties.put("password", password)

          properties.put("defaultRowPrefetch", "#{prefetch_rows}") if prefetch_rows

          properties.put("internal_logon", privilege) if privilege

      

          @raw_connection = java.sql.DriverManager.getConnection(url, properties)

Can you check what url and properties are passed to this method call? These values are coming from database.yml, maybe something wrong is specified there.

Raimonds

[Gustavo de Sá Carvalho Honorato]

Hi Raimonds,

here is my database.yml:

test:
adapter: oracle_enhanced
database: xe
host: 192.168.1.101
username: cas_web
password: 1234
port: 1521

I didn't understand where is the error yet...

Thanks for the help.

>
> --
> You received this message because you are subscribed to the Google Groups
> "Oracle enhanced adapter for ActiveRecord" group.
> To post to this group, send email to oracle-...@googlegroups.com.
> To unsubscribe from this group, send email to
> oracle-enhanc...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/oracle-enhanced?hl=en.
>

[Raimonds Simanovskis]

Inspect the values in the code fragment that I posted (e.g. insert puts with all parameters) to verify that these values are actually passed to JDBC connection method.

Raimonds

[Gustavo de Sá Carvalho Honorato]

I inserted the following code:

properties = java.util.Properties.new
properties.put("user", username)
properties.put("password", password)
properties.put("defaultRowPrefetch", "#{prefetch_rows}") if
prefetch_rows
properties.put("internal_logon", privilege) if privilege

puts "url = #{url}"
puts "user = #{properties.get('user')}"
puts "password = #{properties.get('password')}"
puts "defaultRowPrefetch = #{properties.get('defaultRowPrefetch')}"
puts "internal_logon = #{properties.get('internal_logon')}"

@raw_connection = java.sql.DriverManager.getConnection(url,
properties)

The printed result was:

url = jdbc:oracle:thin:@192.168.1.101:1521:xe
user = cas_web
password = 1234
defaultRowPrefetch = 100
internal_logon =

Did you see anything wrong?

[Raimonds Simanovskis]

I don't see anything wrong... To be sure please check that there are no trailing spaces for any of database.yml parameters.

Are you using ojdbc14.jar? Is it the latest version? (File size of mine is 1555682)

Can you connect to this database from any other Java program using the same jdbc connection parameters and the same Oracle JDBC driver?

Raimonds

[Gustavo de Sá Carvalho Honorato]

On Thu, Dec 9, 2010 at 4:47 PM, Raimonds Simanovskis
<raimonds.s...@gmail.com> wrote:
> I don't see anything wrong... To be sure please check that there are no
> trailing spaces for any of database.yml parameters.

Checked, there isn't any.

> Are you using ojdbc14.jar? Is it the latest version? (File size of mine
> is 1555682)

Mine is 1378346. I downloaded this version from Oracle's site a few days ago.

> Can you connect to this database from any other Java program using the same
> jdbc connection parameters and the same Oracle JDBC driver?

I made a small test and it worked (see the code below):

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);

Properties properties = new Properties();
properties.put("user", "cas_web");
properties.put("password", "1234");
properties.put("defaultRowPrefetch", 100);

String url = "jdbc:oracle:thin:@192.168.1.101:1521:xe";

Connection connection = DriverManager.getConnection(url, properties);

}

I changed the code

FROM
@raw_connection = java.sql.DriverManager.getConnection(url,
properties)
TO
@raw_connection = java.sql.DriverManager.getConnection(url,
username.to_s, password.to_s)

and now I'm getting the following error:

java.sql.SQLException: ORA-01882: timezone region not found

My timezone configuration in enviroment.rb is:

config.time_zone = 'Brasilia'
config.i18n.default_locale = 'pt-BR'

> Raimonds

[Gustavo de Sá Carvalho Honorato]

Additional information: I'm using Oracle 10g XE.

2010/12/9 Gustavo de Sá Carvalho Honorato <gustavo...@gmail.com>:

[KUBO Takehiro]

2010/12/10 Gustavo de Sá Carvalho Honorato <gustavo...@gmail.com>:

> here is my database.yml:
>
> test:
>  adapter: oracle_enhanced
>  database: xe
>  host: 192.168.1.101
>  username: cas_web
>  password: 1234
>  port: 1521

It may be caused by datatype mismatch.
See: http://en.wikipedia.org/wiki/YAML#Casting_data_types

Change the password line as follows:
password: "1234"

[Raimonds Simanovskis]

Yes, numeric password might be an issue - in YAML file you should put numeric password in quotes so that it would be interpreted as string and not as number.

Regarding time zone please include in some initializer

ENV['TZ'] = 'Brazil'

See example at

https://github.com/rsim/rails3_oracle_sample/blob/master/config/initializers/oracle.rb#L1-3

It will ensure that both Ruby local timezone and Oracle session timezone will be the same.

Raimonds

[Gustavo de Sá Carvalho Honorato]

Thanks a lot! I've made some progress, now I'm getting the error:

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in
connect descriptor
The Connection descriptor used by the client was:
192.168.1.101:1521:xe

It looks like more related to JDBC-Oracle driver than oracle_enhanced.
Do you think you can still help me?

[Raimonds Simanovskis]

Hmm, this error means that TNS listener on server accepted connection but on server in $ORACLE_HOME/network/admin/listener.ora file it did not find entry for xe database.

Are you still able to connect to database from other tools? E.g. you can try to use Oracle SQL Developer and use Connection Type Advanced and then you can specify exactly the same JDBC URL.

But also I would suggest to try the same JDBC driver that I am using. You can get it from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

and I use version for Oracle 10g 10.2.0.4 (which works fine also with Oracle 10g XE 10.2.0.1)

ojdbc14.jar (1,555,682 bytes) - classes for use with JDK 1.4 and 1.5 Raimonds

[Gustavo de Sá Carvalho Honorato]

On Fri, Dec 10, 2010 at 10:47 AM, Raimonds Simanovskis
<raimonds.s...@gmail.com> wrote:
>
> Hmm, this error means that TNS listener on server accepted connection but on server in $ORACLE_HOME/network/admin/listener.ora file it did not find entry for xe database.
> Are you still able to connect to database from other tools?

I tried to connect again using the Java test program I wrote before
and the same "ORA-12505, TNS:listener does not currently know of SID
given in connect descriptor" error occurred. After some time trying to
solve this problem, I connected in database using sqlplus and after I
tried again to connect using my Java test and it worked! The error
stopped misteriosly. Now, I can connect in pure java, but using
oracle_enhanced I'm getting the same timezone error. I've created an
oracle.rb file in initializers and inserted "ENV['TZ'] = 'Brazil'"
code into it, but the error is still happening.

Do you have any ideais?

E.g. you can try to use Oracle SQL Developer and use Connection Type
Advanced and then you can specify exactly the same JDBC URL.
> But also I would suggest to try the same JDBC driver that I am using. You can get it from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html
> and I use version for Oracle 10g 10.2.0.4 (which works fine also with Oracle 10g XE 10.2.0.1)
> ojdbc14.jar (1,555,682 bytes) - classes for use with JDK 1.4 and 1.5

I've downloaded this driver and I'm using now.

[Gustavo de Sá Carvalho Honorato]

2010/12/10 Gustavo de Sá Carvalho Honorato <gustavo...@gmail.com>:

> On Fri, Dec 10, 2010 at 10:47 AM, Raimonds Simanovskis
> <raimonds.s...@gmail.com> wrote:
>>
>> Hmm, this error means that TNS listener on server accepted connection but on server in $ORACLE_HOME/network/admin/listener.ora file it did not find entry for xe database.
>> Are you still able to connect to database from other tools?
>
> I tried to connect again using the Java test program I wrote before
> and the same "ORA-12505, TNS:listener does not currently know of SID
> given in connect descriptor" error occurred.

ERRATA: Sorry, I've made a mistake, the error is ORA-01882: TIMEZONE
REGION NOT FOUND

[Gustavo de Sá Carvalho Honorato]

It worked! I set the timezone to ENV['TZ'] = 'America/Sao_Paulo'

Thanks very much!

[Tharindu Abeydeera]

I faced the same issue with Oracle 10g XE , JRUBY with Torquebox i solved it by using loder jdbc driver i.e. ojdbc14.jar and placing it in "{root}/lib" of the rails app.