I have a problem trying to get fields from a SQL statement that retuns 2 fields
If I run the query in phpMyAdmin, it returns all the fields correctly. That means SQL statement is correct
If I run the SELECT statment with only one field, it returns correct information. That means connection is Ok
select p.id_product from ps_product p
left outer join ps_product_lang l
on p.id_product = l.id_product
where p.id_product >= :desde1 and p.id_product <= :hasta1
This works
select p.id_product, l.id_lang from ps_product p
left outer join ps_product_lang l
on p.id_product = l.id_product
where p.id_product >= :desde1 and p.id_product <= :hasta1
This also works, bu returns only one field (id_product). Fields.Count=1 !!!
select p.id_product, l.name from ps_product p
left outer join ps_product_lang l
on p.id_product = l.id_product
where p.id_product >= :desde1 and p.id_product <= :hasta1
This returns this error message: Invalid field size
Note
p.id_product is int(10)
p.id_lang is int(10)
l.name is varchar(128)
I use Delphi 6 with the driver dbxopenmysql50.dll that I downloaded from JustSoftwareSolution
I tryed using TSQLConnection, TSLDataSet y TSQLClientDataSet. all three of them return the same error message in the same instruction: Componente.Open;
I did a simple program for testing. Here are the main functions I use to connect and to get the information I need.
procedure TFMMain.EstableceConexionMySQL;
var
oIni : TiniFile;
begin
//Conecto si no está conectada
with MYSQLConnection do
begin
try
if (not Connected) then
begin
oIni := TInifile.Create('G2k2Plus.ini');
try
DriverName := 'dbxmysql';
GetDriverFunc := 'getSQLDriverMYSQL50';
LibraryName := 'dbxopenmysql50.dll';
VendorLib := 'libmysql.dll';
LoginPrompt := False;
Params.Clear;
Params.Append('BlobSize=-1');
Params.Append('ErrorResourceFile=');
Params.Append('LocaleCode=0000');
Params.Append('Database=' + oIni.ReadString('TiendaVirtual', 'Database ', ''));
Params.Append('User_Name=' + oIni.ReadString('TiendaVirtual', 'User_Name ', ''));
Params.Append('Password=' + oIni.ReadString('TiendaVirtual', 'Password ', ''));
Params.Append('HostName=' + oIni.ReadString('TiendaVirtual', 'HostName ', ''));
finally
oIni.Free;
end;
Open;
end;
except
on e: Exception do
begin
MOutput.Lines.Add('Error al abrir conexion MySQL');
MOutput.Lines.Add(e.Message);
end;
end;
end;
end;
procedure TFMMain.BTraerDatosSQLQueryClick(Sender: TObject);
var
Q : TSQLQuery;
i : integer;
Desde, Hasta : integer;
s : string;
begin
Desde := 0;
Hasta := 24;
BConectar.Click;
if (MYSQLConnection.Connected) then
begin
Q := TSQLQuery.Create(nil);
try
with Q do
begin
try
SQLConnection := MYSQLConnection;
if (Active) then
Close;
SQL.Text := 'select p.id_product, l.name from ps_product p ' +
'left outer join ps_product_lang l ' +
'on p.id_product = l.id_product ' +
'where p.id_product >= :desde1 and p.id_product <= :hasta1';
//PrepareStatement;
Params.FindParam('desde1').Value := Desde;
Params.FindParam('hasta1').Value := Hasta;
Open; // ERROR HERE !!!
MOutput.Lines.Add('Campos: ' + IntToStr(Fields.Count));
for i := 0 to Fields.Count -1 do
begin
MOutput.Lines.Add(' DisplayName '+Fields[i].DisplayName);
MOutput.Lines.Add(' FullName '+Fields[i].FullName);
MOutput.Lines.Add(' FieldName '+Fields[i].FieldName);
MOutput.Lines.Add(' Origin '+Fields[i].Origin);
end;
MOutput.Lines.Add('-----------');
s := '';
for i := 0 to Fields.Count -1 do
s := s + UpperCase(Fields[i].FieldName)+', ';
MOutput.Lines.Add(s);
while (not EOF) do
begin
s := '';
for i := 0 to Fields.Count -1 do
s := s + Fields[i].AsString+', ';
MOutput.Lines.Add(s);
Next;
end;
MOutput.Lines.Add('-----------');
Close;
except
on e: Exception do
begin
MOutput.Lines.Add('-----------');
MOutput.Lines.Add('EXCEPTION');
MOutput.Lines.Add(e.Message);
end;
end;
end;
finally
Q.Free;
end;
end;
end;
