Update element of a SQL database

Question

1.00/5 (2 votes)

See more:

I tried my query in SQLite Expert Personal and it's correct, but if I write query in mfc c++ my database isn't update..

I wrote:

in .h:

Kompex::SQLiteDatabase      *m_pDB;
Kompex::SQLiteStatement     *m_pStmt;

C++

m_pDB = new Kompex::SQLiteDatabase(_T("Mat.db3"));		
m_pStmt = new Kompex::SQLiteStatement(m_pDB);	

        CString strQuery = _T("UPDATE Materiali SET mat_desc = 'lllllll' WHERE mat_codart =' 020 100/5';"); 

		CStringA strA_Query(strQuery);

		const char* c_Query(strA_Query);

		m_pStmt->Sql(c_Query);
		

		m_pStmt->FreeQuery();

What I have tried:

I tried to write this, but my database isn't update

Posted 15-Apr-24 5:47am

Member 14594285

Updated 15-Apr-24 21:42pm

v7

Add a Solution

Comments

RedDk 15-Apr-24 11:53am

Here's a wrapper on GITHUB. There are a couple of .sln files. Try one of these before you do anything else. When you get those, manage to convert your present problem.

https://github.com/zelloptt/kompex-sqlite-wrapper

0x01AA 15-Apr-24 14:11pm

I don't recognize an SQL statement .... ?

2 solutions

Solution 1

Your code doesn't include any actual SQL query, much less an UPDATE or INSERT query with actual data for the DB to accept.

Go back to your working code, and look more closely at exactly what is going on: the code you show us creates an empty CString, creates a second CString from the empty string, converts that to an (empty) char pointer and uses that to build an (empty) SQL command.

And check carefully: SQLite doesn't support multiple commands on a single query, so isn't that prone to SQL INjection. But SQL Server does, and is very exposed to it. Always use parameterised queries unless you really like restoring your DB from the latest back up!

Posted 15-Apr-24 19:23pm

OriginalGriff

Comments

Member 14594285 16-Apr-24 2:52am

I improve my question

OriginalGriff 16-Apr-24 3:33am

Why edit out the actual query? That's the bit that does the work, and it's pretty much vital to determining if there is a problem ...

Member 14594285 16-Apr-24 3:42am

I improved my question

OriginalGriff 16-Apr-24 4:11am

Seriously, don't do it like that. Always use parameterised queries: google "bobby tables" and don't assume it's just a joke.

Secondly, you know that isn't what your code looks like: that's a fixed string, which for an UPDATE query is very unlikely to be useful. You need to check if that is exactly what you are using - because a tiny difference in an SQL command can have a big effect. FOr example, is the WHERE clause actually using a leading space? Does the DB also include that leading space?

Thirdly, how are you checking that your DB is not being updated? If that was your actual query, then every matching field will be changed, so I suspect that either that code isn't getting executed, the DB doesn't contain what you assume it does, or that query isn't what you actually used.

We can't check any of that: we have no access to yoru code while running, or to your DB. So use the debugger, and look at exactly what is going on!

Member 14594285 16-Apr-24 4:14am

ok..but I would like to know if it's right this piece of code to execute a query:

m_pStmt->Sql(c_Query);

m_pStmt->FreeQuery();

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2024-04-15T22:32:00

Solution 2

Looking at your code - you are using Sql which is expecting you to use a parameterised query (as far as I can tell). In your particular case here, using hardcoded values, you should be using SqlStatement instead. As others point out though, you should be looking at a parameterised statement, you probably want something like this

C++

m_pStmt->Sql("UPDATE Materiali SET mat_desc = @desc WHERE mat_codart =@codart");

m_pStmt->BindString(1, "lllllll");      
m_pStmt->BindString(2, " 020 100/5");
 
// execute it and clean-up
m_pStmt->ExecuteAndFree();

Note that we have to execute the statement for it to take effect. If you don't execute it, you've just written a SQL statement that does nothing.

Posted 15-Apr-24 22:32pm

Pete O'Hanlon

Updated 15-Apr-24 23:07pm

v3

Comments

Richard Deeming 16-Apr-24 4:39am

Except you wouldn't want the quotes around the parameter names within the query. :)

Pete O'Hanlon 16-Apr-24 5:06am

That's true - I didn't take as much care copying as I should. I'll correct this.

Member 14594285 16-Apr-24 4:39am

it's the same thing..database isn't update..

Member 14594285 16-Apr-24 4:44am

I wrote:

m_pStmt->Sql(_T("UPDATE Materiali SET mat_desc = 'prova_vale1' WHERE mat_codart =' 020 100/5';"));

// m_pStmt->BindString(1, "prova_vale");
//m_pStmt->BindString(2, " 020 100/5");

// execute it and clean-up
m_pStmt->ExecuteAndFree();

and it works..thank you very much

Pete O'Hanlon 16-Apr-24 5:08am

Glad you got it working.