Devart Forums

thomas_w

This problem is back again (5.0.33)

SELECT saison
     , group_concat(art, ';', anzahl ORDER BY art SEPARATOR ';') AS summen
FROM
  obst
GROUP BY
  saison;

Export Result is correct:

Code: Select all

=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      |            Apfel;1;Bananen;3;Birnen;5            |
-------------------------------------------------------------------------------------------------------
|                      Winter                      |            Apfel;2;Bananen;4;Birnen;6            |
-------------------------------------------------------------------------------------------------------

but dbForge Result-Grid show some hex result like my first message.

with kind regards,
Thomas

thomas_w

Thanks!

thomas_w

.jp wrote:
-- Server version: 5.6.2-m5
By the way, MySQL 5.6.x
isn't supported by our tool either.

hm, I get the same problem using MySQL v.5.5.9.

Why did dbForge for MySQL not doing something like this?

Code: Select all

mysql> show create table geodaten_remote;
+-----------------+------------------------------------------------------------------+
| Table           | Create Table                                                                                                               |
+-----------------+------------------------------------------------------------------+
| geodaten_remote | CREATE TABLE `geodaten_remote` (
  `geodaten_id` int(11) NOT NULL,
  `latitude` int(10) unsigned NOT NULL,
  `longitude` int(10) unsigned NOT NULL,
  PRIMARY KEY (`geodaten_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user:passord@localhost:3307/sample/geodaten' |
+-----------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

It seems to be easy.

with kind regards,
Thomas

thomas_w

Perfekt! 5.0 work well...

Code: Select all

SELECT saison
     , group_concat(art, ';', anzahl ORDER BY art SEPARATOR ';') AS summen
FROM
  obst
GROUP BY
  saison; 


=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      |            Apfel;1;Bananen;3;Birnen;5            |
-------------------------------------------------------------------------------------------------------
|                      Winter                      |            Apfel;2;Bananen;4;Birnen;6            |
-------------------------------------------------------------------------------------------------------

with kind regards,
Thomas

thomas_w

I found some problem with "Generated Schema Script" (using "Devart Default"). I create this table using FEDERATED ENGINE.

Code: Select all

CREATE TABLE geodaten_remote(
  geodaten_id INT(11) NOT NULL,
  latitude INT(10) UNSIGNED NOT NULL,
  longitude INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (geodaten_id)
) ENGINE = FEDERATED
CONNECTION = 'mysql://user:password@localhost:3307/sample/geodaten';

Using Generate Schema Script I get this

Code: Select all

=========== Generate Schema Script =======================================
-- Script was generated by Devart dbForge Studio for MySQL, Version 5.0.28.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 22.04.2011 15:39:35
-- Server version: 5.6.2-m5
-- Client version: 4.1

USE sample;

CREATE TABLE geodaten_remote(
  geodaten_id INT(11) NOT NULL,
  latitude INT(10) UNSIGNED NOT NULL,
  longitude INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (geodaten_id)
)
ENGINE = FEDERATED
AVG_ROW_LENGTH = 35
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

CONNECTION Informations are lost. Any hint?

with kind regards,
Thomas

thomas_w

Hi,

is there any way to get the EXPLAIN EXTENDED "warnings" using dbForge for MySQL (4.50.348)? This special warnings don't show in dbForge.

In MySQL Console I get this...

Code: Select all

CREATE TABLE obst (
 anzahl INT NOT NULL,
 art VARCHAR(20) NOT NULL,
 saison VARCHAR(20) NOT NULL
);

INSERT INTO obst VALUES
(1, 'Apfel', 'Sommer'),
(2, 'Apfel', 'Winter'),
(3, 'Bananen', 'Sommer'),
(4, 'Bananen', 'Winter'),
(5, 'Birnen', 'Sommer'),
(6, 'Birnen', 'Winter'); 

EXPLAIN EXTENDED 
SELECT * FROM obst
WHERE anzahl = 1 
   OR anzahl = 1
   OR anzahl = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Ext
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----
|  1 | SIMPLE      | obst  | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Usi
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-----------------+
| Level | Code | Message

                 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `sample`.`obst`.`anzahl` AS `anzahl`,`sample`.`obst`.`art` AS `art`,`sample`.`obst`.`saison` AS`saison` from `sample`.`obst` where ((`sample`.`obst`.`anzahl` = 1) or (`sample`.`obst`.`anzahl` = 1) or (`sample`.`obst`.`anzahl` = 1)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

with kind regards
Thomas

thomas_w

Uups, I have to change my Query to get the correct result? Sorry, but that is misfit and not handsome. Maybe you find some other solution.

with kind regards,
Thomas

thomas_w · Thanks! Work well with 4.50.348. with kind regards, Thomas

Thanks! Work well with 4.50.348.

with kind regards,
Thomas

thomas_w

devart Studio show wrong result using GROUP_CONCAT

Code: Select all

CREATE TABLE obst (
 anzahl INT NOT NULL,
 art VARCHAR(20) NOT NULL,
 saison VARCHAR(20) NOT NULL
);

INSERT INTO obst VALUES
(1, 'Apfel', 'Sommer'),
(2, 'Apfel', 'Winter'),
(3, 'Bananen', 'Sommer'),
(4, 'Bananen', 'Winter'),
(5, 'Birnen', 'Sommer'),
(6, 'Birnen', 'Winter');

devart Professional Editon 4.50.339

SELECT saison, GROUP_CONCAT( art,  ';', anzahl  ORDER BY art SEPARATOR ';') AS summen 
 FROM obst
GROUP BY saison;
=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      | 41 70 66 65 6C 3B 31 3B 42 61 6E 61 6E 65 6E 3B  |
|                                                  |          33 3B 42 69 72 6E 65 6E 3B 35           |
-------------------------------------------------------------------------------------------------------
|                      Winter                      | 41 70 66 65 6C 3B 32 3B 42 61 6E 61 6E 65 6E 3B  |
|                                                  |          34 3B 42 69 72 6E 65 6E 3B 36           |
-------------------------------------------------------------------------------------------------------


SELECT saison, GROUP_CONCAT( art,  ';', anzahl  ORDER BY art SEPARATOR ';') AS summen 
 FROM obst
GROUP BY saison;
+--------+----------------------------+
| saison | summen                     |
+--------+----------------------------+
| Sommer | Apfel;1;Bananen;3;Birnen;5 |
| Winter | Apfel;2;Bananen;4;Birnen;6 |
+--------+----------------------------+
2 rows in set (0.00 sec)

mysql>

with kind regards,
Thomas

thomas_w

devart studie did not show correct result using WITH ROLLUP. NULL row and colums lost. MySQL Console work well.

Code: Select all

CREATE TABLE obst (
 anzahl INT NOT NULL,
 art VARCHAR(20) NOT NULL,
 saison VARCHAR(20) NOT NULL
);

INSERT INTO obst VALUES
(1, 'Apfel', 'Sommer'),
(2, 'Apfel', 'Winter'),
(3, 'Bananen', 'Sommer'),
(4, 'Bananen', 'Winter'),
(5, 'Birnen', 'Sommer'),
(6, 'Birnen', 'Winter');

SELECT saison, art, SUM(anzahl) AS anzahl
 FROM obst
GROUP BY saison, art WITH ROLLUP;

devart Professional Editon 4.50.339

===================================================================
|         saison           |        art        |       anzahl     |
===================================================================
|         Sommer           |       Apfel       |        1,0       |
-------------------------------------------------------------------
|         Sommer           |      Bananen      |        3,0       |
-------------------------------------------------------------------
|         Sommer           |       Birnen      |        5,0       |
-------------------------------------------------------------------
|         Winter           |       Apfel       |        2,0       |
-------------------------------------------------------------------
|         Winter           |      Bananen      |        4,0       |
-------------------------------------------------------------------
|         Winter           |       Birnen      |        6,0       |
-------------------------------------------------------------------


mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.55-community |
+------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT saison, art, SUM(anzahl) AS anzahl
    ->  FROM obst
    -> GROUP BY saison, art WITH ROLLUP;
+--------+---------+--------+
| saison | art     | anzahl |
+--------+---------+--------+
| Sommer | Apfel   |      1 |
| Sommer | Bananen |      3 |
| Sommer | Birnen  |      5 |
| Sommer | NULL    |      9 |

with kind regards,
Thomas

thomas_w

Okay, I check this out, but I get that:

Code: Select all

+++++++++++++++++++++++++++++++
| id  |path |value|paren|idtyp|
|     |     |     |  t  |     |
+++++++++++++++++++++++++++++++
|  1  | 1,  |HuHu1|  0  |  1  |
-------------------------------
|  2  | 1,  |HuHu2|  0  |  1  |
-------------------------------
|  3  |1,3, |HuHu3|  1  |  2  |
-------------------------------
|  4  |2,4, |HuHu4|  2  |  2  |
-------------------------------

The column width must calc flexible to the result set. I cannot setting it before the result is fetched. Sometime a column width is small, sometime wide. You know mysql command line ...

Thanks!

with kind regads,
Thomas

thomas_w

Hi,

ist there any way to export data in the same format then MySQL Command Line do?

Example "devart":

Code: Select all

======================================================================================================
|                        id                        |                       path                       
======================================================================================================
|                        1                         |                        1,                        
------------------------------------------------------------------------------------------------------
|                        2                         |                        1,                        
------------------------------------------------------------------------------------------------------
|                        3                         |                       1,3,                       
------------------------------------------------------------------------------------------------------
|                        4                         |                       2,4,                       
------------------------------------------------------------------------------------------------------

example "MySQL Command Line":

Code: Select all

mysql> select * from test;
+----+------+-------+--------+-------+
| id | path | value | parent | idtyp |
+----+------+-------+--------+-------+
|  1 | 1,   | HuHu1 |      0 |     1 |
|  2 | 1,   | HuHu2 |      0 |     1 |
|  3 | 1,3, | HuHu3 |      1 |     2 |
|  4 | 2,4, | HuHu4 |      2 |     2 |
+----+------+-------+--------+-------+
4 rows in set (0.00 sec)

mysql>

The MySQL Command Line Output is more handsome I thing.

with kind regards,
Thomas

Devart Forums

Search found 12 matches

Generate Schema Script - Problem with FEDERATED ENGINE

try to get EXPLAIN EXTENDED informations

Bug with GROUP_CONCAT

Bug with ROLLUP

Export Textdata like MySQL Command line