Hello,

Kexi 2.8.1 just successfully built. For the record here we access a postgresql database with kexi. We are accessing an existing database. We previously used kexi 2.4 without this issue.

The behaviour when editing text in existing records is odd.

a. When I delete a character the cursor jumps to the end of the line
b. When we do get the line edited, it cannot be saved at all (see below)
c. When we 'Cancel Record Changes' from the edited field it still cannot be saved
d. If we change fields and then 'Cancel Record Changes' then we can save
e. This particularly affects two specific fields, author_tp & title, other main fields seem normal but I have not checked every single one

Code: Select all

Record updating on the server failed.
SQL statement: UPDATE books SET author_tp=E'
Sharrock, Robert., Van Der Linde., Kathleen. & Smith, Alastair.
' WHERE hash=22780
Server result: 0


Where the E' comes from is unclear. Nothing in front of 'Sharrock' - and I have tried to delete any hidden characters there

Any guides concerning this gratefully received. Maybe this is database related rather than kexi related?

regards
inksi

Special character escaping in PostgreSQL is marked with the "E" prefix; details: http://www.postgresql.org/docs/9.2/stat ... NGS-ESCAPE.

Yes, Kexi has to deal with differences between database backends and such issues are usually backend-specific.

We need to find out why there is end-of-line characet after E' and before closing '. I am assuming you have not put them in Kexi or while pasting the error message.

It would be handy to know the structure of the table books as postgresql understands it.
Are you able to use the psql command line tool? A hint for using DESCRIBE TABLE is here: herehttp://stackoverflow.com/questions/ ... ribe-table
Or are you using other postgresql admin tool?

Another question, what's your postgresql version?

Regarding the editing issue, first question, is it in the Table View or Form View? If in a form, what type of widget?
Finally, are you able to reproduce it in a simple sqlite-based project too?

jstaniek wrote:Special character escaping in PostgreSQL is marked with the "E" prefix; details: http://www.postgresql.org/docs/9.2/stat ... NGS-ESCAPE.

Yes, Kexi has to deal with differences between database backends and such issues are usually backend-specific.

We need to find out why there is end-of-line characet after E' and before closing '. I am assuming you have not put them in Kexi or while pasting the error message.

Your assumption is correct. The eol (newline) after E' is not part of the data. Neither does it show when I export to a delimited format from postgresql, the eol is definitely partof the error message.

jstaniek wrote:It would be handy to know the structure of the table books as postgresql understands it.
Are you able to use the psql command line tool? A hint for using DESCRIBE TABLE is here: herehttp://stackoverflow.com/questions/ ... ribe-table
Or are you using other postgresql admin tool?

Code: Select all

Fables=# \d+ books

                                                Table "public.books"
   Column   |          Type          |                      Modifiers                       | Storage  | Description
------------+------------------------+------------------------------------------------------+----------+-------------
 date_entry | date                   |                                                      | plain    |
 hash       | integer                | not null default nextval('books_hash_seq'::regclass) | plain    |
 subject    | character varying(2)   |                                                      | extended |
 heading    | character varying(200) |                                                      | extended |
 author_tp  | character varying(200) |                                                      | extended |
 title      | character varying(200) |                                                      | extended |
 long_title | text                   |                                                      | extended |
 descrip    | text                   |                                                      | extended |
 state      | text                   |                                                      | extended |
 pub_date   | text                   |                                                      | extended |
 isbn       | character varying(13)  |                                                      | extended |
 note       | text                   |                                                      | extended |
 price_cat  | character varying(10)  |                                                      | extended |
 price_shop | character varying(10)  |                                                      | extended |
 provenance | character varying(100) |                                                      | extended |
 price_buy  | character varying(10)  |                                                      | extended |
 unused     | character varying(200) |                                                      | extended |
 keywords   | text                   |                                                      | extended |
 refs       | character varying(200) |                                                      | extended |
 in_stock   | boolean                | not null default false                               | plain    |
 online     | boolean                | not null default false                               | plain    |
Indexes:
    "books_pkey" PRIMARY KEY, btree (hash)
Has OIDs: yes
 

jstaniek wrote:Another question, what's your postgresql version?

postgresql 8.4
I know you are going to tell me to update

jstaniek wrote:Regarding the editing issue, first question, is it in the Table View or Form View? If in a form, what type of widget?

It is in Form View. It is a Text Edit widget.

Finally, are you able to reproduce it in a simple sqlite-based project too?

I made up a simple database as suggested. I see that the whole kexi face is now very much more polished. However I initially had troubles getting data into the database created, I could not get a record accepted. The fields are:

hash (auto number)
author_tp (char)
title (char)
description (char)
condition (char)

Firstly
The error message was (if I left the autonumber empty)

Code: Select all

Record inserting on the server failed.
Message from server: near "

,'
Norris Newman
','
400pp 8vo
','
IN ZULULAND WITH THE BRITISH
IN ZULULAND WITH THE BRITISH
','
VG, no map
')
Server result: 1 (SQLITE_ERROR)

or thus if I entered a starting number for the first record

Code: Select all

Record inserting on the server failed.
Message from server: near "
1000
,'
Norris Newman
','
400pp 8vo
','
IN ZULULAND WITH THE BRITISH
IN ZULULAND WITH THE BRITISH
','
VG, no map
')
Server result: 1 (SQLITE_ERROR)

Then I got past this and got entries accepted
The hangup was seemingly due to my having set 'Required=Yes' for Author & Title fields in the design of the table. Unsetting this got me under way. Except for the 'Required=' field I left all at default settings.

But now I have the table view showing fields full of html that I cannot get rid of. I cannot get the html code in the table design properties to go away or stay deleted, and it fills up the fields in the table view - but not in the form view.

I hate to mention it here, but the few records that I tried to edit in my production database also became infested with html code. Fortunately only a few records, and I have been able to delete the html using my old kexi.

In essence the sqlite database that I created from sqlite does not exhibit the total bad behaviour of the postgresql one under kexi ... BUT editing in the middle of a field in Form View (say, putting in a puntuation comma, EVEN A BACKSPACE) still sends the cursor to the end of the field - making editing a pain. I can however save the edited record which I cannot do in my postgresql database.

Regards
Ian

Thanks for quality feedback, Ian.

inksi wrote:The eol (newline) after E' is not part of the data. Neither does it show when I export to a delimited format from postgresql, the eol is definitely partof the error message.

Code: Select all

Fables=# \d+ books

                                                Table "public.books"
   Column   |          Type          |                      Modifiers                       | Storage  | Description
------------+------------------------+------------------------------------------------------+----------+-------------
 date_entry | date                   |                                                      | plain    |
 hash       | integer                | not null default nextval('books_hash_seq'::regclass) | plain    |
 subject    | character varying(2)   |                                                      | extended |
 heading    | character varying(200) |                                                      | extended |
 author_tp  | character varying(200) |                                                      | extended |
 title      | character varying(200) |                                                      | extended |
 long_title | text                   |                                                      | extended |
 descrip    | text                   |                                                      | extended |
 state      | text                   |                                                      | extended |
 pub_date   | text                   |                                                      | extended |
 isbn       | character varying(13)  |                                                      | extended |
 note       | text                   |                                                      | extended |
 price_cat  | character varying(10)  |                                                      | extended |
 price_shop | character varying(10)  |                                                      | extended |
 provenance | character varying(100) |                                                      | extended |
 price_buy  | character varying(10)  |                                                      | extended |
 unused     | character varying(200) |                                                      | extended |
 keywords   | text                   |                                                      | extended |
 refs       | character varying(200) |                                                      | extended |
 in_stock   | boolean                | not null default false                               | plain    |
 online     | boolean                | not null default false                               | plain    |
Indexes:
    "books_pkey" PRIMARY KEY, btree (hash)
Has OIDs: yes
 

jstaniek wrote:Another question, what's your postgresql version?

postgresql 8.4
I know you are going to tell me to update

No, we have to be liberal regarding external dependencies. Only our code (Kexi, Calligra) shall be kept up-to-date whenever possible. And you did it perfectly.

inksi wrote:I made up a simple database as suggested. I see that the whole kexi face is now very much more polished. However I initially had troubles getting data into the database created, I could not get a record accepted. The fields are:

hash (auto number)
author_tp (char)
title (char)
description (char)
condition (char)

Firstly
The error message was (if I left the autonumber empty)

Code: Select all

Record inserting on the server failed.
Message from server: near "

,'
Norris Newman
','
400pp 8vo
','
IN ZULULAND WITH THE BRITISH
IN ZULULAND WITH THE BRITISH
','
VG, no map
')
Server result: 1 (SQLITE_ERROR)

or thus if I entered a starting number for the first record

Code: Select all

Record inserting on the server failed.
Message from server: near "
1000
,'
Norris Newman
','
400pp 8vo
','
IN ZULULAND WITH THE BRITISH
IN ZULULAND WITH THE BRITISH
','
VG, no map
')
Server result: 1 (SQLITE_ERROR)

Then I got past this and got entries accepted
The hangup was seemingly due to my having set 'Required=Yes' for Author & Title fields in the design of the table. Unsetting this got me under way. Except for the 'Required=' field I left all at default settings.

As always, it would be handy if you send me this sample .kexi file, maybe privately. I am trying to catch as many issues as possible in this session.

inksi wrote:But now I have the table view showing fields full of html that I cannot get rid of. I cannot get the html code in the table design properties to go away or stay deleted, and it fills up the fields in the table view - but not in the form view.

I hate to mention it here, but the few records that I tried to edit in my production database also became infested with html code. Fortunately only a few records, and I have been able to delete the html using my old kexi.

In essence the sqlite database that I created from sqlite does not exhibit the total bad behaviour of the postgresql one under kexi ... BUT editing in the middle of a field in Form View (say, putting in a puntuation comma, EVEN A BACKSPACE) still sends the cursor to the end of the field - making editing a pain. I can however save the edited record which I cannot do in my postgresql database.

One quick solution here, please set Rich Text property of the Text Editor to false. The data entered so far has been polluted with HTML tags unfortunately already, but after setting Rich Text=False several issues would go away, perhaps also the cursor position issue. There is probably quite a few things to fix in this scenario (rich text is waiting for better times in table view, by the way).

Jaroslaw, hi.

postgresql 8.4
I know you are going to tell me to update

No, we have to be liberal regarding external dependencies. Only our code (Kexi, Calligra) shall be kept up-to-date whenever possible. And you did it perfectly.

For the record Postgresql 8.4 is still supported.

As always, it would be handy if you send me this sample .kexi file, maybe privately. I am trying to catch as many issues as possible in this session.

I would, but even an 'midnight commander' seach from root up finds no '.kexi' file. . Where should it be or, more likely, where is it hiding?

One quick solution here, please set Rich Text property of the Text Editor to false. The data entered so far has been polluted with HTML tags unfortunately already, but after setting Rich Text=False several issues would go away, perhaps also the cursor position issue. There is probably quite a few things to fix in this scenario (rich text is waiting for better times in table view, by the way).

I opened up the sqlite database and then the form view as created with 4 records. Then all fields were set to Rich Text = No.

The good news is that for new records we can now edit properly in the sqlite test database. I rewrote all the data for the (4) records and it behaved well, both Table and Form views look good. No bad news.

I then opened up the postgresql database and then the form view in design mode. All (visible) fields were set to Rich Text = No. Again good news and it looks like we are back on the rails again.

I'll run this as we would use it in production for a few days to see if any hiccups occur. Meanwhile thanks for seeing this through with me.

Not even considering these events, would it not be preferable to set Rich Text = No as the default for new installations? Back to the Least Common Denominator, as it were.

Back to the initial raison d'etre of this whole exercise, the main reason for upgrading was the business of double double quotes - HOCKLEY'S INSOLVENCY LAW being rendered as HOCKLEY'''S INSOLVENCY LAW.. This has now been corrected both in data fields and in sql queries. This is very good to know - but I'll need to upgrade other machines before we can go through the database and rectify all the saved instances of "" or they could simply be reintroduced.

best regards
Ian

inksi wrote:I would, but even an 'midnight commander' seach from root up finds no '.kexi' file. . Where should it be or, more likely, where is it hiding?

Ian, good that the solution seems to work.

By the .kexi file I mean a SQLite-based project file with .kexi extension you'd create from scratch or import from the PostgreSQL-based project. Kexi does not create separate .kexi file for PostgreSQL project. I understand you call the file "sqlite database" in your post.

inksi wrote:would it not be preferable to set Rich Text = No as the default for new installations? Back to the Least Common Denominator, as it were.

Good idea, it can go to Kexi 2.8.2 even if that would be a change of behaviour. For the record, Qt (a framework that's a building block of Kexi) has the property of Text Editor set to true by default.

inksi wrote:Back to the initial raison d'etre of this whole exercise, the main reason for upgrading was the business of double double quotes - HOCKLEY'S INSOLVENCY LAW being rendered as HOCKLEY'''S INSOLVENCY LAW.. This has now been corrected both in data fields and in sql queries. This is very good to know - but I'll need to upgrade other machines before we can go through the database and rectify all the saved instances of "" or they could simply be reintroduced.

Good. Please report even slight annoyance. Thanks for your time.

J.

jstaniek wrote:Good idea, it can go to Kexi 2.8.2 even if that would be a change of behaviour.

Done 2 minutes ago. 2.8.2 is planned in a week: http://community.kde.org/Calligra/Sched ... Plan#2.8.2

@Ian: If you want you can easily update your Kexi compilation to this version quickly, just ask me.