Scripts Liferay Control Panel

Ruby - Execute SQL

QUERY = "select * from Role_ where roleid IN (17,18)" 
MAX_ROWS = 100

# Implementation

java_import java.io.PrintStream
java_import com.liferay.portal.kernel.io.unsync.UnsyncByteArrayOutputStream
java_import com.liferay.portal.kernel.dao.jdbc.DataAccess

if $out.class == UnsyncByteArrayOutputStream # Fix for Liferay version < 6.0.11
  $out = PrintStream.new($out)
end
  
def log(message)
  puts message
  $out.println(message)
end

rs = nil
stmt = nil
con = DataAccess.getConnection()

begin
  stmt = con.createStatement()
  stmt.setMaxRows(MAX_ROWS)
  rs = stmt.executeQuery(QUERY)
  md = rs.getMetaData()
  cc = md.getColumnCount() 
  
  header = "#"
  for column in 1..cc
    value = md.getColumnLabel(column) 
    header = header + "," + value.to_s  
  end
  log(header)

  while rs.next() do
    line = rs.getRow().to_s
    for column in 1..cc
      value =  rs.getObject(column)
      line = line + "," + value.to_s
    end 
    log(line)
  end 
ensure
  DataAccess.cleanUp(con, stmt, rs)
end