Update com Join no MySQL

Como fazer um update em uma tabela do MySQL, com base no valor de um campo de outra tabela com a qual essa se relaciona? Veja o modelo a seguir:

update TabelaQueDesejaAtualizar, TabelaComAQualVaiRelacionar
set TabelaQueDesejaAtualizar.CampoParaAtualizar = TabelaComAQualVaiRelacionar.CampoComValorDesejado
where TabelaQueDesejaAtualizar.CampoParaRelacionar = TabelaComAQualVaiRelacionar.CampoParaRelacionar;

Exemplo:

update Funcionario, PessoaFisica
set Funcionario.codPessoa = PessoaFisica.codPessoa
where Funcionario.codPessoaFisica = PessoaFisica.codPessoaFisica;

Considerando as tabelas Funcionario e PessoaFisica, atribui ao campo codPessoa na tabela Funcionario o valor do campo codPessoa da tabela PessoaFisica, levando em conta que as tabelas Funcionario e PessoaFisica possuem um relacionamento por meio do campo codPessoaFisica existente nas duas tabelas. Desta forma, na tabela Funcionario, no campo codPessoa, teremos o mesmo valor deste campo no registro correspondente na tabela PessoaFisica.

Claro que esse campo codPessoa na tabela Funcionario não faz nenhum sentido e é totalmente redundante, sendo que o mesmo existe na tabela PessoaFisica. O exemplo foi apenas para mostrar como funciona um update com “join” no MySQL, e que deve funcionar em outros bancos. Eu lembro que no SQL Server havia uma forma diferente de se fazer isso, mas não lembro exatamente como.

Driver DBExpress 4 para Firebird no Delphi 2007 e 2009

A IBExpert, desenvolvedora da ferramenta de mesmo nome para administração de banco de dados firebird, agora (não sei desde quando) tem um driver DBExpress 4 para Firebird no Delphi 2007 e 2009. O driver é proprietário mas possui uma versão personal, assim como a ferramenta IBExpert, porém, não pode ser distribuído. Para isto, o desenvolvedor deve comprar uma licença.

Não testei, mas se tiver a mesma qualidade da ferramenta IBExpert, o driver deve ser excelente. Uma das coisas boas é que ele tem suporte total a Unicode.

Mais informações em http://ibexpert.net/ibe/index.php?n=Main.IBExpertDBX4Firebird

Excluindo Constraints Foreign Key no MySQL (drop constraint)

O MySQL as vezes nos surpreende, devido ao fato de alguns comandos SQL ANSI não funcionarem nele, como é o caso do drop constraint para exclusão de chaves estrangeiras, as foreign keys.

O comando padrão, que funciona em vários bancos (Firebird, SQL Server e outros) é alter  table NomeDaTabela drop constraint NomeDaConstraint;

Porém, não funciona no MySQL. O comando que deve ser executado é alter  table NomeDaTabela drop foreign key NomeDaConstraint;

Se você criou uma constaint sem um nome, o sistema InnoDB do MySQL gera um automaticamente. Para saber qual o nome gerado execute show create table NomeDaTabela; Este comando mostra a estrutura da tabela, com as constraints existentes e seus respectivos nomes. Daí, basta executar alter  table NomeDaTabela drop foreign key NomeDaConstraint; para apagar a foreign key desejada.

Referência: MySQL 5.1 Reference Manual

Pegando o último valor de auto incremento inserido no mysql

Bem, vamos ao post rápido de hoje.

No MySQL e em muitos bancos existe um recurso de auto incremento que é usado para campos chave primária das tabelas, impedindo que existam dois registros com o mesmo código.

No MySQL o usuário não tem como saber previamente, de uma maneira segura, qual o próximo valor que será gerado para o campo auto incremento de uma tabela. Apenas após inserir um registro na tabela é que podemos saber. Na linguagem PHP, por exemplo, existe a função mysql_insert_id que retorna o valor gerado para o campo auto incremento no último comando insert executado. Isto é útil quando precisamos incluir registros em outra tabela que serão relacionados com o registro que foi inserido, como por exemplo, quando temos uma tabela venda e outra item_venda. A tabela venda normalmente terá um campo cod_venda auto incremento. E ao registrar uma venda, precisamos incluir pelo menos um item de venda, que estará relacionado com a venda que foi inserida. Para fazer esse relacionamento, a tabela item_venda normalmente terá um campo cod_venda para fazer essa associação, assim, o cod_venda da tabela item_venda deve ter o valor do campo auto incremento cod_venda da tabela venda. Assim, para isso que usamos, em PHP, a função mysql_insert_id.

