VenkatB


Hi,

I'm having problems successfully executing bcp_init through a function pointer retrieved at run time from sqlncli.dll.

The call works when the library is statically linked.

Can someone please point out the problem The commented out code in green executes successfully when the library is statically linked. Code marked in red fails.

SQLGetDiagRecW does not provide any useful information.

Any help is appreciated.

Regards,

Venkat

typedef SQLRETURN (__stdcall *SQLALLOCHANDLEPROC)

(

SQLSMALLINT HandleType,

SQLHANDLE InputHandle,

SQLHANDLE OutputHandle

);

/// typedef native function SQLSetEnvAttr

typedef SQLRETURN (__stdcall *SQLSETENVATTRPROC)

(

SQLHANDLE EnvironmentHandle,

SQLINTEGER Attribute,

SQLPOINTER Value,

SQLINTEGER StringLength

);

/// typedef native function SQLSetConnectAttrW

typedef SQLRETURN ( __stdcall * SQLSETCONNECTATTRPROCW )

(

SQLHANDLE ConnectionHandle,

SQLINTEGER Attribute,

SQLPOINTER ValuePtr,

SQLINTEGER StringLength

);

typedef SQLRETURN ( __stdcall *SQLCONNECTW )

(

SQLHANDLE ConnectionHandle,

SQLWCHAR * ServerName,

SQLSMALLINT NameLength1,

SQLWCHAR * UserName,

SQLSMALLINT NameLength2,

SQLWCHAR * Authentication,

SQLSMALLINT NameLength3

);

typedef RETCODE ( __stdcall *BCPINIT )

(

SQLHANDLE ConnectionHandle,

const WCHAR* TableName,

const WCHAR* DataFileNae,

const WCHAR* ErrorFileName,

SQLINTEGER Direction

);

main()

