Get mysql column name

Question

Get mysql column name

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I have a table in mysql generated by means of a stored procedure, I needed the table to show me some rows in a column and that I have already achieved, but now I do not know how to save the name of these columns in order to work with that data from an application on android, I read that accessing the scheme is achieved, but I do not know in which part of the sp to put it.

I leave the code of my sp:

 CREATE DEFINER='consultax'@'%' PROCEDURE 'TALLAS_COLUMNS'(pv_cve_modelo char(6))
BEGIN
SET group_concat_max_len=5000;
SET @sql = NULL;
SET @pv_cve_modelo = pv_cve_modelo;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
                'IF(COR.PUNTO01 = ''',
                COR.PUNTO01,
                ''', EXI.EXI01, NULL) AS ',
                CONCAT("'",COR.PUNTO01,"'")
                ), 
    CONCAT(
                ' ,IF(COR.PUNTO02 = ''',
                COR.PUNTO02,
                ''', EXI.EXI02, NULL) AS ',
                CONCAT("'",COR.PUNTO02,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO03 = ''',
                COR.PUNTO03,
                ''', EXI.EXI03, NULL) AS ',
                CONCAT("'",COR.PUNTO03,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO04 = ''',
                COR.PUNTO04,
                ''', EXI.EXI04, NULL) AS ',
                CONCAT("'",COR.PUNTO04,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO05 = ''',
                COR.PUNTO05,
                ''', EXI.EXI05, NULL) AS ',
                CONCAT("'",COR.PUNTO05,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO06 = ''',
                COR.PUNTO06,
                ''', EXI.EXI06, NULL) AS ',
                CONCAT("'",COR.PUNTO06,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO07 = ''',
                COR.PUNTO07,
                ''', EXI.EXI07, NULL) AS ',
                CONCAT("'",COR.PUNTO07,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO08 = ''',
                COR.PUNTO08,
                ''', EXI.EXI08, NULL) AS ',
                CONCAT("'",COR.PUNTO08,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO09 = ''',
                COR.PUNTO09,
                ''', EXI.EXI09, NULL) AS ',
                CONCAT("'",COR.PUNTO09,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO10 = ''',
                COR.PUNTO10,
                ''', EXI.EXI10, NULL) AS ',
                CONCAT("'",COR.PUNTO10,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO11 = ''',
                COR.PUNTO11,
                ''', EXI.EXI11, NULL) AS ',
                CONCAT("'",COR.PUNTO11,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO12 = ''',
                COR.PUNTO12,
                ''', EXI.EXI12, NULL) AS ',
                CONCAT("'",COR.PUNTO12,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO13 = ''',
                COR.PUNTO13,
                ''', EXI.EXI13, NULL) AS ',
                CONCAT("'",COR.PUNTO13,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO14 = ''',
                COR.PUNTO14,
                ''', EXI.EXI14, NULL) AS ',
                CONCAT("'",COR.PUNTO14,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO15 = ''',
                COR.PUNTO15,
                ''', EXI.EXI15, NULL) AS ',
                CONCAT("'",COR.PUNTO15,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO16 = ''',
                COR.PUNTO16,
                ''', EXI.EXI16, NULL) AS ',
                CONCAT("'",COR.PUNTO16,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO17 = ''',
                COR.PUNTO17,
                ''', EXI.EXI17, NULL) AS ',
                CONCAT("'",COR.PUNTO17,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO18 = ''',
                COR.PUNTO18,
                ''', EXI.EXI18, NULL) AS ',
                CONCAT("'",COR.PUNTO18,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO19 = ''',
                COR.PUNTO19,
                ''', EXI.EXI19, NULL) AS ',
                CONCAT("'",COR.PUNTO19,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO20 = ''',
                COR.PUNTO20,
                ''', EXI.EXI20, NULL) AS ',
                CONCAT("'",COR.PUNTO20,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO21 = ''',
                COR.PUNTO21,
                ''', EXI.EXI21, NULL) AS ',
                CONCAT("'",COR.PUNTO21,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO22 = ''',
                COR.PUNTO22,
                ''', EXI.EXI22, NULL) AS ',
                CONCAT("'",COR.PUNTO22,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO23 = ''',
                COR.PUNTO23,
                ''', EXI.EXI23, NULL) AS ',
                CONCAT("'",COR.PUNTO23,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO24 = ''',
                COR.PUNTO24,
                ''', EXI.EXI24, NULL) AS ',
                CONCAT("'",COR.PUNTO24,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO25 = ''',
                COR.PUNTO25,
                ''', EXI.EXI25, NULL) AS ',
                CONCAT("'",COR.PUNTO25,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO26 = ''',
                COR.PUNTO26,
                ''', EXI.EXI26, NULL) AS ',
                CONCAT("'",COR.PUNTO26,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO27 = ''',
                COR.PUNTO27,
                ''', EXI.EXI27, NULL) AS ',
                CONCAT("'",COR.PUNTO27,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO28 = ''',
                COR.PUNTO28,
                ''', EXI.EXI28, NULL) AS ',
                CONCAT("'",COR.PUNTO28,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO29 = ''',
                COR.PUNTO29,
                ''', EXI.EXI29, NULL) AS ',
                CONCAT("'",COR.PUNTO29,"'")
                ),
    CONCAT(
                ' ,IF(COR.PUNTO30 = ''',
                COR.PUNTO30,
                ''', EXI.EXI30, NULL) AS ',
                CONCAT("'",COR.PUNTO30,"'")
                )

  )

  INTO @sql
FROM PRODUCTO PRD
  INNER JOIN EXIPRO EXI  ON EXI.PRODUCTO = PRD.CLAVE
  INNER JOIN CORRIDA COR ON COR.LLAVE = PRD.CORRIDA
AND (COR.PUNTO01 != "" 
    OR COR.PUNTO02 != "" 
    OR COR.PUNTO03 != "" 
    OR COR.PUNTO04 != "" 
    OR COR.PUNTO05 != "" 
    OR COR.PUNTO06 IS NOT NULL 
    OR COR.PUNTO07 IS NOT NULL 
    OR COR.PUNTO08 IS NOT NULL 
    OR COR.PUNTO09 IS NOT NULL 
    OR COR.PUNTO10 IS NOT NULL
    OR COR.PUNTO11 IS NOT NULL
    OR COR.PUNTO12 IS NOT NULL
    OR COR.PUNTO13 IS NOT NULL
    OR COR.PUNTO14 IS NOT NULL
    OR COR.PUNTO15 IS NOT NULL
    OR COR.PUNTO16 IS NOT NULL
    OR COR.PUNTO17 IS NOT NULL
    OR COR.PUNTO18 IS NOT NULL
    OR COR.PUNTO19 IS NOT NULL
    OR COR.PUNTO20 IS NOT NULL
    OR COR.PUNTO21 IS NOT NULL
    OR COR.PUNTO22 IS NOT NULL
    OR COR.PUNTO23 IS NOT NULL
    OR COR.PUNTO24 IS NOT NULL
    OR COR.PUNTO25 IS NOT NULL
    OR COR.PUNTO26 IS NOT NULL
    OR COR.PUNTO27 IS NOT NULL
    OR COR.PUNTO28 IS NOT NULL
    OR COR.PUNTO29 IS NOT NULL
    OR COR.PUNTO30 IS NOT NULL)
WHERE PRD.MODELO = @pv_cve_modelo;

SET @sql = CONCAT('SELECT EXI.TIENDA, 
                            PRD.CLAVE AS PRODUCTO, 
                            PRD.NOMBRE, 
                            ', @sql, 
                                                                 '             
                    FROM PRODUCTO PRD
                        INNER JOIN EXIPRO  EXI ON EXI.PRODUCTO = PRD.CLAVE
                        INNER JOIN CORRIDA COR ON COR.LLAVE = PRD.CORRIDA 
                        AND (COR.PUNTO01 != "" 
                                OR COR.PUNTO02 != "" 
                                OR COR.PUNTO03 != "" 
                                OR COR.PUNTO04 != "" 
                                OR COR.PUNTO05 != "" 
                                OR COR.PUNTO06 IS NOT NULL 
                                OR COR.PUNTO07 IS NOT NULL 
                                OR COR.PUNTO08 IS NOT NULL 
                                OR COR.PUNTO09 IS NOT NULL 
                                OR COR.PUNTO10 IS NOT NULL
                                OR COR.PUNTO11 IS NOT NULL)
                        WHERE PRD.MODELO = ''',  @pv_cve_modelo ,''' 
                   ORDER BY 1, 2, 3');

                                                                  -- AND (COR.PUNTO01 != '''' OR COR.PUNTO02 != '''' OR COR.PUNTO03 != '''' OR COR.PUNTO04 != '''' OR COR.PUNTO05 != '''' OR 
                                                                  -- COR.PUNTO06 != '''' OR COR.PUNTO07 != '''' OR COR.PUNTO08 != '''' OR COR.PUNTO09 != '''' OR COR.PUNTO10 != ''''
                   -- AND (COR.PUNTO01 != '''' OR COR.PUNTO02 != '''')
  -- SELECT @SQL;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

/*Nota: Trae aun detalle el de contemplar cada columna de talla(en el select agregar CONCAT(
                ' ,IF(COR.PUNTO(No. Columna) = ''',
                COR.PUNTO((No. Columna)),
                ''', EXI.EXI(No. Columna), NULL) AS ',
                CONCAT("'",COR.PUNTO(No. Columna),"'")
                )

En where agregar OR COR.PUNTO(No. Columna) IS NOT NULL
)*/

END

(I used the sp first in a wcf web service) And this is the table that returns the store procdure, I need to extract the values from the columns:

mysql

asked by Sandra 01.09.2016 в 16:38

source

2 answers

Difference versionCode and versionName on Android Send a multidimensional associative arrangement through the use of forms

score 2 · Answer 1

If you simply want to know the name of the columns of a table (I do not know if I understood you well, you have the following possibilities: In the case of a hypothetical table Articles:

mysql> desc Articulo;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| creado     | datetime     | NO   |     | NULL    |                |
| modificado | datetime     | NO   |     | NULL    |                |
| titulo     | varchar(255) | NO   |     | NULL    |                |
| resumen    | longtext     | NO   |     | NULL    |                |
| texto      | longtext     | NO   |     | NULL    |                |
| pclave     | longtext     | NO   |     | NULL    |                |
| publicado  | tinyint(1)   | NO   |     | 0       |                |
| slug       | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
9 rows in set (0,00 sec)

or:

mysql> show columns from Articulo;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| creado     | datetime     | NO   |     | NULL    |                |
| modificado | datetime     | NO   |     | NULL    |                |
| titulo     | varchar(255) | NO   |     | NULL    |                |
| resumen    | longtext     | NO   |     | NULL    |                |
| texto      | longtext     | NO   |     | NULL    |                |
| pclave     | longtext     | NO   |     | NULL    |                |
| publicado  | tinyint(1)   | NO   |     | 0       |                |
| slug       | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
9 rows in set (0,01 sec)

Or if you just want to know the names:

mysql> select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Articulo';
+-------------+
| COLUMN_NAME |
+-------------+
| id          |
| creado      |
| modificado  |
| titulo      |
| resumen     |
| texto       |
| pclave      |
| publicado   |
| slug        |
+-------------+
9 rows in set (0,00 sec)

score 1 · Answer 2

As I said in my comment, you are not sending the result of your query to any table, so it is almost impossible to get the name of the columns (unless you define them yourself when creating the table).

As an alternative, you could use this:

set @sql = concat("create table miTabla ", "select ext.tienda, ", @sql , ...)

And then you could use what is indicated in the answer by Ricardo Fernandez , or make a query to the database INFORMATION_SCHEMA :

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'BaseDeDatos'
  and TABLE_NAME = 'miTabla'
  -- Agrega otros criterios (por ejemplo, los campos que no quieres mostrar)
;

This will give you a result with the following columns:

+---------------+-------------------+------------------+----------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA      | TABLE_NAME       | COLUMN_NAME          | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE      | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+-------------------+------------------+----------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+