Skip to content

Instantly share code, notes, and snippets.

@rogerleite
Created October 24, 2009 13:41
Show Gist options
  • Save rogerleite/217543 to your computer and use it in GitHub Desktop.
Save rogerleite/217543 to your computer and use it in GitHub Desktop.
Access Oracle with jruby using JDBC. Export data to json format.
=begin
Requisitos Minimos para rodar este script:
#jruby 1.1 ou superior (nao testei com superior :D)
apt-get install jruby
#baixar o oracle jdbc (para o 10g, ou procure a versão que deseje) de:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html
#copiar o ojdbc14.jar para (usar sudo):
/usr/lib/jruby1.1/lib
#dê acesso somente leitura para o jar. Execute:
sudo chmod +r /usr/lib/jruby1.1/lib/ojdbc14.jar
#Para listas as gems instaladas. Execute:
jruby -S gem list
#Caso não tenha as gems abaixo, use os comandos para instalar:
jruby -S gem install jruby-openssl
jruby -S gem install json-jruby
#Para executar este script, use:
jruby oracle_export.rb > result_as.json
=end
require 'java'
require 'rubygems'
require 'json' #http://json-jruby.rubyforge.org/
#load oracle jdbc
Java::JavaClass.for_name("oracle.jdbc.driver.OracleDriver")
url = "jdbc:oracle:thin:@IPorHOST:1521/SERVICE_NAME"
user = "USER"
pass = "PASSWORD"
begin
con = java.sql.DriverManager.get_connection(url, user, pass);
rescue Exception => ex
puts "Connection failed! :X url=#{url}"
puts "Exception: #{ex}"
return
end
if con.nil?
puts "Connection failed! :X url=#{url}"
return
end
st = con.create_statement
query = "
SELECT 'example'
FROM dual
"
rs = st.execute_query(query)
md = rs.get_meta_data
column_count = md.get_column_count
columns = []
for index in 1..column_count do
label = md.get_column_label(index)
columns << label.downcase!.to_sym
end
materias = []
while rs.next
hash_materia = {}
columns.each do |column_name|
hash_materia[column_name] = rs.get_string(column_name.to_s)
end
materias << hash_materia
end
rs.close
st.close
con.close
json_result = JSON(materias)
#parametros usados
puts "/*"
puts "#{Time.now}"
puts "Colunas exportadas: #{columns.inspect}"
puts "Query: #{query}"
puts "*/\n"
#resultado
puts json_result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment