External Procedure : Alternate for Timestamp

Shardul_Lumar

I am trying to create an external procedure for an insert query, however, I am getting syntax error for "Timestamp" does not seem to be a proper data type that can be accepted in the parameter list of the INSERT query. Please suggest the alternative data type that can be used instead of Timestamp. Thanks, Shardul

Navin_C

Hello Shardul, Can you post your insert query. What data format are you using for field Timestamp

Shardul_Lumar

Hi Navin, PFB the content of insert_query.sh: #!/usr/bin/ksh set -a vsql -h hostname -U username -w passsword -c "INSERT INTO dbname.tablename (column1, column2) values ($1, $2);" And below is how I tried creating the procedure: CREATE PROCEDURE ins_quer(col1 timestamp, col2 varchar) as 'insert_query.sh' language 'external' user 'dbadmin'; However, as mentioned earlier also, I was getting syntax error as below: "Parameter type is not valid for an external procedure: Timestamp" Please suggest! Thanks.

Navin_C

Hello Shardul, Try it with DATETIME instead of Timestamp. Also The arg_type parameter is optional in CREATE PROCEDURE statement, try to give parameters without data type. Each parameter is passed to the executable file as string.

Shardul_Lumar

Thanks Navin. Will try both the options and will let you know.