I've defined a new database in postgresql which includes some functions. One of the procedures is defined as following (simplified):

CREATE OR REPLACE FUNCTION spsetservername(pServerId integer, pServerName character varying)
  RETURNS void AS
$BODY$
BEGIN
        UPDATE tblServer SET
            last_name = $2
            WHERE server_id = $1;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

When I add this procedure to the ActionProcedures list, the second parameter shows up as having a size of 1073741824 (exactly 1 GB). However when I call the procedure with a value that's only a few characters long, the following error occurs: ERROR: 22023: length for type varchar cannot exceed 10485760.

According to the documentation of PostgreSQL the maximum amount of data stored by a varchar is 1 GB, however the amount of characters (varchar(n) where n is the amount) is much less than that. See http://www.postgresql.org/docs/8.3/static/datatype-character.html

It appears that LLBLGen detects a size that's simply too large. I'm currently working around this by using a search & replace on the generated code to change to 1073741824 to 10485760. The functions will then execute properly.

How can I solve this problem? Is there a setting on the database / function that will reduce the size variable? Or is there some other way?

Thanks

Before I forget: PostgreSQL 8.3.1 with the latest LLBLGen 2.5, using Adapter template.

We'll see into this. What is the type of the field _tblServer.last_name _you are setting? For example, Could you limit the varying parameter limit as a workaround?

It doesn't detect a size as there's no size. so it picks the maximum.

Is it possible to specify a size for the varchar on the proc? It's good practise to specify a length for variable types in fields/parameters etc.

Well that's the funny thing: I am specifying the length. The function is used in a system which I am porting from MySQL to PostgreSQL. In the table the column is defined as varchar(90), while my original mysql stored procedure was defined to accept varchar(90) as well. I converted this to the postgresql function like this:

CREATE OR REPLACE FUNCTION spsetservername(pServerId integer, pServerName character varying(90))
  RETURNS void AS
$BODY$
BEGIN
        UPDATE tblServer SET
            last_name = $2
            WHERE server_id = $1;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

This is syntactically correct. However when I save the stored procedure and then view its definition, it'll show up like this:

CREATE OR REPLACE FUNCTION spsetservername(pServerId integer, pServerName character varying)

As you can see: the length is stripped away. Although it's not mentioned clearly in the documentation I have been told that PostgreSQL differs from MSSQL/MYSQL at this point and has no use for maximum lengths. However, this is my first encounter with PostgreSQL so I figured I might be doing something wrong instead

I tried switching between protocol '2' and '3' in the database driver settings in the LLBLGen project settings, but this made no difference. Encoding is set to unicode, the database is set to use UTF8.

Thanks Then we have to add a special case check for lengths on varchar parameters when reading the meta-data from the db. As the parameter creation code is generated without checking what the parameter type is, removing the length setting in the parameter creation code in the template is a bit of a problem.

Will add this first thing on monday. If you can't wait long, please grab the sourcecode of the postgresql driver from the SDK in the customer area and add the check to the stored procedure parameter obtain routine in the schemaretriever for postgresql

I don't understand the documentation of Postgresql in this case. Nowhere is the 10485760 limit mentioned. In fact, it specifies that there's a maxlength of 1GB. When there's no limit specified, it's in fact limitless...

So where the 10MB limit comes from is beyond me. The routine obtaining the meta-data sees either a length specified or no length specified. So what should it do when NO length is specified? According to the docs, the size is unlimited. However according to the error, the limit is a lot smaller than 'unlimited'

The error also isn't in the list of postgresql error codes...

Anyway, I'll change the limit from 1GB to 10MB in the driver.

See attached driver for the fix. Refresh the catalog, then regenerate code.

Yes, I've had the same problem with the documentation. It appears to be complete, but I've encountered several details which are not mentioned at all.

Thanks for the fix, I'll be able to test it later today.