SQLConnect

It establishes connections to a driver and a data source, and can only set server, ID, and PWD.

In SQLConnect, if you want to connect Machbase other than 5656 port, you have to change PORT argument by using SQLSetConnectAttr function before SQLConnect.

Syntax

SQLRETURN SQL_API SQLConnect (SQLHDBC aCon,
SQLCHAR *aDSN,
SQLSMALLINT aDSNLength ,
SQLCHAR *aUserId,
SQLSMALLINT aUserIdLength ,
SQLCHAR *aAuthStr,
SQLSMALLINT aAuthStrLength )

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHDBC aCon Input Connection handle
SQLCHAR* aDSN Input Server name in characters
SQLSMALLINT aDSNLength Input Length of server name in characters
SQLCHAR* aUserId Input User ID
SQLSMALLINT aUserIdLength Input Length of user ID
SQLCHAR* aAuthStr Input Authentication string
SQLSMALLINT aAuthStrLength Input Length of authentication string

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Invalid input handle Communication link failure between databases
SQL_INVALID_HANDLE Abnormal termination *Unallocated connection handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
08001 Client unable to establish connection Errors in argument values
08S01 Communication link failure Communication link failure between databases

Sample Code

Refer to SQLAllocConnect for more information.

if( SQLConnect(sCon,
(SQLCHAR*)"127.0.0.1", SQL_NTS,
NULL, 0,
NULL, 0) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Connect error");
}

SQLCopyDesc

It copies descriptor information from one descriptor handle to another.

Syntax

SQLRETURN SQL_API SQLCopyDesc( SQLHDESC aSourceDescHandle,SQLHDESC aTargetDescHandle )

Arguments

Table. Parameter

Type Name of Argument Input/Output Description
SQLHDESC aSourceDescHandle Input Original descriptor handle
SQLHDESC aTargetDescHandle Output Target descriptor handle

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_INVALID_HANDLE Abnormal termination *Unallocated descriptor handle

Sample Code

SQLHDESC hArd0, hApd1;
retcode = SQLCopyDesc(hArd0, hApd1);

SQLDescribeCol

In returns the result descriptor - column name, type, column size, decimal digits, and nullability - for one column in the result set.

Syntax

SQLRETURN SQLDescribeCol( SQLHSTMT aStmt,
SQLUSMALLINT aColumnNumber,
SQLCHAR *aColumnName,
SQLSMALLINT aBufferLength,
SQLSMALLINT *aNameLength,
SQLSMALLINT *aDataType,
SQLULEN *aColumnSize,
SQLSMALLINT *aDecimalDigits,
SQLSMALLINT *aNullable)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLUSMALLINT aColumnNumber Input Number of column
SQLCHAR* aColumnName Output Name of column
SQLSMALLINT aBufferLength Input Size of name buffer
SQLSMALLINT* aNameLength Output Length of column name
SQLSMALLINT* aDataType Output Column type
SQLULEN* aColumnSize Output Column size
SQLSMALLINT* aDecimalDigits Output Decimal digits of column
SQLSMALLINT* aNullable Output Whether the column allows NULL values

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Invalid column number Size of name buffer is less than 0
SQL_INVALID_HANDLE Abnormal termination *Unallocated statement handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
07009 Invalid descriptor index Invalid column number
HY090 Invalid string or buffer length Size of name buffer is less than 0

Sample Code

Refer to SQLDescribeCol for more information.

/*exam table query
"CREATE TABLE CLI_SAMPLE(seq short, score integer, total long, percentage float, ratio double, id varchar(10), srcip ipv4, dstip ipv6, reg_date datetime, tlog text, image binary)";
*/
char sSqlStr[] = "select * from cli_sample";
SQLCHAR sColName[32];
SQLSMALLINT sColType;
SQLSMALLINT sColNameLen;
SQLSMALLINT sNullable;
SQLULEN sColLen;
SQLSMALLINT sDecimalDigits;
SQLLEN sOutlen;
SQLCHAR* sData;
SQLLEN sDisplaySize;
int i;

SQLSMALLINT sColumns;

if(SQLPrepare(gStmt, (SQLCHAR*)sSqlStr, SQL_NTS))
{
outError("sql prepare fail", gStmt);
return -1;
}

if(SQLNumResultCols(gStmt, &sColumns) != SQL_SUCCESS )
{
printf("get col length error \n");
return -1;
}

printf("----------------------------------------------------------------\n");
printf("%32s%16s%10s\n","Name","Type","Length");
printf("----------------------------------------------------------------\n");

for(i = 0; i < sColumns; i++)
{
SQLDescribeCol(gStmt,
(SQLUSMALLINT)(i + 1),
sColName,
sizeof(sColName),
&sColNameLen,
&sColType,
(SQLULEN *)&sColLen,
&sDecimalDigits,
(SQLSMALLINT *)&sNullable);

printf("%32s%16d%10d\n",sColName, sColType, sColLen);
}
printf("----------------------------------------------------------------\n");

SQLDisconnect

It closes the connection associated with a specific connection handle.

Syntax

SQLRETURN SQL_API SQLDisconnect (SQLHDBC aDbc)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHDBC aDbc Input Connection handle

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Connection handle disconnected Communication link failrue between databases
SQL_INVALID_HANDLE Abnormal termination *Unallocated connection handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
08003 Connection not open Connection was not open.
08S01 Communication link failure Communication link failure between databases

Sample Code

Refer to SQLAllocHandle for more information.

SQLDriverConnect

It supports data sources that require more connection information than the three arguments in SQLConnect. If you enter the related-information with connection as string, you can allocate it according to the information.

Syntax

SQLRETURN SQL_API SQLDriverConnect( SQLHDBC aDbc,
SQLHWND aWnd,
SQLCHAR *aConnStrIn,
SQLSMALLINT aConnStrInMax,
SQLCHAR *aConnStrOut,
SQLSMALLINT aConnStrOutMax,
SQLSMALLINT *aConnStrOutLength,
SQLUSMALLINT aDriverCompletion)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHDBC aDbc Input Connection handle
SQLHWND aWnd Input Window handle when outputs dialog boxes.
SQLCHAR* aConnStrIn Input Connection string
SQLSMALLINT aConnStrInMax Input Buffer size of the connection string
SQLCHAR* aConnstrOut Output Connection string that was successfully processed
SQLSMALLINT aConnStrOutMax Input Length of connection string that was successfully processed
SQLSMALLINT* aConnStrOutLength Output Length of connection string that was processed
SQLUSMALLINT aDriverCompletion Input Whether the driver needs additional data except input string

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Invalid connection string Communication link *Failure between databases
SQL_INVALID_HANDLE Abnormal termination *Unassigned connection handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
01S00 Invalid connection string attribute Invalid connection string
08S01 Communication link failure Communication link failure between databases

Sample Code

Refer to SQLAllocHandle for more information.

SQLError

It returns error or status information.

Syntax

SQLRETURN SQLError (SQLHENV aEnv,
SQLHDBC aDbc,
SQLHSTMT aStmt,
SQLCHAR *aSqlState,
SQLINTEGER *aNativeError,
SQLCHAR *aErrorMsg,
SQLSMALLINT aErrorMsgMax,
SQLSMALLINT *aErrorMsg)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHENV aEnv Input Environmen handle
SQLHDBC aDbc Input Connection handle
SQLHSTMT aStmt Input Statement handle
SQLCHAR* aSqlState Output SQL status
SQLINTEGER* aNativeError Output Error code
SQLCHAR* aErrorMsg Output Error message
SQLSMALLINT aErrorMsgMax Input Buffer size of the error message
SQLSMALLINT* aErrorMsgLength Output Length of the error message

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_NO_DATA_FOUND Abnormal termination *Unable to find the relevant value

Sample Code

Refer to SQLFreeHandle for more information.

SQLExecDirect

Enter a static query statement that doesn't have any additional parameter to connect inside the query, and run commands.

Syntax

SQLRETURN SQL_API SQLExecDirect ( SQLHSTMT aStmt,
SQLCHAR *aBuffer,
SQLINTEGER aBufSize)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLCHAR* aBuffer Input SQL statement
SQLINTEGER aBufSize Input Length of statement

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Communication link failure between databases Length of the query was less than 0 but was not SQL_NTS.
SQL_INVALID_HANDLE Abnormal termination *Unassigned statement handle
SQL_NO_DATA_FOUND Abnormal termination *No query result value

Diagnostic Codes

Table. Diagnostic Codes

| Code | Error | Description | | 08S01 | Communication link failure | Communication link failure between databases | | HY009 | Invalid use of null pointer | Length of the query was less than 0 but was not SQL_NTS.|

Sample Code

Refer to Data Input and Output for more information.

#include <stdio.h>
#include <stdlib.h>
#include <mach_sqlcli.h>
#include <string.h>

struct bindData//data binding struct
{
SQLSMALLINT mTargetType;
char mTargetValuePtr[30];
SQLINTEGER mBufferMax;
SQLLEN mBufferLength;
};

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg);//error print function
void connectDB(SQLHENV* aEnv, SQLHDBC* aCon, SQLHSTMT* aStmt);//db connect function
void disconnectDB(SQLHENV* aEnv, SQLHDBC* aCon, SQLHSTMT* aStmt);//db disconnect function

