Пример #1
0
void classTables::LoadTables()
{
  CursorScoper s(listView()) ;
  SQLRETURN    nReturn ;
  SQLCHAR      szTableName[MAX_COLUMN_WIDTH];
  SQLCHAR      szTableType[MAX_COLUMN_WIDTH];
  SQLCHAR      szTableRemarks[MAX_COLUMN_WIDTH];
  SQLLEN       nIndicatorName;
  SQLLEN       nIndicatorType;
  SQLLEN       nIndicatorRemarks;
  classTable   *pTable = NULL;

  // CREATE A STATEMENT
  StatementScoper stmt( hDbc ) ; if ( !stmt() ) return ;

  // EXECUTE OUR SQL/CALL
  if (!SQL_SUCCEEDED(nReturn=SQLTables( stmt(), 0, 0, (SQLCHAR*)qsLibrary.ascii(), SQL_NTS, 0, 0, 0, 0 ) ) )
    return my_msgBox( "classTables", "SQLTables", nReturn, NULL, NULL, stmt() ) ;

  SQLBindCol( stmt(), SQLTables_TABLE_NAME, SQL_C_CHAR, szTableName,    sizeof(szTableName),    &nIndicatorName    );
  SQLBindCol( stmt(), SQLTables_TABLE_TYPE, SQL_C_CHAR, szTableType,    sizeof(szTableType),    &nIndicatorType    );
  SQLBindCol( stmt(), SQLTables_REMARKS,    SQL_C_CHAR, szTableRemarks, sizeof(szTableRemarks), &nIndicatorRemarks );

  // GET RESULTS
  while ( SQL_SUCCEEDED(SQLFetch( stmt() ) ) )
  {
    if ( nIndicatorName == SQL_NULL_DATA )
      listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, "Unknown" ) );
    else
      listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, QString((char*)szTableName).stripWhiteSpace(), QString((char*)szTableType).stripWhiteSpace(), QString((char*)szTableRemarks).stripWhiteSpace(), qsLibrary ) );
  }
}
Пример #2
0
int main()
{
	//定义句柄
	SQLHENV serverhenv;
	SQLHDBC serverhdbc;
	SQLHSTMT serverstmt;
	SQLRETURN ret;

	//初始化环境
	ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &serverhenv);
	ret = SQLSetEnvAttr(serverhenv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	if (ret == SQL_SUCCESS)
	{
		printf(" ------------------------ - \n");
		printf(" 环境分配成功\n");
		printf(" ------------------------ - \n");
	}

	//建立连接
	ret = SQLAllocHandle(SQL_HANDLE_DBC, serverhenv, &serverhdbc);
	ret = SQLConnectW(serverhdbc, (SQLWCHAR *)"David_MySQL" , SQL_NTS, (SQLWCHAR*)"root" , SQL_NTS, (SQLWCHAR*)"Dyc19930418", SQL_NTS);
	if (!SQL_SUCCEEDED(ret))
	{
		printf(" 数据连接失败\n");
		return -1;
	}

	//初始化语句句柄

	ret = SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &serverstmt);
	ret = SQLExecDirect(serverstmt, (SQLWCHAR*)" select * from my_test" , SQL_NTS);

	//处理结果集

	ret = SQLBindCol(serverstmt, 1, SQL_C_CHAR, ID, Name_Len, &userID);
	ret = SQLBindCol(serverstmt, 2, SQL_C_CHAR, PWD, Pwd_Len, &userPassword);

	printf(" userID\t userPassword\t\n");
	while ((ret = SQLFetch(serverstmt)) != SQL_NO_DATA_FOUND)
	{
		if (ret == SQL_ERROR)
		{
			printf(" Fetch data error!\n");
		}
		else
		{

			printf(" %s\t %s\t\n" , ID, PWD);
		}
	}

	//中止处理
	SQLFreeHandle(SQL_HANDLE_STMT, serverstmt);
	SQLDisconnect(serverhdbc);
	SQLFreeHandle(SQL_HANDLE_DBC, serverhdbc);
	SQLFreeHandle(SQL_HANDLE_ENV, serverhenv);
	printf(" ---------------------------- - \n");
	return 0;
}
Пример #3
0
//***********************************************************************
// FETCH ROWS FROM THE TABLE "T1"......select * from T1;
int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
{
    
      int ret;
      int f1=10; // f1 field
      int f2=20;//f2 field

       int rettype ;

    ret = SQLPrepare(stmt,(unsigned char*)"SELECT F3,F2  FROM T1 ",SQL_NTS);
    rettype = ret;
    if(rettype!=0)return 1;

    //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
    ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL);
    
    
    
    ret = SQLBindCol(stmt,2,SQL_C_SLONG,&f2,0,NULL);
    



    int j, count=0; 
    
    ret = SQLExecute(stmt);
    
    SQLSMALLINT  noc;
    ret = SQLNumResultCols(stmt,&noc);
    rettype=ret;
    
    if(rettype != 0 )
    {
      printf("SQLNumResultCol() returns = %d\n",noc);
      return 1;
    }  
     
   while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
    {
        count++;
                
    }
    
    /*SQLSMALLINT noc;
    ret =SQLNumResultCols(stmt,&noc);
        
    printf("SQLNumResultCols() returns=%d\n",noc);*/
    
    ret = SQLCloseCursor(stmt);
    checkrc(ret,__LINE__);
    
    ret = SQLTransact(env,dbc,SQL_COMMIT);
    checkrc(ret,__LINE__);
    
    printf("Total row fetched=%d\n",count);
    return 0;
} 
Пример #4
0
/* display foreign keys, from this table to others */
bool
fetchForeign(char *tname)
{
    char farschema[40], fartab[40];
    char farcol[40];
    char nearcol[40];
    SQLLEN nearcolOut, farschemaOut, fartabOut, farcolOut;
    char *dot;

    newStatement();
    stmt_text = "foreign keys";
    debugStatement();

    dot = strchr(tname, '.');
    if(dot)
	*dot++ = 0;

    rc = SQLForeignKeys(hstmt,
       NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS,
       NULL, SQL_NTS,
       (dot ? tname : NULL), SQL_NTS, (dot ? dot : tname), SQL_NTS);
    if(dot)
	dot[-1] = '.';
    if(rc)
	goto abort;

    SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) farschema, sizeof (farschema),
       &farschemaOut);
    SQLBindCol(hstmt, 3, SQL_CHAR, (SQLPOINTER) fartab, sizeof (fartab),
       &fartabOut);
    SQLBindCol(hstmt, 4, SQL_CHAR, (SQLPOINTER) farcol, sizeof (farcol),
       &farcolOut);
    SQLBindCol(hstmt, 8, SQL_CHAR, (SQLPOINTER) nearcol, sizeof (nearcol),
       &nearcolOut);

    while(SQLFetch(hstmt) == SQL_SUCCESS) {
	printf("%s > ", nearcol);
	if(farschema[0])
	    printf("%s.", farschema);
	printf("%s.%s\n", fartab, farcol);
    }

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    return true;

  abort:
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    return false;
}				/* fetchForeign */
Пример #5
0
bool
showTables(void)
{
    char tabname[40];
    char tabtype[40];
    char tabowner[40];
    SQLLEN tabnameOut, tabtypeOut, tabownerOut;
    char *buf;
    int buflen, cx;
    int truevalue = SQL_TRUE;

/*
SQLSetConnectAttr(hdbc, SQL_ATTR_METADATA_ID,
&truevalue, SQL_IS_INTEGER);
*/

    newStatement();
    stmt_text = "get tables";
    debugStatement();
    rc = SQLTables(hstmt,
       NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
    if(rc)
	goto abort;

    SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) tabowner, sizeof (tabowner),
       &tabownerOut);
    SQLBindCol(hstmt, 3, SQL_CHAR, (SQLPOINTER) tabname, sizeof (tabname),
       &tabnameOut);
    SQLBindCol(hstmt, 4, SQL_CHAR, (SQLPOINTER) tabtype, sizeof (tabtype),
       &tabtypeOut);

    buf = initString(&buflen);
    while(SQLFetch(hstmt) == SQL_SUCCESS) {
	char tabline[140];
	sprintf(tabline, "%s.%s|%s\n", tabowner, tabname, tabtype);
	stringAndString(&buf, &buflen, tabline);
    }

    cx = sideBuffer(0, buf, buflen, 0, false);
    nzFree(buf);
    i_printf(MSG_ShowTables, cx);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    return true;

  abort:
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    return false;
}				/* showTables */
Пример #6
0
/*-------------------------------------------------------------------------
 * AddCol
 *-------------------------------------------------------------------------
 * Purpose:
 *    bind a variable to a column of the sql statement
 * 
 * Parameters:
 *    pvBuff: where to bind the column data to
 * 
 * Returns:
 *    0. Returns a value only so we can call it during global initialization,
 *        before any code blocks execute.
 */
