Recent

Author Topic: can't uncomress blob datafield in mysql table  (Read 668 times)

syalyshev

  • New member
  • *
  • Posts: 24
can't uncomress blob datafield in mysql table
« on: February 11, 2019, 04:15:56 pm »
Hello all!
I'm trying to uncompress blob field 'gain_co' from the table as follows
Code: Pascal  [Select]
  1. SQLTransaction.CommitRetaining;
  2.    SQLQueryMain.Active:=false;
  3.    SQLQueryMain.SQL.Clear;
  4.    SQLQueryMain.sql.add('SELECT satname,');
  5.    SQLQueryMain.sql.add('Uncompress(gain_co)');
  6.    SQLQueryMain.sql.add('from  tst');
  7.    SQLQueryMain.Active:=true;
  8.    SQLQueryMain.Last;
  9.  
after DB connection attempt I obtain "EDatabaseError. SQLQueryMain: Field not found: "gain_co". Of course, this field is present at the table!
I don't understand why...! Especially because of the fact that the following code in one of my previous program works:
Code: Pascal  [Select]
  1. SPECT_QRY_Time.Active:=false;
  2.    SPECT_QRY_Time.SQL.Clear;
  3.    SPECT_QRY_Time.SQL.Add('select SPECT_ID,');
  4.    SPECT_QRY_Time.SQL.Add('SPECT_MEASURE_DATE,');
  5.    SPECT_QRY_Time.SQL.Add('SPECT_CENTER_FREQ,');
  6.    SPECT_QRY_Time.SQL.Add('SPECT_BANDWIDTH,');
  7.    SPECT_QRY_Time.SQL.Add('XPDR_ID,');
  8.    SPECT_QRY_Time.SQL.Add('SPECT_XPOL_ID,');
  9.    SPECT_QRY_Time.SQL.Add('Uncompress(SPECT_Y_VAL)');
  10.    SPECT_QRY_Time.SQL.Add('from T_SPECTRUM_TRACE');
  11.    SPECT_QRY_Time.SQL.Add('where SPECT_ID='''+s2+'''');
  12.    SPECT_QRY_Time.Active:=true;
  13.  
Codes are absolutly identical!
Can anyone help? Thanks in advance!

Zvoni

  • Jr. Member
  • **
  • Posts: 94
Re: can't uncomress blob datafield in mysql table
« Reply #1 on: February 12, 2019, 08:30:30 am »
Which OS on the MySQL-Server?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie

syalyshev

  • New member
  • *
  • Posts: 24
Re: can't uncomress blob datafield in mysql table
« Reply #2 on: February 12, 2019, 09:17:37 am »
It seems to me that it is Linux Red Hat Enterprise, but I'm not sure. Consult with my sysadmin.
Any way You are right: these two codes work with different OS and it may be a reason that one works and the other doesn't...

syalyshev

  • New member
  • *
  • Posts: 24
Re: can't uncomress blob datafield in mysql table
« Reply #3 on: February 12, 2019, 10:17:56 am »
Yes, "working" code is being runned under 5.5 RedHat Enterprise, the "nonworking" code is runned under Ubuntu.

rvk

  • Hero Member
  • *****
  • Posts: 3581
Re: can't uncomress blob datafield in mysql table
« Reply #4 on: February 12, 2019, 10:58:18 am »
after DB connection attempt I obtain "EDatabaseError. SQLQueryMain: Field not found: "gain_co". Of course, this field is present at the table!
I don't understand why...! Especially because of the fact that the following code in one of my previous program works:
I wonder if this is an error from the database or from the TFieldlist.

Do you have static fields assigned to SQLQueryMain? Because in your query I DON'T see a field gain_co. I only see a "calculated" field without a name.

Otherwise you need to change "Uncompress(gain_co)" into "Uncompress(gain_co) as gain_co".
But it is better to remove all the static created fields under SQLQueryMain.

Thaddy

  • Hero Member
  • *****
  • Posts: 7449
Re: can't uncomress blob datafield in mysql table
« Reply #5 on: February 12, 2019, 11:06:48 am »
It seems to me that it is Linux Red Hat Enterprise, but I'm not sure.
open a terminal window and type
Code: Bash  [Select]
  1. uname -a
Anyway: I guess the database versions are more important than FPC here. If it used to work on one version, check the database versions first:
the default compression may be tied to the version, but usually there is a config option for legacy.
« Last Edit: February 12, 2019, 11:13:30 am by Thaddy »
Ad Brexinitum (can't help it)

rvk

  • Hero Member
  • *****
  • Posts: 3581
Re: can't uncomress blob datafield in mysql table
« Reply #6 on: February 12, 2019, 11:22:16 am »
the default compression may be tied to the version, but usually there is a config option for legacy.
Even if the compression is specific to the OS version, it wouldn't result in an error "Field not found: "gain_co".

I still think it is the TSQLQuery that complains about gain_co not being present in the SQL-query (which it isn't, because Uncompress is an unnamed calculated field). Only when forcing a fieldname with "as", you can access that field with a name.

Zvoni

  • Jr. Member
  • **
  • Posts: 94
Re: can't uncomress blob datafield in mysql table
« Reply #7 on: February 12, 2019, 12:18:02 pm »
The reason i asked for the Server-OS: On Linux the Fieldnames are case sensitive (as contrary on Windows).
In that way: Is your field really named "gain_co" or is it maybe something like "GAIN_CO"?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie

Thaddy

  • Hero Member
  • *****
  • Posts: 7449
Re: can't uncomress blob datafield in mysql table
« Reply #8 on: February 12, 2019, 01:15:39 pm »
The reason i asked for the Server-OS: On Linux the Fieldnames are case sensitive (as contrary on Windows).
In that way: Is your field really named "gain_co" or is it maybe something like "GAIN_CO"?
That seems unlikely since OP stated the same code worked before.
We really need to have OS and database versions,
Ad Brexinitum (can't help it)

Zvoni

  • Jr. Member
  • **
  • Posts: 94
Re: can't uncomress blob datafield in mysql table
« Reply #9 on: February 13, 2019, 08:38:12 am »
Thaddy,
if you look closely:
His "working" code has all fieldnames in UpperCase,
and since he refers to a SysAdmin, i don't think he has any say in the Setup of the Server (nevermind the Database itself)
And if it's the same server maintained by the same people it just doesn't make sense to have a table with different naming-conventions
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie

rvk

  • Hero Member
  • *****
  • Posts: 3581
Re: can't uncomress blob datafield in mysql table
« Reply #10 on: February 13, 2019, 08:53:29 am »
His "working" code has all fieldnames in UpperCase,
and since he refers to a SysAdmin, i don't think he has any say in the Setup of the Server (nevermind the Database itself)
If you look at the line add('from  tst'); you'll see that it is from a (lowercase) testtable. I don't suspect that testtable is made by a SysAdmin (but made by OP himself).

But let's wait for OPs answer before speculating any further.

So it can be:
*) upper/lowercase mixing (which might not be supported)
*) static gain_co in tsqlquery (which is not 'named' in the sql)
*) incompatibility with (un)compress between OSes (which I don't expect seeing the error)

Zvoni

  • Jr. Member
  • **
  • Posts: 94
Re: can't uncomress blob datafield in mysql table
« Reply #11 on: February 13, 2019, 09:37:35 am »
His "working" code has all fieldnames in UpperCase,
and since he refers to a SysAdmin, i don't think he has any say in the Setup of the Server (nevermind the Database itself)
If you look at the line add('from  tst'); you'll see that it is from a (lowercase) testtable. I don't suspect that testtable is made by a SysAdmin (but made by OP himself).

But let's wait for OPs answer before speculating any further.

So it can be:
*) upper/lowercase mixing (which might not be supported)
*) static gain_co in tsqlquery (which is not 'named' in the sql)
*) incompatibility with (un)compress between OSes (which I don't expect seeing the error)

Hmmm, true.
From a technical POV:
In what order does MySQL parse the/an SQL-Statement?
Couldn't find anything quickly right now to shed some light on it.
EDIT: Found it: https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/

EDIT: Out of curiosity:
I'd be interested if the Statement "SELECT satname, gain_co from tst" works (without the Uncompress-Function)
« Last Edit: February 13, 2019, 09:51:01 am by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie

rvk

  • Hero Member
  • *****
  • Posts: 3581
Re: can't uncomress blob datafield in mysql table
« Reply #12 on: February 13, 2019, 10:45:50 am »
I just did a few simple tests with mysql:

*) invalid tablename, invalid fieldname in sql:

Project project1 raised exception class 'ESQLDatabaseError' with message:
MySQL57Connection1 : Error executing query: Table 'accounts.users2' doesn't exist

*) valid tablename, invalid fieldname in sql:

Project project1 raised exception class 'ESQLDatabaseError' with message:
MySQL57Connection1 : Error executing query: Unknown column 'non_exisiting' in 'field list'

*) valid tablename and fieldname, but invalid TField.Fieldname connected to the TSQLQuery

Project project1 raised exception class 'EDatabaseError' with message:
SQLQuery1 : Field not found : "non_field"

You be the judge which error comes close to the error from OP:
"EDatabaseError. SQLQueryMain: Field not found: "gain_co"

If the error is in the SQL, the connector would return "Error executing query", which it doesn't in this case.
So there is simply a TField connected with Fieldname "gain_co" to the TSQLQuery, and the SQL doesn't contain that fieldname because Uncompress(gain_co) doesn't have "as gain_co" after it.

Zvoni

  • Jr. Member
  • **
  • Posts: 94
Re: can't uncomress blob datafield in mysql table
« Reply #13 on: February 13, 2019, 12:08:22 pm »
I have to admit: This is convincing evidence empirically gained.
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie