wxSQLite3 Insert Into Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
wxProgrammer
Experienced Solver
Experienced Solver
Posts: 96
Joined: Thu Apr 17, 2014 10:10 am

wxSQLite3 Insert Into

Post by wxProgrammer »

Hi!

Code: Select all

int GLOBAL::SaveAnalysis(cAnalysis* analysis)
{
	if (!analysis) return 0;
		
	wxString cmd  = wxT("insert into analysis (id, name, analysis, care, customerid, delivery) values (");
				cmd << analysis -> GetId()						 << wxT(", ");
				cmd << analysis -> Get(ANALYSIS_FIELDS_NAME)	 << wxT(", ");
				cmd << analysis -> Get(ANALYSIS_FIELDS_ANALYSIS) << wxT(", ");
				cmd << analysis -> Get(ANALYSIS_FIELDS_CARE)     << wxT(", ");
				cmd << analysis -> Get(ANALYSIS_FIELDS_CUSTOMER) << wxT(", ");
				cmd << analysis -> Get(ANALYSIS_FIELDS_DELIVERY) << wxT(");");
	
	int t = 0;
	try
	{
		t = mainDB -> ExecuteUpdate(cmd);
	}
	catch (wxSQLite3Exception& e)
	{
		wxMessageBox(wxString(wxT("ERRORE DURANTE IL SALVATAGGIO NEL DATABASE\n")) << cmd << wxT("\n") << e.GetErrorCode() << wxT(": ") << e.GetMessage(), wxT("ERRORE"), wxICON_ERROR);
	}

	return t;
}
(GetId return an int, Get return a wxString)

The code work but the query don't go and this is the output:

ERRORE DURANTE IL SALVATAGGIO NEL DATABASE
insert into analysis(id, name, analysis, care, customerid, delivery) values (0, test, analisi, cura, 0, consegna);
1: SQL logic error or missing database[1]: no such column: test

This is the creation:

Code: Select all

try
	{
		wxSQLite3Database::InitializeSQLite();
		
		GLOBAL::mainDB = new wxSQLite3Database();
		MAIN_DB -> Open(GLOBAL::mainPATH);

		if (!MAIN_DB -> TableExists(wxT("preferences")))
			MAIN_DB -> ExecuteUpdate(wxT("create table preferences(field char(20), value char(20));"));

		if (!MAIN_DB -> TableExists(wxT("analysis")))
			MAIN_DB -> ExecuteUpdate(wxT("create table analysis(id int primary key, name char(30), analysis text, care text, customerid int, delivery text);"));

		if (!MAIN_DB -> TableExists(wxT("customer")))
			MAIN_DB -> ExecuteUpdate(wxT("create table customer(id int primary key, fullname char(30), addres char(100), fiscalcode char(20), mail char(30));"));
	}
	catch (wxSQLite3Exception& e)
	{
		wxMessageBox(wxString(wxT("ERRORE NELL'INIZIALIZZAZIONE DEL DATABASE\n")) << e.GetErrorCode() << wxT(": ") << e.GetMessage(), wxT("ERRORE"), wxICON_ERROR);
	}
(If I write only "insert into values(val)" (without columns), don't work)
I'm Italian but we can speak C++ :)
User avatar
doublemax
Moderator
Moderator
Posts: 19116
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: wxSQLite3 Insert Into

Post by doublemax »

Strings need to be enclosed in quotes. Also certain characters need to be escaped (like ' or %). Alternatively check the sample that comes with wxSQLite3 for alternative way to update a table. E.g. PrepareStatement/Bind/ExecuteUpdate
Use the source, Luke!
User avatar
xaviou
Super wx Problem Solver
Super wx Problem Solver
Posts: 437
Joined: Mon Aug 21, 2006 3:18 pm
Location: Annecy - France
Contact:

Re: wxSQLite3 Insert Into

Post by xaviou »

Hi

You should try "quoting" your entries :
wxProgrammer wrote:Hi!

Code: Select all

int GLOBAL::SaveAnalysis(cAnalysis* analysis)
{
	if (!analysis) return 0;
		
	wxString cmd  = wxT("insert into analysis (id, name, analysis, care, customerid, delivery) values (");
				cmd << wxT("\"") << analysis -> GetId()						 << wxT("\", ");
				cmd << wxT("\"") << analysis -> Get(ANALYSIS_FIELDS_NAME)	 << wxT("\", ");
				cmd << wxT("\"") << analysis -> Get(ANALYSIS_FIELDS_ANALYSIS) << wxT("\", ");
				cmd << wxT("\"") << analysis -> Get(ANALYSIS_FIELDS_CARE)     << wxT("\", ");
				cmd << wxT("\"") << analysis -> Get(ANALYSIS_FIELDS_CUSTOMER) << wxT("\", ");
				cmd << wxT("\"") << analysis -> Get(ANALYSIS_FIELDS_DELIVERY) << wxT("\");");
	
.........
Regards
Xav'
My wxWidgets stuff web page : X@v's wxStuff
wxProgrammer
Experienced Solver
Experienced Solver
Posts: 96
Joined: Thu Apr 17, 2014 10:10 am

Re: wxSQLite3 Insert Into

Post by wxProgrammer »

Thank you :)
There is a method for auto-escape? (In this case is only alphabetic characters and numbers (converted in strings))
I'm Italian but we can speak C++ :)
User avatar
doublemax
Moderator
Moderator
Posts: 19116
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: wxSQLite3 Insert Into

Post by doublemax »

wxProgrammer wrote:Thank you :)
There is a method for auto-escape? (In this case is only alphabetic characters and numbers (converted in strings))
Please read the wxSQLite documentation and the sample.

Code: Select all

    // SQLite3's printf() functionality. Handles embedded quotes and NULLs

    cout << endl << "SQLite sprintf test" << endl;
    wxSQLite3StatementBuffer bufSQL;
    bufSQL.Format("insert into emp (empname) values (%Q);", "He's bad");
    cout << (const char*)bufSQL << endl;
    db.ExecuteUpdate(bufSQL);
Use the source, Luke!
wxProgrammer
Experienced Solver
Experienced Solver
Posts: 96
Joined: Thu Apr 17, 2014 10:10 am

Re: wxSQLite3 Insert Into

Post by wxProgrammer »

Thank you :) I'll use it. Now work :)
I'm Italian but we can speak C++ :)
wxProgrammer
Experienced Solver
Experienced Solver
Posts: 96
Joined: Thu Apr 17, 2014 10:10 am

Re: wxSQLite3 Insert Into

Post by wxProgrammer »

Excuse me: How can I convert wxSQLite3StatementBuffer to wchar_t*? I could print it with wxMessageBox but I have builded wxWidgets in Unicode and wxSQLite3StatementBuffer have only const char* conversion :\
I'm Italian but we can speak C++ :)
User avatar
doublemax
Moderator
Moderator
Posts: 19116
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: wxSQLite3 Insert Into

Post by doublemax »

This should work (untested):

Code: Select all

wxMessageBox( wxString::FromUTF8((const char*)bufSQL) );
Use the source, Luke!
wxProgrammer
Experienced Solver
Experienced Solver
Posts: 96
Joined: Thu Apr 17, 2014 10:10 am

Re: wxSQLite3 Insert Into

Post by wxProgrammer »

Oh, thank you, work! wxWidgets have practically all! String, Thread, Socket, Plugin, CommandProcessor, GUI, ...all :D
I'm Italian but we can speak C++ :)
Post Reply