Recent

Author Topic: am i damned ? Field "id" has an invalid field type (VarBytes) to base index on.  (Read 8559 times)

eara

  • Jr. Member
  • **
  • Posts: 84
sorry for the title but  ... it is the Nth time i rewrite this app, and now i hit another wall...

Quote
Project AmIDamned raised exception class 'EDatabaseError' with message:
Field "Damned_VARBINARY_guid_representation" has an invalid field type (VarBytes) to base index on.

which is generated in

Quote
procedure TCustomBufDataset.ProcessFieldCompareStruct(AField: TField; var ACompareRec : TDBCompareRec);
begin
  case AField.DataType of
    ftString, ftFixedChar : ACompareRec.Comparefunc := @DBCompareText;
    ftWideString, ftFixedWideChar: ACompareRec.Comparefunc := @DBCompareWideText;
    ftSmallint : ACompareRec.Comparefunc := @DBCompareSmallInt;
    ftInteger, ftBCD, ftAutoInc : ACompareRec.Comparefunc :=
      @DBCompareInt;
    ftWord : ACompareRec.Comparefunc := @DBCompareWord;
    ftBoolean : ACompareRec.Comparefunc := @DBCompareByte;
    ftFloat, ftCurrency : ACompareRec.Comparefunc := @DBCompareDouble;
    ftDateTime, ftDate, ftTime : ACompareRec.Comparefunc :=
      @DBCompareDouble;
    ftLargeint : ACompareRec.Comparefunc := @DBCompareLargeInt;
    ftFmtBCD : ACompareRec.Comparefunc := @DBCompareBCD;
  else
    DatabaseErrorFmt(SErrIndexBasedOnInvField, [AField.FieldName,Fieldtypenames[AField.DataType]]);
  end;

which means as i see it in a first look: TCustomBufDataset.Locate
doesn't like at all my damned VARBINARY(16) primary key field.

What should i do?
a) go to a far away island, under a new name and start a new life ....  8)
b) change the db again and all related stuff...  :'(
c) add a  binary compare function for ftMyDamnedVarBytes case and buy a helmet for the next wall...   %)

Thanks in advance, for your support...





« Last Edit: April 16, 2014, 03:27:20 pm by eara »

taazz

  • Hero Member
  • *****
  • Posts: 5368
in your shoes I would create a proposal for change in the fcl-db suit in short it lacks the ability to append your own compare function it is a serious problem and I bet it will get accepted in a heart bit. My proposal would go something along the lines of
1) add a private array with compare functions eg
Code: [Select]
var CompFunctions :Array[TFieldType] of TCompareFunc;
2) add a public RegisterCompareFunction(aFieldType:TFieldType; aCompareFunc:TCompareFunc); 
Code: [Select]
procedure RegisterCompareFunction(aFieldType:TFieldType; aCompareFunc:TCompareFunc); 
begin
  compFunctions[aFieldType] := aCompareFunc; //no ifs no buts if there is an older version override the end user knows what he is doing.
end;
3) change the TCompareFunc to accept either a record or a typed pointer where you can define the size of the data to be compared eg.
Code: [Select]
Type
  CompVal = record
    Value : pointer;
    Size   : cardinal;//always > 0 if 0 then false or something
  end;
  TCompareFunc = function(subValue, aValue: compval; options: TLocateOptions): int64;

and last change the existing compare function to support the new record type no to much of work I would imagine. This will allow you to add a generic CompareBytes/memory that will be used in all other cases eg. blobs integer arrays etc etc and it will allow the end user to change the comparison to what ever he wants it to.

That would be my recommendation. Of course there is the easy way out add an onCompare event where the user can right what ever he wants and if it is set then do not call ProcessFieldCompareStruct at all and let the end user provide the required comparison.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 691
or 4) simply extend existing ProcessFieldCompareStruct for ftBytes and ftVarBytes field type, which will cover IMO 98% use cases.
I can look at this option if you can wait day or two ;-)

taazz

  • Hero Member
  • *****
  • Posts: 5368
I only gave 2 solutions so yours is 3) :P, kidding aside, I wouldn't do that your solution puts the burden of implementation for every single field type comparison to the fpc development team, my way leaves the door open for alternative implementations or even extended the existing list and does not put any pressure on the team for a release etc. We need more things like that in the base libraries let the users extend change play around and be creative on their own environment I think that you will get more out the community this way.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 691
Okay, I do not say, that there are no users, which will/can benefit from your proposal - custom comparasion functions.

On other side I think, that base data types should be supported (if there is demand for such functionality)
Until now there was no request from users to have it, so they was not implemented.
As there appears demand, it can be reported into bug tracker and if possible can be implemented.

