Styjun

Number of matrices with bounded products of rows and columns

Tikz: The position of a label change step-wise and not in a continuous way

Compute the square root of a positive integer using binary search

What is the opposite of "hunger level"?

What exactly happened to the 18 crew members who were reported as "missing" in "Q Who"?

What happened after the end of the Truman Show?

Can I submit a paper computer science conference using an alias if using my real name can cause legal trouble in my original country

Ending a line of dialogue with "?!": Allowed or obnoxious?

A reccomended structured approach to self studying music theory for songwriting

Can an ally use your Shadow Blade without it dissipating?

Why do aircraft leave cruising altitude long before landing just to circle?

When does The Truman Show take place?

Build a mob of suspiciously happy lenny faces ( ͡° ͜ʖ ͡°)

What allows us to use imaginary numbers?

Why can't I see 1861 / 1871 census entries on Freecen website when I can see them on Ancestry website?

Parse a simple key=value config file in C

Why does this image of cyclocarbon look like a nonagon?

Which basis does the wavefunction collapse to?

What are some tips and tricks for finding the cheapest flight when luggage and other fees are not revealed until far into the booking process?

What's the relationship betweeen MS-DOS and XENIX?

Have made several mistakes during the course of my PhD. Can't help but feel resentment. Can I get some advice about how to move forward?

Polar contour plot in Mathematica?

Did Michelle Obama have a staff of 23; and Melania have a staff of 4?

May the tower use the runway while an emergency aircraft is inbound?

Create a Trigger to genearate a random alphanumeric string in informix

How to get trailing spaces from varchar column in Informix using ODBCHow to overwrite matching key in “load from” statement in informix sqlSerial Fields in Informix for ORM frameworkInformix insert trigger: update value on same table but different recordShell script for insert multiple records into a DatabaseInformix trigger to change inserted valuesInformix 11.7 returns -1 as number of affected rows when using DateTime or string parameter in UPDATE querySpark reading data from IBM Informix database “Not enough tokens are specified in the string representation of a date value”CREATE TRIGGER to monitor a specific table rowCreate a Trigger to change values before insert in Informix

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

To create a trigger before insert using Informix database.
When we try to insert a record into the table it should insert random alphanumeric string into one of the field. Are there any built in functions?

The table consists of the following fields:

• empid serial NOT NULL


• age int


• empcode varchar(10)

and I am running

insert into employee(age) values(10);

The expected output should be something as below:

 id age empcode
 1, 10, asf123*

Any help is appreciated.

informix

share|improve this question

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  In Informix there are no built in functions to generate random strings. You will have to create your own.
  
  – Luís Marques
  Mar 29 at 8:18
  

  
  
  
  

add a comment | 

1

To create a trigger before insert using Informix database.
When we try to insert a record into the table it should insert random alphanumeric string into one of the field. Are there any built in functions?

The table consists of the following fields:

• empid serial NOT NULL


• age int


• empcode varchar(10)

and I am running

insert into employee(age) values(10);

The expected output should be something as below:

 id age empcode
 1, 10, asf123*

Any help is appreciated.

informix

share|improve this question

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  In Informix there are no built in functions to generate random strings. You will have to create your own.
  
  – Luís Marques
  Mar 29 at 8:18
  

  
  
  
  

add a comment | 

To create a trigger before insert using Informix database.
When we try to insert a record into the table it should insert random alphanumeric string into one of the field. Are there any built in functions?

The table consists of the following fields:

• empid serial NOT NULL


• age int


• empcode varchar(10)

and I am running

insert into employee(age) values(10);

The expected output should be something as below:

 id age empcode
 1, 10, asf123*

Any help is appreciated.

informix

share|improve this question

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

insert into employee(age) values(10);

 id age empcode
 1, 10, asf123*

share|improve this question

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

share|improve this question

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

edited Mar 27 at 14:06

einpoklum

42.1k2828 gold badges147147 silver badges292292 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

asked Mar 27 at 13:32

P.S.MahalakshmiP.S.Mahalakshmi

433 bronze badges

1 Answer
1

active

oldest

votes

1

As already commented there is no existing function to create a random string however it is possible to generate random numbers and then convert these to characters. To create the random numbers you can either create a UDR wrapper to a C function such as random() or register the excompat datablade and use the dbms_random_random() function.
Here is an example of a user-defined function that uses the dbs_random_random() function to generate a string of ASCII alphanumeric characters:

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
 let n = mod(abs(dbms_random_random()), 62);
 if (n < 10)
 then
 let n = n + 48;
 elif (n < 36)
 then
 let n = n + 55;
 else
 let n = n + 61;
 end if
 let s = s || chr(n);
