Tipos de tablas de Mariadb CONNECT: ejemplo de tablas de catálogo

Una tabla de catálogo es aquella que devuelve información sobre otra tabla o fuente de datos. Es similar a los comandos de MariaDB como DESCRIBE o SHOW hacer. Aplicado a tablas locales, esto simplemente duplica lo que hacen estos comandos, con la notable diferencia de que son tablas y se pueden usar dentro de consultas como tablas unidas o dentro de sub-selecciones.

Pero su principal interés es permitir la consulta de la estructura de tablas externas que no se pueden consultar directamente con comandos de descripción. Veamos un ejemplo:

Supongamos que queremos acceder a las tablas desde una base de datos de Microsoft Access como una tabla de tipo ODBC. La primera información que debemos obtener es la lista de tablas existentes en esta fuente de datos. Para obtenerlo, crearemos una tabla de catálogo que lo devolverá extraído del conjunto de resultados de la función ODBC de SQLTables:

createtable tabinfo (
  table_name varchar(128)notnull,
  table_type varchar(16)notnull)engine=connect table_type=ODBC catfunc=tables
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

La función SQLTables devuelve un conjunto de resultados con las siguientes columnas:

Nota: El tipo de información y el valor del indicador son interpretaciones de CONNECT de este resultado.

Aquí podríamos haber omitido las definiciones de columna de la tabla de catálogo o, como en el ejemplo anterior, elegir las columnas que devuelven el nombre y el tipo de las tablas. Si se especifica, las columnas deben tener el nombre exacto del conjunto de resultados de SQLTables correspondiente, o recibir un nombre diferente con la especificación del valor del indicador correspondiente.

(El Table_Type puede ser TABLE, SYSTEM TABLE, VIEW, etc.)

Por ejemplo, para obtener las tablas que queremos usar podemos preguntar:

select table_name from tabinfo where table_type ='TABLE';

Esto devolverá:

Ahora queremos crear la tabla para acceder a la tabla CLIENTES. Dado que CONNECT puede recuperar la descripción de columna de las tablas ODBC, no es necesario especificarlas en la declaración de creación de tabla:

createtable Customers engine=connect table_type=ODBC
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Sin embargo, si preferimos especificarlos (para eventualmente modificarlos) debemos saber cuáles son las definiciones de columna de esa tabla. Podemos obtener esta información con una tabla de catálogo. Asi es como se hace:

createtable custinfo engine=connect table_type=ODBC
tabname=customers catfunc=columns
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Alternativamente, es posible especificar qué columnas de la tabla de catálogo queremos:

createtable custinfo (
  column_name char(128)notnull,
  type_name char(20)notnull,
  length int(10)notnull flag=7,
  prec smallint(6)notnull flag=9)
  nullable smallint(6)notnull)engine=connect table_type=ODBC tabname=customers
catfunc=columns
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Para obtener la información de la columna:

select*from custinfo;

que da como resultado esta tabla:

Ahora puede crear la tabla CLIENTES como:

createtable Customers (
  CustomerID varchar(5),
  CompanyName varchar(40),
  ContactName varchar(30),
  ContactTitle varchar(30),
  Address varchar(60),
  City varchar(15),
  Region varchar(15),
  PostalCode varchar(10),
  Country varchar(15),
  Phone varchar(24),
  Fax varchar(24))engine=connect table_type=ODBC block_size=10
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Expliquemos lo que hicimos aquí: En primer lugar, la creación de la tabla de catálogo. Esta tabla devuelve el conjunto de resultados de una función ODBC SQLColumns enviada a la fuente de datos ODBC. Las funciones de columnas siempre devuelven un conjunto de datos que tiene algunas de las siguientes columnas, según el tipo de tabla:

‘*’: Estos nombres han cambiado desde versiones anteriores de CONNECT.

Nota: TODOS incluye los tipos de tabla ODBC, JDBC, MYSQL, DBF, CSV, PROXY, TBL, XML, JSON, XCOL y WMI. Más tarde se podrían agregar más.

