Топ-100
 
CerebroSQL

Openlist procedure

The openlist procedure is used to enable the use of a database connection of one sheet in another.

 

The procedure is supported in 2 operations: create and insert.

IMPORTANT: data transfer is carried out in text form, i.e. on the receiver side, the table will be created with fields of type Text or varchar, depending on the DBMS

CREATE construct

Syntax:

create table <table name> as openlist ('<Sheet name> ', '<Query executed on the source side>')

 

Requirements:

  • escaping quotes inside a request is forbidden

  • only data types that can be cast to text are supported

Openlist - create table.jpg

The query: 06.11.2021 13:56:12
Query text: create table mysql_tables as 
...
---------------------------------------------------------
Position: 35
##################Remote query##################
select * from tdb.my_catalog
##################Parameter##################
Remote list name: LIST2
Remote list type: MYSQL
Current list name: LIST1
Current list type: PG
####################Command####################
create table mysql_tables as 
 
Code: 301
Count: 4
select * from tdb.my_catalog
mysql_tables
Toper: 3
create table mysql_tables ( "TABLE_CATALOG" Text, "TABLE_SCHEMA" Text, "TABLE_NAME" Text, "TABLE_TYPE" Text, "ENGINE" Text, "VERSION" Text, "ROW_FORMAT" Text, "TABLE_ROWS" Text, "AVG_ROW_LENGTH" Text, "DATA_LENGTH" Text, "MAX_DATA_LENGTH" Text, "INDEX_LENGTH" Text, "DATA_FREE" Text, "AUTO_INCREMENT" Text, "CREATE_TIME" Text, "UPDATE_TIME" Text, "CHECK_TIME" Text, "TABLE_COLLATION" Text, "CHECKSUM" Text, "CREATE_OPTIONS" Text, "TABLE_COMMENT" Text)
Table create - successful
Starting load data
Rows transferred: 356 [ALL ROWS:356]
Run time:  0.333c.
Log operation
D:\CerebroSQL\CerebroSQL 21.11\tmp\lists\openist2021_11_06_135612.log
D:\CerebroSQL\CerebroSQL 21.11\tmp\lists\openist2021_11_06_135612.query