Processing 1.0 - Processing Discourse

We closed this forum 18 June 2010. It has served us well since 2005 as the ALPHA forum did before it from 2002 to 2005. New discussions are ongoing at the new URL http://forum.processing.org. You'll need to sign up and get a new user account. We're sorry about that inconvenience, but we think it's better in the long run. The content on this forum will remain online.

‹ Previous Topic | Next Topic ›

Pages: 1 2

MySQL, Processing (Read 4562 times)

katy

MySQL, Processing
Mar 26^th, 2009, 12:48am

Hello

I'm hoping someone can ..help..point me in the right direction. I'll try keep it short.

I've got a database (with information being input via a PHP form) to store text and Processing to output text (using SQLibrary Florian Jenett ).

Basically what i would like to know is how do i get the information that i am grabbing from my database to update while processing is running. RIght now i am just hitting the run button so that it 'getString' from the database every time.

Hope that makes sense.

Many thanks
K

fjen

Re: MySQL, Processing
Reply #1 - Mar 26^th, 2009, 7:12am

you need to check if it updated from time to time. have a look here:

http://processing.org/discourse/yabb_beta/YaBB.cgi?board=Syntax;action=display;num=1237210485;start=10#10

the idea is to have a timestamp attached to each entry. when you read data keep the highest timestamp (latest addition). then from time to time check if there are entries in the database with later timestamps ...

F

Re: MySQL, Processing
Reply #2 - Mar 29^th, 2009, 6:02am

Hi, okay so this what i have is...

Quote:

int last_timestamp;

mysql.query( "SELECT *, date_format(date, '%H:%i') as the_time FROM " + table);

my setup section
------------------

Messages current_message;

