Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » sqlite and Execute
sqlite and Execute [message #25823] Sun, 14 March 2010 12:18 Go to next message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
Hello,

I followed the tutorial and I come to the following very simple code that should perform a query on an existing database:

#include <CtrlLib/CtrlLib.h>
using namespace Upp;

#define LAYOUTFILE <c16/c16.lay>
#include <CtrlCore/lay.h>
#include <plugin/sqlite3/Sqlite3.h>


class c16 : public Withc16Layout<TopWindow> {
public:
	void FillRankingTables();
	typedef c16 CLASSNAME;
	c16();
};

c16::c16()
{
    CtrlLayout(*this, "Window title");

    Sqlite3Session sqlite3;
    if(!sqlite3.Open(ConfigFile("c16.db"))) {
        Exclamation("Can't create or open database file");
        return;
    }

    SQL = sqlite3;
    SQL.Execute("insert into PUNTI_GIOCATORI (IDFSI,IDCIRCOLO,SIGLAPROV,SIGLAREG) select ID_FSI,IDCIRCOLO,SIGLAPROV,SIGLAREG from ISCRITTI");
}

void c16::FillRankingTables()
{
}

GUI_APP_MAIN
{	c16().Run();
}

No compile error (The .lay file contains a button and is irrilevant... I guess).

The relevant part is the SQL.Execute(...) statement that does nothing. If I copy the string inside Execute and paste into SQliteManager (a Firefox plugin) to run the query it is executed without problem and the table is filled with the right values.
Instead SQL.Execute does not perform the query. What am I missing?
It should be not related to a missing schema file.

Thank you.
Luigi

[Updated on: Sun, 14 March 2010 12:19]

Report message to a moderator

Re: sqlite and Execute [message #25850 is a reply to message #25823] Mon, 15 March 2010 20:41 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
forlano wrote on Sun, 14 March 2010 12:18

Hello,

I followed the tutorial and I come to the following very simple code that should perform a query on an existing database:

#include <CtrlLib/CtrlLib.h>
using namespace Upp;

#define LAYOUTFILE <c16/c16.lay>
#include <CtrlCore/lay.h>
#include <plugin/sqlite3/Sqlite3.h>


class c16 : public Withc16Layout<TopWindow> {
public:
	void FillRankingTables();
	typedef c16 CLASSNAME;
	c16();
};

c16::c16()
{
    CtrlLayout(*this, "Window title");

    Sqlite3Session sqlite3;
    if(!sqlite3.Open(ConfigFile("c16.db"))) {
        Exclamation("Can't create or open database file");
        return;
    }

    SQL = sqlite3;
    SQL.Execute("insert into PUNTI_GIOCATORI (IDFSI,IDCIRCOLO,SIGLAPROV,SIGLAREG) select ID_FSI,IDCIRCOLO,SIGLAPROV,SIGLAREG from ISCRITTI");
}

void c16::FillRankingTables()
{
}

GUI_APP_MAIN
{	c16().Run();
}

No compile error (The .lay file contains a button and is irrilevant... I guess).

The relevant part is the SQL.Execute(...) statement that does nothing. If I copy the string inside Execute and paste into SQliteManager (a Firefox plugin) to run the query it is executed without problem and the table is filled with the right values.
Instead SQL.Execute does not perform the query. What am I missing?
It should be not related to a missing schema file.

Thank you.
Luigi


I tried even this
SQL.Execute("INSERT INTO PUNTI_GIOCATORI (IDFSI,IDCIRCOLO,SIGLAPROV,SIGLAREG) SELECT ISCRITTI.ID_FSI,ISCRITTI.IDCIRCOLO,ISCRITTI.SIGLAPROV,ISCRITTI.SIGLAREG FROM ISCRITTI");


that is run without problem in sqlitemanager but gives the following error in Upp:

SQL* INSERT INTO PUNTI_GIOCATORI (IDFSI,IDCIRCOLO,SIGLAPROV,SIGLAREG) SELECT ISCRITTI.ID_FSI,ISCRITTI.IDCIRCOLO,ISCRITTI.SIGLAPROV,ISCRIT TI.SIGLAREG FROM ISCRITTI
ERROR SQL logic error or missing database(0): INSERT INTO PUNTI_GIOCATORI (IDFSI,IDCIRCOLO,SIGLAPROV,SIGLAREG) SELECT ISCRITTI.ID_FSI,ISCRITTI.IDCIRCOLO,ISCRITTI.SIGLAPROV,ISCRIT TI.SIGLAREG FROM ISCRITTI

Rolling Eyes
Re: sqlite and Execute [message #25856 is a reply to message #25850] Tue, 16 March 2010 02:45 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Can you send TestCase with .lay and .db files?

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: sqlite and Execute [message #25860 is a reply to message #25856] Tue, 16 March 2010 09:16 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
sergeynikitin wrote on Tue, 16 March 2010 02:45

Can you send TestCase with .lay and .db files?


Privet Sergey,

it is attached the package and the db. I inquire the db with the firefox plugin Sqlitemanager.

Bolshoie spasibo!
Luigi
  • Attachment: c16.rar
    (Size: 452.64KB, Downloaded 249 times)
Re: sqlite and Execute [message #25869 is a reply to message #25860] Tue, 16 March 2010 22:24 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Try as this code
c16::c16()
{
	CtrlLayout(*this, "Window title");

    Sqlite3Session sqlite3;
    if(!sqlite3.Open(ConfigFile("c16.db"))) {
        Exclamation("Can't create or open database file");
        return;
    }

//#ifdef _DEBUG
    sqlite3.SetTrace();
//#endif
SQL = sqlite3;
SQL.Execute("INSERT INTO PUNTI_GIOCATORI (IDCIRCOLO,SIGLAPROV,SIGLAREG) SELECT IDCIRCOLO,SIGLAPROV,SIGLAREG FROM ISCRITTI;");

	btnFill.WhenPush = THISBACK(FillRankingTables); 
	

	
}


It seems that you are adding records to the database and violate the unique key. Similar entries being added keys are already present in the database. It is necessary to exclude from the query a key field. For SQL Server is not important, but with SqLite such a conflict arises.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: sqlite and Execute [message #25870 is a reply to message #25869] Tue, 16 March 2010 23:21 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
sergeynikitin wrote on Tue, 16 March 2010 22:24

Try as this code
c16::c16()
{
	CtrlLayout(*this, "Window title");

    Sqlite3Session sqlite3;
    if(!sqlite3.Open(ConfigFile("c16.db"))) {
        Exclamation("Can't create or open database file");
        return;
    }

//#ifdef _DEBUG
    sqlite3.SetTrace();
//#endif
SQL = sqlite3;
SQL.Execute("INSERT INTO PUNTI_GIOCATORI (IDCIRCOLO,SIGLAPROV,SIGLAREG) SELECT IDCIRCOLO,SIGLAPROV,SIGLAREG FROM ISCRITTI;");

	btnFill.WhenPush = THISBACK(FillRankingTables); 
	

	
}


It seems that you are adding records to the database and violate the unique key. Similar entries being added keys are already present in the database. It is necessary to exclude from the query a key field. For SQL Server is not important, but with SqLite such a conflict arises.


Thank you for your effort.
Unfortunatly it does not work.
I tried even to drop the table PUNTI_GIOCATORI and create a new one without primary key.
The weird thing is that the same instruction is parsed correctly by sqlitemanager and fill the table as expected.

I will try to break the instruction in two easy ones in the next days. I suspect there is some strange behaviour in the sqlite package.

Poka,
Luigi
Re: sqlite and Execute [message #25874 is a reply to message #25870] Wed, 17 March 2010 00:31 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

I tried to delete all records from PUNTI_GIOCATORI and all start work! (in program and in mozilla firefox Sqlite viewer)

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: sqlite and Execute [message #25875 is a reply to message #25874] Wed, 17 March 2010 08:44 Go to previous message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
sergeynikitin wrote on Wed, 17 March 2010 00:31

I tried to delete all records from PUNTI_GIOCATORI and all start work! (in program and in mozilla firefox Sqlite viewer)


After this last reply I have abondened the plugin sqlite of Mozilla and downloaded another viewer http://sqlitebrowser.sourceforge.net/index.html

With this program the data in PUNTI_GIOCATORI appeared! Surprised Very Happy
So the problem was not in Upp or sqlite but in this plugin that seems bugged.

Thank you very much for your kind assistance!

Luigi

Previous Topic: Workaround: Hybridizing SqlExp usage
Next Topic: sqlite busy/lock timeout patch
Goto Forum:
  


Current Time: Thu Mar 28 11:50:21 CET 2024

Total time taken to generate the page: 0.01942 seconds