Hi, I have a table like this:

[essiprojects]

Hi,
I have a table like this:

create table A
(
id int,
name char(4000)
);

I have a procedure where I assign values to field name, but when the assigned value is greater than 255 characters, the name is truncated.

Does anybody know What I have to do for maintaining all the characters?
Does anybody know any other data type that permits that?

Thank you.

 

[vpshriyan]

Hi,

A CHAR datatype is capable of storing character strings up to 32,767 ASCII characters, where as a VARCHAR type can store character strings of varying length, no greater than 255. In your procedure is seems, you are storing the data first in a VARCHAR type memory variable.

If you are using Informix-4GL for developing applications, you can define the variables implicitly or explicitly. For example:

define c_name like a.name (where a is table name)
define c_name char(4000)

If you could tell more about your procedure where you assign value to the field, it will help in analysing the problem in a better manner.

Regards,
Shriyan

 

[essiprojects]

Hi,

I've got table like this:

create table B (
id integer,
name varchar(255)
);

The table doesn't have primary key. My procedure compacts the table B in table A where
name is a multivalued field. For exemple,

B
----------------------------
id name
----------------------------
1 a
1 b


And, after the procedure, A contains:

A
----------------------------
id name
----------------------------
1 a, b


So I want to create a field, here called name that could contain a String . But this String must
be greater than 256.

I don't know if I have explained it better. If not, I'll try again.

Thank you.

 

[vpshriyan]

Hi,

Use inbuilt SQL function TRIM in your select statement to remove leading and trailing empty spaces in the data. Example:

select trim(name) from B where ...

You can see the effect of trim function in the following example:

create temp table B (id int,name char(255)) with no log;
insert into B values (1,'a');
insert into B values (1,'b');

select "x" || name || "x" from b;
select "x" || trim(name) || "x" from b;

If you have access to Informix-4GL, you can use CLIPPED keyword for triming the trailing blank spaces in the data in varchar data type. Example:

define nameA char(4000)
define nameB1,nameB2 varchar(255)

let nameB1="a "
let nameB2="b "

let nameA = nameB1 clipped, "," nameB2 clipped

The following 4GL routine imitates the SQL TRIM functionality.

function alltrim(in_string)
define i,len smallint, in_string varchar(255)

let len = length(in_string) clipped
for i=1 to len
if in_string[i,i] != " " then
exit for
end if
end for
return in_string[i,len]
end function

Regards,
Shriyan

 

[essiprojects]

Hi,

I have done the trim when I have done the insert. The problem is that there are more than 255 characters and I don't have extra blanks. My problem is that the field name of table A doesn't allow to insert a field greater than 255 characters. When I have insert one field greater than 255 it have truncated it to 255.

I suppose that I must change the data type. I have seen that LVARCHAR permits that, but I haven't found the syntax for the definition.

Do you know if I'm right?

Thank you.

 

[vpshriyan]

Hi,

The following 4GL code works fine and I have tested it. When you do a direct insert from field to field the limitation of 256 characters will prop up.

database testdb
main
define string char(4000)
create temp table B (id int,name char(4000)) with no log;
let cmd=
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
" aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
insert into b values('1',string);
unload to 'x.unl' select * from B;
end main

Alternatively you can consider the TEXT datatype for storing large texual data.

Regards.
Shriyan

 

[essiprojects]

Hi,

I don't know if I have some limitation or not, but I cann't. I tried to define as text but it wasn't possible too. I defined with text, but I don't know how to do to concat with the new values.

For exemple, how to do:

insert into A values (id, b1.name || b2.name);

I suppose that I have to use LET but I cann't do it. It gave me an error. Do you know how I have to do?

Thank you.

 

[vpshriyan]

Hi,

For TEXT and BYTE variables, being BLOB kind, LET can assign only NULL values. The LET statement cannot assign any other values. To assign values to these variables, you can do one of the following:

