Method: QuickBase::Client#doSQLQuery

Defined in:

lib/QuickBaseClient.rb

#doSQLQuery(sqlString, returnOptions = nil) ⇒ Object

Translate a simple SQL SELECT statement to a QuickBase query and run it.

If any supplied field names are numeric, they will be treated as QuickBase field IDs if
they aren't valid field names.

• e.g. doSQLQuery( “SELECT FirstName,Salary FROM Contacts WHERE LastName = ”Doe“ ORDER BY FirstName )

• e.g. doSQLQuery( “SELECT * FROM Books WHERE Author = ”Freud“ )

Note: This method is here primarily for Rails integration. Note: This assumes, like SQL, that your column (i.e. field) names do not contain spaces.

# File 'lib/QuickBaseClient.rb', line 4654

def doSQLQuery( sqlString, returnOptions = nil )

   ret = nil
   sql = sqlString.dup
   dbid = nil
   clist = nil
   slist = nil
   state = nil
   dbname = ""
   columns = []
   sortFields = []
   limit = nil
   offset = nil
   options = nil
   getRecordCount = false

   queryFields = []
   query = "{'["
   queryState = "getFieldName"
   queryField = ""
   
   sql.split(' ').each{ |token|
      case token
         when "SELECT" then state = "getColumns";next
         when "count(*)" then state = "getCount";next
         when "FROM" then state = "getTable";next
         when "WHERE" then state = "getFilter" ;next
         when "ORDER" then state = "getBy";next
         when "BY" then state = "getSort";next
         when "LIMIT" then state = "getLimit";next
         when "OFFSET" then state = "getOffset";next
      end
      if state == "getColumns"
         if token.index( "," )
            token.split(",").each{ |column| columns << column if column.length > 0 }
         else
            columns << "#{token} "
         end
      elsif state == "getCount"
         getRecordCount = true
      elsif state == "getTable"
         dbname = token.strip
      elsif state == "getFilter"
         if token == "AND"
            query.strip!
            query << "'}AND{'["
            queryState = "getFieldName"
         elsif token == "OR"
            query.strip!
            query << "'}OR{'["
            queryState = "getFieldName"
         elsif token == "="
            query << "]'.TV.'"
            queryState = "getFieldValue"
            queryFields << queryField
            queryField  = ""
         elsif token == "<>" or token == "!="
            query << "]'.XTV.'"
            queryState = "getFieldValue"
            queryFields << queryField
            queryField  = ""
         elsif queryState == "getFieldValue"
            fieldValue = token.dup
            if fieldValue[-2,2] == "')"
               fieldValue[-1,1] = ""
            end
            if fieldValue[-1] == "'"
               fieldValue.gsub!("'","")
               query << "#{fieldValue}"
            else   
               fieldValue.gsub!("'","")
               query << "#{fieldValue} "
            end
         elsif queryState == "getFieldName"
            fieldName = token.gsub("(","").gsub(")","").gsub( "#{dbname}.","")
            query << "#{fieldName}"
            queryField << "#{fieldName} "
         end
      elsif state == "getSort"
         if token.contains( "," )
            token.split(",").each{ |sortField| sortFields << sortField if sortField.length > 0 }
         else
            sortFields << "#{token} "
         end
      elsif state == "getLimit"
         limit = token.dup
         if options.nil?
            options = "num-#{limit}" 
         else
            options << ".num-#{limit}"
         end
      elsif state == "getOffset"   
         offset = token.dup
         if options.nil?
            options = "skp-#{offset}" 
         else
            options << ".skp-#{offset}"
         end
      end
   }
   
   if dbname and @dbid.nil?
      dbid = findDBByname( dbname )
   else
      dbid = lookupChdbid( dbname )
   end
   dbid ||= @dbid

   if dbid
      getDBInfo( dbid )
      getSchema( dbid )
      if columns.length > 0
         if columns[0] == "* "
            columns = getFieldNames( dbid )
         end
         columnNames = []
         columns.each{ |column|
            column.strip!
            fid = lookupFieldIDByName( column )
            if fid.nil? and column.match(/[0-9]+/)
               fid = column
               columnNames << lookupFieldNameFromID(fid)
            else   
               columnNames << column
            end
            if fid
               if clist
                  clist << ".#{fid}"
               else
                  clist = fid
               end
            end
         }
      end
      if sortFields.length > 0
         sortFields.each{ |sortField|
            sortField.strip!
            fid = lookupFieldIDByName( sortField )
            if fid.nil?
               fid = sortField if sortField.match(/[0-9]+/)
            end
            if fid 
               if slist
                  slist << ".#{fid}"
               else
                  slist = fid
               end
            end
         }
      end
      if queryFields.length > 0
         query.strip!
         query << "'}"
         queryFields.each{ |fieldName|
            fieldName.strip!
            fid = lookupFieldIDByName( fieldName )
            if fid
               query.gsub!( "'[#{fieldName} ]'", "'#{fid}'" )
            end
         }
      else
         query = nil
      end
      if getRecordCount 
         ret = getNumRecords(dbid)
      elsif returnOptions == :Hash
         ret = getAllValuesForFields(dbid, columnNames, query, nil, nil, clist, slist,"structured",options)
      elsif returnOptions == :Array
         ret = getAllValuesForFieldsAsArray(dbid, columnNames, query, nil, nil, clist, slist,"structured",options)
      else
         ret = doQuery( dbid, query, nil, nil, clist, slist, "structured", options )
      end
   end
   ret
end