Column with name "Extra" was not found

PatySaito · Post by **PatySaito** » 05.10.2007, 22:39

Hi there,

I got the error "Column with name "Extra" was not found" when I try to active any sql. I double check all my field names.

I starting to "trace" and discovery that the metadata was a field called Extra, but I am "stocked" in how to solve this issue.

The SQL is: SELECT * FROM MEMBERS
In a database called "my_db_design"

I am using MySQL 6.0.2 and dbo 6.6.1 - beta.

Sorry, but I already try everting without result, so I am asking for help.

Thanks,

Paty from Brazil.

gto · Post by **gto** » 05.10.2007, 23:13

Hello Paty!

Hum.. Let's See:

- MySQL 6 don't have official support in Zeos
- 6.6.1 Beta isn't the latest version of Zeos.

About supporting MySQL 6, it's beta right now and I don't see any Zeos developer or contributor doing any job on beta releases of MySQL. If you want to dig in and adapt the changes, it's very easy to do it with Zeos.

About getting the latest version, which could help, or not, you can read right here, within SVN download:

http://zeos.firmos.at/viewtopic.php?t=1317

[]'s!

PatySaito · Post by **PatySaito** » 06.10.2007, 15:25

Hi GTO, thank you for the quick answer...

I already tried other versions, DBO 5.x and MySQL5.x too... but with the same error... So, this is why I stop in this versions after this try/error loop

My main skills are in HCI/CG programing... I just tring to do a simple file managemant system for my studio, but I still "stocked"... I choose MySQL just because it appers to be more friedly than Firebird, but maybe I have to go back and reconsider this fact... if get no answers...

In the link you send, the stable dbo versions are the same 6.6.1. On MySQL site, they report that the only real change was the inclusion of a new engine, other parts still the same...

Another fact that I don't report is that I am using InnoDB, but I change my table type to MyIsam (in fact, if I try w/ system's table too, that are MyIsam)and problem still.....

But I still tring, and how people says: "I am brasilian, I never give up"

Tkx,

Post by **mdaems** » 06.10.2007, 19:59

Hi brasilian,

Can you add a TZSqlMonitor to the form and log the queries fired to the database? Is it only this query or do you have the problem with all your queries?

Mark

PatySaito · Post by **PatySaito** » 08.10.2007, 21:34

Hi mdaems,

Here is the result from the monitor:

2007-10-08 17:23:02 cat: Execute, proto: mysql-5, msg: SELECT * FROM MEMBERS E;

2007-10-08 17:23:02 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM my_db_design.members LIKE '%'

When I run this query (SHOW COLUMNS FROM my_db_design.members LIKE '%') in MySQL query Bronwer the result is:

Field, Type, Null, Key, Default, Extra
'MAKER_ID', 'int(10) unsigned', 'NO', 'PRI', '', 'auto_increment'
'NAME', 'varchar(16)', 'YES', '', '', ''
'CONTACT', 'varchar(16)', 'YES', '', '', ''
'PHONE', 'varchar(16)', 'YES', '', '', ''

So, we have a "Extra" column, but some get wrong after this point.

Some idea?

sadeqn · Post by **sadeqn** » 09.10.2007, 06:32