end for

return s;
end function;

This function can then be called from an insert trigger to populate the empcode column of your table.

share|improve this answer

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,thanks for the response I created trigger and called the above functionas below create trigger rand_trigtest INSERT ON employeetest FOR EACH ROW(EXECUTE FUNCTION random_string() INTO empcode) iam getting dbms_random_random cant be resolved.we will try to install the following package,and can u let us know how the above function works
  
  – P.S.Mahalakshmi
  Apr 1 at 5:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @P.S.Mahalakshmi — The +48 converts values 0..9 into digits '0'..'9' (which have code points 48..57 in ASCII (and Unicode, and ISO 8859, etc). The +55 could be regarded as - 10 (to allow for the 10 digits) and + 65 where 65 is the ASCII code for 'A'; it converts codes 10..36 into letters A-Z). And the +61 could be regarded as - 36 (ten digits, twenty-size upper-case letters) and + 97, where 97 is the ASCII code for 'a'; it converts codes 37..63 into letters a-z.
  
  – Jonathan Leffler
  Apr 1 at 17:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,we have implemented the above trigger in one of our environment and facing the below issue. please find the error when application tries to call a post api as below:
  
  – P.S.Mahalakshmi
  May 7 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  2019-05-06 10:39:02 ERROR [Informix_Conn.py : 37] QRY001 error in query : INSERT INTO dots.project_organization_map (project_guid, org_guid, project_manager_uid, project_start_date, project_end_date, cretaedby_empid, createdby_timestamp, last_updatedby_empid, last_updated_timestamp,project_name)
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  VALUES('a234e3b4-1319-11e9-87d3-6a0ba69fbc52', 'pGkFc4niJ6', '0469B4744', '2018-11-08', '2019-10-31', 'AVL2UA744', current, 'AVL2UA744', current, 'calimtest0001'); 2019-05-06 10:39:02 ERROR [Informix_Conn.py : 43] DB001 : Statement Execute Failed: [IBM][CLI Driver][IDS/UNIX64] Unique constraint (informix.project_organization_map_pk) violated. SQLCODE=-268
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  

 | 
show 5 more comments

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55378493%2fcreate-a-trigger-to-genearate-a-random-alphanumeric-string-in-informix%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

As already commented there is no existing function to create a random string however it is possible to generate random numbers and then convert these to characters. To create the random numbers you can either create a UDR wrapper to a C function such as random() or register the excompat datablade and use the dbms_random_random() function.
Here is an example of a user-defined function that uses the dbs_random_random() function to generate a string of ASCII alphanumeric characters:

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
 let n = mod(abs(dbms_random_random()), 62);
 if (n < 10)
 then
 let n = n + 48;
 elif (n < 36)
 then
 let n = n + 55;
 else
 let n = n + 61;
 end if
 let s = s || chr(n);
end for

return s;
end function;

This function can then be called from an insert trigger to populate the empcode column of your table.

share|improve this answer

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,thanks for the response I created trigger and called the above functionas below create trigger rand_trigtest INSERT ON employeetest FOR EACH ROW(EXECUTE FUNCTION random_string() INTO empcode) iam getting dbms_random_random cant be resolved.we will try to install the following package,and can u let us know how the above function works
  
  – P.S.Mahalakshmi
  Apr 1 at 5:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @P.S.Mahalakshmi — The +48 converts values 0..9 into digits '0'..'9' (which have code points 48..57 in ASCII (and Unicode, and ISO 8859, etc). The +55 could be regarded as - 10 (to allow for the 10 digits) and + 65 where 65 is the ASCII code for 'A'; it converts codes 10..36 into letters A-Z). And the +61 could be regarded as - 36 (ten digits, twenty-size upper-case letters) and + 97, where 97 is the ASCII code for 'a'; it converts codes 37..63 into letters a-z.
  
  – Jonathan Leffler
  Apr 1 at 17:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,we have implemented the above trigger in one of our environment and facing the below issue. please find the error when application tries to call a post api as below:
  
  – P.S.Mahalakshmi
  May 7 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  2019-05-06 10:39:02 ERROR [Informix_Conn.py : 37] QRY001 error in query : INSERT INTO dots.project_organization_map (project_guid, org_guid, project_manager_uid, project_start_date, project_end_date, cretaedby_empid, createdby_timestamp, last_updatedby_empid, last_updated_timestamp,project_name)
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  VALUES('a234e3b4-1319-11e9-87d3-6a0ba69fbc52', 'pGkFc4niJ6', '0469B4744', '2018-11-08', '2019-10-31', 'AVL2UA744', current, 'AVL2UA744', current, 'calimtest0001'); 2019-05-06 10:39:02 ERROR [Informix_Conn.py : 43] DB001 : Statement Execute Failed: [IBM][CLI Driver][IDS/UNIX64] Unique constraint (informix.project_organization_map_pk) violated. SQLCODE=-268
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  

 | 
