The following is an example of a DB2 CLI program designed to:
This program can be executed on AIX or Windows NT systems.
Windows NT-specific syntax and output is denoted in comments similar to the following:
/* ****************** On Windows NT,... ****************** */
/******************************************************************************
**
** Source File Name = datalink.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1998
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** PURPOSE :
** Modified version of the CLI sample clisampl.c to demonstrate creating and
** parsing
**
** The following operations are performed:
** - Connect to a database.
** - Create a table with a single datalink
** - Insert a single row using SQLBuildDataLink() and SQLBindParameter()
** - Fetch the data
** - parse information from the retrieved datalink using SQLGetDataLinkAttr()
** - Drop the table
** - Disconnect from the database.
**
******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
/*
* Global Variables for user id and password.
* To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
void getattr(
SQLHSTMT hStmt,
SQLSMALLINT AttrType,
SQLCHAR* DataLink,
SQLCHAR* Attribute,
SQLINTEGER BufferLength )
{
SQLINTEGER StringLength ;
SQLRETURN rc ;
rc = SQLGetDataLinkAttr(
hStmt,
AttrType,
DataLink,
strlen( (char *)DataLink),
Attribute,
BufferLength,
&StringLength
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hStmt, rc ) ;
printf("Attribute #%d) <%s>\n", AttrType, Attribute) ;
return ;
}
int main(int argc, char * argv[] ) {
SQLHANDLE henv, hdbc, hstmt ;
SQLRETURN rc ;
SQLCHAR szCreate[] = "CREATE TABLE DL_SAMPL "
"( "
"DL1 DATALINK "
"LINKTYPE URL "
"FILE LINK CONTROL "
"INTEGRITY ALL "
"READ PERMISSION DB "
"WRITE PERMISSION BLOCKED "
"RECOVERY NO "
"ON UNLINK RESTORE "
")";
SQLCHAR szInsert[]= "INSERT INTO DL_SAMPL VALUES (?)" ;
SQLCHAR szFileLink[] = "http://mycomputer.company.com/nfsdlink/userid/test_1.jpg"
/*
*****************************************************************
On Windows NT, replace "http://...jpg" with:
"unc:\\mycomputer.company.com\nfsdlink\userid\test_1.jpg" ;
*****************************************************************
*/
SQLCHAR szComment[] = "My First Datalink" ;
SQLCHAR szSelect[]= "SELECT * FROM DL_SAMPL" ;
SQLCHAR szDrop[] = "DROP TABLE DL_SAMPL" ;
SQLCHAR szDLCol[254] ;
SQLCHAR szBuffer[254] ;
SQLSMALLINT cCol ;
SQLCHAR szColName[33] ;
SQLSMALLINT fSqlType ;
SQLUINTEGER cbColDef ;
SQLSMALLINT ibScale ;
SQLSMALLINT fNullable ;
SQLINTEGER siLength= SQL_NTS ;
/* macro to initialize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return(terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc );
/*
* Create the sample table. This code assumes
* that the table DL_SAMPL does not exist.
*/
printf( "Create table - %s\n", szCreate ) ;
rc = SQLExecDirect( hstmt, szCreate, SQL_NTS ) ;
CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc ) ;
/* Commit the changes. */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE(SQL_HANDLE_DBC, hdbc, rc ) ;
/* Prepare an insert statement. */
printf( "Insert - %s\n", szInsert ) ;
rc = SQLPrepare( hstmt, szInsert, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Build Datalink */
rc = SQLBuildDataLink( hstmt,
(SQLCHAR *)"URL",
strlen("URL"),
szFileLink,
strlen((char*)szFileLink),
szComment,
strlen((char *)szComment),
szDLCol,
sizeof(szDLCol),
&siLength
);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Set input parameter. */
rc = SQLBindParameter(
hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_DATALINK,
SQL_DATALINK,
sizeof(szDLCol),
0,
(SQLPOINTER)szDLCol,
sizeof(szDLCol),
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Insert row into the database. */
rc = SQLExecute( hstmt) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Commit the changes. */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Reset input parameter. */
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Execute the select statement. */
printf( "Select - %s\n", szSelect ) ;
rc= SQLExecDirect( hstmt, szSelect, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Return number of columns and describe result set. */
rc = SQLNumResultCols( hstmt, &cCol ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "Number of columns - %d\n", cCol ) ;
rc = SQLDescribeCol(hstmt,
1,
szColName,
sizeof( szColName ),
NULL,
&fSqlType,
&cbColDef,
&ibScale,
&fNullable
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "Column name - %s\n", szColName ) ;
printf( "Column type - %d\n", fSqlType ) ;
printf( "Column precision - %ld\n", cbColDef) ;
printf( "Column scale - %d\n", ibScale ) ;
printf( "Column nullable - %s\n", ( fNullable ) ? "TRUE" : "FALSE" ) ;
/* Bind the output parameter. */
rc = SQLBindCol( hstmt, 1, SQL_C_DATALINK, szDLCol, sizeof(szDLCol), NULL ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Fetch data. */
rc = SQLFetch( hstmt ) ;
CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "Column value - %s\n", szDLCol ) ;
getattr(hstmt, 1, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 2, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 3, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 4, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 5, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 6, szDLCol, szBuffer, sizeof(szBuffer) );
getattr(hstmt, 7, szDLCol, szBuffer, sizeof(szBuffer) );
/* Close cursor and free bound columns. */
/* Free statement resources */
rc = SQLFreeStmt(hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc ) ;
/* Drop table. */
rc = SQLExecDirect(hstmt, szDrop, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Commit the changes. */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Disconnect and free up CLI resources. */
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect(hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc= SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return(SQL_SUCCESS ) ;
} /* end main */
/* ===============================================================
** Sample Output:
**
** >Enter Server Name:
** sample
** >Enter User Name:
** userid
** >Enter Password:
** password
** >Connected to sample
** Create table - CREATE TABLE DL_SAMPL
** ( DL1 DATALINK LINKTYPE URL FILE LINK CONTROL INTEGRITY ALL
** READ PERMISSION DB WRITE PERMISSION BLOCKED RECOVERY NO ON UNLINK RESTORE )
** Insert - INSERT INTO DL_SAMPL VALUES (?)
** Select - SELECT * FROM DL_SAMPL
** Number of columns - 1
** Column name - DL1
** Column type - -400
** Column precision - 254
** Column scale - 0
** Column nullable - TRUE
** Column value - 1,URL,79,17,19,HTTP://mycomputer.company.com/nfsdlink/userid/
** HVJ5NXGC0WQ.I5KKB6;test_1.jpgMyFirst Datalink
/*
**************************************************************
On Windows NT, the sample output will be:
** Column value - 1,UNC:\\mycomputer.company.com\nfsdlink\userid\
HVJ5NXGC0WQ.I5KKB6;test_1.jpgMyFirst Datalink
**************************************************************
*/
** Attribute #1) <My First Datalink>
** Attribute #2) <URL>
** Attribute #3) <HTTP://mycomputer.company.com/nfsdlink/userid/
** HVJ5NXGC0WQ.I5KKB6;test_1.jpg>
/*
******************************************************************
On Windows NT, the sample output will be:
** Attribute #3) <UNC:\\mycomputer.company.com\nfsdlink\userid\
** HVJ5NXGC0WQ.I5KKB6;test_1.jpg>
******************************************************************
*/
** Attribute #4) </nfsdlink/userid/HVJ5NXGC0WQ.I5KKB6;test_1.jpg>
/*
********************************************************************
On Windows NT, the sample output will be:
** Attribute #4) <\nfsdlink\userid\HVJ5NXGC0WQ.I5KKB6;test_1.jpg>
********************************************************************
*/
** Attribute #5) </nfsdlink/userid/test_1.jpg>
/*
*******************************************************
On Windows NT, the sample output will be:
** Attribute #5) <\nfsdlink\userid\test_1.jpg>
*******************************************************
*/
** Attribute #6) <HTTP>
/*
*****************************************
On Windows NT, the sample output will be:
** Attribute #6) <UNC>
*****************************************
*/
** Attribute #7) <mycomputer.company.com>
**
** >Disconnecting .....
**
**
=============================================================== */