C++ / Sqlite questions

António Almeida 1

I've created a Visual Studio C++ application for retrieving results from a Sqlite database.

As, on getting those results, I was receiving wrong accented characters, I sucessfully changed that part of my code to static_cast<const wchar_t*>(sqlite3_column_text16(stmt, 1));

But now, if I update a record, from the C++ application, I get again wrong accented characters - I think because Sqlite only accepts UTF8 encoding.

How can I convert the records containing those characters and send to database in order to get them acceptable by Sqlite ?

Second question

Trying to execute multiple statements in one Sqlite query, separated by ";", it only executes the first and second (create a table and insert records from another), ignoring the last two (drop a table and renaming another)

What am I doing wrong ?

Thanks in advance

Flaviu_ 1,011 Reputation points

2024-07-28T15:00:02.12+00:00

"Trying to execute multiple statements in one Sqlite query, separated by ";""

If first two statements are accepted, then other should be accepted. Pay attention to semicolon. If not working, put here your queries.

"Sqlite only accepts UTF8 encoding"

Are you sure? How did you get this?
António Almeida 1 Reputation point

2024-07-29T00:56:19.0833333+00:00

This is my multi query :

querySQL = "CREATE TABLE temp (n_ord INTEGER NOT NULL, dest_ TEXT DEFAULT NULL, inov_ INTEGER DEFAULT NULL, varied_ INTEGER DEFAULT NULL, monum_ INTEGER DEFAULT NULL, paisag_ INTEGER DEFAULT NULL, oport_ INTEGER DEFAULT NULL, clima INTEGER DEFAULT NULL, viag_ INTEGER DEFAULT NULL, instal_ INTEGER DEFAULT NULL, custo INTEGER DEFAULT NULL, total GENERATED ALWAYS AS (inov_ + varied_ + monum_ + paisag_ + oport_ + clima + viag_ + instal_ + custo) VIRTUAL, PRIMARY KEY(n_ord)); INSERT INTO temp (dest_, inov_, varied_, monum_, paisag_, oport_, clima, viag_, instal_, custo) SELECT dest_, inov_, varied_, monum_, paisag_, oport_, clima, viag_, instal_, custo FROM rank; DROP TABLE rank; ALTER TABLE temp RENAME TO rank";

I say Sqlite only accepts UTF8 encoding, because, when inserted value containing accented characters, Sqlite creates a blob and returns wrong characters at concerning field.

So i would like to know what to do in order to get those characters correctly displayed.
Minxin Yu 12,086 Reputation points Microsoft Vendor

2024-07-29T06:21:42.8133333+00:00

Since SQLite is a third-party product, it is recommended that you post the problem on the forums that support it for help.
Flaviu_ 1,011 Reputation points

2024-07-29T06:27:59.0466667+00:00

See here: https://www.sqlite.org/datatype3.html

You can setup econding as you want.
Flaviu_ 1,011 Reputation points

2024-07-29T06:30:20.7033333+00:00

This is my multi query :

Try to execute one by one to see how it works. During your development you can use something like:

https://sqlitebrowser.org

Or:

https://www.codeproject.com/Articles/5308400/Database-Explorer
Flaviu_ 1,011 Reputation points

2024-07-30T07:27:15.5966667+00:00

@Antonio Hey, did you solve the problem? Can I help you?
António Almeida 1 Reputation point

2024-07-30T09:31:10.3066667+00:00

I did not solve any of problems - so. I will use Mysql, instead of Sqlite, because it works better with C++.

One last question :

How can I use a multi column CListbox for retrieving records from database ?

An example would be great

Thank you
Flaviu_ 1,011 Reputation points

2024-07-31T13:45:56.4+00:00

because it works better with C++

C++ works fine with any kind of database. Have you seen Database-Explorer from Codeproject? The same C++ code works with several database types.

How can I use a multi column CListbox

That's a different task, you should know how to populate a CListCtrl with any kind of data. See here how to do it: https://www.codeproject.com/Articles/608/Using-the-List-Control

use Mysql

If you send me an email to flaviu2@yahoo.com then I'll send you a sample project.

But I advice you to use sqlite, can work the same like MySQL, and see here C++ libraries that works woth SQLite: https://github.com/SqliteModernCpp/sqlite_modern_cpp
https://github.com/SRombauts/SQLiteCpp.git
https://github.com/fnc12/sqlite_orm.git
RLWA32 45,701 Reputation points

2024-07-31T15:49:59.4166667+00:00

I'm not an sqlite user but the sqlite documentation at Database File Format makes it pretty clear that the database text encoding can be any of UTF-8, UTF-16LE or UTF-16BE.

1 answer

Flaviu_ 1,011

I put here a SQLite example that uses https://github.com/SqliteModernCpp/sqlite_modern_cpp library to populate a CListCtrl, as you wanted.

#include <sqlite_modern_cpp.h>
CListCtrl listCtrl;
// set listCtrl as detail mode and setup 3 columns
try {
	sqlite::database db("c:/temp/test.db3", config);
	for (auto&& row : db << "select age,name,weight from user")
	{
		int age; std::string name; double weight;
		row >> age >> name >> weight;
		const auto count = listCtrl.GetItemCount();
		listCtrl.Insert(count, name.c_cstr());
		listCtrl.SetItemText(count, 1, std::to_string(age));
		listCtrl.SetItemText(count, 2, std::to_string(weight));
	}
}
catch (const std::exception& e)
{
	std::cout << e.what() << std::endl;
}

Share via

C++ / Sqlite questions

1 answer

Your answer