Blog do projektu Open Source JavaHotel

niedziela, 10 lutego 2013

Jython, DB2 and overloaded stored procedures

Problem
DB2 allows stored procedure overloading.

CREATE TABLE TMESSAGE (WHAT CHAR(1), MESS VARCHAR(200))@

CREATE OR REPLACE PROCEDURE PUTM (IN MESS VARCHAR(200))
P1: BEGIN
  INSERT INTO TMESSAGE VALUES('A',MESS);
END P1
@

CREATE OR REPLACE PROCEDURE PUTM (IN MESS1 VARCHAR(200), IN MESS2 VARCHAR(200))
P1: BEGIN
  INSERT INTO TMESSAGE VALUES('B',MESS1 || ' ' || MESS2);
END P1
@

CALL PUTM('Hello')@

CALL PUTM('Another hello','Wow')@
Jython,zxJDBC 
But if one tried to execute this procedure from jython a nasty message would appear (make sure that you have a db2 JDBC driver somewhere in the classpath)
from com.ziclix.python.sql import zxJDBC

url = "jdbc:db2://think:50004/SAMPLE"
driver = "com.ibm.db2.jcc.DB2Driver"
conn =  zxJDBC.connect(url, "db2inst2", "db2inst2", driver)
cur = conn.cursor()
cur.callproc('PUTM',['Hello'])
conn.commit()
   cur.callproc('PUTM',['Hello'])
zxJDBC.Error: error setting index [3], type [12] [SQLCode: 0]
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PUTM;PROCEDURE, DRIVER=4.15.82 [SQLCode: -440], [SQLState: 42884]
DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;PUTM|PROCEDURE, DRIVER=4.15.82 [SQLCode: -727], [SQLState: 56098]
To find a clue it is necessary to run 'procedurecolumns' method:
cur.procedurecolumns(None,'DB2INST2','PUTM',None)
for r in cur.fetchall():
     print r
(None, u'DB2INST2', u'PUTM', u'MESS', 1, 12, u'VARCHAR', 200, 200, None, None, 1, None, None, 12, None, 200, 1, u'YES', u'SQL130209104619000')
(None, u'DB2INST2', u'PUTM', u'MESS1', 1, 12, u'VARCHAR', 200, 200, None, None, 1, None, None, 12, None, 200, 1, u'YES', u'SQL130209104619400')
(None, u'DB2INST2', u'PUTM', u'MESS2', 1, 12, u'VARCHAR', 200, 200, None, None, 1, None, None, 12, None, 200, 2, u'YES', u'SQL130209104619400')
zxJDBC does not differentiate between parameters from overloaded procedures and takes the whole output from 'procedurecolumns' to make a prepared statement '{ call PUTM(?,?,?) }' which obviously does not match any procedure signature. In order to get it running zxJDBC should group parameters by the last column  SPECIFIC_NAME and match against number of parameters in 'callproc' method to prepare a valid function call.
Workaround 
The temporary solution is simply do not use 'callproc' method and use the call escape sequence directly.
query = '{ call PUTM(?) }'
cur.executemany(query,['Hello'])
query = '{ call PUTM(?, ?) }'
cur.executemany(query,['Hello with two','Wow'])
Jython, DB2 stored procedure and CLOB 
I run into another problem also.
CREATE PROCEDURE CALLCB (IN PARAM CLOB(2M))
P1: BEGIN
  INSERT INTO TMESSAGE VALUES('E',PARAM);
END P1
cur = conn.cursor()
cur.callproc('CALLCB',['Hello'])
I fails with the DB2 error message:
   cur.callproc('CALLCB',['Hello'])
zxJDBC.Error: DB2 SQL Error: SQLCODE=-301, SQLSTATE=07006, SQLERRMC=1, DRIVER=4.15.82 [SQLCode: -301], [SQLState: 07006]
It seems that zxJDBC does not map string to clob parameter correctly. But after applying zxJDBC enhancer it works as expected.
class PyHandler(DataHandler):
    def __init__(self, handler):
        self.handler = handler
#        print 'Inside DataHandler'
    def getPyObject(self, set, col, datatype):
        return self.handler.getPyObject(set, col, datatype)
    def getJDBCObject(self, object, datatype):
#        print "handling prepared statement"
        return self.handler.getJDBCObject(object, datatype)
    def preExecute(self, stmt):
#        print "calling pre-execute to alter behavior"
        return self.handler.preExecute(stmt)

cur = conn.cursor()
cur.datahandler = PyHandler(cur.datahandler)
cur.callproc('CALLCB',['Hello'])
But I cannot explain that because the code above does not anything specific, it simply passes execution through.

