Can't load datasets in 3.5.1 + PostgreSQL

Classic Servoy
1

Hi all,

I have just done a test conversion of one of my large projects from 3.1.6 to 3.5.1 and have come across the following problem:

I have a dataset with one column, generated by a query:

dataset2.getColumnAsArray(1)
[26623,26620,26619,26618,26617,26609,26610,26608,26606,26605,26545,26548,26583,26604,26601,26597,26599,26598,26596,26595,26594,26593,26592,26589,26590]

If I use this dataset to load records, if fails:

currentcontroller.showRecords(dataset2);

I get

com.servoy.j2db.dataprocessing.DataException: ERROR: IN types integer and character varying cannot be matched

In the log I find

2007-09-17 07:40:05,126 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - select document_id, document_type, document_date, document_customer_id, document_customer_acc_no, document_sales_order_id, document_order_taken_by, document_notes, document_paid, modified_timestamp, created_timestamp, document_number, document_address_shipto, document_address_invoiceto, document_terms, document_message, document_person_id, document_company_id, document_customer_name, document_payment_type, document_card_type, document_amount_tendered, document_locked, document_date_from, document_date_to, document_outstanding, transaction_type_code_sales, transaction_type_code_payments, transaction_type_code_carriage, document_account_goods_taken_by, document_location_id, total_gross, document_print_options, document_type_id, document_paid_text, total_turnover, document_student_discount from acc_documents  where document_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) limit ?
org.postgresql.util.PSQLException: ERROR: IN types integer and character varying cannot be matched

Looked a bit further into this by trying

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT document_id FROM acc_documents LIMIT 10', null, 10);
currentcontroller.showRecords(dataset);

which fails. While

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT document_id FROM acc_documents LIMIT 1', null, 10);
currentcontroller.showRecords(dataset);

Works.

My pk is a ‘serial’, which means an integer with a sequence.

nextval('acc_documents_document_id_seq'::regclass)

Any ideas?

PostgreSQL 8.2.4 on Mac OS X 10.4.10
PostgreSQL driver as shipped with servoy and have also tried with
postgresql-8.2-505.jdbc3.jar

2

An update. I have now noticed that the last record in the dataset seems to load normally…

Looks like it happens on all my tables (my solution has 98 + 8).

Have tried to create a test solution in 3.5.1, but I can’t reproduce the problem.

3

I have one table which has a servoy sequence and the problem is still there.

I also get the same messages in client.

4 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT country_id FROM crm_countries LIMIT 10', null, 10);
currentcontroller.showRecords(dataset);

Loading the dataset above results in Servoy firing a large number of queries at the database (pasted from the PostgreSQL log file):

ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) limit $12
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) limit $11
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6, $7, $8, $9) limit $10
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6, $7, $8) limit $9
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6, $7) limit $8
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5, $6) limit $7
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4, $5) limit $6
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4) limit $5
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3) limit $4
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2) limit $3
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3, $4) limit $5
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2, $3) limit $4
ERROR:  IN types integer and character varying cannot be matched
STATEMENT:  select country_id, country_name, country_comment, country_code, 1 as country_flag_SV_BLOB_M, country_hide, country_postcode_placement, country_charge_vat, country_carriage_zone, country_in_eu from crm_countries  where country_id in ($1, $2) limit $3
5

Christian,

Is your current form on acc_documents and is document_id the pk (If it is a different table, different types may cause a problem)?

Since you cannot reproduce with a small test solution in 3.5, maybe there is some issue with the upgrade from 3.1?
Please try a test solution in 3.1.6 and upgrade it to 3.5.

Rob

6

Hi, I the last logs are from a smaller simpler table called crm_countries. It has a Servoy sequence.

This is all working perfectly in Developer 3.1.6 Mac OS X and is in production with 20 users on Server 3.1.6 on Windows 20003.

I made a copy of the production repository and data and upgraded it to 3.5.1.

7

More investigations:

controller.showRecords('SELECT country_id FROM crm_countries LIMIT 100');

Works fine.

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT country_id FROM crm_countries', null, 10);
controller.showRecords('SELECT country_id FROM crm_countries LIMIT 100');

Also works, while

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT country_id FROM crm_countries', null, 10);
controller.showRecords(dataset);

fails big time.

8

Christian,

The first and second code snippets are the same (in the second you don’t use the dataset which is where things seem to go wrong).

If you create a table similar to crm_countries in 3.1.6, put some data in it, and migrate the repository to 3.5.1, do you then get the same error?

Rob

9

Hi Rob,

I’m beginning to think I should rewrite my solutions to load using the query, rather than datasets – it is a better way to do things anyway, since I get rid of the 1,000 record limitation.

It just that I have 93 methods to rewrite :frowning:

C.

10

C.

It is still bizarre that you get this error and can’t reproduce with a sample solution.

Btw, the 1000-record limit has been dropped in servoy-3.5 as part of the revamped sql-engine.

R.

11

I have a similar problem with release 3.5.1 and MySql. If I try to load an array (from a dataset) to a value list

var sqlSet = databaseManager.getDataSetByQuery(controller.getServerName(),sqlQue,null,100000);
infraRoad = sqlSet.getColumnAsArray(1);
infraDb = sqlSet.getColumnAsArray(2);
application.setValueListItems("th_road",infraRoad,infraDb);

Servoy freezes.

This method worked very well in previous releases since 2 (2.2, 3.0, 3.1, 3.5)

Version 3.5.1-build 514
Java version 1.5.0_07-87 (Mac OS X)
Mac OS 10.4.10

12

The issue from Christian has been fixed in release 3.5.2.

I don’t think rioba’s issue is related, please create a small sample solution and file a case in our support system for this.

http://forum.servoy.com/viewtopic.php?t=6789

Rob