DZone Snippets: query code

Adding helpful error messages to your Ruby code

jrobertson (James Robertson) — Wed, 30 Apr 2008 11:40:47 GMT

This Ruby code raises an error if the XPath query fails because the attribute being queried did not exist for the given element.



  def map_pattr(node, fieldx, valuex)

    begin

    parameter = node.root.elements["parameter[@field='#{fieldx}']"]

    parameter.add_attribute('value', valuex)

    parameter

    

    rescue

      puts 'feedpopulated.rb: map_attr() the field ' + fieldx + ' was not found in params.'

      raise

    end

  end

Notice that a raise statement is used to ensure that the system error message is raised and any further code execution is halted.

Without adding a customized helpful message I would be left scratching my head trying to work out what the following system error message meant.



./feedpopulated.rb:27:in `map_pattr': undefined method `add_attribute' for nil:NilClass (NoMethodError)

     from ./feedpopulated.rb:51:in `create_record'

     from ./feedpopulated.rb:49:in `each'

     from ./feedpopulated.rb:49:in `create_record'

     from ./recordx.rb:91:in `call_create'

     from ./s3fileuploader_handler.rb:14:in `call'

     from ./s3fileuploader_handler.rb:40:in `invoke'

     from ./uploadtwitteraudio.rb:22:in `initialize'

     from /usr/lib/ruby/1.8/rexml/element.rb:890:in `each'

     from /usr/lib/ruby/1.8/rexml/xpath.rb:53:in `each'

     from /usr/lib/ruby/1.8/rexml/element.rb:890:in `each'

     from ./uploadtwitteraudio.rb:18:in `initialize'

     from ./uploadtwitteraudio.rb:72:in `new'

     from ./uploadtwitteraudio.rb:72

Reference: Programming Ruby: The Pragmatic Programmer's Guide - Exceptions, Catch, and Throw [ruby-doc.org]

Excel : Make a query on a Oracle database and return the result (useful for sheet formulas)

bouffon69 (Sylvain Le Courtois) — Mon, 10 Sep 2007 15:26:22 GMT

// This should be pasted in a module of the workbook



Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)

  Dim strConOracle, oConOracle, oRsOracle

  Dim StrResult As String

  

  StrResult = ""

  

  strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _

         "CONNECTSTRING=(DESCRIPTION=" & _

         "(ADDRESS=(PROTOCOL=TCP)" & _

         "(HOST=" & strHost & ")(PORT=1521))" & _

         "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

  Set oConOracle = CreateObject("ADODB.Connection")

  Set oRsOracle = CreateObject("ADODB.Recordset")

  oConOracle.Open strConOracle

  Set oRsOracle = oConOracle.Execute(strSQL)

  Do While Not oRsOracle.EOF

      If StrResult <> "" Then

        StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value

      Else

        StrResult = oRsOracle.Fields(0).Value

      End If

    oRsOracle.MoveNext

  Loop

  oConOracle.Close

  Set oRsOracle = Nothing

  Set oConOracle = Nothing

  ORAQUERY = StrResult

End Function

Detect a field edit in Excel and refresh a Query

davetrane (David Davis) — Fri, 02 Mar 2007 04:29:25 GMT



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wks As Worksheet

    Set wks = ActiveSheet



    If Target.Row = 1 And Target.Column = 1 Then

      wks.QueryTables(1).Refresh

    End If



    Set wks = Nothing

End Sub

One liner to list query methods for an object

MarkStevenWoods (Mark Woods) — Thu, 28 Dec 2006 00:41:57 GMT

// List query methods for an object



puts Object.methods.sort.join("\n").grep(/\?/)

conditioner for ActiveRecord-friendly conditions from a collection

moneypenny () — Thu, 31 Aug 2006 22:02:52 GMT

I frequently have a collection of values that I want to match in an ActiveRecord query, but it would be nice if I could let ActiveRecord handle checking the data and escaping it properly. So, I wrote this method to return ActiveRecord-friendly conditions, such as: ["user_id=? AND job_id=?", 3, 4] based on the 'raw' conditions you feed to it, such as: [['user_id', 3], ['job_id', 4]]

# Returns ActiveRecord-friendly conditions based on the given

# raw conditions; handles grouping based on like field names;

# allows different boolean operators in raw conditions;

# allows different comparison operators in raw conditions;

# raw conditions setup:

# [[field name, desired value, bool. op., comp. op.], ...]

# name = condition[0]

# value = condition[1]

# bool_type = condition[2]

# comparison = condition[3]

# raw conditions example:

# [['type_id', '4', 'OR'], ['created_on', Date.new, 'AND', '<=']]