· Use the INTO clause of the SELECT, FOREACH, OPEN, or FETCH statement.
· Pass the name of the variable as an argument to a function.
Informix 4GL performs data type conversion on compatible data types.

However, in your case, you should able to use CHAR datatype without truncating the information, as it is capble to storing up to 32K characters.

Regards
Shriyan

 

[essiprojects]

Hi,

Do you know why I cann't use CHAR data type without that it truncates the String? Do you know how I can resolve it?

Thank you.

 

[vpshriyan]

Hi,

What is the result for the following 4GL code, in your setup ? Is it working properly as intended ?

Regards,
Shriyan

database testdb
main
define string1,string2,string3 char(1000), i smallint
create temp table tmpx (f1 char(1000)) with no log

let string1 = replicate("A", 400)

let i = length(string1 clipped)
display string1 clipped
display i

let string2 = replicate("B", 300)
let i = length(string2 clipped)
display string2 clipped
display i

let string3 = string1 clipped, "+++", string2
let i = length(string3 clipped)
display string3 clipped
display i

insert into tmpx values (string3)
unload to "tmpx.unl" select * from tmpx

end main

function replicate(pattern,ntimes)
define pattern char, ntimes,i smallint, stringx char(1000)
for i=1 to ntimes
let stringx = stringx clipped, pattern
end for
return stringx
end function

 

[essiprojects]

Hi,
Thank you for your response, but in the project that I'm doing, I cann't use 4GL. I have to use PL/SQL, because I'm working in an remote machine (from a client).
Until now I was looking for an error and I think that the problem is in concat operation when concats the attribute retrieved from the database.

I have a table like this where I put the values to see if all is correct:

CREATE TABLE A (
serial integer,
a CHAR(1000)
);


CREATE PROCEDURE "informix".select(p_serial INTEGER)
DEFINE p_a LIKE table.a;

DEFINE len INTEGER;
DEFINE lenaux INTEGER;

DEFINE cad_a CHAR(1000);

LET p_a = "";
LET p_i = 0;
LET len = 0;
LET lenaux = 0;

SELECT count(distinct codasignatura)
INTO p_count
FROM table
WHERE serial = p_serial;

LET cad_a = "";

FOREACH WITH HOLD
SELECT a
INTO p_a
FROM table
WHERE serial = p_serial

ON EXCEPTION
END EXCEPTION

INSERT INTO A (serial, a)
VALUES (len, p_a);

LET cad_a = SUBSTR(cad_a, 1, len) || p_a;

INSERT INTO A (serial, a)
VALUES (len, cad_a);

LET lenaux = LENGTH(p_a);
LET len = lenaux + len;

INSERT INTO A (serial, a)
VALUES (len, cad_a);

END FOREACH;

END PROCEDURE;

It goes wrong when retrieves 'a' and cad_a is greater than 255 characters. p_a has all the values, but when tries the second insert it goes wrong. I have tried to put the values of the database directly, and then goes well. I don't know why it goes wrong.

Does anybody know that?

Thank you.

 

[vpshriyan]

Hi,

Stored Procedure Language (SPL) of Informix is quite primitive. There is limited ability to do string manipulation and in some angle it is brain-dead too.(example: name[i,i] is invalid. Subscripts must always be constants. You cannot use variables as subscripts!).

In the following example I do not know what it offers to remove the trailing blank spaces from the contents; which is actually happening in your stored procedure.

create procedure merge()

define string1 char(20);
define string2 char(20);
define string3 char(40);

set debug file to 'merge.log';
trace on;

let string1 = "A ";
let string2 = "B ";

let string3 = string1 || string2 || "X";

end procedure;

contents of merge.log:

trace on

let string1 = A
let string2 = B
expression|| (|| string1, string2), "X&quot
evaluates to A B X
let string3 = A B X
procedure merge returned no data

I wanted string3 to be containing "ABX".

Regards,
Shriyan