Home » U++ Library support » U++ SQL » How to display fields from different tables in one SQLArray
| Re: How to display fields from different tables in one SQLArray [message #19120 is a reply to message #19110] |
Thu, 13 November 2008 11:26   |
|
|
| luzr wrote on Thu, 13 November 2008 00:23 |
| sergeynikitin wrote on Sun, 02 November 2008 16:09 |
By the way, I have decided to how to display related fields from other tables by using SqlArray.
[code]
struct ConvCompany : Convert
{
Value Format(const Value &q) const
{
SQL * Select(COM_NAME).From(COMPANY).Where(COM_ID == q);
String company;
if(SQL.Fetch())
company = SQL[COM_NAME];
else
company = "";
return q.IsNull() ? Null : company;
}
};
|
WARNING: Fetching from SQL ("SQL.Fetch()") is NOT RECOMMENDED (based on expriences:)
It is way too simple to call something in the fetch loop which will issue different SQL statment through SQL.
Use "Sql sql; sql * Select....; sql.Fetch()" instead.
|
What do you mean? What is the difference between SQL and sql?
In my app I use this initialization code:
MySqlSession session;
if(!session.Connect(app.mysql_username, app.mysql_password, app.mysql_database, app.mysql_server)) {
printf("Can't connect with MySql\n");
PromptOK(t_("Can't connect with MySql"));
return;
}
mysql_set_character_set(session, "utf8");
SQL = session;
Sql sql(session);
If you can - I want a short comment about the differences of my decision and your.
Yes. perhaps this is the best solution - to write complex pieces by hands.
By the way, thanks for the complex example.
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
| Re: How to display fields from different tables in one SQLArray [message #19152 is a reply to message #19120] |
Sat, 15 November 2008 15:02 |
 |
mirek
Messages: 14255
Registered: November 2005
|
Ultimate Member |
|
|
| sergeynikitin wrote on Thu, 13 November 2008 05:26 |
What do you mean? What is the difference between SQL and sql?
|
I mean that SQL is global variable (well, technically, it is more comlicated, but it behaves just like global variable).
And the inherent problem with global variables is that they are problem in reentrant code.
void Sum(int id) {
return SQL % Select(SqlSum(MONEY)).From(TABLE).Where(ID == id);
}
void PrintSums() {
SQL * Select(NAME, MONEY_ID).From(TABLE);
while(SQL.Fetch())
Print(SQL[NAME], Sum(SQL[MONEY_ID]);
}
I have made this bug way too many times -> that is why I always have to warn about fetching from SQL.
| Quote: |
MySqlSession session;
if(!session.Connect(app.mysql_username, app.mysql_password, app.mysql_database, app.mysql_server)) {
printf("Can't connect with MySql\n");
PromptOK(t_("Can't connect with MySql"));
return;
}
mysql_set_character_set(session, "utf8");
SQL = session;
Sql sql(session);
|
This corrent and in fact, you do not need to repeat
Sql sql(session);
because
Sql sql;
is the same as
Sql sql(SQL.GetSession());
There are two purposes for SQL - to serve as simple cursor for inster/delete/update and inline fetches AND to represent default global database connection so that you do not need to repeat 'session' all the time.
Just do not do explicit fetches from it, because while technically possible, it is a sure way how to shoot you leg:)
Mirek
|
|
|
|
|
|
| Re: How to display fields from different tables in one SQLArray [message #20493 is a reply to message #19154] |
Fri, 20 March 2009 17:06 |
|
|
I went on the road, proposed in a previous message, and came across an odd job VectorMap. Here is a piece of code.
Value ConvNomencl::Format(const Value& q) const
{
static VectorMap<int, String> nom;
static Time lastcleartime;
if(GetSysTime()-lastcleartime > 60){
nom.Clear();
lastcleartime=GetSysTime();
}
if(nom.GetCount()==0) nom.Add(0, " "); // <==adding empty entry there.
int q1=q;
int f = nom.Find(q1);
if(f > 0){
String s;
s = nom.Get(q1);
return s;
} else {
Sql sql;
sql * SqlSelect(NOM_ID,NOM_NAME).From(NOMENCL).Where(NOM_ID == q);
String nomencl;
if(sql.Fetch()) {
int sid = sql[NOM_ID];
nom.Add(sid, sql[NOM_NAME]);
nomencl = sql[NOM_NAME];
} else {
nomencl = "";
}
return nomencl;
}
}
After creating or cleaning object VectorMap, the first added item can not be found by .Find() method, and therefore the number of elements corresponding to the first element is continuously growing.
However, if you add the first empty element - then all the rest are working properly. This is well intended, or is it wrong?
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
|
|
|
|
| Re: How to display fields from different tables in one SQLArray [message #22348 is a reply to message #20501] |
Tue, 07 July 2009 09:47 |
|
|
I want to say a special thank you for the construction with prefixes s_.
When many tables (about 5-10) with the same fields, the fields were named as NOM_NAME ED_NAME COM_NAME ...
And especially when the database is not mine and I can not rename a fields.
Overall, an excellent solution !
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
| template + convert problem [message #23153 is a reply to message #22348] |
Sat, 19 September 2009 14:22 |
|
|
I make a table in which multiple fields of reference from other tables. The mapping of the field from another table I made with the converter.
Now it looks as follows:
converts.h
struct ConvCompany : Convert
{
virtual Value Format(const Value& q) const;
};
struct ConvNomencl : Convert
{
virtual Value Format(const Value& q) const;
};
struct ConvManager : Convert
{
virtual Value Format(const Value& q) const;
};
converts.cpp:
Value ConvCompany::Format(const Value &q) const
{
if(q.IsNull()) return Null;
static VectorMap<int, String> comp;
static Time lastcleartime;
if(GetSysTime()-lastcleartime > 600){
comp.Clear();
lastcleartime=GetSysTime();
}
int f = comp.Find(int(q));
if(f >= 0){
return comp[f];
} else {
Sql sql;
sql * Select(COM_NAME).From(COMPANY).Where(COM_ID == q);
String company;
if(sql.Fetch()) {
int sid = int(q);
company = sql[COM_NAME];
comp.Add(sid, company);
} else {
company = "";
}
return company;
}
}
Value ConvNomencl::Format(const Value& q) const
{
if(q.IsNull()) return Null;
static VectorMap<int, String> nom;
static Time lastcleartime;
if(GetSysTime()-lastcleartime > 600){
nom.Clear();
lastcleartime=GetSysTime();
}
int f = nom.Find(int(q));
if(f >= 0){
return nom[f];
} else {
Sql sql;
sql * SqlSelect(NOM_ID,NOM_NAME).From(NOMENCL).Where(NOM_ID == q);
String nomencl;
if(sql.Fetch()) {
int sid = sql[NOM_ID];
nomencl = sql[NOM_NAME];
nom.Add(sid, nomencl);
} else {
nomencl = "";
}
return nomencl;
}
}
Value ConvManager::Format(const Value &q) const
{
if(q.IsNull()) return Null;
static VectorMap<int, String> man;
static Time lastcleartime;
if(GetSysTime()-lastcleartime > 600){
man.Clear();
lastcleartime=GetSysTime();
}
int f = man.Find(int(q));
if(f >= 0){
return man[f];
} else {
Sql sql;
sql * SqlSelect(USR_REALNAME).From(USER).Where(USR_ID == q);
String manager;
if(sql.Fetch()) {
int sid = int(q);
manager = sql[USR_REALNAME];
man.Add(sid, manager);
} else {
manager = "";
}
return manager;
}
}
These converters differ only in the name of the table and field names. I want this to be in the form of a template, something like:
template <SqlId TBL ,SqlId ID, SqlId NAME,int CLEARTIME>
struct ConvDict : Convert
//ConvDict<COMPANY,COM_ID,COM_NAME,600>()
{
virtual Value Format(const Value& q) const {
if(q.IsNull()) return Null;
static VectorMap<int, String> dict;
static Time lastcleartime;
if(GetSysTime()-lastcleartime > CLEARTIME){
dict.Clear();
lastcleartime=GetSysTime();
}
int f = dict.Find(int(q));
if(f >= 0){
return dict[f];
} else {
Sql sql;
sql * Select(NAME).From(TBL).Where(ID == q);
String s;
if(sql.Fetch()) {
int sid = int(q);
s = sql[NAME];
dict.Add(sid, s);
} else {
s = "";
}
return s;
}
}
};
But this code is not compiled, wrote that at this point can not be applied SqlId.
Can someone experienced with this?
How do make a template for the Converter, or Display?
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
|
|
| Re: template + convert problem [message #23155 is a reply to message #23154] |
Sat, 19 September 2009 19:59 |
andrei_natanael
Messages: 262
Registered: January 2009
|
Experienced Member |
|
|
You don't really need a template for this. You may create a setter and initialize the converter with proper values. Something like...
struct ConvDict : Convert
{
SqlId TBL ,ID, NAME, CLEARTIME;
public:
void setValues(SqlId _TBL ,SqlId _ID, SqlId _NAME,int _CLEARTIME)
{
TBL = _TBL;
ID = _ID;
NAME = _NAME;
CLEARTIME = _CLEARTIME;
}
// ...
The only inconvenient is that you have to call setValues before using converter.
[Updated on: Sat, 19 September 2009 20:20] Report message to a moderator |
|
|
|
| Re: template + convert problem [message #23156 is a reply to message #23154] |
Sat, 19 September 2009 22:37 |
|
|
| Zbych wrote on Sat, 19 September 2009 21:30 |
| sergeynikitin wrote on Sat, 19 September 2009 14:22 |
The mapping of the field from another table I made with the converter.
|
Can you tell me why you didn't use 'join', 'where' or 'view', to connect those tables?
|
The reasons may be several:
1. SqlArray is not support join
2. This method saves SQL trafic and network speed on Huge Database.
3. View may not be fully compatible with some database engine.
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
| Re: template + convert problem [message #23157 is a reply to message #23155] |
Sat, 19 September 2009 22:45 |
|
|
| andrei_natanael wrote on Sat, 19 September 2009 21:59 |
You don't really need a template for this. You may create a setter and initialize the converter with proper values. Something like...
struct ConvDict : Convert
{
SqlId TBL ,ID, NAME, CLEARTIME;
public:
void setValues(SqlId _TBL ,SqlId _ID, SqlId _NAME,int _CLEARTIME)
{
TBL = _TBL;
ID = _ID;
NAME = _NAME;
CLEARTIME = _CLEARTIME;
}
// ...
The only inconvenient is that you have to call setValues before using converter.
|
template is interesting because you can create separate converters for different reference tables designating the tables at the time of use in the code.
(I once again repeat - a lot of reference tables, and many links to them from the main table.)
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
| Re: template + convert problem [message #23163 is a reply to message #23156] |
Sun, 20 September 2009 15:47 |
Zbych
Messages: 327
Registered: July 2009
|
Senior Member |
|
|
| sergeynikitin wrote on Sat, 19 September 2009 22:37 |
The reasons may be several:
1. SqlArray is not support join
|
You can always connect tables using 'where', or just use 'join' + ArrayCtrl and fill it "manually". It is simpler than your solution.
| Quote: |
2. This method saves SQL trafic and network speed on Huge Database.
|
If you have huge database, than you usually don't display everything at once. Beside, I guess, it is faster to send one query with 1000 rows, that 1000 queries with 1 row each.
| Quote: |
3. View may not be fully compatible with some database engine.
|
Than you should use databases, that supports views.
[Updated on: Sun, 20 September 2009 15:48] Report message to a moderator |
|
|
|
| Re: template + convert problem [message #23169 is a reply to message #23163] |
Mon, 21 September 2009 07:34 |
|
|
| Zbych wrote on Sun, 20 September 2009 17:47 |
| Quote: |
2. This method saves SQL trafic and network speed on Huge Database.
|
If you have huge database, than you usually don't display everything at once. Beside, I guess, it is faster to send one query with 1000 rows, that 1000 queries with 1 row each.
| Quote: |
3. View may not be fully compatible with some database engine.
|
Than you should use databases, that supports views.
|
What's about decision for notebook+mobile phone. If I use converters, theò users almost do not notice a slow connection, because each row of the table passed 5 integer numbers.
If I use a view or LeftJoin, then each row of the table must be sent to 100 bytes (5 fields of 20 bytes), that is slower to 10 times.
SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
|
|
|
|
| Re: template + convert problem [message #44632 is a reply to message #23169] |
Thu, 30 April 2015 07:55 |
|
|
I've made this template!!!
Universal Lookup Convertor syntax like this:
(Single<ConvLookup<COMPANY,COM_ID,COM_NAME> >() )
Let's test:
dict.h
template<typename T> T& LookupSetup ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid = false, bool valueiscode = true, SqlBool where = true );
template<> DropGrid& LookupSetup ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where );
template<> WithDropChoice<EditString>& LookupSetup ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where );
template<> DropList& LookupSetup ( DropList& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where );
template<typename T> T& LookupSetup1 ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid = false, bool valueiscode = true, SqlBool where = true );
template<> DropGrid& LookupSetup1 ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where );
template<> WithDropChoice<EditString>& LookupSetup1 ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where );
template< SqlId &LookupTable, SqlId &LookupCode, SqlId &LookupValue, bool show_id=false >
struct ConvLookup : Convert
{
Value Format ( const Value &q ) const
{
int sid = q;
if ( q.IsNull() ){
return Null;
} else if ( q == 0 ) {
return "";
}
static VectorMap<int, Value> valuecache;
static Time lastcleartime;
if ( GetSysTime() - lastcleartime > 600 )
{
valuecache.Clear();
lastcleartime = GetSysTime();
}
if ( valuecache.Get ( sid, "X") != "X" )
{
if (show_id) {
return AsString ( sid ) + ": " + AsString(valuecache.Get ( sid, Value() ));
} else {
return valuecache.Get ( sid, Value() );
}
}
else
{
Sql sql;
sql * Select ( LookupValue ).From ( LookupTable ).Where ( LookupCode == sid );
if ( sql.Fetch() )
{
valuecache.Add ( sid, AsString(sql[LookupValue]) );
if ( valuecache.Get ( sid, "X" ) != "X" )
{
if (show_id) {
return AsString ( sid ) + ": " + AsString(valuecache.Get ( sid, Value() ) );
} else {
return valuecache.Get ( sid, Value() );
}
}
return sql[LookupValue] ;
}
else
{
return AsString ( sid ) + ": ??? " + t_(~LookupTable) + " ???" ;
}
}
return "ERROR RETURN PLACE" ;
}
};
dict.cpp
template<typename T> T& LookupSetup ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ) {}
template<> DropGrid& LookupSetup ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where )
{
Sql sql;
if ( grid.GetList().GetColumnCount() == 0 )
{
grid.ClearButton();
grid.Resizeable ( false );
grid.AddColumn ( colid, "", showid ? 10 : 0, true );
grid.AddColumn ( colname, showid ? " : " : "", 70 );
if ( showid )
grid.SetValueColumn ( 0 );
else
grid.SetValueColumn ( 1 );
if ( valueiscode )
{
grid.SetKeyColumn ( 0 );
}
else
{
grid.SetKeyColumn ( 1 );
}
if ( showid )
{
grid.AddValueColumn ( 0 );
}
grid.AddValueColumn ( 1 );
grid.SetFindColumn ( 1 );
grid.NoHeader();
grid.ColorRows();
}
grid.Clear();
sql * Select ( colid,
colname ).From ( tablename ).Where ( where ).OrderBy ( colid );
while ( sql.Fetch() )
{
grid.Add ( sql[colid], sql[colname] );
}
return grid;
}
template<> WithDropChoice<EditString>& LookupSetup ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where )
{
Sql sql;
grid.ClearList();
sql * Select ( colname ).From ( tablename ).Where ( where ).OrderBy ( colname );
while ( sql.Fetch() )
{
grid.AddList ( sql[colname] );
}
return grid;
}
template<> DropList& LookupSetup ( DropList& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where )
{
Sql sql;
if ( grid.GetList().GetColumnCount() == 0 )
{
grid.SetDropLines ( 12 );
}
grid.ClearList();
sql * Select ( colname ).From ( tablename ).Where ( where ).OrderBy ( colname );
while ( sql.Fetch() )
{
grid.Add ( sql[colname] );
}
return grid;
}
template<typename T> T& LookupSetup1 ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ) {}
template<> DropGrid& LookupSetup1 ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where )
{
Sql sql;
if ( grid.GetList().GetColumnCount() == 0 )
{
grid.ClearButton();
grid.Resizeable ( false );
grid.AddColumn ( colid, "", showid ? 10 : 0, true );
grid.AddColumn ( colname1, valueiscode ? " : " : "", 70 );
grid.AddColumn ( colname2, "", 70 );
if ( showid )
grid.SetValueColumn ( 0 );
else
grid.SetValueColumn ( 1 );
if ( valueiscode )
{
grid.SetKeyColumn ( 0 );
}
else
{
grid.SetKeyColumn ( 1 );
}
if ( showid )
{
grid.AddValueColumn ( 0 );
}
grid.AddValueColumn ( 1 );
grid.SetFindColumn ( 1 );
grid.NoHeader();
grid.ColorRows();
}
grid.Clear();
sql * Select ( colid,
colname1, colname2 ).From ( tablename ).Where ( where ).OrderBy ( colid );
while ( sql.Fetch() )
{
grid.Add ( sql[colid], sql[colname1], sql[colname2] );
}
return grid;
}
template<> WithDropChoice<EditString>& LookupSetup1 ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where )
{
Sql sql;
grid.ClearList();
sql * Select ( colname1, colname2 ).From ( tablename ).Where ( where ).OrderBy ( colname1 );
while ( sql.Fetch() )
{
grid.AddList ( AsString ( sql[colname1] ) + " " + AsString ( sql[colname2] ) );
}
return grid;
}
And in AddColumn if GridCtrl you may write as this:
listvisitor.AddColumn ( VISR_COMPANY_ID, t_("Company"), 265 ).SetConvert(Single<ConvLookup<COMPANY,COM_ID,COM_NAME> >()).HeaderAlignCenter();
[Updated on: Thu, 30 April 2015 07:57] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Apr 28 21:49:41 CEST 2025
Total time taken to generate the page: 0.00870 seconds
|
Members
Pages
Help
Register
Login
Home
