I am trying to insert date into a table via a stored procedure. I am attempting to build the SQL statement from parameteres passed into the procedure. Here is the contents of the procedure so far:
Begin
Declare insert_stmt varchar(2048);
Set insert_stmt = 'insert into db2mon.' || branch || ' (TID,ipaddress,functionname,comments) values ('|| userid || ',' || ipaddress || ',' || fname || ','|| char(duration) || ',' || starttime || ',' || comments || ')';
prepare s1 from insert_stmt;
execute s1;
set rstring = insert_stmt;
End
1. I am getting an error SQL0440N error saying 'No authorized routine named || of type function having compatible arguements. I believe this is the result of the integer variable duration. The value in the table is integer but it does not I can use the || function to perform this activity. Suggestions?
2. Is the process of setting a variable, preparing it and then executing it correct?
2 3174
I am trying to insert date into a table via a stored procedure. I am attempting to build the SQL statement from parameteres passed into the procedure. Here is the contents of the procedure so far:
Begin
Declare insert_stmt varchar(2048);
Set insert_stmt = 'insert into db2mon.' || branch || ' (TID,ipaddress,functionname,comments) values ('|| userid || ',' || ipaddress || ',' || fname || ','|| char(duration) || ',' || starttime || ',' || comments || ')';
prepare s1 from insert_stmt;
execute s1;
set rstring = insert_stmt;
End
1. I am getting an error SQL0440N error saying 'No authorized routine named || of type function having compatible arguements. I believe this is the result of the integer variable duration. The value in the table is integer but it does not I can use the || function to perform this activity. Suggestions?
2. Is the process of setting a variable, preparing it and then executing it correct?
- I am no longer getting the SQL0440N error. I performed a CAST on non-charater based values. I used STRIP (CAST(variable as char()). The problem is that I am now getting issues with the data format that I am trying to insert.
-
-
I am getting an SQL0104N An unexpected token ".00" was found following "'8,2007-05-05-00.00"
-
-
Here is the sp call:
-
-
call put_monitor_info ('T50005',Test','20','Test',8,2007-05-05-00.00.00.00000,'test');
-
-
The cast function on the timestamp is set to 26 characters. If I try to wrap it in quotes, I get issues with DB2 finding the procedure due to the defined parameters.
-
-
Hi,
declare startime as varchar when you add starttime try this
char(current date) || substr(char(current time),1,5)
which will give date and time.
Hope this helps.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tim Pascoe |
last post by:
I am using the Dynamic Cross-Tab code supplied in an article from SQL
Server Magazine (http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html).
I modified the script to generate a...
|
by: Richard |
last post by:
Hi all,
I had a stored procedure that i wanted to be able to pass parameters
to so i used dynamic SQL. I am getting very strange errors though when
i try to execute it. My code works just fine,...
|
by: Bane |
last post by:
Hi all
In the SP below im (trying to) do some dynamic sql. As you can see the table
to use is set as a variable and the 'exec' method used to run the
sqlstatements.
My problem is that the 'if...
|
by: amfi1 |
last post by:
Hi
My problem is as follows:
I need to transmit data between two databases on the same server, but I
have to use dynamic database names (they must be configurable). For
example I need to...
|
by: alingsjtu |
last post by:
Hello, every body.
When execute dynamic generated multiple OPENQUERY statements (which
linkes to DB2) in SQLServer, I always got SQL1040N The maximum number
of applications is already connected...
|
by: jeoffh |
last post by:
Background:
I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete...
|
by: BitulYadav |
last post by:
Hi
i am getting the wrong result from the following code:
CREATE PROCEDURE DYNAMIC_QUERY ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------...
|
by: =?ISO-8859-1?Q?Tim_B=FCthe?= |
last post by:
Hi,
we are building a Java webapplication using JSF, running on websphere,
querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and
PreparedStatements only (aka dynamic SQL). Every night,...
|
by: IuliaS |
last post by:
Hello everyone!
I want to create a stored procedure, so I can more easily, and transparent retrieve data from db2. Long story short: when a user wants to put some data in the DB, he also creates...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |