H dftactgrp(*no) H actgrp('QILE') H debug **************************************************************** * Program EX02 - SQL CLI example, SQLPrepare / SQLExecute * * Copyright 2005, Bits & Bytes Programming, Inc. * ALL RIGHTS RESERVED **************************************************************** Fqsysprt o f 132 PRINTER /INCLUDE QSQLCLI,CSQLCLI_HX D baldue... D s 6 2 D baldueParm... D s 6 2 D city... D s 6 D cusnum... D S 6 0 D init... D s 3 D lstnam... D s 8 D state... D s 2 D street... D s 13 D zipcod... D s 5 0 D ptrBaldue... D s like(SQLPOINTER) D inz(%addr(baldue)) D ptrBaldueParm... D s like(SQLPOINTER) D inz(%addr(baldueParm)) D ptrCity... D s like(SQLPOINTER) D inz(%addr(city)) D ptrCusnum... D s like(SQLPOINTER) D inz(%addr(cusnum)) D ptrInit... D s like(SQLPOINTER) D inz(%addr(init)) D ptrLstnam... D s like(SQLPOINTER) D inz(%addr(lstnam)) D ptrState... D s like(SQLPOINTER) D inz(%addr(state)) D ptrStreet... D s like(SQLPOINTER) D inz(%addr(street)) D ptrZipcod... D s like(SQLPOINTER) D inz(%addr(zipcod)) D cbAuthStr... D s like(SQLSMALLINT) D cbDSN... D s like(SQLSMALLINT) D cbSqlStr... D s like(SQLINTEGER) D cbUID... D s like(SQLSMALLINT) D hdbc... D s like(SQLHDBC) D henv... D s like(SQLHENV) D hstmt... D s like(SQLHSTMT) D pcbValue... D s like(SQLINTEGER) D rc... D s like(SQLRETURN) D strLen... D s like(SQLINTEGER) D inz(0) D szAuthStr... D s 10 D szDSN... D s 18 D szSqlStr... D s 1024 D szUID... D s 10 D sql... D c 'SELECT * FROM QIWS.QCUSTCDT - D WHERE BALDUE >= ? - D ORDER BY BALDUE DESC' /free //********************************************************** // allocate environment, connection //********************************************************** rc = SQLAllocEnv(henv); rc = SQLAllocConnect(henv : hdbc); //********************************************************** // connect using default user ID / password //********************************************************** szDSN = 'M270'; cbDSN = SQL_NTS; szUID = ''; cbUID = SQL_NTS; szAuthStr = ''; cbAuthStr = SQL_NTS; rc = SQLConnect(hdbc : szDSN : cbDSN : szUID : cbUID : szAuthStr : cbAuthStr); //********************************************************** // prepare the statement (do this once) //********************************************************** rc = SQLAllocStmt(hdbc : hstmt); szSqlStr = sql; cbSqlStr = SQL_NTS; rc = SQLPrepare(hstmt : szSqlStr : cbSqlStr); //********************************************************** // bind columns //********************************************************** rc = SQLBindCol(hstmt : 1 : SQL_DECIMAL : ptrCusnum : 1536 : pcbValue); rc = SQLBindCol(hstmt : 2 : SQL_CHAR : ptrLstnam : 8 : pcbValue); rc = SQLBindCol(hstmt : 3 : SQL_CHAR : ptrInit : 3 : pcbValue); rc = SQLBindCol(hstmt : 4 : SQL_CHAR : ptrStreet : 13 : pcbValue); rc = SQLBindCol(hstmt : 5 : SQL_CHAR : ptrCity : 6 : pcbValue); rc = SQLBindCol(hstmt : 6 : SQL_CHAR : ptrState : 2 : pcbValue); rc = SQLBindCol(hstmt : 7 : SQL_DECIMAL : ptrZipcod : 1280 : pcbValue); rc = SQLBindCol(hstmt : 10 : SQL_DECIMAL : ptrBaldue : 1538 : pcbValue); //********************************************************** // bind parameter (WHERE clause, BALDUE), execute // (do each time parameter value changes) //********************************************************** baldueParm = 100.00; rc = SQLBindParameter(hstmt : 1 : SQL_PARAM_INPUT : SQL_DECIMAL : SQL_DECIMAL : 6 : 2 : ptrBaldueParm : 0 : strLen); rc = SQLExecute(hstmt); //********************************************************** // process all rows in result set //********************************************************** dow (SQLFetch(hstmt) = SQL_SUCCESS); except exdetail; enddo; //********************************************************** // end of program processing //********************************************************** rc = SQLFreeStmt(hstmt : SQL_DROP); rc = SQLDisconnect(hdbc); rc = SQLFreeConnect(hdbc); rc = SQLFreeEnv(henv); *inlr = *on; /end-free Oqsysprt e exdetail 1 O cusnum O lstnam +2 O init +2 O street +2 O city +2 O state +2 O zipcod +2 O baldue J +2