[Gambas-user] Problem with MySQL LONGTEXT field type

[Luigi Carlotto]

Il giorno ven, 02/01/2009 alle 12.27 +0100, Luigi Carlotto ha scritto:

> Il giorno lun, 22/12/2008 alle 21.41 +0100, Luigi Carlotto ha scritto:
> 
> > Il giorno lun, 22/12/2008 alle 20.51 +0100, Luigi Carlotto ha
> > scritto:
> > 
> > > Il giorno lun, 22/12/2008 alle 20.13 +0100, Luigi Carlotto ha
> > > scritto:
> > > 
> > > > It pardons me, but the table in object is not part of mine
> > > > database, but of the same motor of MySQL.
> > > > The fact that is complete with the data, or without data, is not
> > > > the true problem.
> > > > My function reads the content of this table in order to
> > > > determine the structure of a database, and not the data.
> > > > The data contained in this table, come inserted automatically
> > > > from the motor of MySQL, when they come created objects in the
> > > > database.
> > > > 
> > > > In the normal situation, the table contains the information
> > > > approximately the structure of the objects that MySQL creates of
> > > > default, when it comes created database any.
> > > > The table “routines” is of property of the “information_schema”
> > > > schema, that it is of property of MySQL, and on which writing
> > > > operations cannot be made, but only of reading.
> > > > The same library of Gambas2, uses this table for populate the
> > > > objects “Table”, “Field”, etc
> > > > 
> > > > To this point, it still pardons me, I I do not believe that to
> > > > send the dump of this table he is of great usefullness since, if
> > > > an installation of MySQL is had, it is possible to verify the
> > > > problem directly.
> > > > As creed of to have written, the problem does not seem that or
> > > > caused from the reading of the field, but its maintenance in
> > > > memory, if managed from a complex application… This, obviously,
> > > > is only a hypothesis…
> > > > 
> > > > Thanks to you for the patience!
> > > 
> > > 
> > > In attached the dump of the database of test.
> > > A table is present only...
> > > 
> > > Thanks
> > > 
> > 
> > 
> > The dump it contains a single object of the database of test, a
> > table, and only little records.
> > As I have said, my application does not use the content of the
> > table, but it gains its structure from the tables of system of
> > MySQL.
> > My application uses the structure of the database in order to
> > represent in graphical way the layout of the database, the data is
> > not useful to such scope.
> > 
> > In order to make this operation, it is connected to the database
> > and, through the system tables, it gains the information of the
> > structure of the database.
> > The application can be connected to the types of database:
> > PostgreSQL, MySQL and SQLite.
> > Various from PostgreSQL, some tables of MySQL use fields of type
> > longtext (blob), for which it is necessary to read these fields, for
> > being able to determine, as an example: name of the field, type,
> > dimensions, COMMENT, etc
> > 
> > This happens in the same way with bookcases of Gambas? I believe
> > thus that or, at least from the reading of the code source. Also
> > phpPgAdmin (PHP) executes query on these tables of system, in order
> > to represent the objects contained in the database; same thing in
> > phpMyAdmin.
> > 
> 
> 
> I have executed of the tests, to line of commando from Linux, in order
> to understand the reason for which in Gambas they do not come loaded
> the fields “longtext” from the database “information_schema” with
> MySQL.
> 
> The error, to how much seems, does not seem derived from an anomaly of
> Gambas, but from an anomaly of MySQL, that is, executing the logon and
> the query on the table “information_schema”, like customer root (and
> with the commando “mysql”), the data come extracted all in complete
> and corrected way; if the same operation comes carried out with an
> other customer (not administrative), the type fields “longtext” they
> turn out NULL.
> 
> I have verified the same thing with Gambas and, in effects, it is
> behaved in the same way.
> I have controlled the MySQL logon parameters, so as to verify if some
> various formulation between the several customers exists, but have not
> found some difference.
> I have verified if the behavior were identical in phpMyAdmin (a
> program PHP of management for MySQL), but strangely, the data comes
> always visualized in corrected way, with any customer.
> 
> I have controlled code PHP, but some formulation does not exist, that
> it can condition such behavior.
> To this point, the anomaly sure is caused from MySQL, but I do not
> succeed to understand like resolving the problem.
> Also I have tried to give GRANT to the customers, but for the outline
> “information_schema” it is not possible, since this is a database of
> system.
> 
> How it is possible to resolve this problem?
> 


Examples:

USER: root (mysql --host=localhost --port=5432 --user=root --password
--database=test)
mysql> SELECT routine_name, routine_type, routine_definition FROM
information_schema.routines WHERE routine_name = 'hello' AND
routine_type = 'FUNCTION'   AND routine_schema = 'test';
+--------------+--------------+--------------------------------+
| routine_name | routine_type | routine_definition             |
+--------------+--------------+--------------------------------+
| hello        | FUNCTION     | return concat('Hello, ',s,'!') |
+--------------+--------------+--------------------------------+
1 row in set (0.00 sec)

mysql>

USER: test (mysql --host=localhost --port=5432 --user=test --password
--database=test)
mysql> SELECT routine_name, routine_type, routine_definition FROM
information_schema.routines WHERE routine_name = 'hello' AND
routine_type = 'FUNCTION'   AND routine_schema = 'test';
+--------------+--------------+--------------------+
| routine_name | routine_type | routine_definition |
+--------------+--------------+--------------------+
| hello        | FUNCTION     | NULL               |
+--------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql>