Lazarus 4 + ZEOSLib 8 + MariaDB 11.8.2 = Only connect in ReadOnly mode

[atieborin]

Hello everyone, I'm experiencing a problem that didn't previously occur with previous versions of the aforementioned development environments.

I have a program developed in Lazarus that uses a MariaDB database (formerly MySQL), which uses the ZEOSLib library to connect to the database. The application was working perfectly.

However, when I decided to use the aforementioned versions of the systems, it began to exhibit strange behavior: the developed application reads information from the database normally, but when I try to create new records, change existing data, or even delete them, I'm surprised by an error message with the following content:

"Readonly field can't be assigned a value: Name"

From what I understand, the database connection is being established in read-only mode, preventing any changes to the program's database.

Before you ask, I've already checked the user's permissions on the MariaDB server, which are set to broad authority for creating, modifying, and deleting records, and are working perfectly through phpMyAdmin.

I've also checked the "ReadOnly" property of ZEOSLib's database components, setting it to "False" for all of them.

I'm turning to my colleagues because I'm out of ideas. I hope you can help me.

[dseligo]

ZeosLib 8 changed behaviour from previous versions: you need to 'close' query before you set new 'SQL.Text'. In previous versions, when you set 'SQL.Text', query was closed automatically.

So, try something like this:

Code: Pascal  [Select][+][-]

1. ZQuery1.Close;
2. ZQuery1.SQL.Text := 'insert into yourtable (...';

[atieborin]

I'm not using ZQuery for database operations, but a ZTable. Below is the code for better understanding:

ZTable1.Edit;
ZTable1.FieldByName('Nome').AsString := txtNome.Text;
ZTable1.FieldByName('Login').AsString := txtLogin.Text;
ZTable1.FieldByName('Senha').AsString := txtSenha.Text;
ZTable1.FieldByName('Setor').AsString := txtSetor.Text;
ZTable1.FieldByName('Cargo').AsString := txtCargo.Text;
ZTable1.FieldByName('Funcao').AsString := txtFuncao.Text;
ZTable1.FieldByName('EMail').AsString := txtEMail.Text;
     if cbStatus.Checked = False then ZTable1.FieldByName('Status').AsInteger := 0 else ZTable1.FieldByName('Status').AsInteger := 1;
     if rbAdmin.Checked = True then
     begin
     ZTable1.FieldByName('TipoConta').AsString := 'admin';
     end
     else if rbUser.Checked = True then
     begin
     ZTable1.FieldByName('TipoConta').AsString := 'user';
     end;
     if CheckListBox1.Checked[0] = True then ZTable1.FieldByName('CadClientes').AsInteger := 1 else ZTable1.FieldByName('CadClientes').AsInteger := 0;
     if CheckListBox1.Checked[1] = True then ZTable1.FieldByName('CadFuncionarios').AsInteger := 1 else ZTable1.FieldByName('CadFuncionarios').AsInteger := 0;
     if CheckListBox1.Checked[2] = True then ZTable1.FieldByName('CadFornecedores').AsInteger := 1 else ZTable1.FieldByName('CadFornecedores').AsInteger := 0;
     if CheckListBox1.Checked[3] = True then ZTable1.FieldByName('CadMateriaPrima').AsInteger := 1 else ZTable1.FieldByName('CadMateriaPrima').AsInteger := 0;
     if CheckListBox1.Checked[4] = True then ZTable1.FieldByName('CadMaqEquip').AsInteger := 1 else ZTable1.FieldByName('CadMaqEquip').AsInteger := 0;
     if CheckListBox1.Checked[5] = True then ZTable1.FieldByName('CadMatConsumo').AsInteger := 1 else ZTable1.FieldByName('CadMatConsumo').AsInteger := 0;
     if CheckListBox1.Checked[6] = True then ZTable1.FieldByName('CadProdFinal').AsInteger := 1 else ZTable1.FieldByName('CadProdFinal').AsInteger := 0;
     if CheckListBox1.Checked[7] = True then ZTable1.FieldByName('CadCelProducao').AsInteger := 1 else ZTable1.FieldByName('CadCelProducao').AsInteger := 0;
     if CheckListBox1.Checked[8] = True then ZTable1.FieldByName('CadContaBanco').AsInteger := 1 else ZTable1.FieldByName('CadContaBanco').AsInteger := 0;
     if CheckListBox1.Checked[9] = True then ZTable1.FieldByName('CadFormaPagto').AsInteger := 1 else ZTable1.FieldByName('CadFormaPagto').AsInteger := 0;
     if CheckListBox1.Checked[10] = True then ZTable1.FieldByName('CadPedidos').AsInteger := 1 else ZTable1.FieldByName('CadPedidos').AsInteger := 0;
     if CheckListBox1.Checked[11] = True then ZTable1.FieldByName('ReqMaterial').AsInteger := 1 else ZTable1.FieldByName('ReqMaterial').AsInteger := 0;
     if CheckListBox1.Checked[12] = True then ZTable1.FieldByName('PedidoCompra').AsInteger := 1 else ZTable1.FieldByName('PedidoCompra').AsInteger := 0;
     if CheckListBox1.Checked[13] = True then ZTable1.FieldByName('EntradaNota').AsInteger := 1 else ZTable1.FieldByName('EntradaNota').AsInteger := 0;
     if CheckListBox1.Checked[14] = True then ZTable1.FieldByName('CadBeneficiamento').AsInteger := 1 else ZTable1.FieldByName('CadBeneficiamento').AsInteger := 0;
     if CheckListBox1.Checked[15] = True then ZTable1.FieldByName('RelEntradaNota').AsInteger := 1 else ZTable1.FieldByName('RelEntradaNota').AsInteger:= 0;
     if CheckListBox1.Checked[16] = True then ZTable1.FieldByName('RelCEMP').AsInteger := 1 else ZTable1.FieldByName('RelCEMP').AsInteger := 0;
     if CheckListBox1.Checked[17] = True then ZTable1.FieldByName('RelInventario').AsInteger := 1 else ZTable1.FieldByName('RelInventario').AsInteger := 0;
     if CheckListBox1.Checked[18] = True then ZTable1.FieldByName('RelSaldoEstoque').AsInteger := 1 else ZTable1.FieldByName('RelSaldoEstoque').AsInteger := 0;
     if CheckListBox1.Checked[19] = True then ZTable1.FieldByName('BaixaRequisicao').AsInteger := 1 else ZTable1.FieldByName('BaixaRequisicao').AsInteger := 0;
     if CheckListBox1.Checked[20] = True then ZTable1.FieldByName('PedidoAluguel').AsInteger := 1 else ZTable1.FieldByName('PedidoAluguel').AsInteger := 0;
ZTable1.Post;

[CharlyTango]

I would never use TZTable without special need or extraordinary circumstances. And only if there are very few data records.
My suggestion would be:

Code: Pascal  [Select][+][-]

1. //Aircode !!!
2.  
3.   ZQuery1.Close;
4.   ZQuery1.sql.text := 'INSERT INTO thetablename (Nome,Login,Status,CadClientes,CadFuncionarios....) values (:Nome,:Login,:Status,:0,:1.....)'
5.   ZQuery1.ParamByName('Nome').AsString := 'XXXX';
6.   ZQuery1.ParamByName('Login').AsString := 'XXXX';
7.   ZQuery1.ParamByName('Status').AsInteger := ord(cbStatus.Checked);
8.  
9.   ....
10.  
11.   for i:=0 to 20 do begin
12.   ZQuery1.ParamByName(IntToStr(i)).AsInteger := Ord(CheckListBox1.Checked[i]);
13.   end;
14.  
15.   ZQuery1.ExecSQL;  

depending on INSERT or UPDATE statement it would work similar except the UPDATE statement needs a WHERE clause

[Zvoni]

Does the underlying "Source" of your ZTable contain a join?

[atieborin]

Does the underlying "Source" of your ZTable contain a join?

No, the source don't have a join.

[atieborin]

I would never use TZTable without special need or extraordinary circumstances. And only if there are very few data records.
My suggestion would be:

Code: Pascal  [Select][+][-]