int AddCol(void * pvBuff, SQLSMALLINT ssiCType, SQLPARM parmtype, int cbBuff)
{
  SQLRETURN sqlret;
  static SQLINTEGER cbWhoCares;

  switch (parmtype)
  {
    case SQL_OUT_PARM:
      sqlret = SQLBindCol(g_rghstmt[g_iSql - 1], ++g_iCol, ssiCType, pvBuff, 
                          cbBuff, &cbWhoCares);
      if (SQL_SUCCESS != sqlret)
        SQLWhatsWrong(SQL_HANDLE_STMT, g_rghstmt[g_iSql - 1]);
      break;
    
    case SQL_IN_PARM:
      // we're using the same type for C and SQL, so they must match
      sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_INPUT, 
          ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, NULL);
      break;

    case SQL_OUT_PROC: //out value from stored procedure
      // we're using the same type for C and SQL, so they must match
      sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_OUTPUT, 
          ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, &cbWhoCares);
      break;

    case SQL_INOUT_PROC: //out value from stored procedure
      // we're using the same type for C and SQL, so they must match
      sqlret = SQLBindParameter(g_rghstmt[g_iSql - 1], ++g_iParm, SQL_PARAM_INPUT_OUTPUT, 
          ssiCType, ssiCType, cbBuff, 0, pvBuff, cbBuff, &cbWhoCares);
      break;
  }

  return 0;
}
Пример #7
0
static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
{
	rlm_sql_unixodbc_conn_t *conn = handle->conn;
	SQLINTEGER i;
	SQLLEN len;
	int colcount;
	int state;

	/* Only state = 0 means success */
	if ((state = sql_query(handle, config, query))) {
		return state;
	}

	colcount = sql_num_fields(handle, config);
	if (colcount < 0) {
		return RLM_SQL_ERROR;
	}

	/* Reserving memory for result */
	conn->row = talloc_zero_array(conn, char *, colcount + 1); /* Space for pointers */

	for (i = 1; i <= colcount; i++) {
		SQLColAttributes(conn->stmt, ((SQLUSMALLINT) i), SQL_COLUMN_LENGTH, NULL, 0, NULL, &len);
		conn->row[i - 1] = talloc_array(conn->row, char, ++len);
		SQLBindCol(conn->stmt, i, SQL_C_CHAR, (SQLCHAR *)conn->row[i - 1], len, NULL);
	}

	return RLM_SQL_OK;
}
unsigned int SelectCount(SQLHANDLE hstmt, char *TableName)
{
	char	SelCountStatement[50];
	SQLINTEGER		RecCount;
	SQLINTEGER		RecCountInd;

	RETCODE	rc;

	RecCount = 0;

	strcpy(SelCountStatement, "select count(*) from ");
	strcat(SelCountStatement, TableName);

	rc = SQLExecDirect(hstmt, (SQLCHAR *)SelCountStatement, strlen(SelCountStatement));
	if (rc != SQL_SUCCESS) {
		printf("...select count(*) from %s statement failed\n", TableName);
		displayError(SQL_HANDLE_STMT, hstmt);
	}

	rc = SQLBindCol(hstmt, 1, SQL_C_ULONG, &RecCount, 0, &RecCountInd);
	if (rc != SQL_SUCCESS) {
		printf("...SQLBindCol failed\n");
		displayError(SQL_HANDLE_STMT, hstmt);
	}

	rc = SQLFetch(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);

	return(RecCount);


}
Пример #9
0
BOOL CQuery::Exec(LPCTSTR szSQL)
{
	int c;

	if( S_TYPE != ST_CASHSHOPSERVER )
	{
		g_Window.ServerLogAdd(S_TYPE,"%s", szSQL);
	}

	ret=SQLExecDirect(hStmt,(SQLCHAR *)szSQL,SQL_NTS);
	if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_NO_DATA)) {
		PrintDiag();
		return FALSE;
	}

	SQLRowCount(hStmt,&AffectCount);
	SQLNumResultCols(hStmt,&nCol);
	if (nCol > MAXCOL) {
		//::MessageBox(NULL,"nCol > MAXCOL","CQuery error",MB_OK);
		g_Window.ServerLogAdd(S_TYPE,"CQuery error : nCol > MAXCOL");
		return FALSE;
	}

	if (nCol == 0) {
		Clear();
		return TRUE;
	}

	for (c=0;c<nCol;c++) {
		SQLBindCol(hStmt,c+1,SQL_C_CHAR,Col[c],255,&lCol[c]);
		SQLDescribeCol(hStmt,c+1,ColName[c],30,NULL,NULL,NULL,NULL,NULL);
	}
	return TRUE;
}
Пример #10
0
SQLRETURN unixodbc_backend_debug::do_bind_column(SQLHSTMT statement_handle, SQLUSMALLINT column_id, SQLSMALLINT target_type, SQLPOINTER target_value_ptr, SQLLEN buffer_length, SQLLEN * length_indicator_buffer) const
{
	std::cout << " *DEBUG* bind_column";
	auto const return_code = SQLBindCol(statement_handle, column_id, target_type, target_value_ptr, buffer_length, length_indicator_buffer);
	std::cout << " (return code " << return_code << ")" << std::endl;
	return return_code;
}
Пример #11
0
void OdbcQuery::execQuery(const char *sql) {
    pOdbc->execDML(sql);

    SQLSMALLINT cols = 0;
    struct COL {
        char *name;
        int name_size;
        char *buffer;
        int buffer_size;
        SQLSMALLINT dataType;
    } *pCol;

    SQLNumResultCols(pOdbc->sqlstatementhandle, &cols);
    pCol = new COL[cols];
    for (int i = 0; i < cols; i++) {
        pCol[i].name = new char[128];
        SQLColAttribute(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_DESC_LABEL,
                        pCol[i].name, 128, (SQLSMALLINT *) &pCol[i].name_size, NULL);

        SQLColAttribute(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_COLUMN_TYPE,
                        NULL, 0, NULL, (SQLLEN *) &pCol[i].dataType);

        pCol[i].buffer = new char[MAX_BUFFER_SIZE];
        SQLBindCol(pOdbc->sqlstatementhandle, (SQLUSMALLINT) i + 1, SQL_C_CHAR, pCol[i].buffer, MAX_BUFFER_SIZE,
                   (SQLLEN *) &pCol[i].buffer_size);
//        printf("%s\t", pCol[i].name);
    }
//    printf("\n");
}
Пример #12
0
/*************************************************************************
 *
 *	Function: sql_select_query
 *
 *	Purpose: Issue a select query to the database
 *
 *************************************************************************/
static int sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char *querystr) {
	rlm_sql_unixodbc_conn_t *conn = handle->conn;
	SQLINTEGER column;
	SQLLEN len;
	int numfields;
	int state;

	/* Only state = 0 means success */
	if ((state = sql_query(handle, config, querystr))) {
		return state;
	}

	numfields=sql_num_fields(handle, config);
	if (numfields < 0) {
		return -1;
	}

	/* Reserving memory for result */
	conn->row = (char **) rad_malloc((numfields+1)*sizeof(char *));
	conn->row[numfields] = NULL;

	for(column = 1; column <= numfields; column++) {
		SQLColAttributes(conn->statement,((SQLUSMALLINT) column),SQL_COLUMN_LENGTH,NULL,0,NULL,&len);
		conn->row[column-1] = (char*)rad_malloc((int)++len);
		SQLBindCol(conn->statement, column, SQL_C_CHAR, (SQLCHAR *)conn->row[column-1], len, NULL);
	}
	return 0;
}
Пример #13
0
void DSN_Set_Database(SQLHANDLE Connection)
{
  MADB_Stmt *Stmt= NULL;
  SQLRETURN ret= SQL_ERROR;
  char Database[65];
  MADB_Dsn *Dsn= (MADB_Dsn *)GetWindowLongPtr(GetParent(hwndTab[0]), DWLP_USER);
  

  if (DBFilled)
    return;

  GetDialogFields();
  
  if (SQLAllocHandle(SQL_HANDLE_STMT, Connection, (SQLHANDLE *)&Stmt) != SQL_SUCCESS)
    goto end;

  if (SQLExecDirect((SQLHSTMT)Stmt, (SQLCHAR *)"SHOW DATABASES", SQL_NTS) != SQL_SUCCESS)
    goto end;

  SQLBindCol(Stmt, 1, SQL_C_CHAR, Database, 65, 0);
  ComboBox_ResetContent(GetDlgItem(hwndTab[1], cbDatabase));
  while (SQLFetch(Stmt) == SQL_SUCCESS)
    ComboBox_InsertString(GetDlgItem(hwndTab[1], cbDatabase), -1, Database);
  if (Dsn->Catalog)
  {
    int Idx= ComboBox_FindString(GetDlgItem(hwndTab[2], cbDatabase), 0, Dsn->Catalog);
    ComboBox_SetCurSel(GetDlgItem(hwndTab[2], cbDatabase), Idx);
  }
  ComboBox_SetMinVisible(GetDlgItem(hwndTab[1], cbDatabase),5);
  DBFilled= TRUE;

end:
  if (Stmt)
	  SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE)Stmt);
}
Пример #14
0
// Remove the update queue tables associated with every data source.
void remove_update_queue_tables(void)
{
	SQLRETURN	retcode;
	SQLHSTMT	stmtSelect;
	SQLHSTMT	stmtDelete;
	SQLCHAR		tabname[DB_MAX_SIZE];
	SQLINTEGER	cbTabname;

	char        sql_stmt[2 * DB_MAX_SIZE];    

    SQLAllocHandle(SQL_HANDLE_STMT, sql_db_connection->hdbc, &stmtSelect);
	SQLSetCursorName(stmtSelect, (SQLCHAR *)"C1", SQL_NTS);

	SQLExecDirect(stmtSelect, (SQLCHAR *)"select updqueuename from vl_datasrc", SQL_NTS);
	SQLBindCol(stmtSelect, 1, SQL_C_CHAR, tabname, DB_MAX_SIZE, &cbTabname);

	retcode = SQLFetch(stmtSelect);
	while (retcode != SQL_NO_DATA && retcode != SQL_ERROR)
	{
		sprintf(sql_stmt, "drop table %s", tabname);
		SQLAllocHandle(SQL_HANDLE_STMT, sql_db_connection->hdbc, &stmtDelete);
		SQLExecDirect(stmtDelete, (SQLCHAR *)sql_stmt, SQL_NTS);
		SQLFreeHandle(SQL_HANDLE_STMT, stmtDelete);
		retcode = SQLFetch(stmtSelect);
	}
	SQLFreeHandle(SQL_HANDLE_STMT, stmtSelect);
}
Пример #15
0
unsigned CMssqlConnection::GetInsertIdentity(const char *table_hint)
{
	HSTMT hStmt;

	m_lasterror=SQLAllocStmt(m_hDbc,&hStmt);
	if(!SQL_SUCCEEDED(m_lasterror))
		return 0;

	m_lasterror=SQLExecDirect(hStmt,(SQLWCHAR*)L"SELECT @@IDENTITY",SQL_NTS);
	if(!SQL_SUCCEEDED(m_lasterror))
	{
		SQLFreeStmt(hStmt,SQL_DROP);
		return 0;
	}

	long id;
	SQLINTEGER len;

	m_lasterror=SQLBindCol(hStmt,1,SQL_C_LONG,&id,sizeof(id),&len);
	if(!SQL_SUCCEEDED(m_lasterror))
	{
		SQLFreeStmt(hStmt,SQL_DROP);
		return 0;
	}

	m_lasterror=SQLFetch(hStmt);
	if(!SQL_SUCCEEDED(m_lasterror))
		return 0;

	SQLFreeStmt(hStmt,SQL_DROP);

	return (unsigned)id;
}
Пример #16
0
static t_sql_row* odbc_alloc_row(t_odbc_res *result, SQLINTEGER *sizes)
{
	int i, fieldCount;
	HSTMT stmt = result->stmt;
	t_sql_row *row;

	fieldCount = sql_odbc_num_fields(result);
	/* Create a new row. */
	row = xcalloc(sizeof *row, fieldCount+1);
	if(!row) {
		return NULL;
	}
	row[fieldCount] = NULL;
	sizes = xcalloc(sizeof *sizes, fieldCount);

	for(i=0; i<fieldCount; i++) {
		TCHAR *cell;
		SQLLEN cellSz;
		SQLColAttribute(stmt, i+1, SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &cellSz);
		cell = xcalloc(sizeof *cell, ++cellSz);
		if(!cell) {
			return NULL;
		}
		SQLBindCol(stmt, i+1, SQL_C_CHAR, cell, cellSz, &sizes[i]);

		row[i] = cell;
	}
	return row;
}
Пример #17
0
int OrderCustDetails(SQLHANDLE hdbc)
{

  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE hstmt; /* statement handle */
  SQLVARCHAR xmldata[3000];

  /* query to be executed */
  SQLCHAR *stmt = (SQLCHAR *)"for $custinfo in db2-fn:xmlcolumn('CUSTOMER.INFO')"
                             "/customerinfo[addr/@country=\"Canada\"]"
                             " order by $custinfo/name"
                             " return $custinfo";

  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  DBC_HANDLE_CHECK(hdbc, cliRC);

    /* Set the attribute SQL_ATTR_XQUERY_STATEMENT to indicate that the query is an XQuery */
  rc = SQLSetStmtAttr(hstmt, SQL_ATTR_XQUERY_STATEMENT, (SQLPOINTER)SQL_TRUE, SQL_NTS);

  if (rc != 0)
  {
    return rc;
  }

  printf("\n  Directly execute the statement\n");
  printf("    %s\n", stmt);

  /* directly execute the statement */
  cliRC = SQLExecDirect(hstmt, stmt, SQL_NTS);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* bind column 1 to variable */
  cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  
  /* fetch each row and display */
  cliRC = SQLFetch(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  if (cliRC == SQL_NO_DATA_FOUND)
  {
    printf("\n  Data not found.\n");
  }
  while (cliRC != SQL_NO_DATA_FOUND)
  {
    printf("%s \n\n",xmldata);

    /* fetch next row */
    cliRC = SQLFetch(hstmt);
    STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  }

  /* free the statement handle */
  cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  return rc;
} /* OrderCustDetails */
Пример #18
0
static void
Test(const char *type, const char *value_to_convert, SQLSMALLINT out_c_type, const char *expected)
{
	unsigned char out_buf[256];
	SQLLEN out_len = 0;
	SQL_NUMERIC_STRUCT *num;
	int i;

	SQLFreeStmt(Statement, SQL_UNBIND);
	SQLFreeStmt(Statement, SQL_RESET_PARAMS);

	/* execute a select to get data as wire */
	sprintf(sbuf, "SELECT CONVERT(%s, '%s') AS data", type, value_to_convert);
	Command(Statement, sbuf);
	SQLBindCol(Statement, 1, out_c_type, out_buf, sizeof(out_buf), &out_len);
	if (SQLFetch(Statement) != SQL_SUCCESS) {
		fprintf(stderr, "Expected row\n");
		exit(1);
	}
	if (SQLFetch(Statement) != SQL_NO_DATA) {
		fprintf(stderr, "Row not expected\n");
		exit(1);
	}
	if (SQLMoreResults(Statement) != SQL_NO_DATA) {
		fprintf(stderr, "Recordset not expected\n");
		exit(1);
	}

	/* test results */
	sbuf[0] = 0;
	switch (out_c_type) {
	case SQL_C_NUMERIC:
		num = (SQL_NUMERIC_STRUCT *) out_buf;
		sprintf(sbuf, "%d %d %d ", num->precision, num->scale, num->sign);
		i = SQL_MAX_NUMERIC_LEN;
		for (; i > 0 && !num->val[--i];);
		for (; i >= 0; --i)
			sprintf(strchr(sbuf, 0), "%02X", num->val[i]);
		break;
	case SQL_C_BINARY:
		assert(out_len >= 0);
		for (i = 0; i < out_len; ++i)
			sprintf(strchr(sbuf, 0), "%02X", (int) out_buf[i]);
		break;
	case SQL_C_CHAR:
		out_buf[sizeof(out_buf) - 1] = 0;
		sprintf(sbuf,"%u %s", (unsigned int) strlen((char *) out_buf), out_buf);
		break;
	default:
		/* not supported */
		assert(0);
		break;
	}

	if (strcmp(sbuf, expected) != 0) {
		fprintf(stderr, "Wrong result\n  Got: %s\n  Expected: %s\n", sbuf, expected);
		result = 1;
	}
}
Пример #19
0
int basicproduct(SQLHANDLE hdbc,float price)
{
  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE hstmt; /* statement handle */
  SQLVARCHAR xmldata[3000];

  /* SQL/XML statement to be executed */
  SQLCHAR *stmt = (SQLCHAR *) "select xmlquery('for $prod in db2-fn:xmlcolumn(\"PRODUCT.DESCRIPTION\")"
                              "/product/description"
                              " return ( if ($prod/price < $price)"
                              " then <product basic = \"true\">{fn:data($prod/name)}</product>"
                              " else <product basic = \"false\">{fn:data($prod/name)}</product>)'"
                              " passing by ref cast(? as float) as \"price\")"
                              " from SYSIBM.SYSDUMMY1";


  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  DBC_HANDLE_CHECK(hdbc, cliRC);

  printf("    %s\n", stmt);
  cliRC = SQLPrepare(hstmt,(SQLCHAR *)stmt,SQL_NTS);
  
  /* Bind the parameter marker */ 
  printf("\nBind the parameter marker with the value %f", price);
  SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,8,0,&price,8,NULL);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  printf("\nExecute the Statement.....");
  cliRC = SQLExecute(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* Bind column 1 to variable */
  cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  
  /* Fetch each row and display */
  cliRC = SQLFetch(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  if (cliRC == SQL_NO_DATA_FOUND)
  {
    printf("\n  Data not found.\n");
  }
  while (cliRC != SQL_NO_DATA_FOUND)
  {
    printf("%s \n\n",xmldata);

    /* fetch next row */
    cliRC = SQLFetch(hstmt);
    STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  }

  /* Free the statement handle */
  cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  return rc;
} /* basicproduct */
Пример #20
0
int conditionalCustDetails1(SQLHANDLE hdbc,sqlint32 cid)
{
  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE hstmt; /* statement handle */
  SQLVARCHAR xmldata[3000];
  
  /* SQL/XML statement to be executed */
  SQLCHAR *stmt = (SQLCHAR *) "select xmlquery('for $customer in $cust/customerinfo"
                              " where ($customer/@Cid gt $id)"
                              " return <customer id=\"{$customer/@Cid}\">"
                              " {$customer/name} {$customer/addr} </customer>'"
                              " passing by ref customer.info as \"cust\", cast(? as integer) as \"id\")"
                              " from customer";
 
                             
  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  DBC_HANDLE_CHECK(hdbc, cliRC);

  printf("    %s\n", stmt);
  cliRC = SQLPrepare(hstmt,(SQLCHAR *)stmt,SQL_NTS); 
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  
  /* Bind the parameter value */ 
  printf("\nBind the parameter markers with the value %d", cid);
  SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,4,0,&cid,4,NULL); 

  printf("\nExecute the Statement.....\n");
  cliRC = SQLExecute(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* Bind column 1 to variable */
  cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 3000, NULL);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  
  /* Fetch each row and display */
  cliRC = SQLFetch(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  if (cliRC == SQL_NO_DATA_FOUND)
  {
    printf("\n  Data not found.\n");
  }
  while (cliRC != SQL_NO_DATA_FOUND)
  {
    if(strcmp((char *)xmldata,"")!=0)
    printf("%s \n\n",xmldata);

    /* fetch next row */
    cliRC = SQLFetch(hstmt);
    STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  }

  /* free the statement handle */
  cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  return rc;
} /* conditionalCustDetails1 */
Пример #21
0
void DisplayResults(HSTMT       hStmt,
                    SQLSMALLINT cCols,
		    bool        silent)
{
  SQLSMALLINT     cDisplaySize;
  RETCODE         RetCode = SQL_SUCCESS;
  int             iCount = 0;
  long long numReceived = 0;

  // Allocate memory for each column 
  SQLCHAR buffer[MAXCOLS][BUFFERLEN];
  SQLLEN indPtr[MAXCOLS];
  int iCol;

  for (iCol = 0; iCol < cCols; iCol++) {
    TRYODBC(hStmt,
	    SQL_HANDLE_STMT,
	    SQLBindCol(hStmt,
		       iCol+1,
		       SQL_C_CHAR,
		       (SQLPOINTER) buffer[iCol],
		       (BUFFERLEN) * sizeof(char),
		       &indPtr[iCol]));    
  }

  // Fetch and display the data

  bool fNoData = false;

  do {
    // Fetch a row

    TRYODBC(hStmt, SQL_HANDLE_STMT, RetCode = SQLFetch(hStmt));

    if (RetCode == SQL_NO_DATA_FOUND)
      {
	fNoData = true;
      }
    else
      {
	if (!silent) {
	  // Display the data.   Ignore truncations	
	  printf("%s", buffer[0]);
	  for (iCol = 1; iCol < cCols; iCol++) {
	    printf(",%s", buffer[iCol]);
	  }
	  printf("\n");
	}

	numReceived++;
      }
  } while (!fNoData);

 Exit:
  printf("numRecieved = %lld\n", numReceived);
}
Пример #22
0
main() {
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLRETURN ret; /* ODBC API return status */
   SQLSMALLINT columns; /* number of columns in result-set */
   SQLCHAR table[ 64 ];
   SQLCHAR column[ 64 ];
   SQLINTEGER type;
   SQLLEN indicator[ 3 ];
   int i;

   /* Allocate an environment handle */
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

   /* We want ODBC 3 support */
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

   /* Allocate a connection handle */
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

   /* Connect to the DSN mydsn */
   /* You will need to change mydsn to one you have created and tested */
   SQLDriverConnect(dbc, NULL, "DSN=PostgreSQL-rupen-aws;uid=poc;pwd=****;", SQL_NTS,
                 NULL, 0, NULL, SQL_DRIVER_COMPLETE);

   /* Allocate a statement handle */
   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   /* Retrieve a list of columns */

   SQLColumns(stmt, NULL, 0, NULL, 0, "t1", SQL_NTS, NULL, 0);

   ret = SQLBindCol( stmt, 3, SQL_C_CHAR,
         table, sizeof( table ), &indicator[ 0 ] );
   ret = SQLBindCol( stmt, 4, SQL_C_CHAR,
         column, sizeof( column ), &indicator[ 1 ] );
   ret = SQLBindCol( stmt, 5, SQL_C_LONG, &type, 0, &indicator[ 2 ] );

   /* Fetch the data */
   while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
      printf( "%s.%s type %d\n", table, column, type );
   }
}
void DbHelper::BindResultColumnInt(int* r)
{
	SQLLEN len = 0;
	SQLRETURN ret = SQLBindCol(mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_LONG, r, 4, &len);

	if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret)
	{
		PrintSqlStmtError();
	}
}
void DbHelper::BindResultColumnBool(bool* r)
{
	SQLLEN len = 0;
	SQLRETURN ret = SQLBindCol(mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_TINYINT, r, 1, &len);

	if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret)
	{
		PrintSqlStmtError();
	}
}
Пример #25
0
int maxpriceproduct(SQLHANDLE hdbc)
{
  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE hstmt; /* statement handle */
  SQLVARCHAR xmldata[3000];

  /* query to be executed */
  SQLCHAR *stmt = (SQLCHAR *)" let $prod := for $product in db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')"
                   " /product/description" 
	           " order by $product/price return $product"
                   " return <product> {$prod[1]/name} </product>";

  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  DBC_HANDLE_CHECK(hdbc, cliRC);

  /* Set the attribute SQL_ATTR_XQUERY_STATEMENT to indicate that the query is an XQuery */
  rc = SQLSetStmtAttr(hstmt, SQL_ATTR_XQUERY_STATEMENT, (SQLPOINTER)SQL_TRUE, SQL_NTS);
  printf("%d", rc);

  if (rc != 0)
  {
    return rc;
  }
 
  printf("\n  Directly execute the statement\n");
  printf("    %s\n", stmt);

  /* directly execute the statement */
  cliRC = SQLExecDirect(hstmt, stmt, SQL_NTS);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* Bind column 1 to variable */
  cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, &xmldata, 1000, NULL);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  
  /* Fetch each row and display */
  cliRC = SQLFetch(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  if (cliRC == SQL_NO_DATA_FOUND)
  {
    printf("\n  Data not found.\n");
  }
   else 
   printf("%s \n\n",xmldata);

  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* free the statement handle */
  cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  return rc;
} /* maxpriceproduct */
void DbHelper::BindResultColumnText(wchar_t* text, size_t count)
{
	SQLLEN len = 0;
	//todo: wchar_t*형 결과 컬럼 바인딩
	SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_WCHAR, text, count * 2, &len ); ///# 버퍼 크기를 넣어줘야지... count*2

	if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret)
	{
		PrintSqlStmtError();
	}
}
Пример #27
0
static SQLRETURN doSelect(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT shdl, SQLCHAR sql[], int *rcnt)
{
	SQLCHAR name[33];
	SQLINTEGER empno, col2sz, col1sz;
	SQLRETURN rc = SQLExecDirect(shdl, sql, SQL_NTS);

	CHECK_HANDLE(SQL_HANDLE_STMT, shdl, rc, "SQLExecDirect", sql);
 
	/* bind empno and name to columns 1 and 2 of the fetch */
	SQLBindCol(shdl, 1, SQL_C_LONG, (SQLPOINTER) &empno, (SQLINTEGER) sizeof (SQLINTEGER), (SQLINTEGER *) &col1sz);
	SQLBindCol(shdl, 2, SQL_C_CHAR, (SQLPOINTER) name, (SQLINTEGER) sizeof (name), &col2sz);
 
	*rcnt = 0;
	while (SQLFetch(shdl) == SQL_SUCCESS) {
		*rcnt += 1;
/*		btlogger_debug("(%ld,%s)\n", empno, name);*/
	}

	return SQL_SUCCESS;
}
void DbHelper::BindResultColumnFloat(float* r)
{
	SQLLEN len = 0;
	//todo: float형 결과 컬럼 바인딩
	SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_FLOAT, r, sizeof( float ), &len ); ///# 왜 7?? 버퍼 크기를 넣어줘야 함
	// 버퍼 크기 = sizeof( float ) = 4  by sm9

	if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret)
	{
		PrintSqlStmtError();
	}
}
Пример #29
0
void DbHelper::BindResultColumnFloat(float* r)
{
	SQLLEN len = 0;
	//todo: float형 결과 컬럼 바인딩
	SQLRETURN ret = SQLBindCol( mCurrentSqlHstmt, mCurrentResultCol++, SQL_C_FLOAT, r, 15, &len ); ///# 왜 15??
	// WIP

	if (SQL_SUCCESS != ret && SQL_SUCCESS_WITH_INFO != ret)
	{
		PrintSqlStmtError();
	}
}
Пример #30
0
// SQL문을 실행한다. 실패시 진단 정보를 출력하고 FALSE를 리턴한다.
BOOL CQuery::Exec(LPCTSTR szSQL)
{
	int c;

	while( true )
	{		
		gWindow.PrintLog("%s", szSQL);
		// SQL문을 실행한다. SQL_NO_DATA를 리턴한 경우도 일단 성공으로 취급한다. 
		// 이 경우 Fetch에서 EOF를 리턴하도록 되어 있기 때문에 진단 정보를 출력할 필요는 없다.
		ret=SQLExecDirect(hStmt,(SQLCHAR *)szSQL,SQL_NTS);
		if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_NO_DATA))
		{			
			bool bReConnect = false;

			PrintDiag(bReConnect);

			Clear();

			if(bReConnect == true)
			{
				::Sleep(1);
				continue;
			}
			return FALSE;
		}

		// Update, Delete, Insert 명령시 영향받은 레코드 개수를 구해 놓는다.
		SQLRowCount(hStmt,&AffectCount);

		SQLNumResultCols(hStmt,&nCol);
		if (nCol > MAXCOL) {
			//::MessageBox(NULL,"최대 컬럼 수를 초과했습니다","CQuery 에러",MB_OK);
			gWindow.PrintLog("CQuery error :최대 컬럼 수를 초과했습니다");
			return FALSE;
		}

		// nCol이 0인 경우는 Select문 이외의 다른 명령을 실행한 경우이므로 
		// 바인딩을 할 필요가 없다.
		if (nCol == 0) {
			Clear();
			return TRUE;
		}

		// 모든 컬럼을 문자열로 바인딩해 놓는다. Col배열은 zero base, 
		// 컬럼 번호는 one base임에 유의할 것
		for (c=0;c<nCol;c++) {
			SQLBindCol(hStmt,c+1,SQL_C_CHAR,Col[c],255,&lCol[c]);
			SQLDescribeCol(hStmt,c+1,ColName[c],30,NULL,NULL,NULL,NULL,NULL);
		}
		return TRUE;
	}
}