Hello. I am old school mainframer trying to learn new things.
I am trying to run visual cobol against db2 community version on windows 11.
i am able to connect in the windowsform but i can not get a select statement without format errors.
i did saw something about installing ibm.data.db2 but i do not know if that is a option.
below is my code snippet.
try invoke conn::Open() set label1::Text to "DB2 Connected" move 'y' to db2-connected set cmd to new System.Data.OleDb.OleDbCommand("SELECT LastName FROM Employee WHERE EmployeeID = 1", conn)
set reader to cmd::ExecuteReader() catch ex as type Exception set label1::Text to "it did not connect"
end-try
the set cmd and set reader both have red underline errors.
is there something i ned to install or is there another way to do this.
The connect works so why not the select.
Going nuts can’t figure it out!
Best answer by Chris Glazier
In order to use the IBM.Data.DB2 provider, you would need to install the DB2 Client and then add a reference to your project to that assembly.
At the top of your program you could then add:
$set ilusing"IBM.Data.DB2"
Here is an example which works with IBM.Data.DB2 using ADO syntax to access the Samples database:
$set ilusing"IBM.Data.DB2" program-id. Program1 as "testadosyntax.Program1".
data division. working-storage section. *> replace the user id, password and server ip or name with your own. 01 conn type DB2Connection value new DB2Connection("Database=sample;User ID=support;Password=XXXXXXX;Server=0.0.0.0"). 01 cmd type DB2Command. procedure division.
try invoke conn::Open set cmd to new DB2Command("SELECT A.LASTNAME FROM SUPPORT.EMPLOYEE A WHERE A.Empno = '000010'", conn) declare reader as type DB2DataReader = cmd::ExecuteReader() invoke reader::Read display "LastName = " reader::GetString(0) catch ex as type Exception display ex::Message end-try goback.
A simpler approach is to use the Visual COBOL OpenESQL preprocessor to use embedded SQL code.
exec sql connect to "db2client" end-exec display SQLCODE move "000010" to employee-empno exec sql SELECT A.LASTNAME INTO :EMPLOYEE-LASTNAME FROM SUPPORT.EMPLOYEE A WHERE (A.EMPNO = :EMPLOYEE-EMPNO) END-EXEC
display employee-lastname exec sql disconnect current end-exec
In order to use the IBM.Data.DB2 provider, you would need to install the DB2 Client and then add a reference to your project to that assembly.
At the top of your program you could then add:
$set ilusing"IBM.Data.DB2"
Here is an example which works with IBM.Data.DB2 using ADO syntax to access the Samples database:
$set ilusing"IBM.Data.DB2" program-id. Program1 as "testadosyntax.Program1".
data division. working-storage section. *> replace the user id, password and server ip or name with your own. 01 conn type DB2Connection value new DB2Connection("Database=sample;User ID=support;Password=XXXXXXX;Server=0.0.0.0"). 01 cmd type DB2Command. procedure division.
try invoke conn::Open set cmd to new DB2Command("SELECT A.LASTNAME FROM SUPPORT.EMPLOYEE A WHERE A.Empno = '000010'", conn) declare reader as type DB2DataReader = cmd::ExecuteReader() invoke reader::Read display "LastName = " reader::GetString(0) catch ex as type Exception display ex::Message end-try goback.
A simpler approach is to use the Visual COBOL OpenESQL preprocessor to use embedded SQL code.
exec sql connect to "db2client" end-exec display SQLCODE move "000010" to employee-empno exec sql SELECT A.LASTNAME INTO :EMPLOYEE-LASTNAME FROM SUPPORT.EMPLOYEE A WHERE (A.EMPNO = :EMPLOYEE-EMPNO) END-EXEC
display employee-lastname exec sql disconnect current end-exec
You can download the IBM Data Server Driver Package from the IBM site : Download initial Version 11.5 clients and drivers You should download the version that has the same bitism as your application.
You will have to create an IBM id to download if you dont already have one.
IBM.Data.DB2 is a managed .NET ADO Provider which is to be used by .NET Framework projects like Windows Forms.
ODBC is used with native (non-.NET) code.
The embedded SQL can be used directly in a Windows Forms program or within a native console or Windows application. If you are using Forms then you would set this to use ADO and if you are using native code you would set it to use ODBC.
This is done with the compiler directive SQL(DBMAN=ADO) or SQL(DBMAN=ODBC)
The full version of the embedded example I gave to you was in a Windows Form in a button event handler.
$set sql(dbman=ado) class-id testdb2ado.Form1 is partial inherits type System.Windows.Forms.Form.
working-storage section. exec sql include sqlca end-exec. EXEC SQL INCLUDE EMPLOYEE END-EXEC.
method-id NEW. procedure division. invoke self::InitializeComponent goback. end method.
method-id button1_Click final private. procedure division using by value sender as object e as type System.EventArgs. exec sql connect to "db2client" end-exec display SQLCODE move "000010" to employee-empno exec sql SELECT A.LASTNAME INTO :EMPLOYEE-LASTNAME FROM SUPPORT.EMPLOYEE A WHERE (A.EMPNO = :EMPLOYEE-EMPNO) END-EXEC
display employee-lastname exec sql disconnect current end-exec
OMG Chris you rock! Thank you so much. this stuff can make you crazy but having guys like you willing to help is a blessing. so now i see there are two ways to do this , the odbc way and the ibm provider data.db2 way.
it would help to know which way big shops prefer, i’m assuming odbc embedded.
thanks for that bit on compiler directives, of course i would prefer to use embedded.
