MVSFORUMS.com

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

We all may be knwoing the syntax for inserting hex into table
is below

insert into Table_name
values ( X'hex value').

How we can use the same syntax in a program.
How can we do this in a program (I mean what is for ????)

insert into Table_nale
values ( ????? :ws-hex-value)
_________________
Raghu

Nic Clouston · Posted: Tue May 29, 2007 2:14 pm Post subject:

Just assign your hex value into our ws-hex-value variable - assuming it is defined as being CHAR (PIC X in Cobol, I believe). I also believe that your ws variables should be of the same type as the column you are inserting into.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

Nic,

The underlying Column is Bit datatype.

The value I am getting is Hex format.

If I can not add X' in my SQL , how the DB2 knows i am supplying Hex format.

sorry If I have not put my question correctly. thanks
_________________
Raghu

kolusu · Posted: Tue May 29, 2007 3:19 pm Post subject:

suggala_r,

what are you trying to acheive? What are going to do by storing in hex that too binary field?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

dbzTHEdinosauer · Posted: Tue May 29, 2007 3:21 pm Post subject:

in any sql guide that gives you column definitions, there is also an accompanying blurb about what the equivalent COBOL/C/PL1 definition.
_________________
Dick Brenholtz
American living in Varel, Germany

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

Ok.

My requirement is as below

We have a table which needs to be inserted/populated online/runtime.

Technology : Front end : Java/J2EE. Backend : mainframe.

Stored procedure is the interface. One of the column in table is defined as
Bit type. Need for us is we need to insert data around 4000-5000 rows on realtime.

If we do each row insert, it will be too much.4000-5000 calls for DB2.
We took the approach of mass insert. Java will be sending 500 rows of Data as string per call(Stored procedure). I need parse the data and insert.

Since the string(Char) format is the input.
The Data for the column which was defined as Bit type is coming as Hex format.
That hex format value we need to insert into the Table.

If there is any other good method in this scenerio please advice.

Hope I presented the issue better than earlier. thanks
_________________
Raghu

Nic Clouston · Posted: Wed May 30, 2007 1:01 am Post subject:

All you have to do is MOVE each piece of data as you parse it into a host variable of the appropriate type.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

kolusu · Posted: Wed May 30, 2007 4:58 am Post subject:

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

Hi Kolusu,

You can google on the Datatype : Varchar for Bit in DB2 for more info.

let me get back to the my initial question :

What is the syntax for in Programming perspective

The SPUFI syntax is below :

insert into Table_name
values ( X'hex value', column2, column3).

What is the syntax in programming style :

insert into Table_name
values ( ??????, :ws-column2, :ws-column3).
_________________
Raghu

Nic Clouston · Posted: Wed May 30, 2007 12:21 pm Post subject:

If you look at the SQL reference you will see that there is no BIT data type.

If you are programming why not just assign the hex value to a working storage host variable the same as your other data? If it is hex data it will go into one of the CHAR datatypes - after all a CHAR holds all values from x'00' to x'FF'.

Also, there is no real difference between the SQL statements used in SPUFI and those used in a program.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

NIC,

Hex value will not be the same as original field length.

We need to tell DB2 that we are passing Hex value (for ex: X'FFFFFF').

If I Move FFFFF to working storage variable ws-hex-value

If my insert statement like below it won't work .

Insert table-name
Values (:WS-hex-value)

We may have to put like this or some other equivalent form.

Insert table-name
Values (X(:WS-hex-value) ) or the other.

If I use above format, I am getting bind error.DB2 is not recognizing it.
_________________
Raghu

kolusu · Posted: Wed May 30, 2007 12:44 pm Post subject:

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

kolusu · Posted: Wed May 30, 2007 2:01 pm Post subject:

suggala_r,

I give up. You are right and I am wrong. Happy. sorry for even trying to help you

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

suggala_r · Beginner Joined: 07 Nov 2004 Posts: 12 Topics: 2

Kolusu,

I am trying to present my issue here and get the answer.

Not to point to any one. This just a discussion forum.

There is no rule that every idea/thought is correct idea/thought.

As far as I am concerned, I use this forum to enrich myself.
Atlast no one is 100% knowledgeable. mostly Our work may not allow to expose all the new things happening around...

Since you mentioned that there is no Bit type on Z/Os. I put the answer.

No feelings please.

thanks again..
_________________
Raghu