int main()
{
SQLHENV sEnv = SQL_NULL_HANDLE;
SQLHDBC sCon = SQL_NULL_HANDLE;
SQLHSTMT sStmt = SQL_NULL_HANDLE;
int i = 0;
int sColumnCount = 0;
int sTotalCount = 0;
char *sSql = "create table sqltest(int1 integer, char1 varchar(30))";

connectDB(&sEnv, &sCon, &sStmt);

sSql = "drop table sqltest";
if( SQLExecDirect( sStmt, (SQLCHAR*)sSql, strlen(sSql)) == SQL_ERROR )
{
printf("SQL Exec Direct error(%s)\n",sSql);
}

sSql = "create table sqltest(int1 integer, char1 varchar(30))";
if( SQLExecDirect( sStmt, (SQLCHAR*)sSql, strlen(sSql)) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Exec Direct error");
}
printf("%s success\n", sSql);

sSql = "insert into sqltest values(10, 'direct insert')";
for( i = 0; i < 10; i++ )
{
if( SQLExecDirect( sStmt, (SQLCHAR*)sSql, strlen(sSql)) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Exec Direct error");
}
SQLRowCount( sStmt, (SQLLEN*)&sColumnCount );
printf("%s success(row count : %d)\n", sSql,sColumnCount);
sTotalCount += sColumnCount;
}

printf("total insert row : %d\n", sTotalCount);

disconnectDB(sEnv, sCon, sStmt);
return 0;
}

void connectDB(SQLHENV* aEnv, SQLHDBC* aCon, SQLHSTMT* aStmt)
{
char sConnStr[40] = "DSN=127.0.0.1;CONNTYPE=1;PORT_NO=9875";

if( SQLAllocEnv(aEnv) == SQL_ERROR )
{
printError(*aEnv, NULL, NULL, "SQL Alloc Env Handle error\n");
}

if( SQLAllocConnect(*aEnv, aCon) == SQL_ERROR )
{
printError(*aEnv, *aCon, NULL, "SQL Alloc Con Handle error\n");
}

if( SQLDriverConnect(*aCon, NULL,
(SQLCHAR*)sConnStr, SQL_NTS,
NULL, 0,
NULL, SQL_DRIVER_NOPROMPT) == SQL_ERROR )
{
printError(*aEnv, *aCon, NULL, "SQL Driver Connect error");
}

if( SQLAllocStmt(*aCon, aStmt) == SQL_ERROR )
{
printError(*aEnv, *aCon, *aStmt, "SQL Alloc Stmt Handle error");
}
}

void disconnectDB(SQLHENV* aEnv, SQLHDBC* aCon, SQLHSTMT* aStmt)
{
if( SQLFreeStmt( aStmt, SQL_DROP ) == SQL_ERROR )
{
printError(aEnv, aCon, aStmt, "SQL Free Stmt Handle error");
}

if( SQLDisconnect( aCon ) == SQL_ERROR )
{
printError(aEnv, aCon, NULL, "SQL Disconnect error");
}

if( SQLFreeConnect( aCon ) == SQL_ERROR )
{
printError(aEnv, aCon, NULL, "SQL Free Con Handle error");
}

if( SQLFreeEnv( aEnv ) == SQL_ERROR )
{
printError(aEnv, NULL, NULL, "SQL Free Env Handle error");
}
}

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg)
{
SQLINTEGER sErrNo;
SQLSMALLINT sMsgLength;
SQLCHAR sErrMsg[1024];
SQLCHAR sSqlCode[6];

printf("%s\n", msg);
if( SQLError(aEnv, aCon, aStmt, sSqlCode, &sErrNo, sErrMsg, 1024, &sMsgLength) == SQL_SUCCESS )
{
printf("SQL[%s] Machbase[%d] - %s\n", sSqlCode, sErrNo, sErrMsg);
}
exit(-1);
}

SQLExecute

It executes a prepared statement, using the current values of the parameter marker variables if any parameters exist in the statement.

Syntax

SQLRETURN SQLExecute (SQLHSTMT aStmt)

Arguments

Table 50. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination Invalid conversion type Communication link failure between databases *Statement handle was not prepared.
SQL_NO_DATA Abnormal termination *No data results
SQL_INVALID_HANDLE Abnormal termination *Unassigned statement handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
07006 Restricted data type attribute violation Invalid conversion type
08S01 Communication link failure Communication link failure between databases
HY010 Function sequence error Statement handle was not prepared.

Sample Code

Refer to SQLPrepare for more information.

results matching ""

    No results matching ""