if mysql.connect())
{
mysql.query( "SELECT *, date_format(date, '%H:%i') as time FROM " + table);
while(mysql.next())
{
int new_timestamp = mysql.getInt("time");
int last_tiimestamp = mysql.getInt("time");
if (last_timestamp <=new_timestamp);
last_timestamp = new_timestamp;
}

println(last_timestamp); // only gives me the hour ...how do i format correcly?

I'm sure my logic is wrong...be easy im a beginner..

thank you

fjen

Re: MySQL, Processing
Reply #3 - Mar 29^th, 2009, 8:13am

you are creating a local variable "last_timestamp" with the same name as the global one. you need to remove the datatype ("int").

i did a little test and this works for me:

Code:


import de.bezier.data.sql.*;

/*
CREATE TABLE `test` (
  `id` int(10) NOT NULL auto_increment,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
)
*/

MySQL ms;
java.sql.Timestamp last_ts;

String database = "ddd", user = "", pass = "";

void setup ()
{
    ms = new MySQL( this, "localhost", database, user, pass );
    last_ts = new java.sql.Timestamp( 0 ); 

    if ( ms.connect() )
    {
	  ms.query( "SELECT * FROM test ORDER BY time ASC LIMIT 1" );
	  ms.next();
	  last_ts = ms.getTimestamp("time");
    }
}

void draw ()
{
    if ( ms.connect() )
    {
	  ms.query( "SELECT * FROM test WHERE time > '"+last_ts+"' ORDER BY time ASC" );
	  while( ms.next() )
	  {
		println( ms.getInt( "id" ) );
		last_ts = ms.getTimestamp( "time" );
	  }
    }
}

let me know how it goes ..
F

fjen God Member Offline Posts: 965	Re: MySQL, Processing Reply #4 - Mar 29^th, 2009, 8:18am oh, and i should have mentioned that in my code the very first entry is not processed ( the one i load in setup to get the earliest timestamp ). F

k Ex Member	Re: MySQL, Processing Reply #5 - Mar 29^th, 2009, 4:06pm hi, i got it working (and ill post some code later..) Another thing is...how do i get it to update less frequently...say every 50 secs?

fjen

Re: MySQL, Processing
Reply #6 - Mar 29^th, 2009, 10:57pm

if it does not need to be a perfect timing the easiest way is to count frames.

Code:

if ( frameCount % 60 == 0 ) {
   // every 60th frame, that is about a second when you are running 60 fps
   println( "now " + frameRate );
}

a little harder is exact timing .. it's done the same way as we did for the queries: you have to keep a timestamp.

Code:

int lastTime;

void setup ()
{
    lastTime = millis();
}

void draw ()
{
   if ( millis() - lastTime > 1000 )
   {
	  // again, every second
	  println( "now" );
	  lastTime = millis();
   } 
}

k Ex Member	Re: MySQL, Processing Reply #7 - Apr 1^st, 2009, 6:37am Another little problem is i have the sql query as mysql.query(" SELECT message, date_format(time, '%H:%i') as time FROM " + table); but i get an error Quote: SQL.getTimestamp(): java.sql.SQLException. java.sql.SQLException: Cannot convert value '12:31' from column 2 to TIMESTAMP. etc Thanks ...again K

fjen

Re: MySQL, Processing
Reply #8 - Apr 1^st, 2009, 9:32am

you should learn some SQL

it says whatever is in that column of the entry can't be converted to a timestamp, so what is in there? is that really a timestamp? i mean type "timestamp"? can you post a dump (SELECT *) of that entry?

how does your table schema look like (DESCRIBE your_table_name_here)? this is what i used for the example above.

Code:


+-------+-----------+------+-----+-------------------+----------------+
| Field | Type	| Null | Key | Default	     | Extra	    |
+-------+-----------+------+-----+-------------------+----------------+
| id    | int(10)   |	| PRI | NULL		  | auto_increment |
| time  | timestamp | YES  |     | CURRENT_TIMESTAMP |		    |
+-------+-----------+------+-----+-------------------+----------------+

Re: MySQL, Processing
Reply #9 - Apr 1^st, 2009, 2:54pm

its a php time thing im using

$date2=date('c');

this gets inserted in my DB

$query="insert into table_name values ('".NULL."','".$message."','".$email."','".$date2."')";

i have 4 columns, the time column one is type datetime...

FIELD TYPE NULL KEY DEFAULT EXTRA
id   int(7) NO MUL null auto_increment
message text    YES null
email text    YES null
time    datetime YES    null

..thanks Cheesy

fjen

Re: MySQL, Processing
Reply #10 - Apr 1^st, 2009, 10:59pm

ok then change that to be a mysql timestamp and it should work.

ALTER TABLE your_table_name_here MODIFY 'time' timestamp NOT NULL default CURRENT_TIMESTAMP

or you need to have the php date in the same format as mysql timestamp. the exact format depends on your db i think.

or you change the db table column to be an int and work with the php time() instead. that's how most content management systems do it since it's faster on the db (only sees the int) and can be passed in and out without conversion.

F

« Last Edit: Apr 2^nd, 2009, 1:47am by fjen »

k Ex Member	Re: MySQL, Processing Reply #11 - Apr 2^nd, 2009, 12:56am changing it to the mysql timestamp what do i change this line to? mysql.query(" SELECT message, date_format(time, '%H:%i') as time FROM " + table); thnks k

fjen God Member Offline Posts: 965	Re: MySQL, Processing Reply #12 - Apr 2^nd, 2009, 1:48am mysql.query("SELECT message, time FROM " + table); i guess .. or do you need time to be in a specific format?

Re: MySQL, Processing
Reply #13 - Apr 2^nd, 2009, 5:47am

yeh i need to have it formatted to say the .. 16.30pm

or even better would be 10 mins ago, 20 mins etc..

but ill settle for the first Wink

right now i have..

mysql.query(" SELECT message, date_format(time, '%H:%i') as time FROM " + table + " ORDER BY time");

i've changed DB to...
id int(7) NO MUL null auto_increment
prayer text YES null
time timestamp NO CURRENT_TIMESTAMP

but im still getting the errors

k

fjen God Member Offline Posts: 965	Re: MySQL, Processing Reply #14 - Apr 2^nd, 2009, 7:37am and "the errors" are?

Pages: 1 2

‹ Previous Topic | Next Topic ›