-2

I need to create a stored procedure that can insert a record into the two tables

I have the two tables below:

create table "sacro".doc_submitted 
(
doc_id serial not null constraint "sacro".nc_doc_sub00,
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
job_id integer,
cdr_label varchar(255),
image_release char(1)
);


create table "sacro".job 
(
job_id serial8 not null ,
ent_no varchar(11,1),
compl_user varchar(8),
trak_no varchar(12),
job_type varchar(10),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint,
primary key (job_id)  constraint "informix".pk_job00
);

My aim is to have a stored procedure that can insert a record in both these tables based on the columns from both tables excluding job_id and doc_id.

This is what I have so far:

CREATE PROCEDURE sp_IndexDocument (
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
cdr_label varchar(255),
image_release char(1),
compl_user varchar(8),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint)

INSERT INTO sacro:doc_submitted (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,job_id,cdr_label,image_release)
VALUES (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,cdr_label,image_release);
INSERT INTO sacro:job (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send)
VALUES (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send);
END PROCEDURE;

I execute the below procedure to insert values into the two tables:

EXECUTE PROCEDURE sp_IndexDocument ('100', 'M1997554436', 'M1997554436', 'HEN481', 'EXTEND(MDY(6,28,1999), YEAR to SECOND)+9 UNITS HOUR+6 UNITS MINUTE+22 UNITS SECOND', '1', '0', '\19990107\M\1997\014436\100\1.tif', '2', '1', 'xcel', 'EXTEND(MDY(7,8,1999), YEAR to SECOND)+10 UNITS HOUR+56 UNITS MINUTE+4 UNITS SECOND', 'CM31', '692118', '19990095', 'Y','B1998025909', 'Good', 'B19980259089', 'FILE', 'N', '1', NULL, NULL, NULL, NULL)

I get the below error code:

674: Routine (sp_indexdocument) can not be resolved.

I don't understand where the problem lies if the data types are the same

1

This seems to work for me:

CREATE PROCEDURE sacro.sp_indexdocument (
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
cdr_label varchar(255),
image_release char(1),
compl_user varchar(8),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint,
job_job_type varchar(10))

DEFINE jobid INTEGER;
SELECT MAX(job_id) + 1 INTO jobid FROM job;

--Add the record to the Job Table
INSERT INTO sacro:job (ent_no, compl_user, trak_no, job_type, scan_indicator, job_accept, date_received, date_accept, agent_code, fax_send) 
               VALUES (ent_no, compl_user, trak_no, job_job_type, scan_indicator, job_accept, date_received, date_accept, agent_code, fax_send);

--Add the record to the Doc Submitted Table                                                                                     
INSERT INTO sacro:doc_submitted (doc_category_id, ent_no, trak_no, indexuser, dateindexed, doc_status, doc_mode, doc_path, doc_pages, image_exists, scan_user, scan_date, job_type, job_id, cdr_label, image_release) 
                         VALUES (doc_category_id, ent_no, trak_no, indexuser, dateindexed, doc_status, doc_mode, doc_path, doc_pages, image_exists, scan_user, scan_date, job_type, jobid, cdr_label, image_release);


--Update the workflow table to indicate the document approved date             
UPDATE sacro:bk_name_workflow set doc_approve_date=dateindexed where reference_no=trak_no;

END PROCEDURE;

The insert statement I used is below:

EXECUTE PROCEDURE sacro.sp_indexdocument(30, "M1996000465", "A0814390", "HEN500", current, 1, 0, "\19990160\M\1905\021141\90\A0814386.tif", 5, 1, "DELETE", current, "CM29", "19980018", "Y","OK","N",1,today,today,"B0245",1,"TEST")
  • This would be a better answer if you explained what you changed and why (and why what was there before was wrong). The select mechanism used for determining the job ID is dubious — it is not reliable in a multi-user environment. It isn't 100% clear what alternative to use. Since the job_id column is a SERIAL8 (it would be better to use a BIGSERIAL), you should simply insert a 0 in it (or not list it in the INSERT statement) to allocate a number automatically. Alternatively, you could use a SEQUENCE to generate the unique number. – Jonathan Leffler Jun 6 '17 at 21:41
  • Your code has a reference to job_job_type which I think is one too many jobs. – Jonathan Leffler Jun 6 '17 at 21:42
0

Don't put quotes around things that aren't strings!

You have (with some newlines and one space-after-comma added):

EXECUTE PROCEDURE sp_IndexDocument('100', 'M1997554436', 'M1997554436', 'HEN481',
    'EXTEND(MDY(6,28,1999), YEAR to SECOND)+9 UNITS HOUR+6 UNITS MINUTE+22 UNITS SECOND',
    '1', '0',
    '\19990107\M\1997\014436\100\1.tif',
    '2', '1', 'xcel',
    'EXTEND(MDY(7,8,1999), YEAR to SECOND)+10 UNITS HOUR+56 UNITS MINUTE+4 UNITS SECOND',
    'CM31', '692118', '19990095', 'Y', 'B1998025909', 'Good', 
    'B19980259089', 'FILE', 'N', '1', NULL, NULL, NULL, NULL);

The '100' will be converted from string to DECIMAL(9,0), but omitting the quotes would be clearer.