Porém, em outras linguagem como o Delphi, não existe uma função semelhante a mysql_insert_id do PHP, logo, como fazer para retornar o valor auto incremento gerado pelo último insert? Simples, use a instrução sql select LAST_INSERT_ID(); . Independente da linguagem de programação que você usa, isto vai funcionar. Você pode até criar uma função de nome mysql_insert_id na linguagem de programação que desejar, para executar a sql mostrada acima e assim, apenas chamar a função quando desejar. Veja exemplo em Delphi utilizando os componentes DBExpress:

function mysql_insert_id(SQLConnection: TSQLConnection): Integer;
var SqlDataSet: TSqlDataSet;
begin
result:= 0;
SqlDataSet:= TSqlDataSet.Create(nil);
try
SqlDataSet.SQLConnection:= SQLConnection;
SqlDataSet.CommandText:= ’select LAST_INSERT_ID()’;
SqlDataSet.Open;
result:= SqlDataSet.Fields[0].AsInteger;
SqlDataSet.Close;
finally
SqlDataSet.Free;
end;
end;

Para chamar a função deve-se passar um objeto da classe TSQLConnection por parâmetro, que representa a conexão com o banco de dados.

Espero que seja útil, como foi pra mim.

T+

MySQL 5 e Firebird 2 no Delphi 2007 com DBExpress

Veja post no novo site em http://manoelcampos.com/2008/06/04/mysql-5-e-firebird-2-no-delphi-2007-com-dbexpress/

Alterando a pasta de dados do PostgreSQL 8 no Windows

Bem, hoje vou postar algo sobre uma necessidade que tive no PostgreSQL no Windows, e que porém, deu bem mais trabalho de resolver do que com o MySQL, por exemplo. Eu tenho o costume de colocar todos meus dados em uma unidade D: , para evitar de precisar formatar o Windows ( o que não é raro ) e perder arquivos importantes. Assim, sempre altero a pasta padrão onde os servidores de bancos de dados que uso (Firebird, SQL Server, MySQL e PostgreSQL) para esta unidade para evitar de perder meus bancos de dados.

Para realizar esta tarefa no PostgreSQL, logo procurei um arquivo postgresql.conf. Logo encontrei o arquivo na pasta data de instalação do PostgreSQL. Porém, ao encontrar, descomentar e alterar a chave data_directory no arquivo, o servidor não iniciou mais. Alterei as permissões da pasta, tentei colocar barra no final do endereço da pasta, tentei usando as barras \ e / e nada.

Na luta em tentar descobrir como resolver o problema, olhando no serviço do PostgreSQL na janela de administração dos serviços do Windows, verifiquei que o endereço da pasta de dados era passado por parâmetro para o serviço. Logo, pensei em reinstalar o serviço. Para isto, depois de buscar no google e encontrar apenas pistas de como resolver, encontrei na pasta bin do PostgreSQL o utilitário pg_ctl, utilizado para  configurar e controlar o serviço do PostgreSQL. Vendo os parâmetros disponíveis, chamando pg_ctl –help, logo descobri como remover e reinstalar o serviço, que deve ser feito com um usuário administrador.

Assim, abra o prompt de comando na pasta bin de instalação do PostgreSQL.
Para  remover o serviço digite:
  pg_ctl unregister -N “nome_serviço_pgsql” -U WinUser  -P SenhaWin

nome_serviço_pgsql é o nome do serviço do PostgreSQL nos serviços do Windows. Para saber qual é esse nome, na janela de adminsitração de serviços do Windows, encontre o PostgreSQL e pressione ALT+ENTER para abrir as propriedades do serviço. Na janela que abre, na primeira aba, existe um campo “Nome do serviço” contendo o nome que deve ser utilizado para remover o serviço.  Veja exemplo da utilização do comando:

    pg_ctl unregister -N “pgsql-8.2″ -U postgres -P postgres

WinUser deve ser o nome do usuário do Windows, criado para rodar o PostgreSQL. Este usuário foi definido durante a instalação do PostgreSQL e o nome padrão é postgres. SenhaWinUser é a senha deste usuário, também definida durante a instalação do PostgreSQL.

Para  registrar o serviço digite:
  pg_ctl register -N “nome_serviço_pgsql” -U WinUser  -P SenhaWinUser -D “diretorio_desejado”

diretorio_desejado deve ser o diretório onde deseja que os bancos do PostgreSQL sejam salvos. Não esqueça de dar permissão total para o usuário postgres neste diretório.

