C++ mysql UPDATE command

[Silvershaft]

Hey! I am making a program that keeps track of users so the program has to update the number in the database how do I actually do it? I know how to select things and such, but have no idea how the update works.. I didn't really find anything for c++ in google :S. Thanks in advance.

[Excizted]

Take a look at this example MySQL query

UPDATE my_table SET password = '111111' WHERE id = '5'

or

UPDATE my_table SET point = point+1000 WHERE rank > '0'

I hope this answers your question :)

[Silvershaft]

I got only one entry and one column in my table so would I do like this

stmt = con->createStatement();
res = stmt->executeQuery("UPDATE users SET usersactive = usersactive+1");

This isn't working.. What do I do wrong

[Excizted]

It should work. Are you sure you are connected to your database with a user allowed to manipulate users?

If you retrieve the error message, it would be easier to determine :)

[Silvershaft]

Error code is 0 somehow when I try that.

[Excizted]

That's supposed to mean everything went ok, afaik :)

Maybe if you try not incrementing, instead set the value to something? Just to make sure we can do something.

res = stmt->executeQuery("UPDATE users SET usersactive = '10'");

[Silvershaft]

It results to same <MySQL error code: 0, SQLState: 00000> it actually worked but I get error still xd Dunno

[tesuji]

I got only one entry and one column in my table so would I do like this

stmt = con->createStatement();
res = stmt->executeQuery("UPDATE users SET usersactive = usersactive+1");

This isn't working.. What do I do wrong

Maybe this is finally missing:

res = stmt->executeQuery("COMMIT");

It's always necessary after a sequence of INSERT, UPDATE, or DELETE statements if your database is configured with "set autocommit off;".

-- tesu

[Silvershaft]

Nope, the value get's changed in the database, but I still get error I read that this <MySQL error code: 0, SQLState: 00000> means that the operation succeeded so what's the point to throw expection about it and how should I handle it?

[Excizted]

Sounds strange. You could just ignore the exception, but that would not be too correct.
I really don't know, haven't used the C++ MySQL library before, only in C, Ruby and PHP.

[tesuji]

Please, submit the complete method where these both statement

>> stmt = con->createStatement();
>> res = stmt->executeQuery("UPDATE users SET usersactive = usersactive+1");

be executed and the exception be thrown.

-- tesu

[Silvershaft]

How I actually can ignore it?

and here is method

void startChecking(int interval)
{
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;
		int inter = interval;
		int timesChecked = 0;
		bool opened = false;

	try {

		 /* Create a connection */
		driver = get_driver_instance();
		string host = "tcp://127.0.0.1:3306";
		con = driver->connect(host, "root", "asdasdasdadasdad");
		/* Connect to the MySQL test database */
		con->setSchema("lockerz");
		stmt = con->createStatement();
		res = stmt->executeQuery("SELECT usersactive FROM users");
		res->next();
		int usersactive2 = res->getInt("usersactive");
		stmt = con->createStatement();
		res = stmt->executeQuery("UPDATE users SET usersactive = '10' WHERE id='1'");


		while(!IsKeyPressed(VK_ESCAPE) )
		{

			stmt = con->createStatement();
			res = stmt->executeQuery("SELECT isRedeemOn FROM isredeemon");
			res->next();

			if (res->getInt("isRedeemOn") == 0)
			{
				system("cls");
				cout << "...... " << inter << " seconds..\n";
				cout << "......" << timesChecked << "\n";
			}
			else
			{
				system("cls");
				cout << "....... " << inter << " seconds..\n";
				cout << "........\n";
				if ( !opened )
					ShellExecute(NULL, "open", "........", NULL, NULL, SW_SHOWNORMAL);
				opened = true;
				PlaySound("win.wav", 0, SND_LOOP);
			}

			timesChecked += 1;
			wait(inter);

		}

		delete res;
		delete stmt;
		con->close();
		delete con;

	}
	
catch (sql::SQLException &e) {

	int i = 10000;

	while(true)
	{
		if ( i == 10000)
		{
			cout << "ERROR: SQLException in " << __FILE__;
			cout << "ERROR: " << e.what();
			cout << " (MySQL error code: " << e.getErrorCode();
			cout << ", SQLState: " << e.getSQLState() << ")" << endl;
			i = 0;
		}
	}

}
}

[tesuji]

well, if in

>> while(!IsKeyPressed(VK_ESCAPE) )

IsKeyPressed functions as I am supposing, then your exception is NOT thrown by an sql mistake but thrown by for example most likely heap/stack overflow because

>> stmt = con->createStatement();
>> res = stmt->executeQuery("SELECT isRedeemOn FROM isredeemon");

will be executed as often as not.

Please test it by deleting the while loop.

btw, what do you want to do within the while-loop, accessing the rows and their column values?

-- tesu

[Silvershaft]

I still get the error if I delete the loop, this is so stupid, it does the query, but it still gives error seriously..

[tesuji]

sounds really strange.

btw, which line between line# 14 and 59 is actually responsible for this strange exception?

-- tesu

[Silvershaft]

The executequery where I do the update thing.

[tesuji]

The executequery where I do the update thing.

Ah sorry, I should have read your code more seriously. So the error happens there:

stmt = con->createStatement();
res = stmt->executeQuery("UPDATE users SET usersactive = '10' WHERE id='1'");

You redefine a prior stmt, what could lead to problems with its dynamically allocated structure.

Simply try the following:

newstmt = con->createStatement();
res = newstmt->executeQuery("UPDATE users SET usersactive = '10' WHERE id='1'");

If I remember correctly, you must use a new stmt if you didn't close the old stmt priorly because of some reentrance problem of mysql c++ connector.

Try it, what is the result?

-- tesu

[tesuji]

I am sure the exception around the update statement now disappeared. But there would be some problems within while loop too.

-- tesu

[Silvershaft]

So what do I actually do I did this:

sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::Statement *newstmt;
		sql::ResultSet *res;
		int inter = interval;
		int timesChecked = 0;
		bool opened = false;

	try {

		 /* Create a connection */
		driver = get_driver_instance();
		string host = "tcp://127.0.0.1:3306";
		con = driver->connect(host, "root", "asdasdadasd");
		/* Connect to the MySQL test database */
		con->setSchema("lockerz");

		stmt = con->createStatement();
		res = stmt->executeQuery("SELECT usersactive FROM users");
		res->next();
		int usersactive2 = res->getInt("usersactive");

		newstmt = con->createStatement();
		res = newstmt->executeQuery("UPDATE users SET usersactive = '10' WHERE id='1'");

Doesn't work with this

[tesuji]

Well, I am stumped now. I would have bet everything that redefining an already used stmt had caused that error. Now last try: delete stmt just before newstmt = con->createStatement();.

additional question: what is the current value of usersactive2? Is it acceptable?

To be more precise, what actually means: "Doesn't work with this" ? does it mean that the exception still occurs ?

-- tesu

[Silvershaft]

Nope didn't work but I actually solved it by trying:
stmt->executeUpdate("UPDATE users SET usersactive = '20' WHERE id='1'");

[tesuji]

Nope didn't work but I actually solved it by trying:
stmt->executeUpdate("UPDATE users SET usersactive = '20' WHERE id='1'");

WOW true! I have to apologize for not seeing the wood for the trees :-O

True, executeQuery is in the wrong place here! Maybe simple execute(.) would have also functioned correctly.

-- tesu

[Silvershaft]

Yep thanks anyway!