Help with MySQL Query.

Tar.Aldarion

I'm trying to set a boolean field to true when the current time in the timestamp field is the past 5 seconds or newer.
If the timestamp is greater than 5 seconds then the field should be set to false.

The query I have so far is

UPDATE `active` `ActiveDevOwn` = '1' WHERE `ActiveTime` <= NOW( ) -5

or else ActiveDevOwn should be 0, this gives a syntax error, anybody know the syntax for this?

Tom Dunne

Shouldn't that be

UPDATE `active`SET `ActiveDevOwn` = '1' WHERE `ActiveTime` <= NOW( ) -5

seamus

More readable code might work as such:

UPDATE `active` SET `ActiveDevOwn` = '1' WHERE `ActiveTime` <= DATE_SUB(NOW( ), INTERVAL 5 SECOND)

Apparently doing arithmetic with DATETIME data types results in a double (or an int, depending on the version of MySQL), so I don't know if NOW() - 5 will work as intended.

Tar.Aldarion

/duh
Thanks.


UPDATE `active`SET `ActiveDevOwn` = '1' WHERE `ActiveTime` >= NOW( ) -5; is the right one.

If there are further problems I'll use your way Seamus, thanks.

Tar.Aldarion

Bah, more problems. I am doing these SQL queries through C/C++ and I can either have a select statement last and make the program kindo of work(every timestamp is updated instead of just the right one) of put the select statement in the right place and get an error.

...
//MySQL stuff goes here!
		/* Make the query */
		//51 is the length of the query without digits
		char *query = (char *) malloc(60 + strlen(table) + strlen(table_ZN) + 3 + strlen(table_SN) + 8); 
		sprintf(query,"UPDATE `%s` SET `%s` = '%d', `%s`=NOW() WHERE `%s`='%s';", table, table_ZN, packet_zone, table_time, table_SN, packet_sn);
		

		/* send SQL query */
		if (mysql_query(conn, query)) {
			char *message="MySQL query failed\n";
			printf("%s\n\n%s", message,mysql_error(conn));
			retval = sendto(msgsock, message, strlen(message), 0, (struct sockaddr *)&from, fromlen);
			continue;
		}

		free(query);


		query = (char *) malloc(1000 + packet_snlength + strlen(table) + strlen(table_SN)); 
		sprintf(query,"SELECT * FROM `%s` WHERE `%s` = '%s';", table, table_SN, packet_sn);

		/* send SQL query */
		if (mysql_query(conn, query)) {
			char *message="MySQL query failed\n";
			printf("%s\n\n%s", message,mysql_error(conn));
			retval = sendto(msgsock, message, strlen(message), 0, (struct sockaddr *)&from, fromlen);
			continue;
		}
         free(query); 
		res = mysql_use_result(conn);



        query = (char *) malloc(100 + packet_snlength + strlen(table) + strlen(table_SN)); 
		sprintf(query,"UPDATE `%s` SET `%s` = '0' WHERE 1;", table, table_actdevown);
		
		
			/* send SQL query */
		if (mysql_query(conn, query)) {
			char *message="MySQL query failed\n";
			printf("%s\n\n%s", message,mysql_error(conn));
			retval = sendto(msgsock, message, strlen(message), 0, (struct sockaddr *)&from, fromlen);
			continue;
		}
         free(query); 


        query = (char *) malloc(1000 + packet_snlength + strlen(table) + strlen(table_SN)); 
		sprintf(query,"UPDATE `%s` SET `%s` = '1' WHERE `%s` >= NOW( ) -5;",table, table_actdevown, table_time);
		
			/* send SQL query */
		if (mysql_query(conn, query)) {
			char *message="MySQL query failed\n";
			printf("%s\n\n%s", message,mysql_error(conn));
			retval = sendto(msgsock, message, strlen(message), 0, (struct sockaddr *)&from, fromlen);
			continue;
		}
         free(query); 


		/* output fields 1 and 2 of each row */
		if (row = mysql_fetch_row(res))
			printf("SELECT Received: %s, %s, %s, %s, %s,\n", row[0], row[1], row[2], row[3], row[4]);
		else {
			//Tell the user it failed
			char *message = "ID is not in database or server error occured \n";
			retval = sendto(msgsock, message, strlen(message), 0, (struct sockaddr *)&from, fromlen);
			continue; 
		}
		



	   //Now send the mysql data back to the client

		printf("Server: Received %d bytes, data \"%s\" from client\n", retval, Buffer);
 
        printf("Server: Echoing the relevent data back to client...\n");

		char *clientstring = (char *) malloc(strlen(row[0]) + strlen(row[1]) + strlen(row[4]) + 4);
		sprintf(clientstring, "%s,%s,%s", row[0], row[1], row[4]);

		/* Release memory used to store results*/
		mysql_free_result(res);

		//Send back "message received"
        retval = sendto(msgsock, clientstring, strlen(clientstring), 0, (struct sockaddr *)&from, fromlen);
       
		free(clientstring);

...

If I run a select before an update I get.
'Commands out of sync; you can't run this command now

http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

I don't really know why it not be working, trying to follow the link.

Tar.Aldarion

I forgot to say what I want.
I want to select a DeviceSN and only update the fields in that. However when i runa select and then an update I get the error mentioned.
I had something like this working before so it is very annoying...

Tar.Aldarion

Got this working it was some other error in the database settings making it not work.