Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Insert VARCHAR into table with REXX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
oerdgie

New User


Joined: 22 Nov 2007
Posts: 55
Location: Germany

PostPosted: Wed May 27, 2009 4:49 pm    Post subject: Insert VARCHAR into table with REXX
Reply with quote

Hi Folks,

does anybody have an exemple how to insert a VARCHAR field into a DB2 table with REXX ?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 27, 2009 5:21 pm    Post subject:
Reply with quote

This Redbook Dynamic SQL has a rather large chapter on REXX with examples.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed May 27, 2009 5:23 pm    Post subject:
Reply with quote

Thanks Dick, another one dowloaded to yonder laptop.
Back to top
View user's profile Send private message
oerdgie

New User


Joined: 22 Nov 2007
Posts: 55
Location: Germany

PostPosted: Wed May 27, 2009 6:27 pm    Post subject: Reply to: Insert VARCHAR into table with REXX
Reply with quote

Hey Dick, many thanks !

Greatings from Hartmut (DL Berlin ;-)
Back to top
View user's profile Send private message
abhisinghal2002

New User


Joined: 02 Jul 2008
Posts: 12
Location: Delhi

PostPosted: Sun May 31, 2009 7:26 pm    Post subject: Reply to: Insert VARCHAR into table with REXX
Reply with quote

Not able to download the book on my lappy...icon_sad.gif

can any one send it across.....@....e-addr removed per forum policy.


Thanks a lot in advance
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 31, 2009 7:57 pm    Post subject:
Reply with quote

abhisinghal2002 wrote:
Not able to download the book on my lappy


Then open this link and then RIGHT CLICK on the 'download PDF hyperlink' (on the displayed page) and 'SAVE LINK AS...'.
That will cause a direct download via your browser to start instead of invoking ADOBE to open (download) the PDF.
Back to top
View user's profile Send private message
abhisinghal2002

New User


Joined: 02 Jul 2008
Posts: 12
Location: Delhi

PostPosted: Thu Jun 04, 2009 4:44 pm    Post subject: Reply to: Insert VARCHAR into table with REXX
Reply with quote

Thanks a lot...
Back to top
View user's profile Send private message
Vishwamurthy

New User


Joined: 11 Mar 2008
Posts: 57
Location: India

PostPosted: Thu Jun 04, 2009 8:54 pm    Post subject:
Reply with quote

Hi!

I've tried inserting a row into DB2 using the following code

The address and contact fields are VARCHAR. How do i modify my code in order to make it run successful?

Current output window shows
Code:

INSERT OPERATION FAILED. ERROR DETAILS ARE
SQLCODE -301                             
SQLSTATE 42895                           
SQL ERROR MESSAGE 004:                   
***                                       


Actual code is
Code:

/* REXX */                                                              00010000
ADDRESS TSO "SUBCOM DSNREXX"                                            00020000
IF RC = 0 THEN                                                          00030000
   CALL RUN                                                             00040000
ELSE                                                                    00050000
   DO                                                                   00060000
      SIGNAL ON SYNTAX NAME RXSUBCOM_FAILED                             00070000
      RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')                          00080000
      SAY 'DSNREXX MADE EXPLICITLY AVAILABLE. RETURN CODE' RC           00090000
      SIGNAL OFF SYNTAX                                                 00100000
   END                                                                  00110000