Support for ftBytes and ftVarBytes was just commited into FPC trunk  :)

taazz

  • Hero Member
  • *****
  • Posts: 5368
Well I never said that the team should stop adding support as need arises I just said that it will make it easier for the end user to work around problems with the default comparison or even change it to accommodate less used comparison methods ee not a byte by byte comparison but a more application specific code. In any case its just a proposal that will open the door to the end user to change improve or even destroy the current system.

And the most important part I do not have to wait for a release or use the unstable trunk version.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

eara

  • Jr. Member
  • **
  • Posts: 84
thank you both for the psychological support...

well, what your are saying in short terms is ... add a compare function and you are done ?

Cause i don't know where i will stuck after this with it...  is this all that i need, or are more things behind  ? (to buy the right helmet)

Currently i am a little bit pressed by time  (as we all are also, i suspect) and touching FCL,  outside of bug fixes is not an option (for me) right now, i would ruther go on by bypassing the problem (and get rid the VARCHAR(16) from primary keys in db, with all consequences), and pray for an extend in next lazarus version....

and here is my code from my base database form that calls the locate

Code: [Select]
{
  Stores key of current record, refreshes qry and then tries to locate it.
  if locate fails then goes to first record.
}
procedure TfrmDb.refreshQry(qry:TSQLQuery);
var kf: string; keyValues: variant;
begin
  if Assigned(qry) then with qry do begin
    kf:=getKeyFields(qry);
    keyValues:=getKeyValues(qry); try
      DisableControls; try
        Refresh;
        if not empty(qry) then begin
          Last;
          if(kf<>'')
          and (VarType(keyValues)<>varempty)
          and not Locate(kf,keyValues,[]) then
            First;
        end;
      finally EnableControls end;
    finally VarClear(keyValues) end;
  end;
end;

eara

  • Jr. Member
  • **
  • Posts: 84
or 4) simply extend existing ProcessFieldCompareStruct for ftBytes and ftVarBytes field type, which will cover IMO 98% use cases.
I can look at this option if you can wait day or two ;-)

Sure i can, but how i will get it ?

LacaK

  • Hero Member
  • *****
  • Posts: 691
@taazz:  :) I agree, I repeat: I do not say, that your opinion is wrong

@eara: You do not need wait, just download patched version of bufdataset.pas (where is already support for ftVarBytes) or whole fcl-db from TRUNK. It is stable enough, believe me :)

eara

  • Jr. Member
  • **
  • Posts: 84

LacaK

  • Hero Member
  • *****
  • Posts: 691
SVN repository: http://svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/

There you can download files by files.
Or you can use some SVN tool to synchronize whole fcl-db or FPC repository and then in your project set search paths to points to these source files.

It is bit tricky ..., you can ask on forum or search on: how to get trunk version of fpc etc. ... I am sure that there are other users which will help you (and explain) better than me.  :)

eara

  • Jr. Member
  • **
  • Posts: 84
"yes i am"  :'(

i made the mistake and tried to load the patched file (plz note that i dont blame anyone else, only my self) and messed up (since i dont know how to intergrated it).
I  get now "ERangeError" from mysqlconn.inc line 574
Code: [Select]
      C.RowsAffected := mysql_affected_rows(FMYSQL);

i unistalled lazarus, and installed it again, but same error goes on... somehow the "link" with mysql is broken.... should i reinstall mysql also ???

eara

  • Jr. Member
  • **
  • Posts: 84
ouff, after a reinstalling lazarus &  mysql server, i got again the same error, so i tried a new clean test project to test MySQL connection  that worked, so problem is in my project code. It was the fcl directories that where included in my project path settings that caused the problem...
Another funny story was an access violation in IDE that arised when i tried to delete the TSQLConnector from a form, this has deleted all code in my .lpr file.

What i have learned from this story ?
Quote
"be more cautious in title selection for the topic, cause it may come real"

have a nice day...
« Last Edit: April 16, 2014, 01:53:30 pm by eara »

eara

  • Jr. Member
  • **
  • Posts: 84
 :D, well it took me 10 min to get rid of VARBINARY(16). Now i use
VARCHAR(38) as primary key, which due to utf8 may be 38x3=114 bytes for primary key  :P
now i can continue with my project, untill next version comes out with UTF8StringField & ftVarArray support on Locate function (i hope)....
« Last Edit: April 16, 2014, 10:54:58 pm by eara »

taazz

  • Hero Member
  • *****
  • Posts: 5368
check mysql documentation but as far I know when using a unicode encoding the number becomes the bytes the field will occupy not the characters. That said GUIDs are numbers only so there is no way that it will take more than 1 bytes per character.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

TinyPortal © 2005-2018