1. //Aircode !!!
2.  
3.   ZQuery1.Close;
4.   ZQuery1.sql.text := 'INSERT INTO thetablename (Nome,Login,Status,CadClientes,CadFuncionarios....) values (:Nome,:Login,:Status,:0,:1.....)'
5.   ZQuery1.ParamByName('Nome').AsString := 'XXXX';
6.   ZQuery1.ParamByName('Login').AsString := 'XXXX';
7.   ZQuery1.ParamByName('Status').AsInteger := ord(cbStatus.Checked);
8.  
9.   ....
10.  
11.   for i:=0 to 20 do begin
12.   ZQuery1.ParamByName(IntToStr(i)).AsInteger := Ord(CheckListBox1.Checked[i]);
13.   end;
14.  
15.   ZQuery1.ExecSQL;  

depending on INSERT or UPDATE statement it would work similar except the UPDATE statement needs a WHERE clause

I appreciate your code suggestion, which was brilliantly written. However, I've been using the ZTable component throughout the application, and it has worked perfectly so far. Replacing the component with ZQuery and modifying the code as suggested will require restructuring the entire application, which is currently impractical. Any idea what caused the change in behavior?

[marsupilami79]

Hello atieborin,

I tried to find the error string "Readonly field can't be assigned a value: Name" in Zeos but this isn't a Zeos error message. Maybe it helps if you create a small sample project that demonstrates the problem.

Best regards,

Jan

[paweld]

@marsupilami79 : such an error reports an exception: RaiseFieldReadOnlyError
Possible occurrences of this error are in the procedure: TZAbstractRODataset.SetFieldData and TZAbstractRODataset.Prepare4DataManipulation, and the error condition is:

Code: Pascal  [Select][+][-]

1. if Field.ReadOnly and (Field.FieldKind <> fkLookup) and
2.   not (State in [dsSetKey, dsCalcFields, dsFilter, dsBlockRead, dsInternalCalc, dsOpening]) then
3.   RaiseFieldReadOnlyError(Field);

[marsupilami79]

Hello paweld,

you are right. RaiseFieldReadOnlyError will create an exception that sedmantically says the same. But >Field '%s' cannot be modified< isn't the same message as >Readonly field can't be assigned a value: %s<. So we don't know which part of the stack creates the message and I don't know where to search.

Best regards,

Jan

[paweld]

Hi Jan,
In the current version, this message is exactly the same - see image.

[Zvoni]

Maybe a stupid question:
is your Field/Column called "nOme" or "nAme"?

In Pawel's screenshot i see a formatted String inserting "Field.DisplayName" into the Error-Message

[rvk]

Maybe a stupid question:
is your Field/Column called "nOme" or "nAme"?

In Pawel's screenshot i see a formatted String inserting "Field.DisplayName" into the Error-Message

Setor, senha, funcao, nome... That's Portuguese 

[Zvoni]

Maybe a stupid question:
is your Field/Column called "nOme" or "nAme"?

In Pawel's screenshot i see a formatted String inserting "Field.DisplayName" into the Error-Message

Setor, senha, funcao, nome... That's Portuguese 

https://www.freepascal.org/docs-html/fcl/db/tfield.displayname.html

DisplayName is the name of the field as it will be displayed to the user e.g. in grid column headers. By default it equals the FieldName property, unless assigned another value.

I seriously doubt, TS has an Alias for the FieldName.....

[marsupilami79]

Hello paweld,

Hi Jan,
In the current version, this message is exactly the same - see image.

you are right. I checked this on Delphi and in Delphi we seem to use the SFieldReadOnly constant from Delphis DBConsts unit which lead me in the wrong direction...

Anyway - the mariadb driver has a line that explains why the field is readonly:

Code: [Select]

Result.ReadOnly := (FieldOffsets.org_table <0) or (Result.TableName = '') or (Result.ColumnName = '');
We have a test that newly fails with Mariadb 11.8.2 on Linux: bugreport.TZTestCompMySQLBugReport.Test981208
In that test a column is marked readonly because MariaDB doesn't give us a table name or a column name.

I assume something similar happens in the case of the thread creator. For today I don't have the time to find out why this happens in our case. It would help to have the case of the thread creator.

Best regards,

Jan