def conditioner( raw_conditions )

  return nil if raw_conditions.nil? || raw_conditions.empty?

  

  conditions = ['(']

  count = 0

  prev_name = raw_conditions[0][0]

  raw_conditions.each do |condition|

    name = condition[0]

      

    conditions[0] << ') AND ' if prev_name != name

    conditions[0] << ' ' << ( condition[2] || 'OR' ) << ' ' unless count == 0 || prev_name != name

    conditions[0] << '(' if prev_name != name

    conditions[0] << name + ' ' << ( condition[3] || '=' ) + ' ?'

      

    conditions << condition[1]

      

    prev_name = name

    count += 1

  end

  conditions[0] << ')'



  conditions

end

This way, you can do something like the following:

model_ids = Model.find( :all ).map( &:id )

raw_conditions = model_ids.collect { |id| ['model_id', id] }

conditions = conditioner( raw_conditions )

desired_collection = OtherModel.find( :all, :conditions => conditions )

If your query needs to depend on more than one factor, you might do something like the following:

if test

  raw_conditions = [['user_id', 3]]

else

  raw_conditions = [['user_id', 4], ['groups.name', 'dev']]

end



team_ids.each { |id| raw_conditions << ['team_id', id, 'AND'] }

Execute arbitary SQL in JDBC & get column names etc from the meta data

mikewilsonuk (Mike Wilson) — Sun, 14 May 2006 20:25:52 GMT

This will execute an arbitary SQL string in JDBC and extract the column names. I extracted this code from a much larger module I wrote years ago, so it isn't complete and hasn't been tested. You have been warned.



    /*

    ** connection is a java.sql.Connection obtained in the usual way.

    */



    PreparedStatement statement =

	connection.prepareStatement (sqlString);

    statement.setMaxRows (configModel.getMaxRows ());



    if (statement.execute ())

    {

	ResultSet resultSet = statement.getResultSet ();

	ResultSetMetaData metaData = resultSet.getMetaData ();



	/*

	** Get the column names.

	*/



	for (int i = 0 ; i < metaData.getColumnCount () ; i++)

	{

	    int columnType = metaData.getColumnType (i + 1);

	    String columnName = metaData.getColumnLabel (i + 1);



	    /*

	    ** Do something with columnType & columnName.

	    */



	}



	/*

	** Fetch the rows.

	*/



	while (resultSet.next ())

	{

	    String value;



	    for (int i = 0 ; i < metaData.getColumnCount () ; i++)

		value = resultSet.getString (i + 1);

	}

    }

    else

    {

	/*

	** Query was probably update/insert/delete.

	*/



	int rowCount = statement.getUpdateCount ();

    }



    statement.close ();

Create Temporary MySQL Table

offspinner () — Thu, 09 Feb 2006 16:04:54 GMT

// description of your code here



USE reference;



CREATE TEMPORARY TABLE fulltxt

SELECT surname,atitle,title,type,journal,pqid,volume, issn,issue,spage,year

FROM citations

WHERE volume > 0

AND issue > 0

AND spage > 0

AND issn != ''

AND pqid != ''

;

query same table simultaneously with mysql

offspinner () — Thu, 09 Feb 2006 15:50:15 GMT

// description of your code here



USE reference;



SELECT c2.pqid AS candidate,c1.surname,c1.atitle,c1.title,c1.type,c1.journal,c1.pqid,c1.volume, c1.issn,c1.issue,c1.spage,c1.year

FROM citations AS c1, citations AS c2

WHERE c1.issn = c2.issn

AND c1.volume = c2.volume

AND c1.issue = c2.issue

AND c1.year = c2.year

AND c1.spage = c2.spage

AND c1.pqid != c2.pqid

AND c1.pqid = ''

;

Parsing a query string with String#scan into a Hash

ntk () — Sun, 29 Jan 2006 21:48:29 GMT

For more information on the desired hash output, etc. see:

http://redhanded.hobix.com/inspect/injectingAHashBackwardsAndTheMergeBlock.html





qs = "post[id]=4&post[nick]=_why&post[message]=GROSS & FOO!&a=1"  # query string



hash1 = {}

hash2 = {}

resulthash = {}

count = 0



qs.scan(/(post|a)(\[|=)(.*?)(?=(&post\[|&a=|$))/) {



var = $1 << $2 << $3  





case var



when /^(post)\[(.*?)\]=(.*?)$/ then 

               count += 1

               postnum = $1 << count.to_s

               hash1.update(postnum => {$2 => $3})



when /^(a)=(.*?)$/ then resulthash.update($1 => $2)



end





}



hash1.each_pair {|k, v| hash2.update(v)}

resulthash.update("post" => hash2)



puts resulthash.inspect