Multiple SQL statements through OLE DB on DB2/400

15 pts.
Tags:
DB2/400
IBMDA400
OLE DB
SQL
We have a third party application built on SharePoint, and we want to use DB2/400 on our System i for the application's backend database. The app uses ADO and OLE DB to query whatever database it links to, and the vendor has it working on a number of different databases, including DB2 on Windows and Linux and MS SQL Server. However, we can't get it working on DB2/400. We're using the IDMDA400 OLE DB provider, and have tried the IBMDASQL provider too. Both work fine with single SQL select statements, but when we hit an OLE DB SQL request that contains multiple SQL statements separate by the ';' statement terminator, they are rejected  with error "SQL0104 Token ; was invalid. Valid tokens END-OF-STATEMENT". It looks to me like the OLE provider for DB2/400 only allows single SQL statements per call. Is there a solution to this? If not, we're going to have a lot of work to do - the application does most if its database access via multiple-statement SQL calls, usually with three steps; declare temp table, then populate temp table, then return results from temp table. It can be re-engineered, but we'd rather avoid that, as the cost is significant.

Software/Hardware used:
DB2/400 OLE DB IBMDA400

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ToddN2000

    I did not have much luck running multiple SQL in the manner you mentioned either. My work around, because they were not very involved was nesting the select statements.

    Something similar to this.

    select * from
    (select * from 
    (select * from nfp where nftyp = 'TL') as t1 
    where nfdesc like 'F%') as t2
    where nfdesc like '%/%'
    
    15,605 pointsBadges:
    report
  • AndyFarrar
    Thanks for that. I've had some more feedback elsewhere on this. It looks like we're going to have to re-engineer the code to use single SQL statements. Not difficult, but there's a lot to do, and because it's a third party app, the vendor will want to charge us a lot for doing it I'm sure. I did hear that multiple statements wrapped in a BEGIN...END might work on V7R1, but we're not at that level yet - stuck on V5R4 due to issues with our very old ERP system. 
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following