{

// Load library and initialize function pointers.

HMODULE hModule = ::LoadLibrary(_T("SQLNCLI.dll"));

SQLALLOCHANDLEPROC fnAlloc = (SQLALLOCHANDLEPROC)::GetProcAddress( hModule, _T("SQLAllocHandle") );

SQLSETENVATTRPROC fnEnvAttSet = (SQLSETENVATTRPROC)::GetProcAddress( hModule, _T("SQLSetEnvAttr") );

SQLSETCONNECTATTRPROCW fnConnectAttSet = (SQLSETCONNECTATTRPROCW)::GetProcAddress( hModule, _T("SQLSetConnectAttrW") );;

SQLCONNECTW fnConnectProc = (SQLCONNECTW)::GetProcAddress( hModule, _T("SQLConnectW") );

BCPINIT fnBCPInit = (BCPINIT)::GetProcAddress( hModule, _T("bcp_initW") );

// Allocate ODBC handles.

SQLHENV m_SQLEnvironment;

SQLHDBC m_SQLConnection;

SQLHSTMT m_SQLStatement;

SQLRETURN iReturn;

iReturn = fnAlloc( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);

//iReturn = SQLAllocHandle( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);

iReturn = fnEnvAttSet(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);

//iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);

iReturn = fnAlloc(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

//iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

// Set up bulk copy.

int nType = SQL_COPT_SS_BCP;

SQLPOINTER pvAttribute = (SQLPOINTER)SQL_BCP_ON;

SQLINTEGER nValue = SQL_IS_INTEGER;

iReturn = fnConnectAttSet(m_SQLConnection, nType, pvAttribute, nValue );

//iReturn = SQLSetConnectAttrW(m_SQLConnection, nType, pvAttribute, nValue );

// Connect.

BSTR ODBCSource = :: SysAllocString(L"SQL2005");

BSTR UserName = :: SysAllocString(L"sa");

BSTR Password = :: SysAllocString(L"xxx");

iReturn = fnConnectProc(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);

//iReturn = SQLConnectW(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);

// Init bulk copy.

BSTR sTableName = :: SysAllocString(L"test");

BSTR sErrorFile = :: SysAllocString(L"c:\\test4.txt");

int nDirection = DB_IN;

RETCODE nRet = fnBCPInit( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 0

//RETCODE nRet = bcp_initW( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 1

}




Re: ODBC bcp_init run time dynamic link library

VenkatB


Database - Sql Server 2005 SP2.






Re: ODBC bcp_init run time dynamic link library

Anton Klimov - MSFT

This is a rough guess, since I do not have source access at this moment (and I could check tomorrow) but I guess that when you link statically you are going through ODBC Driver Manager (odbc32.dll), when you link dynamically you are going direclty against the driver. Now if you are going through ODBC DM you are getting DM handles which wrap driver's handles, when you are linking dynamically you are getting raw driver's handles. I suppose bcp_init really expects an ODBC DM handle and dynamic linking is not supported.





Re: ODBC bcp_init run time dynamic link library

VenkatB

Hi Anton,

Thanks for the reply. Can you please verify if bcp_xxx calls can be made on a dynamially linked library

What are my options if bcp_xxx functions cannot be executed against dynamically loaded SQLNCLI.dll

Will the following code offer the next best alternative in terms of performance with dyn loaded SQLNCLI.dll

SQLPrepare(Stmt);

ForEachColumn()

{

SQLBindParameter();

}

ForEachDataRow()

{

Set up bound pointers.

Invoke SQLExecute.

}

Thanks again for the quick reply.

Regards,

Venkat





Re: ODBC bcp_init run time dynamic link library

Anton Klimov - MSFT

bcp_xxx calls can be made but they expect ODBC DM handles not raw driver's handles.

Instead of getting the basic ODBC functions directly from the driver you should get them from ODBC32. Then you will get ODBC DM handles and that should work when passed into bcp_init.




Re: ODBC bcp_init run time dynamic link library

Waseem Basheer - MSFT

Anton is correctly pointing out the fact that if you use odbc32.dll in your dynamic loading code then you will be able to invoke the bcp API functions. Also note that dynamic loading is not strictly necessary in this case. What you can do instead is to use the SQLDriverConnect API and choose the appropriate SQL Server ODBC Driver instead of dynamically trying to get each function address through GetProcAddress.

Thanks

Waseem Basheer






Re: ODBC bcp_init run time dynamic link library

VenkatB

Waseem, Anton,

Thank you for your answers.

Our application supports a number of databases. Clients working with Oracle may not want to install sqlncli.dll only to allow our application to start up. This forces us to dynamically load data connectivity libraries including sqlncli.dll.

I don't see any bcp_xxx functions exposed through odbc32.dll. Am i missing something

Regards,

Venkat

Is this what you asked me to try If so, bcp_init fails again.

SQLALLOCHANDLEPROC fnAlloc = (SQLALLOCHANDLEPROC)::GetProcAddress( hModule, _T("SQLAllocHandle") );

SQLSETENVATTRPROC fnEnvAttSet = (SQLSETENVATTRPROC)::GetProcAddress( hModule, _T("SQLSetEnvAttr") );

SQLSETCONNECTATTRPROCW fnConnectAttSet = (SQLSETCONNECTATTRPROCW)::GetProcAddress( hModule, _T("SQLSetConnectAttrW") );;

SQLCONNECTW fnConnectProc = (SQLCONNECTW)::GetProcAddress( hModule, _T("SQLConnectW") );

BCPINIT fnBCPInit = (BCPINIT)::GetProcAddress( hModule, _T("bcp_initW") );

SQLDRIVERCONNECT fnDriverConnect = (SQLDRIVERCONNECT)::GetProcAddress( hModule, _T("SQLDriverConnectW") );

SQLHENV m_SQLEnvironment;

SQLHDBC m_SQLConnection;

SQLHSTMT m_SQLStatement;

SQLRETURN iReturn;

iReturn = fnAlloc( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);

iReturn = fnEnvAttSet(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);

iReturn = fnAlloc(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

int nBCPParameter = SQL_COPT_SS_BCP;

SQLPOINTER nBCPOn = (SQLPOINTER)SQL_BCP_ON;

SQLINTEGER nType = SQL_IS_INTEGER;

iReturn = SQLSetConnectAttrW(m_SQLConnection, nBCPParameter, nBCPOn , nType );

SQLCHAR sOutString[ MAX_CONN_OUT ];

SQLSMALLINT nOutConnectStringLen = 0;

SQLUSMALLINT nDriverConnection = SQL_DRIVER_NOPROMPT;

sOutString[ 0 ] = '\0';

BSTR sConnectionString = :: SysAllocString(L"DSN=SQL2005;UID=sa;PWD=xxx;DRIVER={SQL Native Client};");

iReturn = fnDriverConnect( m_SQLConnection, NULL, sConnectionString, SQL_NTS, sOutString, MAX_CONN_OUT, &nOutConnectStringLen, nDriverConnection );

iReturn = fnAlloc(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);

BSTR sTableName = :: SysAllocString(L"test");

BSTR sErrorFile = :: SysAllocString(L"w:\\test4.txt");

int nDirection = DB_IN;

RETCODE nRet = fnBCPInit( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection );





Re: ODBC bcp_init run time dynamic link library

Anton Klimov - MSFT

The suggestion was to get SQLAllocHandle, SQLSetEnvAttr, SQLSetConnectAttrW, SQLConnectW, SQLDriverConnectW from ODBC32.DLL; and still get bcp_initW from SQLNCLI.DLL





Re: ODBC bcp_init run time dynamic link library

VenkatB

Thank you very much!