hi everybody,

i want to search the r,g,b values in a database and compare it to the rgb values of a pixel. Now the problem with this is that i cannot find a easy way to search in processing tru this sqlite database i've converted. It shows the values correctly and i can view everything in the database. It's a database of 300 MB, so putting everything in a string array is not realy a option.
Code:

// fjenett 20081129

import de.bezier.data.sql.*;
String[] rString = new String[0];
String[] gString = new String[0];
String[] bString = new String[0];
String[] colourString = new String[0];

SQLite db;

void setup()
{
    size( 100, 100 );

    db = new SQLite( this, "text.db" );  // open database file

    if ( db.connect() )
    {
	  // list table names
	  db.query( "SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"" );
	  
	  while (db.next())
	  {
		println( db.getString("Name")+" naam" );
	  }
	  
	  // read all in table "table_one"
	  db.query( "SELECT * FROM answers" );
	  
	  while (db.next())
	  {
	    // String user = db.getString("user_id") ;
		 String r = db.getString("r") ;
		    //  println( db.getString("g") );
				   // println( db.getString("b") );
					 //  println( db.getString("colorname") );
	     // println( db.getInt("field_two") );
		String g = db.getString("g") ;
		 String b = db.getString("b") ;
		 String colorname = db.getString("colorname") ;
		//     rString = append(rString, r);
		//	gString = append(gString, g);
		//	 bString = append(bString, b);
		//	  colourString = append(colourString, colorname);
	  }
    }
}
void draw(){
  
 println(rString[0]);
}
 

the database is from here: http://blog.xkcd.com/2010/05/03/color-survey-results/

Amusing database.
I wanted to play with it a bit too, so I recycled an old sketch I had with MySQL and adapted it to this data.
For the record, I created the database using the line:
cat D:\Archives\mainsurvey_sqldump.txt | sqlite3 XKCD_Colors

So I made a quick sketch out of that.
[EDIT] I changed the sketch as my requests weren't doing what I expected... I allow to stop the sketch and choose one predefined request. A more sophisticated version would use GUI to choose intervals...
I have to split the sketch in two parts.
Code:

import de.bezier.data.sql.*;
import java.sql.Timestamp;

static final String USERS_TABLE_NAME = "users";
static final String ANSWERS_TABLE_NAME = "answers";
static final int CHECK_INTERVAL = 100; // milliseconds
static final int MESSAGE_DISPLAY_NB = 35;
int lastCheck = - CHECK_INTERVAL - 1;
boolean bStop;

SQLite sqlite;
PFont fontHeader;
PFont fontMessage;

int userNb;
int answerNb;
ArrayList colorNames_red;
ArrayList colorNames_green;
ArrayList colorNames_blue;
int lastMessageDisplayedIndex;
int totalMessageNb;

void setup()
{
  size(800, 700);
  smooth();
  fontHeader = createFont("Arial", 30);
  fontMessage = createFont("Verdana", 16);

  Connect();
  GetPureColors();
}

void stop()
{
  println("Stopping connection");
  sqlite.close();
}

void draw()
{
  int time = millis();
  if (!bStop && time - lastCheck > CHECK_INTERVAL)
  {
    lastCheck = time;

    background(128);
    textFont(fontHeader, 36);
    fill(#FFCC88);
    text("XKCD Colors", 10, 36);
//    ShowCount(); // Slow!

    if (lastMessageDisplayedIndex < totalMessageNb - 1)
    {
	lastMessageDisplayedIndex++;
    }
    else
    {
	lastMessageDisplayedIndex = 0;
    }

    ShowIndex(lastMessageDisplayedIndex);
    for (int i = 0; i < MESSAGE_DISPLAY_NB; i++)
    {
	int idx = 1 + lastMessageDisplayedIndex - MESSAGE_DISPLAY_NB + i;
	if (idx < 0)
	  continue;
	ColorName cn = (ColorName) colorNames_red.get(idx);
	ShowColorName(cn, 0, i);
	cn = (ColorName) colorNames_green.get(idx);
	ShowColorName(cn, 1, i);
	cn = (ColorName) colorNames_blue.get(idx);
	ShowColorName(cn, 2, i);
    }
  }
}

void mousePressed()
{
  bStop = true;
}

void mouseReleased()
{
  bStop = false;
}

void keyPressed()
{
  if (key == 'p' || key == 'P')
  {
    GetPureColors();
  }
  else if (key == 'l' || key == 'L')
  {
    GetLightColors();
  }
  else if (key == 'o' || key == 'O')
  {
    GetOtherColors();
  }
  else if (key == 'd' || key == 'D')
  {
    GetDifferentColors();
  }
}

void DoStats()
{
  totalMessageNb = min(
	colorNames_red.size(),
	colorNames_green.size(),
	colorNames_blue.size()
  );
  if (totalMessageNb == 0)
  {
    println("Not much to show!");
    exit();
  }
  lastMessageDisplayedIndex = 0;
}

void GetPureColors()
{
  colorNames_red = GetColorNames(255, 0, 0, -20, 0, -20);
  colorNames_green = GetColorNames(0, -20, 255, 0, 0, -20);
  colorNames_blue = GetColorNames(0, -20, 0, -20, 255, 0);
  DoStats();
}

void GetLightColors()
{
  colorNames_red = GetColorNames(255, 0, 255, 20, 255, 20);
  colorNames_green = GetColorNames(255, 20, 255, 0, 255, 20);
  colorNames_blue = GetColorNames(255, 20, 255, 20, 255, 0);
  DoStats();
}

void GetOtherColors()
{
  colorNames_red = GetColorNames(255, 0, 127, -1, 127, -1);
  colorNames_green = GetColorNames(127, -1, 255, 0, 127, -1);
  colorNames_blue = GetColorNames(127, -1, 127, -1, 255, 0);
  DoStats();
}

void GetDifferentColors()
{
  colorNames_red = GetColorNames(255, 0, 128, 1, 128, 1);
  colorNames_green = GetColorNames(128, 1, 255, 0, 128, 1);
  colorNames_blue = GetColorNames(128, 1, 128, 1, 255, 0);
  DoStats();
}

void ShowIndex(int idx)
{
  textFont(fontHeader, 20);
  fill(#AACCFF);
  text(idx + "/" + totalMessageNb, width / 2 - 100, 20);
}

void ShowCount()
{
  if (userNb == 0)
  {
    userNb = GetCount(USERS_TABLE_NAME);
    answerNb = GetCount(ANSWERS_TABLE_NAME);
  }
  textFont(fontHeader, 20);
  fill(#AACC88);
  text("Number of users: " + userNb, width / 2, 20);
  text("Number of answers: " + answerNb, width / 2, 40);
}

void ShowColorName(ColorName colorName, int col, int pos)
{
  int xPos = 20 + col * (width - 20) / 3;
  int yPos = 100 + pos * 16;
  textFont(fontMessage);
  fill(color(colorName.r, colorName.g, colorName.b));
  text(colorName.name, xPos, yPos);
} 

Oh, this is a pretty cool Database. I not to forget i love xkcd