RUN:                                                                    00120000
SQLSTMT1 = "SELECT MAX(AGENTID) FROM AGENT"                             00121000
SQLSTMT2 = "INSERT INTO AGENT VALUES ( ?, ?, ?, ?)"                     00130000
SAY 'ENTER YOUR DATE OF BIRTH'                                          00130100
PULL DOBA                                                               00130200
SAY 'ENTER YOUR ADDRESS'                                                00130300
PULL ADDRESSA                                                           00130400
SAY 'ENTER YOUR CONTACT NUMBER'                                         00130500
PULL CONTACTA                                                           00130600
ADDRESS DSNREXX                                                         00130700
"CONNECT DSN1"                                                          00130800
"EXECSQL DECLARE C1 CURSOR FOR S1"                                      00130900
"EXECSQL DECLARE C2 CURSOR FOR S2"                                      00131000
"EXECSQL PREPARE S1 FROM :SQLSTMT1"                                     00131100
"EXECSQL DESCRIBE S1 INTO :OUTSQLDA"                                    00131200
"EXECSQL PREPARE S2 FROM :SQLSTMT2"                                     00131300
"EXECSQL OPEN C1"                                                       00131400
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"                           00131500
IF SQLCODE = 0 THEN                                                     00131600
   DO                                                                   00131700
      AGENTIDA = OUTSQLDA.1.SQLDATA                                     00131801
      AGENTIDA = AGENTIDA + 1                                           00131901
      "EXECSQL CLOSE C1"                                                00132004
      DOBA = '1985-03-24'                                               00132105
      ADDRESSA = 'ASIA'                                                 00132205
      CONTACTA = '+919740536135'                                        00132305
      AGENTIDA = 10000004                                               00132405
      "EXECSQL EXECUTE S2 USING :AGENTIDA, :DOBA, :ADDRESSA, :CONTACTA" 00132504
      IF SQLCODE = 0 THEN                                               00132601
         SAY 'INSERT SUCCESSFUL'                                        00132701
      ELSE                                                              00133000
         DO                                                             00134000
            SAY 'INSERT OPERATION FAILED. ERROR DETAILS ARE'            00135000
            SAY 'SQLCODE' SQLCODE                                       00136005
            SAY 'SQLSTATE' SQLSTATE                                     00136105
            SAY 'SQL ERROR MESSAGE' SQLERRMC                            00136205
         END                                                            00137000
   END                                                                  00137100
ELSE                                                                    00137200
   DO                                                                   00137300
      SAY 'DATABASE ERROR OCCURRED IN CALCULATING THE AGENT ID'         00137400
      SAY 'SQLCODE' SQLCODE 'AND SQLSTATE' SQLSTATE                     00137500
   END                                                                  00137600
"DISCONNECT DSN1"                                                       00138000
EXIT                                                                    00139000
RXSUBCOM_FAILED:                                                        00140000
SAY 'PROBLEM LOADING DSNREXX. RETURN CODE' RC                           00150000
EXIT                                                                    00160000


Thanks in advance.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 04, 2009 9:52 pm    Post subject:
Reply with quote

Just a guess:
CONTACTA = '+919740536135'
though you entered it as a string, I image the plus sign caused the interpreter during the SQL submission to send CONTACTA as signed numeric.
Back to top
View user's profile Send private message
Vishwamurthy

New User


Joined: 11 Mar 2008
Posts: 57
Location: India

PostPosted: Fri Jun 05, 2009 10:27 am    Post subject:
Reply with quote

I gave CONTACTA = '9740536135' but still it throws the same message. The possibility as it shows SQLCODE=-301 could be due to the varchar nature of the columns ADDRESS and CONTACT
Back to top
View user's profile Send private message
Vishwamurthy

New User


Joined: 11 Mar 2008
Posts: 57
Location: India

PostPosted: Fri Jun 05, 2009 11:23 am    Post subject:
Reply with quote

Problem solved now icon_smile.gif. If I am accepting CONTACT from the console, I should give in quotes like '9740536135' and if I am assigning in the routine, i should do it this way CONTACTA = "'+919972524373'"
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jun 05, 2009 5:58 pm    Post subject:
Reply with quote

Vishwamurthy,

thx for the feedback.
Back to top
View user's profile Send private message
Rexxit

New User


Joined: 24 Jun 2009
Posts: 1
Location: New York

PostPosted: Fri Jun 26, 2009 5:34 pm    Post subject:
Reply with quote

I'm having trouble with varchar when inserting using DSNREXX. The varchar defined as 3000 bytes but rexx is changing it to char so I'm unable to insert anything larger than 255 bytes. Any ideas? Thanks. Joe.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us