nome_serviço_pgsql é o nome que você deseja dar para o serviço a ser registrado.
Veja exemplo de utilização do comando abaixo:

  pg_ctl register -N “pgsql8″ -U postgres -P postgres -D “D:\PostgreSqlData\”

Agora, você precisa copiar (ou recortar) todos os arquivos da pasta Data de instalação do PostgreSQL para a pasta que você especificou no parâmetro -D .

Depois reinicie o servidor PostgreSQL e é isso. T+

Hospedagem de sites com banco MySQL 5

Recentemente precisei hospedar um sistema que desenvolvi em ASP.NET. Não sou muito fã do MySQL pois este só passou a ter recursos como Triggers, Views e Stored Procedures na versão 5 enquanto bancos como o Firebird já tem isto há tempos, é bastante simples de se implementar. As Storeds Procedures do Firebird são imbatíveis, principalmente pelo fato de você poder fazer elas retornarem um conjunto de dados, se comportando no final como uma View.

Ao procurar uma empresa de hospedagem para o sistema que comentei notei que a maioria só disponibilizava incluso no preço do pacote de hospedagem ou o MySQL ou o SQL Server 2005 Express, mas a maioria só dava o MySQL incluso. Assim, para não aumentar o valor que o cliente deveria pagar pela hospedagem, resolvi usar o MySQL mesmo.

Utilizo bastante estes recursos de Views, Triggers e Stored Procedures. Durante o desenvolvimento da aplicação no meu servidor local tudo funcionava bem. Utilizando a ferramenta EMS SQL Manager 2007 for MySQL criei algumas views e triggers no MySQL sem problemas. Ao gerar o script para criar o banco no servidor remoto da empresa de hospedagem começaram os problemas. Tanto a criação de views quanto de triggers dava um erro referente a permissões de acesso para executar os comandos. Por padrão a ferramentas de administração utilizada é o phpMyAdmin, uma ferramenta web para administração de bancos MySQL. Devido ter passado por muitos problemas na primeira empresa que contratei para hospedar o sistema e ter achado anti-profissional o atendimento deles, vou informar aqui o nome da mesma, a qual não recomendo a ninguém que hospede sites lá. Estou falando da empresa Dominal. Depois de vários chamados abertos tentando resolver o problema, o “profissional” que estava tratando do meu caso insistia em dizer que as minhas instruções SQL estavam erradas, sendo que as mesmas eu rodava perfeitamente no meu banco local. Este dito “profissional” teve a audácia de me mandar uma apostila de SQL insinuando que eu precisava aprender SQL. Até mandei pra ele os links da documentação oficial do MySQL mostrando que meus comandos estavam corretos, porém ele insistia em dizer que estavam errados.

Pra não ficar de teimoso, até mesmo tentei rodar os comandos que ele julgava estarem certos, tanto no meu banco, como no banco do servidor da dominal, e os comandos, é óbvio, davam como incorretos. Pra mim, o recebimento da apostila de SQL foi a gota d’água, cancelei no mesmo dia a hospedagem.

Agora estou hospedando o sistema na hospedix. O preço é bom, 25,90 mensal. Lá tive os mesmos problemas, porém, quando abri o chamado de suporte, detalhando o que estava acontecendo, não me mandaram uma resposta dizendo que meus comandos SQL estavam errados. Estão tentando resolver o problema com profissionalismo. O problema ainda não foi resolvido mas acredito que descobri o motivo. A versão da biblioteca libmysql.dll usada no phpMyAdmin da hospedix é 4.x e no meu phpMyAdmin local a versão é 5.x. No meu phpMyAdmin os comandos também rodaram sem problemas. Já até mandei essa informação para o suporte e estou aguardando um retorno. Mas de agora, recomendo a hospedix, pois são bastante profissionais, tem um sistema de administração muito bom, bastante automatizado, e o preço é excelente, diferente da conceituada locaweb que só fornecia incluso no pacote o Microsoft Access (Acredite se quiser. Eca, isso nem é banco de dados) e só pelo MySQL eram 50,00 a mais na mensalidade, agora que mudaram os planos, mas já tinha contratado o serviço do hospedix. Como não conecto no servidor usando usuário root, o problema pode ser também, provavelmente, nas permissões concedidas ao meu usuário.

Bem, resolvi escrever este post para tornar pública a minha indignação com a empresa de hospedagem dominal.com e ajudar quem possa estar passando por um problema desses com o MySQL 5.

T+

Identificadores lowercase em MySql no Windows

O MySql, por ser um banco muito ágil, leve e bastante popular, está disponível em diversos planos de hospedagem de sites por aí. Eu particularmente prefiro o PostgreSQL e o Firebird, pois possuem recursos de views, stored procedures e triggers a bastante tempo, coisas que o MySql só veio ter na versão 5.