Elegimos entre estas columnas las que eran útiles para nuestra declaración de creación, usando el valor de la bandera cuando les dimos un nombre diferente (no distingue entre mayúsculas y minúsculas).

Las opciones utilizadas en esta definición son las mismas que se utilizaron más adelante para las tablas de datos de CLIENTES reales, excepto que:

1. los TABNAME La opción es obligatoria aquí para especificar cuál es el nombre de la tabla consultada.
2. los CATFUNC Se agregó la opción tanto para indicar que se trata de una tabla de catálogo como para especificar que queremos información de columna.

Nota: Si el TABNAME Si no se hubiera especificado la opción, esta tabla habría devuelto las columnas de todas las tablas definidas en la fuente de datos conectada.

Actualmente el disponible CATFUNC están:

Nota: Solo se requiere la parte en negrita de la especificación del nombre de la función.

los DATASOURCE y DRIVERS Las funciones devuelven respectivamente la lista de fuentes de datos disponibles y controladores ODBC disponibles en el sistema.

La función SQLDataSources devuelve un conjunto de resultados con las siguientes columnas:

Para obtener la fuente de datos, puede hacer, por ejemplo:

createtable datasources (engine=CONNECT table_type=ODBC catfunc=DSN;

La función SQLDrivers devuelve un conjunto de resultados con las siguientes columnas:

Puede obtener la lista de controladores con:

createtable drivers
engine=CONNECT table_type=ODBC catfunc=drivers;

Otro ejemplo, tabla WMI

Para crear una tabla de catálogo que devuelva los nombres de atributo de una clase WMI, use las mismas opciones de tabla que se usan con la tabla WMI normal más la opción adicional ‘catfunc = columnas’. Si se especifica, las columnas de dicha tabla de catálogo se pueden elegir entre las siguientes:

Si desea utilizar un nombre diferente para una columna, configure la opción de columna Marcar.

Por ejemplo, antes de crear la tabla “csprod”, podría haber creado la tabla de información:

createtable CSPRODCOL (
  Column_name char(64)notnull,
  Data_Type int(3)notnull,
  Type_name char(16)notnull,
  Length int(6)notnull,
  Prec int(2)notnull flag=6)engine=CONNECT table_type='WMI' catfunc=col;

Ahora la consulta:

select*from csprodcol;

mostrará el resultado:

Esto puede ayudar a definir las columnas de la tabla normal coincidente.

Nota 1: La longitud de la columna, tanto para la tabla Info como para la tabla normal, se puede elegir arbitrariamente, solo debe ser suficiente para contener la información devuelta.

Nota 2: La columna Escala devuelve 1 para las columnas de texto (lo que significa que no distingue entre mayúsculas y minúsculas); 2 para columnas flotantes y dobles; y 0 para otras columnas numéricas.

Límite de tamaño de resultado de la tabla de catálogo

Debido a que las tablas de catálogo se procesan como la información recuperada por “Discovery” cuando las columnas de la tabla no se especifican en una instrucción Create Table, su conjunto de resultados se recupera por completo y se asigna memoria.

De forma predeterminada, esta asignación se realiza para un número máximo de línea de retorno de:

Cuando el número de líneas recuperadas para una tabla supera este máximo, CONNECT emite una advertencia. Esto es propenso a ocurrir principalmente con columnas (y también tablas) con algunas fuentes de datos que tienen muchas tablas cuando no se especifica el nombre de la tabla.

Si esto sucede, es posible aumentar el límite predeterminado usando la opción MAXRES, por ejemplo:

createtable allcols engine=connect table_type=odbc
connection='DSN=ORACLE_TEST;UID=system;PWD=manager'
option_list='Maxres=110000' catfunc=columns;

De hecho, debido a que todo el resultado de la tabla se memoriza antes de que se ejecute la consulta; el valor devuelto estaría limitado incluso en una consulta como:

selectcount(*)from allcols;

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.