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












SourceForge.net Logo
Home » U++ Library support » U++ SQL » 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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11700
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 #19154 is a reply to message #19152] Sat, 15 November 2008 15:48 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

I understand the idea and faced similar issues in other environments.

And the solution is produced in general is simple and beautiful.

Just forget about the context reentrant.

Thanks for comment.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 #20498 is a reply to message #18765] Fri, 20 March 2009 21:37 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 790
Registered: November 2005
Location: Czech Republic - Praha
Contributor

if (f > 0) ?
Find returns position of element (0..N-1) or negative value if not found.

See http://www.ultimatepp.org/src$Core$AMap$en-us.html#AMap..cla ss
Re: How to display fields from different tables in one SQLArray [message #20501 is a reply to message #20498] Sat, 21 March 2009 08:04 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

Oh!!! yes !!!

Thanks.. Razz


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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 #23154 is a reply to message #23153] Sat, 19 September 2009 19:30 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 304
Registered: July 2009
Senior Member
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?
Re: template + convert problem [message #23155 is a reply to message #23154] Sat, 19 September 2009 19:59 Go to previous messageGo to next message
andrei_natanael is currently offline  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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 304
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 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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 Go to previous message
sergeynikitin is currently offline  sergeynikitin
Messages: 735
Registered: January 2008
Location: Moscow, Russia
Contributor

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

Previous Topic: DBF crash error while writing record
Next Topic: PATCH/BUGFIX Oracle asTable Failure - Solution
Goto Forum:
  


Current Time: Sat Feb 16 12:51:23 CET 2019

Total time taken to generate the page: 0.00695 seconds