FetchAll=False problem

rant801601 · Post by **rant801601** » Mon 17 Jul 2006 08:14

Test info:
MySQL 5.0.22
Delphi 7 Ent SP1
Trial CoreLab driver (latest)
DriverName=MySQL Direct (Core Lab)

AfterConnect I execute SET NAMES = 'cp1250' to have
correct characters. This all works ok.

When I add FetchAll=False to Params (I have TCRSQLConnection)
characters are no more cp1250.

I have some info that it is not advised to use FetchAll=False.

With Regards,
Radovan Antloga

Antaeus · Post by **Antaeus** » Mon 17 Jul 2006 10:54

If you set FetchAll parameter to False, one additional session to server for every query is created and query is executed in this additional session. SET NAMES brings influence only on main session in this case. We can suggest you following solutions:

1) to give up using FetchAll=False
2) to set settings you need in global variables so that they are not issued in every session
3) to add SET NAMES statement before every query you execute. For example:
Code: Select all
```
      SQLQuery.SQL.Clear;
      SQLQuery.SQL.Add('SET NAMES ''cp1250'';');
      SQLQuery.SQL.Add('SHOW VARIABLES LIKE ''%char%'';');
      ClientDataSet.Open;
```

rant801601 · Post by **rant801601** » Mon 17 Jul 2006 12:01

Sorry I don't understand what you mean (by answ. 2).

1. I have already correct global variables

| character_set_client | cp1250
| character_set_connection | cp1250
| character_set_database | cp1250
| character_set_filesystem | binary
| character_set_results | cp1250
| character_set_server | cp1250
| character_set_system | utf8

Charaters are not cp1250 when I connect with CRSQLConnection
unless I use SET NAMES 'cp1250' after connect. I found that in your forums (so global variables are not read by your dbExpress driver).

In step 2 I tried to use FetchAll=False because some querys are opening
to long. When I do that I don't have cp1250 any more.

With Regards,
Radovan Antloga

Antaeus · Post by **Antaeus** » Mon 17 Jul 2006 13:25

Try restarting server with --default-character-set=cp1250 parameter. Please read MySQL Reference Manual for more information.

rant801601 · Post by **rant801601** » Mon 17 Jul 2006 13:46

I have that already in my.ini

default-character-set=cp1250

I can send you my.ini. It is not problem with my settings. I was testing microOLAP dbExpress driver in it works ok but is slower than yours and I don't like how their driver map fields (bigint,decimal > ftFloat).

So with same server and settings I get cp1250 with microOLAP but not with CoreLab driver. I have same test program I just change driver. So it must be problem with your driver.

With Regards,
Radovan Antloga

Antaeus · Post by **Antaeus** » Mon 17 Jul 2006 14:26

Try adding parameter ServerCharSet to CRSQLConnection.Parameters and set its value to cp1250. If this won't help to solve the problem, please send us (evgeniyD*crlab*com) a complete small sample to demonstrate it, including script to create and fill table.

rant801601 · Post by **rant801601** » Mon 17 Jul 2006 14:55

That is working now !!

Characters are now OK. I found still one problem
when FetchAll=False I cant apply updates from clientDataset.
FetchAll=True does work.

I was testing also your MySQL Developer Studio. There I also
must do SET NAMES to post cp1250 characters from client.
Othervise I get error line to long. I think it could auto detect default character set.

With Regards,
Radovan Antloga

Duke · Post by **Duke** » Tue 18 Jul 2006 06:36

I was testing also your MySQL Developer Studio. There I also
must do SET NAMES to post cp1250 characters from client.

MySQL Developer Studio takes default code page from your operating system to perform client charset conversions, so if your code page differs from cp1250 you won't see correct characters.
To avoid this issue you need to turn on Use Unicode option for connection. Your server must support Utf-8.

rant801601 · Post by **rant801601** » Wed 19 Jul 2006 10:48

My default code page is ok. This is not so import
I can do SET NAMES if I need.

More important is dbExpress driver problem.
You did not answer why I can't apply updates
when FetchAll=False with ClientDataset.
Please check this befaure you answer.

With Regards,
Radovan Antloga

Antaeus · Post by **Antaeus** » Wed 19 Jul 2006 12:15

This may happen because of locking issues. To prevent records from changing while someone reads them, server locks block of records that is being read currently. In FetchAll=False mode a situation happens very often in which only some records of locked block is read and block stays locked for some time while user works with already fetched records. If user has edited records that are locked in the server and tries to post changes, conflict happens. This is one of FetchAll=False mode limitations.
You can try using InnoDB storage. Server performs row-level locking when it works with InnoDB engine.

rant801601 · Post by **rant801601** » Wed 19 Jul 2006 12:31

I have InnoDB default engine and test tables are InnoDB.

I would understand that but no users are connected in
test db. I can not insert, update, delete when FetchAll=False.
It just do nothing. When I change back FetchAll=True insert,
update, delete all is working.

Did you try it?

With Regards,
Radovan Antloga

Antaeus · Post by **Antaeus** » Thu 20 Jul 2006 09:45

Thank you for information. We have reproduced the problem. After applying updates no errors are raised but changes are not applied to database in fact.
The investigation of the problem is in progress. Unfortunately now we cannot give you any information. As soon as we solve the problem we'll let you know.

Antaeus · Post by **Antaeus** » Fri 21 Jul 2006 12:51

We have fixed the problem. This fix will be included in the next DbxMda build.