show 5 more comments

1

As already commented there is no existing function to create a random string however it is possible to generate random numbers and then convert these to characters. To create the random numbers you can either create a UDR wrapper to a C function such as random() or register the excompat datablade and use the dbms_random_random() function.
Here is an example of a user-defined function that uses the dbs_random_random() function to generate a string of ASCII alphanumeric characters:

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
 let n = mod(abs(dbms_random_random()), 62);
 if (n < 10)
 then
 let n = n + 48;
 elif (n < 36)
 then
 let n = n + 55;
 else
 let n = n + 61;
 end if
 let s = s || chr(n);
end for

return s;
end function;

This function can then be called from an insert trigger to populate the empcode column of your table.

share|improve this answer

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,thanks for the response I created trigger and called the above functionas below create trigger rand_trigtest INSERT ON employeetest FOR EACH ROW(EXECUTE FUNCTION random_string() INTO empcode) iam getting dbms_random_random cant be resolved.we will try to install the following package,and can u let us know how the above function works
  
  – P.S.Mahalakshmi
  Apr 1 at 5:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @P.S.Mahalakshmi — The +48 converts values 0..9 into digits '0'..'9' (which have code points 48..57 in ASCII (and Unicode, and ISO 8859, etc). The +55 could be regarded as - 10 (to allow for the 10 digits) and + 65 where 65 is the ASCII code for 'A'; it converts codes 10..36 into letters A-Z). And the +61 could be regarded as - 36 (ten digits, twenty-size upper-case letters) and + 97, where 97 is the ASCII code for 'a'; it converts codes 37..63 into letters a-z.
  
  – Jonathan Leffler
  Apr 1 at 17:47
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi,we have implemented the above trigger in one of our environment and facing the below issue. please find the error when application tries to call a post api as below:
  
  – P.S.Mahalakshmi
  May 7 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  2019-05-06 10:39:02 ERROR [Informix_Conn.py : 37] QRY001 error in query : INSERT INTO dots.project_organization_map (project_guid, org_guid, project_manager_uid, project_start_date, project_end_date, cretaedby_empid, createdby_timestamp, last_updatedby_empid, last_updated_timestamp,project_name)
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  VALUES('a234e3b4-1319-11e9-87d3-6a0ba69fbc52', 'pGkFc4niJ6', '0469B4744', '2018-11-08', '2019-10-31', 'AVL2UA744', current, 'AVL2UA744', current, 'calimtest0001'); 2019-05-06 10:39:02 ERROR [Informix_Conn.py : 43] DB001 : Statement Execute Failed: [IBM][CLI Driver][IDS/UNIX64] Unique constraint (informix.project_organization_map_pk) violated. SQLCODE=-268
  
  – P.S.Mahalakshmi
  May 7 at 14:06
  

  
  
  
  

 | 
show 5 more comments

As already commented there is no existing function to create a random string however it is possible to generate random numbers and then convert these to characters. To create the random numbers you can either create a UDR wrapper to a C function such as random() or register the excompat datablade and use the dbms_random_random() function.
Here is an example of a user-defined function that uses the dbs_random_random() function to generate a string of ASCII alphanumeric characters:

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
 let n = mod(abs(dbms_random_random()), 62);
 if (n < 10)
 then
 let n = n + 48;
 elif (n < 36)
 then
 let n = n + 55;
 else
 let n = n + 61;
 end if
 let s = s || chr(n);
end for

return s;
end function;

This function can then be called from an insert trigger to populate the empcode column of your table.

share|improve this answer

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
 let n = mod(abs(dbms_random_random()), 62);
 if (n < 10)
 then
 let n = n + 48;
 elif (n < 36)
 then
 let n = n + 55;
 else
 let n = n + 61;
 end if
 let s = s || chr(n);
end for

return s;
end function;

share|improve this answer

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges

answered Mar 29 at 9:24

Simon RiddleSimon Riddle

38111 silver badge22 bronze badges