Acesso a Bancos de Dados Externos

O modelo de acesso ao banco de dados consiste em usar o objeto global Database para criar, gerenciar, e remover conexões com o banco de dados. As conexões permitem a execução de comandos e consultas. As consultas retornam cursores, os quais permitem a recuperação dos dados.

A API foi inspirada no pacote LuaSQL, mas a implementação é completamente independente deste projeto.

obs

As funções do Gerenciador de Acesso a Banco de Dados Externos podem ser utilizadas também em scripts do Viewer a partir da versão 1.3.02 do HIscada Pro.

Para verificar quais os gerenciadores de bancos de dados e suas respectivas versões que são compatíveis com o HIscada Pro, acesse aqui.

Modelo de Dados

cnx_object = Database.Connect(connection_name, db_parameters)

Descrição:

Abre uma nova conexão com o banco de dados especificado por db_parameters. Registra o nome da conexão (connection_name) no gerenciador Database, de forma que esta conexão possa ser recuperada por outro script dado seu nome de registro.

Parâmetros de entrada:

connection_name: string com nome de registro da conexão

db_parameters: tabela Lua com a configuração de acesso ao banco. Os parâmetros dependem do banco alvo e sua configuração. As chaves da tabela de configuração devem ser: driver, library, host, database, port, username, password.

Parâmetros de saída:

Instância de conexão ou nil caso não seja possível abrir conexão com o banco.

A instância de conexão possui os atributos Name e Error.

Exemplo de utilização:

-- Parametriza o acesso a um banco de dados no MySQL
local mysql_dsn = {driver='MySQL', host='localhost', database='dbteste', port=3306, username='root', password='root'}
local oracle_dsn = {driver='Oracle', database='XE', username='SYSTEM', password='oraclexe'} -- as demais configurações estão no arquivo tnsnames.ora.
local postgre_dsn = {driver='PostgreSQL', host='localhost', database='dbteste', port=5432, username='postgres', password='postgres'}
-- Teste de Abertura e fechamento de conexão por nome, sem usar "wrapped con object"
local con = Database.Connect('my_cnx1', mysql_dsn) -- mudar para oracle_dsn ou para postgre_dsn para chavear de banco.

if con.Error then
  print("Erro na abertura de conexão:" .. con.Error)
  return
else
  print("Conexão aberta com sucesso.")
end

print("Conexão " ..  tostring(con))
print("Conexão " ..  con.Name) -- forma alternativa

obs

Driver PostgreSQL disponível para o HIscada Pro a partir da versão 1.3.16.

cnx = Database.Get(connection_name ou path_database)

Descrição:

Recupera uma instância de conexão dado seu nome, desde que a conexão tenha sido previamente criada. O método Get permite que scripts recuperem conexões que não foram eles próprios que abriram.Este recurso permite a criação de um pool de conexões permanentemente abertas com o Banco de Dados. Quando utilizado o parâmetro path_database, recupera a conexão realizada pelo Kernel ao item Database especificado.

Parâmetros de entrada:

connection_name: string com nome da conexão

path_database: Caminho completo até o item Database.

Parâmetros de saída:

Instância de conexão ou nil caso não exista conexão com este nome.

obs

Quando utilizada a conexão para um item Database, não é necessário a execução das funções de Connect e Disconnect, pois o tratamento da função conexão ou desconexão é gerenciada de forma automática pelo Kernel.

Exemplo de utilização utilizando-se o nome da conexão:

local con_alias = Database.Get('my_cnx1')
print("Conexão via Get " ..  tostring(con_alias)) -- mesma conexão, apenas outra forma de obter referência

Exemplo de utilização utilizando-se um item Database previamente configurado no projeto:

local con_database = Database.Get('Globals.DataBases.DataBase_001') --
--Caminho completo até o item DataBase_001
print("Conexão via o database " .. con_database.Name)

cnx_table = Database.List()

Descrição:

Lista todas as conexões abertas. Retorna tabela cujas chaves são os nomes das conexões, e cujos valores são objetos Conexão (wrapped).

Parâmetros de saída:

Tabela LUA cujas chaves são os nomes de conexão, e os valores são instâncias de conexão, da mesma forma que as retornadas por Database.Get().

obs

Está função não lista as conexões realizadas pelo Kernel aos itens Database.

Exemplo de utilização:

local oracle_dsn = {driver='Oracle', database='XE', username='SYSTEM', password='oraclexe'}
local con1 = Database.Connect('my_cnx1', oracle_dsn)

local mysql_dsn = {driver='MySQL', library='dbxmys.dll', host='localhost', database='HIscada Pro',
port=3306, username='root', password='root'}
local con2 = Database.Connect('my_cnx2', mysql_dsn)

