Can't update or insert into database

[Rust]

Hey.

For some reason the queries my program runs have no effect on the database. Simple select queries work just fine, but insert and updates don't work. I've tried to follow the qt 5.6 guide as much as possible.

These are my current qt queries:

UPDATE

qry->prepare("UPDATE clientes SET nome = :in_nome_cliente, morada = :in_morada, cod_postal = :in_cod_postal WHERE num_cliente = :in_num_cliente");
qry->bindValue(":in_nome_cliente", _NomeCliente);
qry->bindValue("in_morada_cliente", _MoradaCliente);
qry->bindValue(":in_cod_postal", _CodigoPostal);

qry->exec();

INSERT

qry->prepare("INSERT INTO clientes (num_cliente, nome, morada, pontos, cod_postal) VALUES (NULL, :in_nome_cliente, :in_morada_cliente, 0, :in_cod_postal)");
qry->bindValue(":in_nome_cliente", _NomeCliente);
qry->bindValue("in_morada_cliente", _MoradaCliente);
qry->bindValue(":in_cod_postal", _CodigoPostal);

qry->exec(); //execute query

Can someone kindly point me what i'm doing wrong?

[kshegunov]

@Rust
Hello,
Your code looks pretty okay. Are you sure you have permissions to insert/update for that table? Could you check that perhaps (you don't seem to make the check if your query has run properly).
Additionally:

qry->bindValue("in_morada_cliente", _MoradaCliente);

There's no : for the binding name here, is this a typo?

And finally, you could simplify your life if you retrieve the errors the sql driver returns when your query fails to execute, it might also give you some insight why, e.g.:

if (!qry->exec())  {
    // The query failed
    qDebug() << qry->lastError().text() << endl;
}

Kind regards.

[Rust]

@kshegunov

Thank you, kshegunov, for the great tips! I've added the debugging parts to my code and fixed the bindValue parts too, silly me didn't spot that.

Noticed that over the terminal output it never reaches the point to output the status of the sql query, seems like the function breaks before it reaches that point, that could be the reason why it's never commiting the changes.

Before the query is executed i call upon another window to ask for user confirmation they want to update the database values, as such:

greenlight greenlightWindow;
greenlightWindow.setFlag(&greenlightFlag);
greenlightWindow.exec();

After the i call exec() the previous windows from which this greenlight subwindow is called closes, could it be that it's being terminated as well as all its execution?

UPDATE...
Okay i tried to change my permissions to the following:

GRANT ALL PRIVILEDGES ON loja_tech TO '%'@'%' IDENTIFIED BY '%';

query completed, but still can't manipulate database. Am i granting permissions the right way?

[kshegunov]

@Rust said:

After the i call exec() the previous windows from which this greenlight subwindow is called closes, could it be that it's being terminated as well as all its execution?

This shouldn't ordinarily happen. Are you running you program in debug mode, try adding a breakpoint before and after you show that window. Make sure all the variables are in good condition (i.e. you don't call methods on NULL pointers and things like that).

Kind regards.

[Rust]

@kshegunov

I've tried debugging, but unfortunately for me, the debugging session hangs at start up, unsure as to whether this is a misconfigured kit.

[kshegunov]

@Rust said:

I've tried debugging, but unfortunately for me, the debugging session hangs at start up, unsure if this is a misconfigured kit.

I don't know, it may be, although the possibility is a remote one. You should really fix it, though. Without debugger it'd be hell to track down practically any error. What OS are you using?

[Rust]

@kshegunov

Windows 10. SQL server runs on a virtual Linux machine locally. I already had issues using the standalone Windows MySQL server, refusing to open a port to local connections.

Windows 10 is still a very buggy OS.

I've switched to MinGW compiler, i was using MSCV to which Qt creator was unable to predefine a debugger for, with MingGW it's already there configured.

[kshegunov]

@Rust
Usually MinGW runs out of the box. Perhaps some antivirus/antimalware software is interfering and preventing it from running properly? False positives happen a lot with debuggers. The SQL server and its location is of no consequence in this case.

[Rust]

@kshegunov

Attempted inserting values in another part of the program and it worked just fine. Apparently it's an issue with the rest of the function i wrote, i guess i have alot of stuff to review on my code xd

Thank you for the help!

UPDATE

Nevermind, my issues were mistypings throughout the sql query syntax and bindValues.