We have same problem with MySQL 5.1.22rc and last version of ZeosLib (updated using SVN) :-( Is there any idea? I'm not sure but i think we have not this problem in older version of ZeosLib.

- Sadeq

Post by **mdaems** » 09.10.2007, 14:41

Hi,

Can you please put a debug point in TZMySQLDatabaseMetadata.GetColumns
at line

Code: Select all

Result.UpdateString(12, GetStringByName('Extra'));

and debug the execution of the GetStringByName function?
It loops through the resultset of the Show Columns statement and should find the Extra column but doesn't in your case. Can you find out why not? (What columns do exist, ...)

I still suppose you have this problem for all queries?

BTW : I think commenting out the line mentioned above should solve your problem as it's just 'additional information' concerning the columns.
Ohoh. No, that's not true. This column is also needed to detect autoincrement columns... As long as ou don't need this info to be correct everything should work when you also comment out some lines lower :

Code: Select all

Result.UpdateBooleanByName('AUTO_INCREMENT',
              Trim(LowerCase(GetStringByName('Extra'))) = 'auto_increment');

(ie. if you don't insert rows without filling the autoincrement column)

sadeqn · Post by **sadeqn** » 10.10.2007, 09:06

I think this is because of NULL value of Extra field. I used Try Except to handle the problem. and all things work fine including auto increament fields.
I do not know how can check the field without Try Except

-Sadeq

PatySaito · Post by **PatySaito** » 10.10.2007, 11:33

Hi there,

Mdaems I try what you said, and discovery that:

When we get columns from the table:
SHOW COLUMNS FROM members LIKE '%'

We get six fields:
Field, Type, Null, Key, Default, Extra

But in the GetColumnCount says that are only five:

Code: Select all

//ZDbcResultSetMetadata
function TZAbstractResultSetMetadata.GetColumnCount: Integer;
begin
  Result := FResultSet.ColumnsInfo.Count;
end;

With this result (5), the GetColumnIndex cause a exception, because we get "zero" as result, since we never reach the last field "Extra", that are the 6º field:

Code: Select all

function TZAbstractResultSet.GetColumnIndex(const ColumnName: string): Integer;
begin
  Result := FindColumn(ColumnName); // none found!!!

  if Result < 1 then
    raise EZSQLException.Create(Format(SColumnWasNotFound, [ColumnName]));
end;

So, I bilieve that something still wrong... but it become a little deep for me as a beginer in db development... I still needing your help guys...

Some that ocours to me is the fact that maybe I doing some wrong with the settings...
I saw in code that there's some itens

Code: Select all

Key := Format('get-columns:%s:%s:%s:%s',
    [Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern]);

that I dont set, like Catalog and SchemaPattern, and maybe this can cause this errors... Am I wrong???

Sadeqn, can you show me where did you put the try-except handler to avoid this? ... It´s not clear for me.

I still believe that we are very near to solve this issue...

sadeqn · Post by **sadeqn** » 10.10.2007, 12:07

My test show that this exception raise when have not any special field (this means all of record have empty result for Extra field)

I use this code in ZDbcMySqlMetadata:

Code: Select all

			try
				Result.UpdateString(12, GetStringByName('Extra'));
			except
			  	Result.UpdateString(12, '');
			end;

and

Code: Select all

		try
			Result.UpdateBooleanByName('AUTO_INCREMENT',
				Trim(LowerCase(GetStringByName('Extra'))) = 'auto_increment');
		except
			Result.UpdateBooleanByName('AUTO_INCREMENT',False);
		end;

And it seems all things is ok! but this is not clean code :-(

PatySaito · Post by **PatySaito** » 10.10.2007, 21:42

Hi there,

I tried the code suggested, but the code bellow still raise the exception, because the return value is "zero".

Code: Select all

function TZAbstractResultSet.GetColumnIndex(const ColumnName: string): Integer;
begin
  Result := FindColumn(ColumnName);

  if Result < 1 then
    raise EZSQLException.Create(Format(SColumnWasNotFound, [ColumnName]));
end;

When I "disable" this check, I get others erros (range check), in the column index.

Anyone have some idea if a "wrong" charset/collat settings can generate this errors?

BTW, some brazilian member, can send me the correct/or usual charset/collate? I am using latin1 w/ latin1_general_ci in InnoDB tables.

Post by **mdaems** » 10.10.2007, 23:10

PatySaito,

Can you check why it's only 5 columns? Which 5? Maybe add some 'showmessage's when looping through the available columns?

Mark

PatySaito · Post by **PatySaito** » 23.10.2007, 13:11

Hi Guys,

I was little busy this days...

I did some "tracking" and get this:

There are 6 columns: Field, Type, Null, Key, Default, Extra

When the loop start, the FindColumn function runs over the fields 'Field','Type','Null','Default' and 'Extra', but as I said, the 'GetColumnCount' function returns 5... and the error remains.

I did some tests @ home with personnal Delphi7 and it's become more 'odd', because it´s stays with the same error, but it works on design time when I activate the query

, but not when I try to run it.

I did this tests with the "Controls" exemple that are embedded in distribution.

But I have one

, maybe some one can send me a compiled version of Controls example, so I can try it and see if works or are something external to the binary program (I mean, in conector/MySql).

Could someone send me this?

Thanks for now

Post by **mdaems** » 23.10.2007, 13:52

Why is the 'key' field skipped in the loop? Or did you forget that one?
I'll try not to forget sending you the compile sample program.
Difference between design and runtime behaviour make me think about mysql dll versions.

Mark

PatySaito · Post by **PatySaito** » 23.10.2007, 15:25

Hi Mark,

I don't forget the 'key' field... It's really not included in loop. As I said, the test was done in my home and I can't re-due it here, but I will try to build a "more complet log" and try to post it in this week yet...

Is there a sequence in mysql.dll 'search' loading process?

Tks,