The quoted 'EXTEND(…)…' expression is just a string; it is not a date-time literal, and cannot be interpreted as one. Frankly, I think you'd do better with:

DATETIME(1999-02-28 09:06:22) YEAR TO SECOND

or even (with the quotes):

'1999-02-28 09:06:22'

instead of the expression:

EXTEND(MDY(6,28,1999), YEAR to SECOND)+9 UNITS HOUR+6 UNITS MINUTE+22 UNITS SECOND

(When the expression is quoted, you get the error SQL -1262: Non-numeric character in datetime or interval.)

You have some curious types in the table — DECIMAL(2,0) is not a common type (consider doc_status SMALLINT CHECK(doc_status BETWEEN -99 AND +99), for example — though I suspect you mean BETWEEN 0 AND 99).

The other problem is that you've passed 26 arguments to the procedure, but the sp_IndexDocument() you created only takes 22 arguments. You're also trying to pass '19990095' where the procedure expects a image_release CHAR(1). In fact, after the second EXTEND argument, the alignment of arguments given and arguments expected goes haywire; I can't work out what is supposed to be what. If ever you needed an argument against huge parameter lists, this code is it.

This mismatch leads to the SQL -674: Routine (sp_indexdocument) can not be resolved error because functions in Informix can be overloaded based on the argument list, so your call doesn't match the function that is defined, so presumably you were attempting to call a different function that couldn't be found.

When you get past those issues, by editing the EXECUTE PROCEDURE statement, you then run into problems with the notation:

INSERT INTO sacro:doc_submitted(…) VALUES(…);

Using sacro:doc_submitted means 'the table called doc_submitted found in the database sacro', whereas you want to refer to the table owned by user sacro in the current database. For that, you need a . (not a :) between the user name (schema name in standard SQL) and the table name.

When that's fixed, you get the error:

SQL -236: Number of columns in INSERT does not match number of VALUES.

This is a mismatch between the list of column names and the list of values in one or the other of the two insert statements. I've tracked that down using careful layout so that I could see that each variable matches each column name. In the first INSERT, you list column job_id in the list of columns but don't pass a value for job_id. In fact, you don't accept a job_id as a parameter, so it is easiest to remove the name.

This code 'works' — you'll have to experiment from here:

CREATE TABLE "sacro".doc_submitted 
(
doc_id SERIAL NOT NULL CONSTRAINT "sacro".nc_doc_sub00,
doc_category_id DECIMAL(9,0),
ent_no VARCHAR(11,1),
trak_no VARCHAR(12),
indexuser VARCHAR(8),
dateindexed DATETIME YEAR TO SECOND,
doc_status DECIMAL(2,0),
doc_mode DECIMAL(2,0),
doc_path VARCHAR(254),
doc_pages INTEGER,
image_exists SMALLINT,
scan_user VARCHAR(8),
scan_date DATETIME YEAR TO SECOND,
job_type VARCHAR(10),
job_id INTEGER,
cdr_label VARCHAR(255),
image_release CHAR(1)
);


CREATE TABLE "sacro".job 
(
job_id SERIAL8 NOT NULL ,
ent_no VARCHAR(11,1),
compl_user VARCHAR(8),
trak_no VARCHAR(12),
job_type VARCHAR(10),
scan_indicator VARCHAR(1),
job_accept SMALLINT,
date_received DATE,
date_accept DATE,
agent_code VARCHAR(6),
fax_send SMALLINT,
PRIMARY KEY (job_id)  CONSTRAINT "informix".pk_job00
);

CREATE PROCEDURE sp_IndexDocument (
doc_category_id DECIMAL(9,0),
ent_no VARCHAR(11,1),
trak_no VARCHAR(12),
indexuser VARCHAR(8),
dateindexed DATETIME YEAR TO SECOND,
doc_status DECIMAL(2,0),
doc_mode DECIMAL(2,0),
doc_path VARCHAR(254),
doc_pages INTEGER,
image_exists SMALLINT,
scan_user VARCHAR(8),
scan_date DATETIME YEAR TO SECOND,
job_type VARCHAR(10),
cdr_label VARCHAR(255),
image_release CHAR(1),
compl_user VARCHAR(8),
scan_indicator VARCHAR(1),
job_accept SMALLINT,
date_received DATE,
date_accept DATE,
agent_code VARCHAR(6),
fax_send SMALLINT)

INSERT INTO sacro.doc_submitted
           (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,cdr_label,image_release)
    VALUES (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,cdr_label,image_release);
INSERT INTO sacro.job
           (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send)
    VALUES (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send);
END PROCEDURE;

EXECUTE PROCEDURE sp_IndexDocument(
    100,
    'M1997554436',
    'M1997554436',
    'HEN481',
    EXTEND(MDY(6,28,1999), YEAR TO SECOND)+9 UNITS HOUR+6 UNITS MINUTE+22 UNITS SECOND,
    '1',
    '0',
    '\19990107\M\1997\014436\100\1.tif',
    '2',
    '1',
    'xcel',
    EXTEND(MDY(7,8,1999), YEAR TO SECOND)+10 UNITS HOUR+56 UNITS MINUTE+4 UNITS SECOND,
    'CM31',
    '692118',
    'Y',
    'B1998025909',
    'N',
    1,
    NULL,
    NULL,
    NULL,
    NULL);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.