|
|
Home » U++ Library support » U++ SQL » SqlArray Join
SqlArray Join [message #4061] |
Wed, 19 July 2006 02:47 |
|
I just found U++ yesterday, so this is all new to me.
The program I am working on has a SqLite3 backend with two tables. One table references the other (like a foreign key). I was working on implementing this when I found the Join() functions. I could not, however, make these work as I expected. Are there any examples? It would be great to see the Address book example written with a SqLite backend.
Also, I'm not sure I even understand what SqlDetails does, much less how to use it. Can anyone shed some light on this?
Thanks.
|
|
|
Re: SqlArray Join [message #4062 is a reply to message #4061] |
Wed, 19 July 2006 07:27 |
|
Ok, so after playing with it some more I figured it out somewhat.
My SqlArray's are now properly Join'ed and working as they should. Now how do I set the SqlArray to allow inserts, deletes, and modifications? I think for modifications I can do something like
EditString editDesc;
AddColumn("DESCRIPTION","Description").Edit(editDesc);
which seems to work ok.
I also have SqlDetail setup to populate some EditStrings with the values. Now how can I make it so edits to these are saved back to the database?
[Updated on: Wed, 19 July 2006 07:28] Report message to a moderator
|
|
|
|
Re: SqlArray Join [message #4081 is a reply to message #4068] |
Wed, 19 July 2006 17:07 |
|
luzr wrote on Wed, 19 July 2006 05:11 | If what you want to do is to edit some columns of record of single table in SqlArray rows and rest in separate Ctrls, just add those Ctrls to SqlArray (ArrayCtrl::AddCtrl).
|
Ok, I tried that but there are a few problems. The control I added is not automatically populated with the value from the database- should it be or do I have to do this manually?
This is why I was using SqlDetail, I have a 1-to-1 because I am editing the same row on the same table. Maybe not what it was designed for, but it seems to work here.
I'm sure I'll have some more questions in a little bit.
Let me also add that I had to do some core hacking to even get it to compile (I'm using the Win32 package that includes MinGW). The SqLite3 package referenced the DocTypes package which complained about a missing DocTypes.h. I removed those dependencies. It then had problems with ambiguous calls that I resolved by adding TRUE to them.
|
|
|
|
Re: SqlArray Join [message #4086 is a reply to message #4082] |
Wed, 19 July 2006 18:36 |
|
luzr wrote on Wed, 19 July 2006 11:13 | It should be. How are you trying that? The right approach is
SqlArray table;
...
table.AddCtrl(COLUMNID, myctrl);
|
I have the SqlArray named "CListView" on a TabCtrl named "tab" with a tab named "list". I used the designer to add a StringEdit called "strName1" to the list layout.
list.CListView.AddCtrl("NAME",list.strName1);
I eventually want to move the controls to a different tab, but I have them here for testing.
When I navigate to different rows, it does look like the control might be refreshing, but there is nothing there. It will save data to the database, but not show it.
luzr wrote on Wed, 19 July 2006 11:13 | Strange, what U++ version?
|
The current release, 605, with MinGW bundled.
Thanks for your help.
|
|
|
Re: SqlArray Join [message #4093 is a reply to message #4086] |
Wed, 19 July 2006 21:17 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
rbmatt wrote on Wed, 19 July 2006 12:36 |
luzr wrote on Wed, 19 July 2006 11:13 | It should be. How are you trying that? The right approach is
SqlArray table;
...
table.AddCtrl(COLUMNID, myctrl);
|
I have the SqlArray named "CListView" on a TabCtrl named "tab" with a tab named "list". I used the designer to add a StringEdit called "strName1" to the list layout.
list.CListView.AddCtrl("NAME",list.strName1);
I eventually want to move the controls to a different tab, but I have them here for testing.
When I navigate to different rows, it does look like the control might be refreshing, but there is nothing there. It will save data to the database, but not show it.
luzr wrote on Wed, 19 July 2006 11:13 | Strange, what U++ version?
|
The current release, 605, with MinGW bundled.
Thanks for your help.
|
Hard to say. Can you pack and send the whole package?
|
|
|
Re: SqlArray Join [message #4095 is a reply to message #4093] |
Wed, 19 July 2006 22:53 |
|
luzr wrote on Wed, 19 July 2006 15:17 | Hard to say. Can you pack and send the whole package?
|
Thanks for looking into it. Make sure you have simple2.db in the same folder you run it from.
|
|
|
Re: SqlArray Join [message #4096 is a reply to message #4095] |
Wed, 19 July 2006 23:48 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
OMG:
#include <SqlCtrl/SqlArray.cpp>
#include <SqlCtrl/SqlDetail.cpp>
Should be
#include <SqlCtrl/SqlCtrl.h>
If I compile it that way (minus menu line commented out...), it works as I would expect.
BTW,
// Kinda dumb that we have to do this
// isn't there a better way?
SqlId JOBID("JOBID");
SqlId NAME("NAME");
SqlId CUSTOMER_ID("CUSTOMER_ID");
You #define something like this (actually, in TSql package it is defined, but 'T' packages are somewhat 'less canonical'):
#define SQLID(x) const SqlId x(#x);
Anyway, the best way is to do db schema in U++ and get those defines for free.
Mirek
[Updated on: Wed, 19 July 2006 23:53] Report message to a moderator
|
|
|
Re: SqlArray Join [message #4098 is a reply to message #4096] |
Thu, 20 July 2006 00:09 |
|
Must be some Mirek Magic
I made the change to the #include's and commented out my menu line (was testing and I forgot to remove it before I packaged it) but no luck. It still doesn't work here. Just a blank box that has some hidden functionality. Are you using MinGW to compile or MS VC++?
|
|
|
Re: SqlArray Join [message #4108 is a reply to message #4098] |
Fri, 21 July 2006 06:35 |
|
I just installed MS VC++ Express and the Windows SDK, and I get the same results. The test name box is not populated with the name. Interesing though, if you enter something in the box, it will show that in the box for all subsequent times you view that row. It also saves that to the database.
|
|
|
Re: SqlArray Join [message #4112 is a reply to message #4108] |
Fri, 21 July 2006 14:33 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
OK, I hope I have found it.
The trouble is that bug exposes only in the start (you double-click on first record). I have tested after clicking to another row - there it was OK.
Anyway, the bug is caused by wrong placement of
You are performing the query _before_ you join with detail. Query sets the cursor to the first row, means detail will not get loaded for this row....
Putting it at the end of constructor fixed the problem.
I have taken the oportunity a tried to improve the code a bit:
#include <CtrlLib/CtrlLib.h>
#include <SqlCtrl/SqlCtrl.h>
#include <plugin/Sqlite3/Sqlite3.h>
#define LAYOUTFILE <EzellApp4/EzellApp4.lay>
#include <CtrlCore/lay.h>
class EzellApp4 : public WithEzellApp4Layout<TopWindow> {
WithListLayout<ParentCtrl> list;
WithDetailLayout<ParentCtrl> detail;
// The SQLite session
// For editing
EditDate editDate;
EditString editDesc;
EditString editCost;
public:
typedef EzellApp4 CLASSNAME;
void SwitchView();
void CrossUpdate(int);
EzellApp4();
};
void EzellApp4::CrossUpdate(int i)
{
// In progress
PromptOK("Yea, OK");
list.CListView.Refresh();
}
EzellApp4::EzellApp4()
{
// Setup the layouts
CtrlLayout(*this, "Bean Heat and Air- Customer Files");
CtrlLayout(list);
CtrlLayout(detail);
Sizeable();
Zoomable();
// Tell tab control what slave ctrls to use
tab.Add(list, "Customers");
tab.Add(detail, "Details");
// Kinda dumb that we have to do this
// isn't there a better way?
SqlId JOBID("JOBID");
SqlId NAME("NAME");
SqlId CUSTOMER_ID("CUSTOMER_ID");
// Setup primary customer list
list.CListView.SetTable("CUSTOMERS");
list.CListView.AddColumn("ID", "Id");
list.CListView.HeaderObject().HideTab(0);
list.CListView.AddColumn("NAME","Name");
list.CListView.AddColumn("ADDRESS","Address");
list.CListView.AddColumn("CSZ","City, State Zip");
list.CListView.AddColumn("PHONE1","Phone 1");
list.CListView.AddCtrl("NAME",list.strName1);
list.CListView.AddCtrl("NAME", detail.strName);
list.CListView.AddCtrl("ADDRESS", detail.strAddress);
list.CListView.AddCtrl("CSZ", detail.strCSZ);
list.CListView.SetOrderBy(NAME);
list.CListView.WhenLeftDouble = THISBACK(SwitchView);
detail.detailList.SetTable("JOBS");
detail.detailList.AddColumn("JOBID","Id");
detail.detailList.HeaderObject().HideTab(0);
detail.detailList.AddColumn("DONEDATE","Date").Edit(editDate);
detail.detailList.AddColumn("DESCRIPTION","Description").Edit(editDesc);
detail.detailList.AddColumn("COST","Cost").Edit(editCost);
detail.detailList.SetOrderBy(JOBID);
detail.detailList.Join(CUSTOMER_ID, list.CListView);
detail.detailList.Removing().Appending();
list.CListView.Query();
}
void EzellApp4::SwitchView()
{
tab.Set(1);
}
GUI_APP_MAIN
{
Sqlite3Session session;
if(!session.Open(ConfigFile("simple2.db"))) {
Exclamation("Error opening the database file");
return;
}
#ifdef _DEBUG
session.SetTrace();
session.LogErrors(true);
#endif
SQL = session;
EzellApp4().Run();
}
Changes:
SqlDetails are really not necessary.
Most application do not use more than single DB engine, therefore it is a good ideal to define default session - you do not have to se it everywhere ("SQL=session").
Opening session in constructor is really bad thing
BTW, is your database schema fixed in existing .db file, or is it a part of application you are developing? U++ has nice support for schemas... I would be happy to help you with that...
...because, in fact, it would not be a bad idea to finally add some nice SQL example to U++
Mirek
|
|
|
Re: SqlArray Join [message #4115 is a reply to message #4112] |
Fri, 21 July 2006 17:16 |
|
luzr wrote on Fri, 21 July 2006 08:33 | OK, I hope I have found it.
| Thanks so much
luzr | Anyway, the bug is caused by wrong placement of
You are performing the query _before_ you join with detail. Query sets the cursor to the first row, means detail will not get loaded for this row....
Putting it at the end of constructor fixed the problem.
| Makes sense now
luzr | I have taken the oportunity a tried to improve the code a bit:
| Wow, thanks a million.
luzr | Most application do not use more than single DB engine, therefore it is a good ideal to define default session - you do not have to se it everywhere ("SQL=session").
|
Makes sense, good tip.
luzr | Opening session in constructor is really bad thing
|
I guess this is also because there will only be one DB session but possibly multiple instances of my app window, also performance.
luzr | BTW, is your database schema fixed in existing .db file, or is it a part of application you are developing? U++ has nice support for schemas... I would be happy to help you with that...
|
I have lots of data that is currently in a spreadsheet. I will use php or perl to parse it out into a usable CSV or something similar. The actual structure of the DB can still be changed. The reason I did not opt for schemas was because I thought it might add a little extra (code size, memory usage) to the final product. If all the processing is pre-compile, it might be beneficial to add it in here. Advice?
luzr | ...because, in fact, it would not be a bad idea to finally add some nice SQL example to U++
|
I'll be glad to offer my app as a tutorial and help write it.
Thanks again
|
|
|
Re: SqlArray Join [message #4117 is a reply to message #4115] |
Sat, 22 July 2006 07:42 |
|
Ok, so I worked through several things today.
I added schema support so while in debug mode it will update my schema based on the .sch file. Then I use sch to automatically define all my SqlId's.
I found out why I was having a problem with data reverting to old states. Got that (mostly) fixed.
I don't think there is a way for me to manually make the changes to the database (short of actually writing the code ). What I mean is UpdateRow() is protected so I cannot call it. There are no member functions that say "write this to the db" that I see.
Also inserting does not seem to be working. I traced the code as best I could.
The program does a late insert but somehow it looks like it only wants to do an update. The update code looks like:
update CUSTOMERS set NAME = 'test', ADDRESS = NULL, CSZ = NULL, PHONE1 = NULL, NAME = 'test' where ID is NULL
Well, there is no entry where ID is NULL so nothing happens.
And to get the autonumber we need to set ID = NULL which it does not do.
I've attached my new version.
Thanks.
P.S. What are the lines in the DocEdit? Is there a way to turn that off?
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 04 10:29:48 CEST 2024
Total time taken to generate the page: 0.02570 seconds
|
|
|