local postgre_dsn = {driver='PostgreSQL', host='localhost', database='dbteste', port=5432,
username='postgres', password='postgres'} --compativel( 1.3.16")
local con3 = Database.Connect('my_cnx3', postgre_dsn)

print("\n\n")
for name,con in pairs(Database.List()) do
  print("Name " .. name)
  print("Con " .. tostring(con))
  print("ConName " .. tostring(con.Name))
  print("ConError " .. tostring(con.Error))
  Database.Disconnect(name)
end

obs

Driver PostgreSQL está disponível para o HIscada Pro a partir da versão 1.3.16.

ok = Database.Disconnect(connection_name)

Descrição:

Encerra uma conexão que tenha sido previamente criada dado seu nome.

Parâmetros de entrada:

connection_name: string com nome da conexão

Parâmetros de saída:

Booleano indicando true se a conexão foi de fato encontrada e encerrada, ou false caso não tenha encontrado a conexão.

obs

Função não disponível para conexões a um item Database.

Exemplo de utilização:

-- encerra conexão
if Database.Disconnect('my_cnx1') then
    print("Desconexão com sucesso")
else
    print("Falha para desconectar, conexão provavelmente inexistente")
end

Conexão

trans,error = cnx: BeginTransaction()

Descrição:

Inicia uma transação no contexto da conexão corrente.

Parâmetros de saída:

trans: Instância de objeto que representa a transição ou nil em caso de erro.

error: Mensagem de erro em caso de falha ou string vazia em caso de sucesso(“”).

Exemplo de utilização:

-- Início de Transação
trans, error = con: BeginTransaction()
if not trans then
  print("Falha para iniciar transação. Causa: " .. error)
end

error = cnx:Commit(trans)

Descrição:

Persiste todas as operações realizadas no contexto da transação.

Parâmetros de entrada:

trans: objeto retornado por cnx: BeginTransaction()

Parâmetros de saída:

error = nil ou string com mensagem de erro.

Exemplo de utilização:

-- Consolida Transação
error = con:Commit(trans)

error = cnx:Rollback(trans)

Descrição:

Desfaz todas as operações realizadas no contexto da transação.

Parâmetros de entrada:

trans: objeto retornado por cnx:BeginTransaction().

Parâmetros de saída:

error = nil ou string com mensagem de erro.

Exemplo de utilização:

-- Desfaz Ações
error = con:Rollback(trans)

cursor_or_affected_rows, error = cnx:Execute(sql)

Descrição:

Executa um comando ou uma consulta SQL (SELECT).

Parâmetros de entrada:

sql: string com comando SQL na sitaxe suportada pelo banco de dados alvo.

Parâmetros de saída:

cursor_or_affected_rows: se sql for um comando retorna o número de tuplas do banco afetadas, se sql for uma consulta (SELECT) então retorna uma instância de cursor, se houver erro este parâmetro será nil.

error: se não houver erro este parâmetro será nil, caso contrário terá a mensagem de erro gerada pelo Banco de Dados.

Exemplo de utilização:

-- Executa SQL
affected_rows, error = con:Execute([[INSERT INTO agenda (nome,email) VALUES ('beltrano','4321@mail.com')]]);

if error == nil then
  print('Insert after Rollback ' .. affected_rows)
else
  print('Falha na execução de SQL: ' .. error)
end

-- Executa SQL
cursor, error  = con:Execute("SELECT * FROM agenda;")
print('Numero de registros recuperados ' .. cursor.NumRows)

if error == nil then
  print('Insert after Rollback ' .. affected_rows)
else
  print('Falha na execução de SQL: ' .. error)
end

list_of_table_descriptors = cnx:InfoTables()

Descrição:

Lista as tabelas existentes no banco de dados vinculado a conexão. Mais detalhes na documentação oficial.

Parâmetros de saída:

Tabela Lua cujas chaves são os nomes das tabelas, e os valores são dicionários (descritor de tabela) que descrevem as tabelas. As chaves do descritor para o MySQL são:

TableName

TableType

SchemaName

CatalogName

Exemplo de utilização:

-- recupera uma lista de registros que descrevem cada uma das tabelas do banco de dados
info_tables = con:InfoTables()
-- faz o loop na lista
for table_name in pairs(info_tables) do
    print("\t Tabela " .. table_name)
end

list_of_column_descriptors = cnx:InfoColumns(table_name)

Descrição:

Lista as colunas existentes na tabela especificada por table_name no banco de dados vinculado a conexão. Mais detalhes na documentação oficial.

Parâmetros de entrada:

table_name: string com nome da tabela.

Parâmetros de saída:

Tabela Lua cujas chaves são os nomes das colunas, e os valores são dicionários (descritor de coluna) que descrevem as colunas da tabela. As chaves do descritor para o MySQL são:

ColumnName

DefaultValue

TableName

CatalogName

SchemaName

TypeName

DbxDataTyp

MaxInline

Precision

Scale

Ordinal

IsFixedLength

IsUnsigned

IsNullable

IsLong

IsAutoIncrement

IsUnicode

Exemplo de utilização:

-- meta informação sobre uma dada tabela em específico
cols = con:InfoColumns(table_name)
-- loop nas colunas da tabela
for pos in pairs(cols) do
    col_info = cols[pos]
    print("\n")
    -- loop de detalhamento dos atributos de uma dada coluna em específico
    for prop in pairs(col_info) do
        print("\t\t" .. prop .. ": " .. col_info[prop])
    end
end

bool = cnx:Disconnect()

Descrição:

Encerra a conexão, faz o mesmo que Database.Disconnect(connection_name).

Parâmetros de saída:

Boleano indicando se a conexão foi encerrada ou não.

Exemplo de utilização:

done = con:Disconnect()

Cursor

row = cursor:Fetch()

Descrição:

Recupera a próxima tupla de dados disponível na última consulta que gerou este cursor.

Parâmetros de saída:

Nil se não houver mais dados disponíveis, ou uma tabela Lua representando a tupla do bannco de dados. As chaves são os nomes de colunas e os valores são os dados propriamente ditos da tupla armazenada no banco de dados.

Exemplo de utilização:

-- Executa SQL
cursor, error  = con:Execute("SELECT * FROM agenda;")
print('Numero de registros recuperados ' .. cursor.NumRows)
print("Error" ..  tostring(error))

row = cursor:Fetch() -- recupera dicionario {nome_coluna=valor}
while row do
    print("Registro")
    -- quando não se souber o nome das colunas
    for coluna in pairs(row) do
      print("\t" .. coluna .. "->" .. row[coluna])
    end
    -- forma alternativa com nome das colunas explícito
    -- print(string.format("Nome: %s, E-mail: %s", row["nome"], row["email"]))

    -- recupera o próximo registro
    row = cursor:Fetch()
end

cursor:Close()

Descrição:

Fecha o cursor e libera os recursos associados.

Exemplo de utilização:

-- encerra uso do cursor
cursor:Close()

Exemplo Completo

-- Parametriza o acesso a um banco de dados no MySQL
local mysql_dsn = {driver='MySQL',
library='dbxmys.dll', host='localhost', database='Hsp',
port=3306, username='root', password='root'}
-- Teste de Abertura e fechamento de conexão por nome, sem usar "wrapped con object"
local con = Database.Connect('my_cnx1', mysql_dsn)

if con.Error then
  print("Erro na abertura de conexão:" .. con.Error)
  return
else
  print("Conexão aberta com sucesso.")
end

print("Conexão " ..  tostring(con))
print("Conexão " ..  con.Name) -- forma alternativa

-- O método Get permite que scripts recuperem conexões que não foram eles próprios que abriram
-- este recurso permite a criação de um pool de conexões permanentemente abertas com o Banco
local con_alias = Database.Get('my_cnx1')
print("Conexão via Get " ..  tostring(con_alias)) -- mesma conexão, apenas outra forma de obter referência

-- Início de Transação (o suporte à transações depende do banco de dados)
trans = con:BeginTransaction()

local  affected_rows;

-- Executa SQL
affected_rows, error = con:Execute('DROP  TABLE agenda;')
print('Drop Table ' .. affected_rows)
print("Error" ..  tostring(error))

-- Executa SQL (utilizando recurso de strings multi-linha do Lua)
affected_rows, error = con:Execute([[
CREATE TABLE agenda(
  nome  varchar(50),
  email varchar(50));

print('Create Table ' .. affected_rows)
print("Error" ..  tostring(error))

-- Executa SQL
local dados = {"('fulano','1234@mail.com')", "('ciclano','ciclanus@mail.com')"}
for k,v in ipairs(dados) do
   print(v)
   local cmd_SQL = string.format("INSERT INTO agenda (nome,email) VALUES %s;",v)
   affected_rows, error = con:Execute(cmd_SQL)
   print('Insert ' .. affected_rows)
   print("Error" ..  tostring(error))
end

-- Executa SQL
cursor, error = con:Execute([[
    SELECT * from agenda;

-- Retorna número de tuplas geradas por consulta
local numrows = cursor.NumRows
print('Select ' ..  numrows)
print("Error" ..  tostring(error))

-- Consolida Transação
con:Commit(trans)

-- Início de Transação
trans = con:BeginTransaction()

-- Executa SQL
affected_rows, error = con:Execute([[
    INSERT INTO agenda (nome,email) VALUES ('beltrano','4321@mail.com');

print('Insert after Rollback ' .. affected_rows)
print("Error" ..  tostring(error))

-- Desfaz Ações
con:Rollback(trans)

-- Meta informação sobre tabelas existentes no sistema
print("Meta-Informação")
-- recupera uma lista de registros que descrevem cada uma das tabelas do banco de dados
info_tables = con:InfoTables()
-- faz o loop na lista
for table_name in pairs(info_tables) do
    print("\t Tabela " .. table_name)
    info = info_tables[table_name]
    -- faz loop nos campos do registro que descrevem uma dada tabela
    for k in pairs(info) do
        print("\t\t" .. k .. ": " ..  info[k])
    end
    -- meta informação sobre uma dada tabela em específico
    cols = con:InfoColumns(table_name)
    -- loop nas colunas da tabela
    for pos in pairs(cols) do
        col_info = cols[pos]
        print("\n")
        -- loop de detalhamento dos atributos de uma dada coluna em específico
        for prop in pairs(col_info) do
            print("\t\t" .. prop .. ": " .. col_info[prop])
        end
    end
end

-- Executa SQL
cursor, error  = con:Execute("SELECT * FROM agenda;")
print('Numero de registros recuperados ' .. cursor.NumRows)
print("Error" ..  tostring(error))

row = cursor:Fetch() -- recupera dicionario {nome_coluna=valor}
while row do
    print("Registro")
    -- quando não se souber o nome das colunas
    for coluna in pairs(row) do
      print("\t" .. coluna .. "->" .. row[coluna])
    end
    -- forma alternativa com nome das colunas explícito
    -- print(string.format("Nome: %s, E-mail: %s", row["nome"], row["email"]))

    -- recupera o próximo registro
    row = cursor:Fetch()
end

-- encerra uso do cursor
cursor:Close()

-- encerra conexão
if Database.Disconnect('my_cnx1') then
  print("Desconexão com sucesso")
else
  print("Falha para desconectar, conexão provavelmente inexistente")
end

-- forma alternativa
-- con:Disconnect()

print("Fim do teste")

Resultado da execução do exemplo acima:

Conexão my_cnx1 (1)
Conexão via Get my_cnx1 (1)
Drop Table 0
Create Table 0
('fulano','1234@mail.com')
Insert 1
('ciclano','ciclanus@mail.com')
Insert 1
Select 2
Insert after Rollback 1
Meta-Informação
    Tabela agenda
        TableName: agenda
        TableType: TABLE
        SchemaName:
        CatalogName: HIscada Pro


        IsFixedLength: False
        Scale:
        IsUnsigned: False
        IsNullable: True
        SchemaName:
        Ordinal: 2
        IsLong: False
        IsAutoIncrement: False
        ColumnName: email
        DbxDataType: 1
        IsUnicode: True
        CatalogName: HIscada Pro
        MaxInline: -1
        TableName: agenda
        Precision: 50
        TypeName: varchar
        DefaultValue:


        IsFixedLength: False
        Scale:
        IsUnsigned: False
        IsNullable: True
        SchemaName:
        Ordinal: 1
        IsLong: False
        IsAutoIncrement: False
        ColumnName: nome
        DbxDataType: 1
        IsUnicode: True
        CatalogName: HIscada Pro
        MaxInline: -1
        TableName: agenda
        Precision: 50
        TypeName: varchar
        DefaultValue:
Numero de registros recuperados 2
Registro
    nome->fulano
    email->1234@mail.com
Registro
nome->ciclano
    email->ciclanus@mail.com
    Fim do teste

Acesso a Bancos de Dados Externos

Modelo de Dados

cnx_object = Database.Connect(connection_name, db_parameters)

cnx = Database.Get(connection_name ou path_database)

cnx_table = Database.List()

ok = Database.Disconnect(connection_name)

Conexão

trans,error = cnx: BeginTransaction()

error = cnx:Commit(trans)

error = cnx:Rollback(trans)

cursor_or_affected_rows, error = cnx:Execute(sql)

list_of_table_descriptors = cnx:InfoTables()

list_of_column_descriptors = cnx:InfoColumns(table_name)

bool = cnx:Disconnect()

Cursor

row = cursor:Fetch()

cursor:Close()

Exemplo Completo

Outras Referências Relevantes