U++ framework: Newbie corner » sqlarray and sqlite

Home » Community » Newbie corner » sqlarray and sqlite (Trying to use sqlite example but...)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlarray and sqlite [message #54819]

Fri, 18 September 2020 22:49

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Hi everyone,

I've been looking over the example for sqlite and these forums, and basically I want to bind my table to the array... When I try to code this, I get "not defined" errors, and I am not seeing where in the example these things were defined other than the schema. I'm lost as to where to move forward here, before adding query functions etc.

My schema:

TABLE (tbl_customers)
	INT	(id) NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING (name, 150) NOT_NULL
	STRING (email, 150)
	STRING (contact, 150)
	STRING (address, 150)
	STRING (city, 50)
	STRING (state, 20)
	STRING (zip, 10)
	INT (taxable) NOT_NULL
END_TABLE

My include file:

sqlinclude.h:
#ifndef _Invoices_sqlinclude_h_
#define _Invoices_sqlinclude_h_
#include <SqlCtrl/SqlCtrl.h>
#include <plugin/sqlite3/Sqlite3.h>

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#define MODEL "Invoices/Tables/Invoices.sch"
#include <Sql/sch_header.h>

#endif

customers.h:
#ifndef _Invoices_customers_h_
#define _Invoices_customers_h_
#include "Invoices/sqlinclude.h"
struct CustomersWindow : WithCustomersWindowLayout<TopWindow> {
public:
	CustomersWindow();
	void Paint(Draw& w) {
        w.DrawRect(GetSize(), Color(204, 255, 255)); // <= enter your background color here
     }
};
	

#endif

In my layout I have ITEM(SqlArray, CustArray, LeftPosZ(436, 656).TopPosZ(28, 420))

And my cpp code:

#include "Invoices.h"
#include "customers.h"

CustomersWindow::CustomersWindow() {
	CtrlLayout(*this, "Customers");
	
	CustArray.SetTable(tbl_customers);
	
	CustArray.AddKey(id);
	// CustArray.Join(BOOK_ID, book); // joins id from other db to this id
	CustArray.AddColumn(name, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(email, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(contact, "Phone");
	CustArray.AddColumn(address, "Address");
	CustArray.AddColumn(city, "City");
	CustArray.AddColumn(state, "State");
	CustArray.AddColumn(zip, "Zip");
	CustArray.AddColumn(taxable, "Taxable?");
	CustArray.ColumnWidths("50 50 20 50 20 15 10 2");
	CustArray.SetOrderBy(id);
	// CustArray.WhenBar = THISBACK(BorrowMenu);
	// CustArray.WhenLeftDouble = THISBACK(EditBorrow);
	CustArray.GoEndPostQuery();
}

None of the ids from the schema are recognized, so I have to declare the table another way?
Thank you all for the guidance...

Jim

Report message to a moderator

Re: sqlarray and sqlite [message #54825 is a reply to message #54819]

Sat, 19 September 2020 05:50

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Additional info: My original app has a customerstable.cs definition file (converted to c):

struct customersTable
{
	int id;
	String name;
	String email;
	String contact;
	String address;
	String city;
	String state;
	String zip;
	int taxable;
};

Do I need something like this here?
Thanks.

Report message to a moderator

Re: sqlarray and sqlite [message #54826 is a reply to message #54819]

Sat, 19 September 2020 06:57

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Ok - In the upper left of Upp, I right click and add packages Sql, SqlCtrl, and plugin/sqlite3... and in customers I add this line:
SqlId tbl_customers("tbl_customers"), id("id"), name("name"), email("email"), contact("contact"), address("address"), city("city"), state("state"), zip("zip"), taxable("taxable");

That replaces my table structure, and the code compiles without a problem. Next up, getting the table to show up in the sql array with a query I think...

Report message to a moderator

Re: sqlarray and sqlite [message #54827 is a reply to message #54819]

Sat, 19 September 2020 08:04

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Last post of the evening, I promise Wink

Wink

I now have my table populated with values, not sure if this is the best way (I'm a newb at this after all) but here's the working code:

#include "Invoices.h"
#include "customers.h"

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#include "Sql/sch_source.h"


CustomersWindow::CustomersWindow() {
	String DBFile;
	String configfile = ConfigFile();
	CtrlLayout(*this, "Customers");
	
	if(FileExists(configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(configfile);
        DBFile = cfg.Get("DBFile", Null);
	}
	else 
	{
		Exclamation("Can't find DB");
	}
	SQL;
	Sqlite3Session sqlite3;
	if(!sqlite3.Open(DBFile)) {
		Exclamation("Can't create or open database file\n");
		return;
	}
	SQL = sqlite3;
	SqlId tbl_customers("tbl_customers"), id("id"), name("name"), email("email"), contact("contact"), address("address"), city("city"), state("state"), zip("zip"), taxable("taxable");

	CustArray.SetTable(tbl_customers);
		
	CustArray.AddKey(id);
	// CustArray.Join(BOOK_ID, book); // joins id from other db to this id
	CustArray.AddColumn(name, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(email, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(contact, "Phone");
	CustArray.AddColumn(address, "Address");
	CustArray.AddColumn(city, "City");
	CustArray.AddColumn(state, "State");
	CustArray.AddColumn(zip, "Zip");
	CustArray.AddColumn(taxable, "Taxable?");
	CustArray.ColumnWidths("40 40 20 50 20 15 10 5");
	CustArray.SetOrderBy(id);
	
	Sql sql(sqlite3);
	sql.Execute("select * from tbl_customers");


	while(sql.Fetch())
		CustArray.Add(sql);
	// CustArray.WhenBar = THISBACK(BorrowMenu);
	// CustArray.WhenLeftDouble = THISBACK(EditBorrow);
	CustArray.GoEndPostQuery();
}

Good night all ...

Report message to a moderator

Re: sqlarray and sqlite [message #54845 is a reply to message #54827]

Mon, 21 September 2020 08:19

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

If you add '_' to .sch definition

TABLE_ (tbl_customers)
	INT_	(id) NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING_ (name, 150) NOT_NULL

it will make it produce SqlId constants that you are defining in the constructor. However, populating global namespace with such variables would not be a good idea, that is why standard in U++ is upppercase for sql constants to minimize name clashes.

Also struct customersTable is in fact produced by .sch mechanism as S_customersTable too and you can fetch it from the table, insert it etc... It is not needed, but sometimes handy.

Report message to a moderator

Re: sqlarray and sqlite [message #54850 is a reply to message #54845]

Mon, 21 September 2020 22:54

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Hello Mirek, And thank you - both for this very helpful information, and for this amazing effort!

Report message to a moderator

Re: sqlarray and sqlite [message #54851 is a reply to message #54819]

Tue, 22 September 2020 03:05

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Ok, I've modded the files per Mirek's suggestions, but I keep hitting a wall here Sad

Sad

I get the error:
/home/james/upp/MyApps/Invoices/UI/customers.cpp (33): error: use of undeclared identifier 'CUSTOMERS'; did you mean 'CUSTOMERID'?

So, I change:
CustArray.SetTable(CUSTOMERS, CUST_ID);

CustArray.SetTable(S_CUSTOMERS, CUST_ID);

And I get:
/home/james/upp/MyApps/Invoices/UI/customers.cpp (33): error: 'S_CUSTOMERS' does not refer to a value

My header info:

#ifndef _Invoices_customers_h_
#define _Invoices_customers_h_
#include <Invoices/UI/sqlincludes.h>


struct CustomersWindow : WithCustomersWindowLayout<TopWindow> {
	String DBFile;
	String	configfile = ConfigFile();
	String	cfg;

	FileSel selectdbwin;
public:
    CustomersWindow();
	virtual void Paint(Draw& w) {
        w.DrawRect(GetSize(), Color(204, 255, 255)); // <= enter your background color here
    }
    void btnAddCustomerClick();
	void btnUpdateCustomerClick();
	void btnSearchCustomerClick();
	void EditRow();
	String SelectDB();
	void FakeStub();
};
	
#endif
                    AND
#ifndef _Invoices_sqlincludes_h_
#define _Invoices_sqlincludes_h_
#include <SqlCtrl/SqlCtrl.h>

#include <plugin/sqlite3/Sqlite3.h>

using namespace Upp;
#define LAYOUTFILE <Invoices/UI/Invoices.lay>
#include <CtrlCore/lay.h>

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h> // <-- Mirek informed needed in header for sql schema use
#define MODEL "Invoices/Tables/Invoices.sch"
#include <Sql/sch_header.h>
#undef MODEL

#endif

CPP File:

#include "customers.h"
#define MODEL <Invoices/Tables/Invoices.sch>
#include <Sql/sch_source.h>

#undef MODEL

CustomersWindow::CustomersWindow() {
	CtrlLayout(*this, "Customers");
	
	btnAddCustomer << [=] { btnAddCustomerClick(); }; // THISBACK is not needed in c++11 world and could be replaced with lambda.
	btnSearchCustomer << [=] { btnSearchCustomerClick(); }; // assisted by forum user Klugier
	btnUpdateCustomer << [=] { btnUpdateCustomerClick(); };
	
	btnFake << [=] { FakeStub(); };
	
	if(FileExists(configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(configfile);
        DBFile = cfg.Get("DBFile", Null);
	}
	else {
		DBFile = SelectDB();
	}
	SQL;
	Sqlite3Session sqlite3;
	if(!sqlite3.Open(DBFile)) {
		Exclamation("Can't create or open database file\n");
		return;
	}
	SQL = sqlite3; // switching to using schema instead of sqlid, per Mirek
	// SqlId Customers("Customers"), id("id"), name("name"), email("email"), contact("contact"), address("address"), city("city"), state("state"), zip("zip"), taxable("taxable");
	
	CustArray.SetTable(S_CUSTOMERS, CUST_ID);
		
	CustArray.AddColumn(CUSTNAME, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(EMAIL, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(CONTACT, "Phone");
	CustArray.AddColumn(ADDRESS, "Address");
	CustArray.AddColumn(CITY, "City");
	CustArray.AddColumn(STATE, "State");
	CustArray.AddColumn(ZIP, "Zip");
	CustArray.AddColumn(TAXABLE, "Taxable?");
	CustArray.ColumnWidths("40 40 20 50 20 15 10 5");
	CustArray.SetOrderBy(CUST_ID);
	
	Sql sql(sqlite3);
	sql.Execute("select * from CUSTOMERS");
	

	while(sql.Fetch())
		CustArray.Add(sql);
	// CustArray.WhenBar
	CustArray.WhenLeftDouble = [=] { EditRow(); };
	
	CustArray.GoEndPostQuery();
}

void CustomersWindow::btnAddCustomerClick()
{

	PromptOK(__func__);
}

void CustomersWindow::btnUpdateCustomerClick()
{
	PromptOK(__func__);
}

void CustomersWindow::btnSearchCustomerClick()
{
	PromptOK(__func__);
}

void CustomersWindow::EditRow()
{
	PromptOK(__func__);
}

void CustomersWindow::FakeStub()
{
}

String CustomersWindow::SelectDB()
{
	selectdbwin.Type(t_("Invoices DB"), "*.jts");
	if(!selectdbwin.ExecuteOpen(t_("Select DB File")))
	{
        return "";
	}
	/*
	from http://leonardoce.interfree.it/leowiki.html "simple configfile"
	*/
	cfg << "DBFile=" << selectdbwin.Get() << "\n";
	if(!SaveFile(ConfigFile(), cfg))
	{
    	Exclamation("Error saving configuration!");
	}
	return selectdbwin.Get();
}

My customers table in schema:

TABLE (CUSTOMERS)
	INT_	(CUST_ID) NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING_ (CUSTNAME, 150) NOT_NULL
	STRING_ (EMAIL, 150)
	STRING_ (CONTACT, 150)
	STRING_ (ADDRESS, 150)
	STRING_ (CITY, 50)
	STRING_ (STATE, 20)
	STRING_ (ZIP, 10)
	INT_ (TAXABLE) NOT_NULL
END_TABLE

All caps, as suggested, and changed some ids to make all unique.

Obviously I have a mistake (mistakes?) here somewhere but I can't seem to find them? Any (more) help would be appreciated Smile

Smile

Thanks y'all Smile

Smile

EDIT: I just realized that I didn't add _ to TABLE... Fixed it Smile

Smile

[Updated on: Tue, 22 September 2020 03:11]

Report message to a moderator

Re: sqlarray and sqlite [message #54852 is a reply to message #54851]

Tue, 22 September 2020 09:08

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

Your .sch creates

SqlId CUSTOMERS("CUSTOMERS");

struct S_CUSTOMER {
.....
};

SetTable obviously needs CUSTOMERS as parameter....

Additional suggestion: CustArray is SqlArray, right? In that case it is better to leave fetching the data to

CustArray.Query();

You can also add where condition e.g.:

CustArray.Query(EMAIL == "smith@email.com");

Sql sql(sqlite3);

You do need to specify the session here, as you have assigned a global default session by

SQL = sqlite3;

And it is better to avoid text SQL commands with SqlExp:

	sql.Execute("select * from CUSTOMERS");

sql * SelectAll().From(CUSTOMERS);

see https://www.ultimatepp.org/src$Sql$SqlExp_en-us.html

This way your commands get at least partially checked by C++ compiler and more importantly it avoids SQL injection problem.

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #54854 is a reply to message #54852]

Tue, 22 September 2020 17:43

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

mirek wrote on Tue, 22 September 2020 03:08

Your .sch creates

SqlId CUSTOMERS("CUSTOMERS");

struct S_CUSTOMER {
.....
};

SetTable obviously needs CUSTOMERS as parameter....

Yes, I caught that and had corrected it, thank you...

Quote:

Additional suggestion: CustArray is SqlArray, right? In that case it is better to leave fetching the data to
CustArray.Query();
You can also add where condition e.g.:
CustArray.Query(EMAIL == "smith@email.com");
Sql sql(sqlite3);
You do need to specify the session here, as you have assigned a global default session by
SQL = sqlite3;
And it is better to avoid text SQL commands with SqlExp:
	sql.Execute("select * from CUSTOMERS");
sql * SelectAll().From(CUSTOMERS);
see https://www.ultimatepp.org/src$Sql$SqlExp_en-us.html

This way your commands get at least partially checked by C++ compiler and more importantly it avoids SQL injection problem.

Mirek

That URL is perfect - I missed that in my searches obviously... I was wondering how to do more as in my c#:

        public bool Delete(customersTable dc)
        { ...
                //SQL Query to Delete Data from database
                string sql = "DELETE FROM CUSTOMERS WHERE CUST_ID=@id";

                //SQL command to pass the value
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                //Passing the value
                cmd.Parameters.AddWithValue("@id", dc.CUST_ID);
                //Open DB Connection
                conn.Open();
                //integer variable
                int rows = cmd.ExecuteNonQuery();
                if(rows>0) //Success
                {
...

Thank you again Smile

Smile

Report message to a moderator

Re: sqlarray and sqlite [message #54855 is a reply to message #54854]

Tue, 22 September 2020 17:54

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

        public bool Delete(customersTable dc)
        { ...
                //SQL Query to Delete Data from database
                string sql = "DELETE FROM CUSTOMERS WHERE CUST_ID=@id";

                //SQL command to pass the value
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                //Passing the value
                cmd.Parameters.AddWithValue("@id", dc.CUST_ID);
                //Open DB Connection
                conn.Open();
                //integer variable
                int rows = cmd.ExecuteNonQuery();
                if(rows>0) //Success
                {

Yeah, thats crazy, compared to

SQL * Delete(CUSTOMERS).Where(CUST_ID == id);

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #54856 is a reply to message #54855]

Tue, 22 September 2020 18:07

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

I see that now LOL but I keep getting an xmessage fatal error, invalid memory access when I change

sql.Execute("select * from CUSTOMERS");
to
sql * SelectAll().From(CUSTOMERS);

And then run the program...

Report message to a moderator

Re: sqlarray and sqlite [message #54857 is a reply to message #54856]

Tue, 22 September 2020 18:47

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

Not enough context to figure that out.

However, in case of crashes, usually the fastest route is to run it in debugger (just press F5), then investigate backtrace. You can even copy it (in Debug/Copy backtrace of all threads) and send here.

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #54859 is a reply to message #54819]

Tue, 22 September 2020 20:41

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

----------------------------------
Thread: 1

----------------------------------
Thread: 2

../sysdeps/unix/sysv/linux/poll.c:29
/usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
g_main_context_iteration () from /usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
/usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
/usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 3

../sysdeps/unix/sysv/linux/poll.c:29
/usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
g_main_loop_run () from /usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
/usr/lib/x86_64-linux-gnu/libgio-2.0.so.0
/usr/lib/x86_64-linux-gnu/libglib-2.0.so.0
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 4

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=0) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de58ec) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de58e0) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de5920) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de5920) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 5

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=1) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de59ac) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de59a0) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de59e0) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de59e0) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 6

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=2) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de5a6c) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de5a60) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de5aa0) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de5aa0) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 7

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=3) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de58ac) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de58a0) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de5b20) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de5b20) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 8

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=4) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de5bac) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de5ba0) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de5be0) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de5be0) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