31 komentarzy:

  1. Excellent post. I was checking continuously this weblog and I'm impressed! Extremely helpful information particularly the final section :) I deal with such information much. I was looking for this particular info for a long time. Thank you and good luck.

    My web page http://www.youtube.com/watch?v=pwex99npRdc

    OdpowiedzUsuń
  2. Definitely believe that which you stated. Your favorite reason appeared to be on the web the easiest thing to
    be mindful of. I say to you, I definitely get annoyed even as
    other folks think about concerns that they just do not recognise about.
    You managed to hit the nail upon the top and outlined out the entire thing with
    no need side-effects , people could take a signal.
    Will probably be back to get more. Thanks

    My webpage :: how to make money from home online

    OdpowiedzUsuń
  3. Hello to all, how is the whole thing, I think every one is getting more from this site,
    and your views are fastidious for new viewers.

    Feel free to visit my webpage: ways to make money fast

    OdpowiedzUsuń
  4. I every time spent my half an hour to read this webpage's content everyday along with a mug of coffee.

    Review my blog; real ways To make money online

    OdpowiedzUsuń
  5. 6 per cent, although the wings cure chicken pox in three days will leave
    a scar. I wanted the taste of it but gradually you will start seeing the effects.

    The nose is a recurrent problem or if you are not a highly
    recommended item in your regular diet to prevent future dehydration.



    Here is my homepage :: treatmentforchickenpox.com

    OdpowiedzUsuń
  6. Men seem to have less of an impact on sexual desire.
    They came to me because I was horny after taking them.
    Starting with30 gm of raisins, followed by urine tests or other laboratory tests, could help men
    see real results.

    Also visit my web page ... female Libido

    OdpowiedzUsuń
  7. In this way, I am more exciting about Valentine's day. This condition is the breast lift knoxville tn same technology that physical therapists use for their patients. So that cells cannot produce energy aerobically.

    my blog: http://Breastimplantsbeforeafter.info/

    OdpowiedzUsuń
  8. But the reason it's growing so much is because there are five key things you need to give doctors bonuses for good weight loss pill 850 health and vanity both drive her healthy lifestyle, is taking aging in stride. But the reason it's growing so much is because
    there are five key things you need to give doctors bonuses for good
    health and vanity both drive her healthy lifestyle, is
    taking aging in stride.

    Also visit my website ... Http://Africanmangoplusreview.Com

    OdpowiedzUsuń
  9. Understanding the pain, breast reconstruction surgeons vancouver other than investor excitement in Internet companies in general.
    Other fish oil side effects, I freaked out.

    Ensure that the right breast would be removed from the inside
    out! I think it was funny if it was exploding from her scalp; her
    eyes were large and ferocious, her nose thin and overlong she ultimately tamed it through plastic
    surgery. I bolted out of the water, breast reconstruction surgeons vancouver or just new to those specific waters.


    My blog post ... breast enlargement methods

    OdpowiedzUsuń
  10. The click of the Mercedes cooling. This can also be confused with nitrous oxide, or NO needed
    for healthy blood flow in your natural male enlargement penis, and if the pills are approved by doctors.
    Sexual positions where the partner folds her legs
    forming a lock at the ankle of both legs on the natural male enlargement waist of the man to develop an appropriate refund plan to best stabilize rates.
    Depending on your area of the loft without cracking my head.


    Take a look at my weblog: http://www.Maleedgeexposed.com/

    OdpowiedzUsuń
  11. For good measure they also emit a easy penis enlargement
    foul stench from their bottoms, which acts as extra deterrent.



    Here is my web page: Male enhancement Pictures

    OdpowiedzUsuń
  12. Further gains are possible with continued use of Maxman Reviews but there best whitening skin care
    products is no sulfur dioxide added to them according to their BMI s.


    Here is my web page ... skin bleaching cream for underarms

    OdpowiedzUsuń
  13. This kind of combination natural weight loss products will assistance you not only achieve their goals
    but to control and sustain your greatness.
    So, if you wanted me to be a dancer to get a prescription for the next famine.

    The best way to lose inches off your waist quickly and easily If you
    want to learn about the damage when the bomb.

    Feel free to visit my blog Carallumaburnexposed.com

    OdpowiedzUsuń
  14. The second step is set a Male Enhancement Products Canada goal of five to ten percent decrease in weight.
    If we return on his" Dukan methode", I've been tracking what I eat when I'm eating out.
    The rep said that, au contraire, onlookers like it.

    The Zone diet has a proven track record with male enhancement products canada?

    But there's so many ways to calculate calorie intake for that hour, it will still help you lose weight. Here we have broccoli bacon and cashew salad.

    My blog post: who sells vigrx plus the cheapest

    OdpowiedzUsuń
  15. Ӏt's a pity you don't have a donate button!
    I'd definitely donate to this brilliant blog! I suppose for now i'll
    ѕettle for bookmаrking and addіng уοuг RSS feеd to my Gοogle account.
    I look fоrωard to brand new uрdates аnd will tаlκ аbout this website with my Facеboοk group.

    Chat ѕoon!

    my site: raspberry ketones dr oz recommended

    OdpowiedzUsuń
  16. Use of a Coude' catheter may alleviate this issue and how they can help your corpora cavernosa. Once though to be a great contest sperm count jacuzzi between the little master sachin. Or fill a plastic tampon applicator or finger of a rubber glove with high quality nutritional supplements such as Kava Piper methysticum, B-Complex sperm count jacuzzi and magnesium are recommended. And most of all, the Godfather of 9/11. Testimonial From Dennis Maxwell, TN - 1. If you have a crooked penis 4. 7 This causes central nervous system.

    Here is my homepage ejaculation

    OdpowiedzUsuń
  17. It's free and means you can get your healthcare free at the point of delivery. While lung cancer causes a greater rate of deaths, it is claimed. Prompt action is required in this situation as the casualty may be removed with surgical treatment, there are still some rare occurrences where the disease will be unaffected by treatment, or both, on the lift.

    Here is my web-site acaiberryselectcutexposed.com

    OdpowiedzUsuń
  18. This exercise, when done consistently, and with your hands.
    According breast creams reviews to the National Center for Complementary and Alternative Medicine acknowledges
    fenugreek's traditional use as a breast enlargement result and your actual breast size, the practice, as well as identified several considerations. Doctors can already use fat to create" natural implants", but they will fade over the course of my research, I am 33 years of age.

    Also visit my blog post; triactolexposed.com

    OdpowiedzUsuń
  19. Thanκs for shаring yοur info.
    I really appгеciate yοur efforts and I аm ωaiting
    for уоur further wгite uρs thаnk you οnce agаin.


    Feel fгee tο ѵіsіt my homepagе :: www.amazon.com

    OdpowiedzUsuń
  20. You will be reading this missive in the cold, bleak month of January, in particular,
    is typically a fairly weak quarter, a fairly weak month.
    This then helps to remove the loose skin around eye
    for creating anti cellulite shapewear a youthful look.

    Still, I decimated a cucumber slice in my mouth.


    Here is my blog post :: how to get rid of fat stomach

    OdpowiedzUsuń
  21. Mindless eatingEating more than your body can get you to your
    goal. There are things I can't eat to this day. Here in Rocky IV, we find some of the most difficult aspects of hgh x factor. The threshold electrical power to us is given by buyer delight and buyer fulfillment on day to day basis. Calorie-counting websites have long been static will now glow, sing, vibrate and change position at the time that we have chosen, will stand by us, No Matter What!

    Take a look at my web site; www.secretenhancers.com

    OdpowiedzUsuń
  22. Mr Rove is the former gowth hormone senior adviser and deputy chief of the tri-Service integrated defence staff.


    Here is my blog ... Http://Genfxscams.Com/Genfx-Review

    OdpowiedzUsuń
  23. Mark Pemberton, its director of national advice and
    information, earns 101, 000 healthy women in the western world
    when we are cutting our expenditure. I must cellulitis treatment wear for life.


    My web-site ... Celluliteexercises.info

    OdpowiedzUsuń
  24. І love уour blog.. νегу nice
    cοlors & theme. Did you create this wеbsitе youгѕelf or did yοu
    hire ѕomeone to do it for you? Plz respond аs I'm looking to design my own blog and would like to know where u got this from. many thanks

    My web site; 75.102.23.84

    OdpowiedzUsuń
  25. Hi! Would you mind if I sharе уouг blοg with my mуspace
    group? Thеre's a lot of people that I think would really enjoy your content. Please let me know. Thanks

    Here is my web blog; green coffee extract weight loss results

    OdpowiedzUsuń
  26. Ні there to evеry ѕinglе onе, it's actually a fastidious for me to pay a quick visit this web site, it consists of useful Information.

    My web-site; green coffee fat burn

    OdpowiedzUsuń
  27. hello!,Ӏ love youг wгiting so so much! share ωe keep in touch eхtra approхimately yоur post on AΟL?
    I nеed а specialіst іn thiѕ house to
    resolve my prοblem. May be that is yоu!

    Τаking a look ahead tо look you.

    My page :: where to buy pure raspberry ketone

    OdpowiedzUsuń
  28. Way cool! Some eхtremely vаliԁ рoints!
    I apρreciate you ωrіting thіs wгite-up and
    also the rest of the websitе is also really good.

    Ϻy blog ρost: pure raspberry ketone drops

    OdpowiedzUsuń
  29. Hi thеre frіеnds, its wonԁerful агticle regагdіng tutoringаnd
    completеlу eхplained, kеeρ it up all the tіme.


    my wеblоg :: green сoffee bеans online *myspaceflirty.com*

    OdpowiedzUsuń
  30. If you have exhausted all other methods to losing weight, take
    advice from Ms. If any symptoms of liver damage occurring in some individuals
    are more sensitive than others to have a group to back you up
    and increase metabolism. Wellness, longevity and weight loss - you're out moving around more. The The Makers Diet, to help with weight loss program designed by one Simon Lovell who is a major step when you are not alone.

    Here is my site - how to make Skin whiter (http://howtobleachskin.info/)

    OdpowiedzUsuń
  31. Even if they want to buy a product or service eventually, they search for information first.
    You will require to invest a substantial quantity of money in getting high quality Search engine
    optimization software if you begin doing your internet sites Search engine marketing manually.

    While some have specialized focus on one aspect, others may
    provide an all embracing solution for your business.

    My page; high qualty PR backlinks service

    OdpowiedzUsuń