Bezier SQL question: how to iterate a specific row within a column

walkingintheweeds

I am trying to retrieve one value from a sql table, but I keep running into an error that states that I have not made a query. Though I thought I did. I am trying to submit an iterative query that reads one row in a specific column at a time that feeds into the getFloat function.

import de.bezier.data.sql.*; 

int r;

PostgreSQL pgsql;

float database () {
    String user     = "";
    String pass     = "";

    // name of the database to use
    //
    String database = "db";

    // connect to database on "localhost"
    //
    pgsql = new PostgreSQL( this, "127.0.0.1", database, user, pass );

    // connected?
    //connect to database
    if ( pgsql.connect() )
    {
        //class submits query for a column, but the console reads that 'I need to query() something first for the getFloat("co2")'
        pgsql.query(( " SELECT ROW_NUMBER ("  + str(r) +  ") AS co2 FROM tree"));
        //iterate r
        r++;  

        return( pgsql.getFloat("co2") );
    }
    else
    {
        //disconnect from database
    }
    return(0);
}

PhiLho

Are you sure of the SQL request syntax?

How this problem is related to your topic subject?

Why do you make near duplicate threads?

Why do you put double parentheses in the query() call? (Not a real problem, just curious...)

walkingintheweeds

I am trying to be sure of the syntax...

you might suggest another query.

Anyways, the pgSQL table provides data for a visualization in Processing via the bezier class. The visualization updates at regular time intervals with a singular data point, rather than an entire column or row.

I am making near duplicate threads because 1) newbie to programming (sorry PhiLho), 2) this is the final small bug that connects a simulated Pi to Amazon pgSQL database to Processing (I would like to get success), and 3) the project was bigger than my knowledge. I am a proverbial programming test crash dummy trying to integrate python, processing, and SQL simultaneously within 2 weeks.

I would have deleted the previous threads if I had that ability.

To answer the last question, a typo in the multiple syntax changes that have occurred in the last several attempts to try to get it right.

walkingintheweeds

thanks for the link. its the first real useful thing i have read.

walkingintheweeds

ok, the query syntax is now fine, but here is the thing I don't comprehend. I follow all of the structures I have seen on the web for this and even though the code connects, has a query in the program, the processing console returns this mess that states java errors.... like 7 of them and that I need to submit a query. pgsql.query is making the call, but it is not receiving the query. It reads "You need to query() something first." Have I not done that in this code?

PhiLho

Better paste here this "mess that states java errors", we are used to interpret it.

walkingintheweeds

Here are the errors.

hereSQL.query(): java.sql.SQLException.

// I am not sure where this came from. I do not use this line of code.

    SELECT relname AS 'table_name' FROM pg_stat_user_tables WHERE schemaname='public'
    org.postgresql.util.PSQLException: ERROR: syntax error at or near "'table_name'"
      Position: 19
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
    at de.bezier.data.sql.SQL.queryOrExecute(Unknown Source)
    at de.bezier.data.sql.SQL.query(Unknown Source)
    at de.bezier.data.sql.PostgreSQL.getTableNames(Unknown Source)
    at de.bezier.data.sql.SQL.connect(Unknown Source)
    at sketch_131205a.database(sketch_131205a.java:48)
SQL.next(): You need to query() something first.
    at sketch_131205a.draw(sketch_131205a.java:30)
Using driver org.postgresql.Driver 9.2
    at processing.core.PApplet.handleDraw(PApplet.java:2306)
    at processing.core.PGraphicsJava2D.requestDraw(PGraphicsJava2D.java:243)
    at processing.core.PApplet.run(PApplet.java:2177)
    at java.lang.Thread.run(Unknown Source)

//I use this followinf line of code that works in the command line of the pgSQL database

SQL.query(): java.sql.SQLException.

SELECT co2 FROM table ORDER BY co2 DESC LIMIT 1; 
org.postgresql.util.PSQLException: ERROR: syntax error at or near "table"
  Position: 17
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
    at de.bezier.data.sql.SQL.queryOrExecute(Unknown Source)
    at de.bezier.data.sql.SQL.query(Unknown Source)
    at sketch_131205a.database(sketch_131205a.java:50)
    at sketch_131205a.draw(sketch_131205a.java:30)
hereSQL.next(): You need to query() something first.
SQL.getFloat(): You need to query() something first.
0.0 at processing.core.PApplet.handleDraw(PApplet.java:2306)
    at processing.core.PGraphicsJava2D.requestDraw(PGraphicsJava2D.java:243)
    at processing.core.PApplet.run(PApplet.java:2177)
    at java.lang.Thread.run(Unknown Source)

PhiLho

Perhaps table is a reserved word? Better use another name for your table.

Not sure about the first one, but it might be related (looks like an internal request failing somewhere).

walkingintheweeds

A person on stack exchange I think nailed it. The errors are caused by moving the code from within the setup function to a helper file, which that migration is what I am trying to accomplish. I don't fully understand it, but it is related to the extension of the Papplet in the Java base... maybe it is just finnicky?

walkingintheweeds

So the code has improved bc of the observations from others. I can't get to the point of getting the code to work because the IDE reports a token error in the class Database.

import de.bezier.data.sql.*; 

PostgreSQL pgsql;
Database col;

void setup()
{
    size( 100, 100 );
    String user     = "aaron";
    String pass     = "aaron";
    String database = "aarondb";
    pgsql = new PostgreSQL( this, "127.0.0.1", database, user, pass );
    println ("ok");
}

void draw()
{
  lnprint (col);
}

Class Database 
{
  float val;
  Database (float col)
  {
    val = col;
  }

  void update( ) 
  { 
    col;
    if ( pgsql.connect() )
    {
      pgsql.query( "SELECT col FROM table ORDER BY col DESC LIMIT 1; " );
      return( pgsql.getFloat("col") );
    }
    else
    {
      return float (col = 0);
    }
  }
}

PhiLho

"Class Database"
Java (and Processing) is case-sensitive. Must be class Database.

The col; statement is useless / nonsensical too. So is return float (col = 0); There is no field named col, only a constructor parameter.