----------------------------------
Thread: 9

futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0xe82d60 <Upp::CoWork::GetPool()::pool+114848>) at ../sysdeps/nptl/futex-internal.h:183
pthread_cond_wait.c:508
pthread_cond_wait.c:638
Upp::ConditionVariable::Wait (this=0xe82d38 <Upp::CoWork::GetPool()::pool+114808>, m=..., timeout_ms=-1) at Mt.cpp:624
Upp::CoWork::Pool::ThreadRun (tno=5) at CoWork.cpp:155
Upp::CoWork::Pool::InitThreads(int)::$_21::operator()() const (this=0x7ffff4de5c6c) at CoWork.cpp:31
Upp::Function<void ()>::Wrapper<Upp::CoWork::Pool::InitThreads(int)::$_21>::Execute() (this=0x7ffff4de5c60) at Function.h:17
Upp::Function<void ()>::operator()() const (this=0x7ffff4de5ca0) at /home/james/upp/uppsrc/Core/Function.h:76
Upp::sThreadRoutine (arg=0x7ffff4de5ca0) at Mt.cpp:91
start_thread (arg=<optimized out>) at pthread_create.c:477
clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

The code:

CustomersWindow::CustomersWindow() {


	CtrlLayout(*this, "Customers");
	
	btnAddCustomer << [=] { btnAddCustomerClick(); }; // THISBACK is not needed in c++11 world and could be replaced with lambda.
	btnSearchCustomer << [=] { btnSearchCustomerClick(); }; // assisted by forum user Klugier
	btnUpdateCustomer << [=] { btnUpdateCustomerClick(); };
	
	btnFake << [=] { FakeStub(); };
	
	if(FileExists(configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(configfile);
        DBFile = cfg.Get("DBFile", Null);
	}
	else {
		DBFile = SelectDB();
	}
	SQL;
	Sqlite3Session sqlite3;
	if(!sqlite3.Open(DBFile)) {
		Exclamation("Can't create or open database file\n");
		return;
	}
	SQL = sqlite3;
	Sql sql;
	CustArray.SetTable(CUSTOMERS, CUST_ID);
		
	// CustArray.Join(BOOK_ID, book); // joins id from other db to this id
	CustArray.AddColumn(CUSTNAME, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(EMAIL, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(CONTACT, "Phone");
	CustArray.AddColumn(ADDRESS, "Address");
	CustArray.AddColumn(CITY, "City");
	CustArray.AddColumn(STATE, "State");
	CustArray.AddColumn(ZIP, "Zip");
	CustArray.AddColumn(TAXABLE, "Taxable?");
	CustArray.ColumnWidths("40 40 20 50 20 15 10 5");
	CustArray.SetOrderBy(CUST_ID);
	
	// sql.Execute("select * from CUSTOMERS");
	sql * SelectAll().From(CUSTOMERS);
	
	// while(sql.Fetch()) 	CustArray.Add(sql);
	CustArray.Query();
	
	// CustArray.WhenBar
	CustArray.WhenLeftDouble = [=] { EditRow(); };
	
	// CustArray.GoEndPostQuery();

}

Report message to a moderator

Re: sqlarray and sqlite [message #54873 is a reply to message #54859]

Wed, 23 September 2020 16:32

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

I do not see apparent reason in the code posted. One suspicios line is

SQL;

which should normally do nothing, but would crash if you have assigned a session prior to constructor and that session was meanwhile destroyed.

If you can .zip the whole package and post here, I am glad to investigate further.

BTW, you do not need to do that SelectAll query here if you are using SqlArray::Query (that one does its own select).

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #54876 is a reply to message #54873]

Wed, 23 September 2020 17:14

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Thank you for looking at this and for all your help, Mirek...

I've stripped things down to the main and one (customers) module, with a few support files, and everything is compiling and running without error now.

For curiosity's sake, I'm putting the current zip here. And again, I wish I'd found this site a long time ago!

Attachment: Invoices.zip
(Size: 15.91KB, Downloaded 289 times)

Report message to a moderator

Re: sqlarray and sqlite [message #54880 is a reply to message #54819]

Wed, 23 September 2020 19:57

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

I realize that I've been going about this all wrong...
I've been trying to do a 1 - 1 conversion from my c# code to the c++ and U++... That's wrong thinking.

I need to embrace the U++, so I went back to the examples, and am using the book borrowing app. I've come up with the following code:

#include "customers.h"
#define MODEL <Invoices/Tables/Invoices.sch>
#include <Sql/sch_source.h>

#undef MODEL
class AddCustomer : public WithCustomerAddLayout<TopWindow> {
	public:
		SqlCtrls ctrls;
		typedef AddCustomer CLASSNAME;
		AddCustomer();
};

/*
	ITEM(Option, chkTaxable, SetLabel(t_("Taxable?")).SetFont(SansSerifZ(16)).LeftPosZ(228, 108).TopPosZ(36, 16))
	ITEM(EditString, txtCustSearch, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(404, 19))
	ITEM(EditString, txtCustName, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(76, 19))
	ITEM(EditString, txtCustEmail, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(116, 19))
	ITEM(EditString, txtCustPhone, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(156, 19))
	ITEM(EditString, txtCustAddress, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(196, 19))
	ITEM(EditString, txtCustCity, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(232, 19))
	ITEM(EditString, txtCustState, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(272, 19))
	ITEM(EditString, txtCustZip, MaxChars(10).SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 64).TopPosZ(308, 19))

	STRING_ (CUSTNAME, 150) NOT_NULL
	STRING_ (EMAIL, 150)
	STRING_ (CONTACT, 150)
	STRING_ (ADDRESS, 150)
	STRING_ (CITY, 50)
	STRING_ (STATE, 20)
	STRING_ (ZIP, 10)
	INT_ (TAXABLE) NOT_NULL
*/

AddCustomer::AddCustomer()
{
	CtrlLayoutOKCancel(*this, "Add Customer");
	ctrls
		(CUSTNAME, txtCustName)
		(EMAIL, txtCustEmail)
		(CONTACT, txtCustPhone)
		(ADDRESS, txtCustAddress)
		(CITY, txtCustCity)
		(STATE, txtCustState)
		(ZIP, txtCustZip)
		// (TAXABLE, chkTaxable)
	;
}


CustomersWindow::CustomersWindow() {


	CtrlLayout(*this, "Customers");
	/*
	btnAddCustomer << [=] { btnAddCustomerClick(); }; // THISBACK is not needed in c++11 world and could be replaced with lambda.
	btnSearchCustomer << [=] { btnSearchCustomerClick(); }; // assisted by forum user Klugier
	btnUpdateCustomer << [=] { btnUpdateCustomerClick(); };
	*/
	
	if(FileExists(myConfig.configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(myConfig.configfile);
        myConfig.DBFile = cfg.Get("DBFile", Null);
	}
	else {
		myConfig.DBFile = myConfig.SelectDB();
	}
	
	Sqlite3Session sqlite3;
	if(!sqlite3.Open(myConfig.DBFile)) {
		Exclamation("Can't create or open database file\n");
		return;
	}
	
	SQL = sqlite3;
	/*
	Sql sql;
	*/
	
	CustArray.SetTable(CUSTOMERS, CUST_ID);
		
	// CustArray.Join(BOOK_ID, book); // joins id from other db to this id
	CustArray.AddColumn(CUSTNAME, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(EMAIL, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(CONTACT, "Phone");
	CustArray.AddColumn(ADDRESS, "Address");
	CustArray.AddColumn(CITY, "City");
	CustArray.AddColumn(STATE, "State");
	CustArray.AddColumn(ZIP, "Zip");
	CustArray.AddColumn(TAXABLE, "Taxable?");
	CustArray.ColumnWidths("40 40 20 50 20 15 10 5");
	CustArray.Appending().NoRemoving();
	CustArray.SetOrderBy(CUST_ID);
	
	CustArray.Query();
	
	CustArray.WhenLeftDouble = [=] { EditRow(); };
	
}

void CustomersWindow::EditRow()
{
	int idNum;
	if(!CustArray.IsCursor())
		return;
	idNum = CustArray.GetKey();
	AddCustomer dlg;

	SQL * Select(CUST_ID).From(CUSTOMERS).Where(CUST_ID == idNum);
	if(!dlg.ctrls.Fetch(SQL))
		return;
	if(dlg.Run() != IDOK)
		return;
	SQL * dlg.ctrls.Update(CUSTOMERS).Where(CUST_ID == idNum);
	CustArray.Query();
	CustArray.FindSetCursor(idNum);
}
/*
void CustomersWindow::btnAddCustomerClick()
{

	PromptOK(__func__);
}

void CustomersWindow::btnUpdateCustomerClick()
{
	PromptOK(__func__);
}

void CustomersWindow::btnSearchCustomerClick()
{
	PromptOK(__func__);
}
*/

Now, when I run this, it gets to line 109:
SQL * Select(CUST_ID).From(CUSTOMERS).Where(CUST_ID == idNum);

And gives me an invalid memory access with:

Question

Question

index.php?t=getfile&id=6219&private=0

Attachment: latest.png
(Size: 477.06KB, Downloaded 1095 times)

[Updated on: Thu, 24 September 2020 01:04]

Report message to a moderator

Re: sqlarray and sqlite [message #54882 is a reply to message #54819]

Thu, 24 September 2020 06:08

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

I may have fixed my own problem, and found my mistake... in my code, I was using sql statements to generate the global SQL in the customers module. I needed to move those to the main line. So here's tne new code for anyone looking:

#main.cpp
#include "Invoices.h"

#define MODEL "Invoices/Tables/Invoices.sch"
#include <Sql/sch_schema.h>
#undef MODEL

void Invoices::MainMenu(Bar& bar)
{
	bar.Add(t_("Customers"),  [=]{ if(!cwin.IsOpen()) cwin.Open(this); });

//    bar.Add(t_("Products"), [=]{ if(!prodwin.IsOpen()) prodwin.Open(this); });
	
//	bar.Sub(t_("Transactions"), THISFN(TransactionsMenu));
//	bar.Sub(t_("Reports"), THISFN(ReportsMenu));
	bar.Sub(t_("Management"), THISFN(ManagementMenu));
	bar.Add(t_("Exit"), THISFN(Close));
}
/*
void Invoices::TransactionsMenu(Bar& bar)
{
	bar.Add(t_("Create Invoice"), [=]{ if(!createinvoicewin.IsOpen())
	createinvoicewin.Open(this); });
	bar.Add(t_("List Invoices"), [=]{ if(!listinvoiceswin.IsOpen())
	listinvoiceswin.Open(this); });
	bar.Add(t_("List Line Items"), [=]{ if(!listlineitemswin.IsOpen())
	listlineitemswin.Open(this); });
}

void Invoices::ReportsMenu(Bar& bar)
{
	bar.Add(t_("Income / Sales Tax"), [=]{ if(!taxreportwin.IsOpen()) taxreportwin.Open(this); });
	bar.Add(t_("Income / Sales Tax by Customer"), [=]{ if(!taxbycustomerwin.IsOpen()) taxbycustomerwin.Open(this); });
	bar.Add(t_("Income by Customer"), [=]{ if(!incomewin.IsOpen()) incomewin.Open(this); });
	bar.Add(t_("Profit / Loss"), [=]{ if(!profitwin.IsOpen()) profitwin.Open(this); });
}
*/
void Invoices::ManagementMenu(Bar& bar)
{
	bar.Add(t_("Select Database File"), [=]{myConfig.DBFile = myConfig.SelectDB(); });
	bar.Add(t_("Show Current Database File"), [=]{PromptOK(DeQtf("Current Database file is: \n" + myConfig.DBFile));});
//	bar.Add(t_("Set Company Info"), [=]{if(!setcompanywin.IsOpen()) setcompanywin.Open(this); });
	bar.Add(t_("Select Output Directory"), [=]{ myConfig.OutputDirectory = myConfig.GetOutputDirectory(); });
}

Invoices::Invoices()
{
	CtrlLayout(*this, "Invoices");
	AddFrame(mainmenu);
	mainmenu.Set(THISFN(MainMenu));
	
	// from http://leonardoce.interfree.it/leowiki.html "simple configfile"
	
	if(FileExists(myConfig.configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(myConfig.configfile);
        myConfig.DBFile = cfg.Get("DBFile", Null);
	}
	else {
		myConfig.GetOutputDirectory();
		myConfig.DBFile = myConfig.SelectDB();
	}
}

// Shortcut keys defined
/*
bool Invoices::Key(dword key, int count)
{
	if(key == K_CTRL_E && !ewin.IsOpen())
		ewin.Open(this);
	else
	if(key == K_CTRL_F && !fwin.IsOpen())
		fwin.Open(this);
	return false;
}
*/

GUI_APP_MAIN
{
	Configs myConfig;
	if(FileExists(myConfig.configfile))
	{
        VectorMap<String, String> cfg = LoadIniFile(myConfig.configfile);
        myConfig.DBFile = cfg.Get("DBFile", Null);
	}
	else {
		myConfig.DBFile = myConfig.SelectDB();
	}
	
	Sqlite3Session sqlite3;
	if(!sqlite3.Open("/home/james/upp/MyApps/Customers/sample.db")) {
		Exclamation("Can't create or open database file\n");
		return;
	}
	
	SQL = sqlite3;
	
	Invoices().Run();
}

#Invoices.h
#ifndef __Invoices__Invoices_h_
#define __Invoices__Invoices_h_

#include <CtrlLib/CtrlLib.h>

using namespace Upp;

#include <Invoices/UI/sqlincludes.h>
#include "customers.h"
#include "../configs.h"

// Just a quick info dialog showing currently selected file
struct ShowDBWindow : WithShowDBWindowLayout<TopWindow> {
	ShowDBWindow() { CtrlLayout(*this, "Show Current Database File"); }
};

class Invoices : public WithInvoicesLayout<TopWindow> {
	CustomersWindow	cwin;
	MenuBar		mainmenu;

public:
	typedef Invoices CLASSNAME;
	Configs myConfig;

	Invoices();
	
	// Menu definition
	void		MainMenu(Bar& bar);

	// void		TransactionsMenu(Bar& bar);
	// void		ReportsMenu(Bar& bar);
	void		ManagementMenu(Bar& bar);
	
	// Keys for shortcuts
	// bool Key(dword key, int count) override;
};
#endif

#customers.cpp
#include "customers.h"
#define MODEL <Invoices/Tables/Invoices.sch>
#include <Sql/sch_source.h>

#undef MODEL
class AddCustomer : public WithCustomerAddLayout<TopWindow> {
	public:
		SqlCtrls ctrls;
		typedef AddCustomer CLASSNAME;
		AddCustomer();
};

/*
	ITEM(Option, chkTaxable, SetLabel(t_("Taxable?")).SetFont(SansSerifZ(16)).LeftPosZ(228, 108).TopPosZ(36, 16))
	ITEM(EditString, txtCustSearch, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(404, 19))
	ITEM(EditString, txtCustName, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(76, 19))
	ITEM(EditString, txtCustEmail, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(116, 19))
	ITEM(EditString, txtCustPhone, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(156, 19))
	ITEM(EditString, txtCustAddress, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(196, 19))
	ITEM(EditString, txtCustCity, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(232, 19))
	ITEM(EditString, txtCustState, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 256).TopPosZ(272, 19))
	ITEM(EditString, txtCustZip, MaxChars(10).SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(136, 64).TopPosZ(308, 19))

	STRING_ (CUSTNAME, 150) NOT_NULL
	STRING_ (EMAIL, 150)
	STRING_ (CONTACT, 150)
	STRING_ (ADDRESS, 150)
	STRING_ (CITY, 50)
	STRING_ (STATE, 20)
	STRING_ (ZIP, 10)
	INT_ (TAXABLE) NOT_NULL
*/

AddCustomer::AddCustomer()
{
	CtrlLayoutOKCancel(*this, "Add Customer");
	ctrls
		(CUSTNAME, txtCustName)
		(EMAIL, txtCustEmail)
		(CONTACT, txtCustPhone)
		(ADDRESS, txtCustAddress)
		(CITY, txtCustCity)
		(STATE, txtCustState)
		(ZIP, txtCustZip)
		(TAXABLE, chkTaxable)
	;
}


CustomersWindow::CustomersWindow() {
	CtrlLayout(*this, "Customers");
	// Major assistance on the following code from 
	btnNewCustomer << [=] { AddNewCustomer(); }; // THISBACK is not needed in c++11 world and could be replaced with lambda.
	/*
	btnSearchCustomer << [=] { btnSearchCustomerClick(); }; // assisted by forum user Klugier
	btnUpdateCustomer << [=] { btnUpdateCustomerClick(); };
	*/
	
	CustArray.SetTable(CUSTOMERS, CUST_ID);
		
	// CustArray.Join(BOOK_ID, book); // joins id from other db to this id
	CustArray.AddColumn(CUSTNAME, "Name"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(EMAIL, "Email"); // .SetConvert(DateIntConvert());
	CustArray.AddColumn(CONTACT, "Phone");
	CustArray.AddColumn(ADDRESS, "Address");
	CustArray.AddColumn(CITY, "City");
	CustArray.AddColumn(STATE, "State");
	CustArray.AddColumn(ZIP, "Zip");
	CustArray.AddColumn(TAXABLE, "Taxable?");
	CustArray.ColumnWidths("40 40 20 50 20 15 10 5");
	CustArray.NoRemoving();
	CustArray.SetOrderBy(CUST_ID);
	
	CustArray.Query();
	
	CustArray.WhenLeftDouble = [=] { EditRow(); };
	
}

void CustomersWindow::EditRow()
{
	int idNum;
	if(!CustArray.IsCursor())
		return;
	idNum = CustArray.GetKey();
	if (IsNull(idNum))
		return;
	AddCustomer dlg;
	dlg.Title("Edit Customer");

	//if(!dlg.ctrls.Load(CUSTOMERS, CUST_ID == idNum))
	//	return;
	SQL * Select(dlg.ctrls).From(CUSTOMERS).Where(CUST_ID == idNum);
	if(!dlg.ctrls.Fetch(SQL))
		return;
	if(dlg.Run() != IDOK)
		return;
	SQL * dlg.ctrls.Update(CUSTOMERS).Where(CUST_ID == idNum);
	CustArray.ReQuery();
	CustArray.FindSetCursor(idNum);
}
void CustomersWindow::AddNewCustomer()
{
    AddCustomer dlg;
    dlg.Title("New Customer");
    if(dlg.Execute() != IDOK)
        return;
    SQL * dlg.ctrls.Insert(CUSTOMERS);
    int id = SQL.GetInsertedId();
    CustArray.ReQuery();
    CustArray.FindSetCursor(id);
}
/*
void CustomersWindow::btnUpdateCustomerClick()
{
	PromptOK(__func__);
}

void CustomersWindow::btnSearchCustomerClick()
{
	PromptOK(__func__);
}
*/

#customers.h
#ifndef _Invoices_customers_h_
#define _Invoices_customers_h_
#include <Invoices/UI/sqlincludes.h>
#include "../configs.h"

struct CustomersWindow : WithCustomersWindowLayout<TopWindow> {
	Configs	myConfig;
	// EditString	name;
	
public:
    CustomersWindow();
	virtual void Paint(Draw& w) {
        w.DrawRect(GetSize(), Color(204, 255, 255)); // <= enter your background color here
    }
	void AddNewCustomer();
	// void btnUpdateCustomerClick();
	// void btnSearchCustomerClick();
	void EditRow();

	// void FakeStub();
};
	

#endif
#Invoices.Lay
LAYOUT(InvoicesLayout, 460, 116)
END_LAYOUT

LAYOUT(TaxWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(TaxByCustomerWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(IncomeByCustomerWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(ProfitLossWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(SelectDBWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(ShowDBWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(SetCompanyWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(SelectOutputDirWindowLayout, 400, 200)
END_LAYOUT

LAYOUT(CustomersWindowLayout, 664, 460)
	ITEM(Button, btnNewCustomer, SetLabel(t_("Add New Customer")).SetFrame(ThinInsetFrame()).LeftPosZ(272, 120).TopPosZ(436, 15))
	ITEM(SqlArray, CustArray, AutoHideSb(true).LeftPosZ(4, 656).TopPosZ(4, 420))
END_LAYOUT

LAYOUT(CustomerAddLayout, 436, 368)
	ITEM(Label, dv___0, SetLabel(t_("Name")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).VSizePosZ(16, 332))
	ITEM(Label, dv___1, SetLabel(t_("Email")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(56, 19))
	ITEM(Label, dv___2, SetLabel(t_("Contact")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(96, 19))
	ITEM(Label, dv___3, SetLabel(t_("Address")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(136, 19))
	ITEM(Label, dv___4, SetLabel(t_("City")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(172, 19))
	ITEM(Label, dv___5, SetLabel(t_("State")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(212, 19))
	ITEM(Label, dv___6, SetLabel(t_("Zip")).SetFont(SansSerifZ(16)).LeftPosZ(36, 100).TopPosZ(248, 19))
	ITEM(Option, chkTaxable, SetLabel(t_("Taxable?")).SetFont(SansSerifZ(16)).LeftPosZ(152, 108).TopPosZ(300, 16))
	ITEM(EditString, txtCustName, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(16, 19))
	ITEM(EditString, txtCustEmail, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(56, 19))
	ITEM(EditString, txtCustPhone, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(96, 19))
	ITEM(EditString, txtCustAddress, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(136, 19))
	ITEM(EditString, txtCustCity, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(172, 19))
	ITEM(EditString, txtCustState, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 256).TopPosZ(212, 19))
	ITEM(EditString, txtCustZip, MaxChars(10).SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(152, 64).TopPosZ(248, 19))
	ITEM(Button, ok, SetLabel(t_("OK")).LeftPosZ(68, 64).TopPosZ(332, 24))
	ITEM(Button, cancel, SetLabel(t_("Cancel")).LeftPosZ(292, 64).TopPosZ(332, 24))
END_LAYOUT

LAYOUT(ProductsWindowLayout, 960, 464)
	ITEM(SqlArray, ProductArray, SetFrame(ThinInsetFrame()).LeftPosZ(384, 572).TopPosZ(20, 440))
	ITEM(Label, dv___1, SetLabel(t_("Product ID")).SetFont(SansSerifZ(16)).LeftPosZ(8, 88).TopPosZ(28, 32))
	ITEM(Label, dv___2, SetLabel(t_("Date Purchased")).SetFont(SansSerifZ(16)).LeftPosZ(8, 132).TopPosZ(116, 32))
	ITEM(Label, dv___3, SetLabel(t_("Description")).SetFont(SansSerifZ(16)).LeftPosZ(8, 88).TopPosZ(160, 32))
	ITEM(Label, dv___4, SetLabel(t_("Cost")).SetFont(SansSerifZ(16)).LeftPosZ(8, 88).TopPosZ(304, 32))
	ITEM(Label, dv___5, SetLabel(t_("Invoice")).SetFont(SansSerifZ(16)).LeftPosZ(188, 72).TopPosZ(28, 32))
	ITEM(EditIntSpin, txtInvoiceNo, Min(-1).NotNull(true).SetFrame(FieldFrame()).LeftPosZ(268, 80).TopPosZ(32, 19))
	ITEM(EditString, txtProductName, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(104, 248).TopPosZ(76, 19))
	ITEM(EditString, txtProductCost, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(108, 244).TopPosZ(312, 19))
	ITEM(Label, dv___9, SetLabel(t_("Name")).SetFont(SansSerifZ(16)).LeftPosZ(8, 88).TopPosZ(72, 32))
	ITEM(EditInt, txtProductID, Min(1).NotNull(true).SetEditable(false).SetFrame(FieldFrame()).LeftPosZ(104, 64).TopPosZ(32, 19))
	ITEM(DropDate, dtpDatePurchased, SetFrame(FieldFrame()).LeftPosZ(152, 104).VSizePosZ(120, 325))
	ITEM(Button, btnUpdateProduct, SetLabel(t_("Update")).SetFont(SansSerifZ(16)).SetFrame(ButtonFrame()).LeftPosZ(108, 84).TopPosZ(352, 28))
	ITEM(Button, btnAddProduct, SetLabel(t_("Add")).SetFont(SansSerifZ(16)).SetFrame(ButtonFrame()).LeftPosZ(16, 84).TopPosZ(352, 28))
	ITEM(Button, btnShowAllProduct, SetLabel(t_("Show All")).SetFont(SansSerifZ(16)).SetFrame(ButtonFrame()).LeftPosZ(16, 84).TopPosZ(408, 28))
	ITEM(Button, btnProductRange, SetLabel(t_("Show")).SetFont(SansSerifZ(16)).SetFrame(ButtonFrame()).LeftPosZ(108, 84).TopPosZ(408, 28))
	ITEM(DropDate, dtpDateStart, SetFrame(FieldFrame()).LeftPosZ(248, 104).TopPosZ(384, 19))
	ITEM(DropDate, dtpDateEnd, SetFrame(FieldFrame()).LeftPosZ(248, 104).TopPosZ(412, 19))
	ITEM(DocEdit, txtDesctription, SetFont(SansSerifZ(16)).SetFrame(FieldFrame()).LeftPosZ(108, 244).TopPosZ(168, 128))
END_LAYOUT

LAYOUT(CreateInvoiceWindowLayout, 1100, 728)
END_LAYOUT

LAYOUT(ListInvoicesWindowLayout, 1128, 584)
	ITEM(SqlArray, ListInvoicesArray, MultiSelect(true).SetFrame(ThinInsetFrame()).RightPosZ(8, 928).VSizePosZ(8, 12))
	ITEM(Button, btnPrint, SetLabel(t_("Print Selected")).LeftPosZ(4, 80).TopPosZ(12, 15))
	ITEM(Button, btnApplyPayment, SetLabel(t_("Apply Payment")).LeftPosZ(4, 80).TopPosZ(32, 15))
	ITEM(Button, btnEdit, SetLabel(t_("Edit Selected")).LeftPosZ(96, 80).TopPosZ(12, 15))
	ITEM(Button, btnVoid, SetLabel(t_("Void Selected")).LeftPosZ(4, 80).TopPosZ(52, 15))
	ITEM(Button, btnPaidInFull, SetLabel(t_("Mark PIF")).LeftPosZ(96, 80).TopPosZ(52, 15))
	ITEM(Button, btnFixDate, SetLabel(t_("Fix Dates")).LeftPosZ(4, 80).TopPosZ(72, 15))
	ITEM(Button, btnByPaid, SetLabel(t_("All Paid")).LeftPosZ(8, 80).TopPosZ(112, 15))
	ITEM(Button, btnByBalanceDue, SetLabel(t_("Balance Due")).LeftPosZ(100, 76).TopPosZ(112, 15))
	ITEM(LabelBox, dv___9, SetLabel(t_("Show:")).LeftPosZ(4, 180).TopPosZ(92, 124))
	ITEM(EditDouble, edbPayment, AlignRight(true).LeftPosZ(96, 80).TopPosZ(32, 19))
	ITEM(Button, btnByDates, SetLabel(t_("Date Range")).LeftPosZ(8, 80).TopPosZ(196, 15))
	ITEM(Button, btnByCustomer, SetLabel(t_("By Customer")).LeftPosZ(52, 80).TopPosZ(132, 15))
	ITEM(Button, btnByVoided, SetLabel(t_("All Voided")).LeftPosZ(8, 80).TopPosZ(172, 15))
	ITEM(DropDate, ddRange2, LeftPosZ(92, 88).TopPosZ(192, 19))
	ITEM(DropDate, ddRange1, LeftPosZ(92, 88).TopPosZ(168, 19))
	ITEM(DropDate, ddFixDate, LeftPosZ(96, 88).TopPosZ(72, 19))
	ITEM(DropList, dlCustomers, LeftPosZ(8, 172).TopPosZ(148, 19))
END_LAYOUT

LAYOUT(ListLineItemsWindowLayout, 1044, 540)
	ITEM(SqlArray, ListLineItemsArray, SetFrame(ThinInsetFrame()).LeftPosZ(188, 848).TopPosZ(4, 528))
	ITEM(Button, btnFake, LeftPosZ(168, 56).TopPosZ(632, 15))
END_LAYOUT

#invoices.sch
TABLE_ (CUSTOMERS)
	INT_	(CUST_ID) NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING_ (CUSTNAME, 150) NOT_NULL
	STRING_ (EMAIL, 150)
	STRING_ (CONTACT, 150)
	STRING_ (ADDRESS, 150)
	STRING_ (CITY, 50)
	STRING_ (STATE, 20)
	STRING_ (ZIP, 10)
	INT_ (TAXABLE) NOT_NULL
END_TABLE

TABLE_ (INVOICES)
	INT_ (INVOICE_ID)    NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	INT_ (INVOICENUMBER)    NOT_NULL
	INT_ (CUSTOMERID)
	INT_ (TRANSACTIONDATE)
	STRING_ (TERMS, 20) NOT_NULL
	DOUBLE_ (NONTAXABLESUB)
	DOUBLE_ (TAXABLESUB) 	
	DOUBLE_ (TAX)
	DOUBLE_ (GRANDTOTAL)
	DOUBLE_ (AMTPAID)
	INT_ (DATEPAID)
	INT_ (STATUS)
END_TABLE

TABLE_ (PRODUCTS)
	INT_ (PROD_ID)	NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING_ (PRODNAME, 150)	NOT_NULL
	STRING_ (PRODDESCRIPTION, 200)
	INT_ (DATEPURCHASED)
	DOUBLE_ (COST)	NOT_NULL
	INT_ (INVOICEID)	NOT_NULL
END_TABLE

TABLE_ (LINEITEMS)
	INT_ (LINEITEM_ID)	NOT_NULL PRIMARY_KEY AUTO_INCREMENT
	STRING_ (PRODUCTNAME, 200) NOT_NULL
	STRING_ (DESCRIPTION, 200)
	DOUBLE_ (PRICE)	 NOT_NULL
	DOUBLE_ (QTY)	 NOT_NULL
	DOUBLE_ (TOTAL)	 NOT_NULL
	INT64_ (INVOICEIDNUMBER)	 NOT_NULL
	INT_ (ISTAXABLE)	 NOT_NULL
END_TABLE

The remaining support files are in the attached zip... Now I should make much more rapid progress until I get to the printing/reporting stage...

Thanks again for all the help Smile

Smile

Jim

Attachment: support_files.zip
(Size: 10.23KB, Downloaded 304 times)

Report message to a moderator

Re: sqlarray and sqlite [message #54884 is a reply to message #54882]

Thu, 24 September 2020 13:46

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

jimlef wrote on Thu, 24 September 2020 06:08

Now I should make much more rapid progress until I get to the printing/reporting stage...

In advance:

https://www.ultimatepp.org/srcdoc$RichText$QTF$en-us.html
https://www.ultimatepp.org/reference$Reports$en-us.html

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #54885 is a reply to message #54884]

Thu, 24 September 2020 16:40

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

mirek wrote on Thu, 24 September 2020 07:46

jimlef wrote on Thu, 24 September 2020 06:08

Now I should make much more rapid progress until I get to the printing/reporting stage...

In advance:

https://www.ultimatepp.org/srcdoc$RichText$QTF$en-us.html
https://www.ultimatepp.org/reference$Reports$en-us.html

Mirek

Bookmarked - and Thanks again Smile

Smile

Report message to a moderator

Re: sqlarray and sqlite [message #54886 is a reply to message #54819]

Thu, 24 September 2020 19:28

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

And for your amuse... er info, here's the c# code I'm converting:

Attachment: Invoices.7z
(Size: 131.22KB, Downloaded 280 times)

Report message to a moderator

Re: sqlarray and sqlite [message #54966 is a reply to message #54886]

Fri, 02 October 2020 11:40

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

I have looked through the code and I am impressed, you got it almost all right in really record time!

Some tidbits:

	SQL * Select(dlg.ctrls).From(LINEITEMS).Where(LINEITEM_ID == idNum);

Using SQL for Select with Fetch loop is possible but not quite recommended (mostly on past experience, I have shoot my leg several times here Smile

Smile

. The problem is that very often your Fetch loop will call some routine and that routine will use SQL for something else (e.g. Update or another Select) and that will erase the current resultset... Better declare Sql sql and fetch from this.

Note that usage like SQL % Select ... (selecting single value from single line, no fetching) is still fine.

Value ConvLineItemCls::Scan ( const Value &q ) const

I see that you have not used it afterwards, but anyway... Scan is supposed to convert from "display" format - usually text - to Value. Your usage seems exactly opposite. I guess it should have been Format here (format from Value to usually text).

SQL & ::Insert(LINEITEMS)

Sql::operator& is deprecated. It makes the statment throw exception, but that approach was not quite as useful as it seemed. Use * and test for error aftewards...

Mirek

[Updated on: Fri, 02 October 2020 11:40]

Report message to a moderator

Re: sqlarray and sqlite [message #54972 is a reply to message #54819]

Fri, 02 October 2020 15:13

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Thank you again - the help I've been getting from you and the others on this forum have made this all possible Smile

Smile

I have some time this afternoon to do some more work so I will take these pointers to heart.

Report message to a moderator

Re: sqlarray and sqlite [message #54976 is a reply to message #54819]

Sat, 03 October 2020 00:17

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

I have eliminated the need for the line item convert functions, and cleaned up more code in lineitems & createinvoice Smile

Smile

Thank you!

I'm keeping github updated, https://github.com/phoenixjim/Invoices - easy access for all.

Report message to a moderator

Re: sqlarray and sqlite [message #54998 is a reply to message #54976]

Sun, 04 October 2020 10:05

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

I think you should get your Values right....

StrDbl(arrayLineItems.GetColumn(i,2).ToString())

This is really not how we meant it Smile

Smile

In general, GetColumn returns Value and if you have put double into ArrayCtrl or SqlArray, it will return double Value back. So normally above should be something like

(double)arrayLineItems.GetColumn(i,2);

Also

 	SQL * Select(CUST_ID, CUSTNAME).From(CUSTOMERS);
 	while (SQL.Fetch())
 	{
 		cbCustomers.Add(~SQL[CUST_ID], ~SQL[CUSTNAME]);
 	}

operator~ in this context converts perfectly good typed Value into String. Probably not something I would do to CUST_ID.

if (txtPrice.GetData().IsNull()) return;

You can just write IsNull(txtPrice). Instead of GetData, consider using operator~ (that in U++ has general meaning something like "other value representation").

arrayLineItems.AddColumn("Description", 80);
....
(DESCRIPTION, arrayLineItems.GetColumn(i,1).ToString())

Sometimes it is worth providing IDs even if you are not directly dealing with SQL, leads to cleaner code

arrayLineItems.AddColumn(DESCRIPTION, "Description", 80);
....
(DESCRIPTION, arrayLineItems.GetColumn(i,DESCRIPTION))

(ToString here is not needed as well as AFAIK it is already String).

	SQL * Select(TAXABLE).From(CUSTOMERS).Where(CUST_ID == idNum);
	SQL.Fetch();
	optCustTaxable.Set(SQL[0]);

As this is pretty common code, there is a shortcut:

	optCustTaxable.Set(SQL % Select(TAXABLE).From(CUSTOMERS).Where(CUST_ID == idNum));

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #55005 is a reply to message #54819]

Sun, 04 October 2020 17:27

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Again, Thank you Mirek!
Quote:

Sometimes it is worth providing IDs even if you are not directly dealing with SQL, leads to cleaner code

arrayLineItems.AddColumn(DESCRIPTION, "Description", 80);
....
(DESCRIPTION, arrayLineItems.GetColumn(i,DESCRIPTION))

Only one issue when I implement these, I keep getting:

102	if (optCustTaxable.Get() == true && arrayLineItems.GetColumn(i, ISTAXABLE)  == 1) {
103	     taxable = round((double)arrayLineItems.GetColumn(i, PRICE) * (double)arrayLineItems.GetColumn(i, QTY), 2);

/home/james/upp/MyApps/Invoices/UI/CreateInvoice.cpp (102): error: no viable conversion from 'Upp::SqlId' to 'int'
/home/james/upp/MyApps/Invoices/UI/CreateInvoice.cpp (103): error: no viable conversion from 'Upp::SqlId' to 'int'
...
/home/james/upp/MyApps/Invoices/UI/CreateInvoice.cpp (171): error: no viable conversion from 'Upp::SqlId' to 'int'

Every GetColumn call in the code generates this error?

Jim

Report message to a moderator

Re: sqlarray and sqlite [message #55006 is a reply to message #55005]

Sun, 04 October 2020 19:56

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

Sorry, you need to call Get(i, DESCRIPTION).

Mirek

Report message to a moderator

Re: sqlarray and sqlite [message #55008 is a reply to message #54819]

Sun, 04 October 2020 20:37

jimlef
Messages: 90
Registered: September 2020
Location: US

Member

Ahh, that's the ticket Smile

Smile

Thanks!

Report message to a moderator

Previous Topic:	Friends? Cousins? Half-siblings?
Next Topic:	how to make an editfield accept only number or date? have any examples in the documentation?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

PDF

]

Current Time: Thu Sep 10 16:08:04 GMT+2 2026

Total time taken to generate the page: 0.01880 seconds