Pelo fato de o MySql ser incluído sem custo nos planos de hospedage, tive que utilizá-lo num sistema web de gerenciamento financeiro que estou desenvolvendo para um cliente, utilizando Borland Developer Studio 2006, com linguagem ASP.NET e Delphi.

O MySql no Windows é case insensitive, porém, em um servidor Linux é case sensitive. Aí começam os problemas. No Windows, o padrão do MySql é criar os identificadores (tabelas, nomes de campos e tudo mais) em minúsculas (lower case). Mesmo que você coloque o nome de uma tabela, por exemplo, entre apóstrofos, o MySql no Windows cria a tabela com nome em minúsculas. Assim, mesmo fazendo create table Cliente …. ou create table `Cliente`…, ele criará a tabela como cliente (tudo minúsculo). Ao fazer a sincronização da estrutura do banco de dados de desenvolvimento, no servidor Windows, para um servidor Linux de produção serão descobertos os problemas. Como no Linux o MySql, por padrão, obedece o case dos nomes dos objetos, se você tem uma tabela Cliente e gerar um script sql a partir do seu banco no servidor Windows com uma instrução como drop table cliente a mesma não será executada, pois no banco no servidor Linux não existe uma tabela cliente e sim uma tabela Cliente.

Desta forma, o script sql gerado no seu banco no servidor Windows deverá ser todo modificado manualmente para poder rodar no servidor MySql no Linux.

Depois de estar de saco cheio de ter que ficar ajustando script na mão, resolvi procurar na net como resolver isto, até que encontrei algumas perguntas em fóruns que me levaram a este link no site do MySql. Lá mostra que existe um parâmetro que você pode usar no MySql para definir este comportamento. Assim, resumindo, você precisa adicionar a linha a seguir no arquivo my.ini, localizado na pasta de instalação do mysql, que sendo a versão 5, normalmente está em c:\arquivos de programas\mysql\mysql server 5\

set-variable = lower_case_table_names=0

Onde o parâmetro lower_case_table_names define se os identificadores ficarão todos em minúsculas ou não. Assim, defini 0 para indicar que não.

Em Windows, por ter um sistema de arquivos case insensitive, segundo o site do MySQL, forçar os identificadores a serem case sensitive, mudando lower_case_table_names para zero, pode corremper índices ao acessar tabelas MyISAM com o case diferente do que foi definido. Mas como não uso tabelas MyISAM, não tenho com o que me preocupar.

Mais informações em http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Isto foi o que aprendi de novo hoje.
Espero que ajude muita gente.
T+

Usando as ferramentas de linha de comando do MySQL

Quando o mysql é instalado, são instaladas ferramentas de linha de comando. No Windows estas ferramentas são instaladas dentro da pasta bin do mysql, que normalmente fica em Arquivos de Programas\MySQL.

Se você não tiver um servidor MySQL local, precisará copiar estas ferramentas a partir de um servidor MySQL. Instalando o MySQL no seu PC, o instalador disponibiliza uma opção para adicionar a pasta bin no path do sistema operacional, para permitir que executemos as aplicações, existentes nesta pasta, a partir de qualquer pasta, somente digitando o nome do executável.

Se a pasta bin não foi adicionada no path do SO, você precisará entrar na pasta onde estão as ferramentas para poder executar os comandos a seguir.

Conectar no MySQL:

mysql -h ip_ou_nome_dns_do_servidor -u usuario -p
//em seguida o programa pede a senha do servidor
//depois basta digitar comandos como:

show databases; //mostra os bancos de dados existentes;
use meu_banco; //usar o banco meu_banco

show tables; //mostrar as tabels do banco selecionado
//mostrar os campos da tabela minha_tabela
show fields from minha_tabela;

Para gerar um backup do banco usando o mysqldump.exe no prompt:

//gera um script sql do banco meu_banco
//no servidor ip_meu_servidor
//e grava em um arquivo meu_banco.backup.sql
mysqldump -h ip_meu_servidor meu_banco -u usuario -p > meu_banco.backup.sql

Para rodar um arquivo de script a partir do mysql.exe no prompt:

//conectar ao servidor, sem seguida é solicitada a senha
mysql -h ip_ou_nome_dns_do_servidor -u usuario -p
use meu_banco; //usar o banco meu_banco

//executar o arquivo script.sql que contém instruções sql
//as barras devem ser no padrão linux, ou seja, para a direita //a barra para a esquerda não funciona
source c:/temp/script.sql ;