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 » Community » Newbie corner » sqlarray and sqlite (Trying to use sqlite example but...)
sqlarray and sqlite [message #54819] Fri, 18 September 2020 22:49 Go to next message
jimlef is currently offline  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
Re: sqlarray and sqlite [message #54825 is a reply to message #54819] Sat, 19 September 2020 05:50 Go to previous messageGo to next message
jimlef is currently offline  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.
Re: sqlarray and sqlite [message #54826 is a reply to message #54819] Sat, 19 September 2020 06:57 Go to previous messageGo to next message
jimlef is currently offline  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...
Re: sqlarray and sqlite [message #54827 is a reply to message #54819] Sat, 19 September 2020 08:04 Go to previous messageGo to next message
jimlef is currently offline  jimlef
Messages: 90
Registered: September 2020
Location: US
Member
Last post of the evening, I promise 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 ...
Re: sqlarray and sqlite [message #54845 is a reply to message #54827] Mon, 21 September 2020 08:19 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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.
Re: sqlarray and sqlite [message #54850 is a reply to message #54845] Mon, 21 September 2020 22:54 Go to previous messageGo to next message
jimlef is currently offline  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!
Re: sqlarray and sqlite [message #54851 is a reply to message #54819] Tue, 22 September 2020 03:05 Go to previous messageGo to next message
jimlef is currently offline  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 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

Thanks y'all Smile

EDIT: I just realized that I didn't add _ to TABLE... Fixed it 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 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: sqlarray and sqlite [message #54854 is a reply to message #54852] Tue, 22 September 2020 17:43 Go to previous messageGo to next message
jimlef is currently offline  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
Re: sqlarray and sqlite [message #54855 is a reply to message #54854] Tue, 22 September 2020 17:54 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: sqlarray and sqlite [message #54856 is a reply to message #54855] Tue, 22 September 2020 18:07 Go to previous messageGo to next message
jimlef is currently offline  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...

Re: sqlarray and sqlite [message #54857 is a reply to message #54856] Tue, 22 September 2020 18:47 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: sqlarray and sqlite [message #54859 is a reply to message #54819] Tue, 22 September 2020 20:41 Go to previous messageGo to next message
jimlef is currently offline  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();

}


Re: sqlarray and sqlite [message #54873 is a reply to message #54859] Wed, 23 September 2020 16:32 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: sqlarray and sqlite [message #54876 is a reply to message #54873] Wed, 23 September 2020 17:14 Go to previous messageGo to next message
jimlef is currently offline  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 119 times)
Re: sqlarray and sqlite [message #54880 is a reply to message #54819] Wed, 23 September 2020 19:57 Go to previous messageGo to next message
jimlef is currently offline  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
index.php?t=getfile&id=6219&private=0
  • Attachment: latest.png
    (Size: 477.06KB, Downloaded 406 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 Go to previous messageGo to next message
jimlef is currently offline  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
Jim

Re: sqlarray and sqlite [message #54884 is a reply to message #54882] Thu, 24 September 2020 13:46 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: sqlarray and sqlite [message #54885 is a reply to message #54884] Thu, 24 September 2020 16:40 Go to previous messageGo to next message
jimlef is currently offline  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
Re: sqlarray and sqlite [message #54886 is a reply to message #54819] Thu, 24 September 2020 19:28 Go to previous messageGo to previous message
jimlef is currently offline  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 120 times)
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:
  


Current Time: Fri Mar 29 06:17:29 CET 2024

Total time taken to generate the page: 0.01529 seconds