/*------------------------------------------------------------------------------*
 * File Name datasource.c 														*
 * Creation: 																	*
 * Purpose: Basic utility functions for ADO access from Origin C				*
 * Copyright (c) Originlab Corp.	2006										*
 * All Rights Reserved															*
 * 																				*
 * Modification Log:															*
 * Nicole 07/05/2007 v8.0655 REMOVE_SPACE_FOR_LOCATION_STRING						*
 * AW 09/15/07 QA80-9625 MORE_ON_CANCEL_LOAD_ODQ								*
 * AW 09/22/07 QA80-9265 MORE_ON_PREVIW											*
 *	CPY 11/4/2007 CLEAN_ERR_MSG													*
 *	CPY 11/4/2007 QUERY_IMPORT_ASK_PASSWORD_NEED_LOCALIZED_AND_PARENT_HWND		*
 *	CPY 1/21/2008 QA70-10972 LT_CONTROL_OF_DB_ACCESS							*
 *	CPY 1/22/2008 QA70-10972 MYSQL_ODBC_PROVIDER_PREVIEW_NOT_SHOW				*
 *	ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET					*
 *	AW 02/04/08 MORE_ON_LOGIN_PROMPT											*
 *  Fisher 03/10/08 QA80-11219 SUPPORT_FILE_ADO_RECENT_IMPORT                   *
 *	Sophy 5/13/2009 QA80-12574 FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
 *	AW SOPHY 05/14/2009 QA80-13574-P1 FIX_ORACLE_PREVIEW_ISSUE					*
 *	Sophy 5/15/2009 SHOULD_ALLOW_USER_UNCHECK_REMEMBER_PASSWORD_IN_ADQ_FILE_AND_REQUIRE_AUTHORIZATION_LATER_FOR_ALL_PROVIDERS
 *	Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
 *	Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
 *	Sophy 8/31/2009 QA80-14057-P1 FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
 *	ML 11/9/2009 QA70-14617 IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
 *------------------------------------------------------------------------------*/
 
#include <Origin.h>
#include <GetNBox.h>
#include <ocu.h>
#include <okocUtils.h>
#include "datasource.h"
///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
#include <..\Originlab\OODBC.h>
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER


#define STR_QUERY_SQL					STR_SQL		  //--- CPY QA70-8636 SAVE_CONN_OBJ_IN_WKS, moved this to oc_const.h
#define STR_QUERY_CONN					STR_DATA_SOURCE	  //--- CPY QA70-8636 SAVE_CONN_OBJ_IN_WKS
#define STR_DS_OLD						"Data_Source"
#define STR_SECTION_FORMAT				"[%s]\r\n"
///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
#define	STR_QUERY_MODE					"UseODBC"
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER

BOOL db_load_data_source(LPCSTR lpcszFileName, string& strDatasource, string* pstrDSfilename)
{
	string strQueryFileName = lpcszFileName;
	if(!strQueryFileName.IsFile())
		return false;
	
	int nRet = okutil_read_file_section(strQueryFileName, STR_QUERY_CONN, &strDatasource, pstrDSfilename);	 
	if ( nRet < 0 )
	{
		nRet = okutil_read_file_section(strQueryFileName, STR_DS_OLD, &strDatasource, pstrDSfilename);
		if(nRet < 0)
			return FALSE;
	}
	return true;	
}

/// AW 02/04/08 MORE_ON_LOGIN_PROMPT
BOOL db_need_login_prompt(LPCSTR lpcszConnStr) 
{
	///Sophy 5/15/2009 SHOULD_ALLOW_USER_UNCHECK_REMEMBER_PASSWORD_IN_ADQ_FILE_AND_REQUIRE_AUTHORIZATION_LATER_FOR_ALL_PROVIDERS
	//int nProviderType = db_quess_provider(lpcszConnStr);
	//
	//if ( DB_PROVIDER_SQL_SERVER != nProviderType )
	//{
		//// NOTE!!! We need test Oracle later, now I don't have much time to cost here, hope GZ can help testing here
		//// And, for mySQL, only work through ODBC
		//return false;
		//
	//}
	///end SHOULD_ALLOW_USER_UNCHECK_REMEMBER_PASSWORD_IN_ADQ_FILE_AND_REQUIRE_AUTHORIZATION_LATER_FOR_ALL_PROVIDERS
	
	string strConn = lpcszConnStr;
	strConn.TrimLeft(); strConn.TrimRight();
	if(strConn.IsEmpty())
		return false;
	vector<string> vsKeys, vsValues, vsKeysLower;
	int nRet = ocu_separate_key_values(strConn, &vsKeys, &vsValues);
	vsKeysLower = vsKeys;
	
	BOOL bSecurity = false;
	BOOL bHasPW = false;
	for(int ii = 0; ii < vsKeys.GetSize(); ii++) 
	{
		string strKey = vsKeysLower[ii];
		strKey.MakeLower();
		if ( strKey.Find("security") >= 0 )
		{
			bSecurity = vsValues[ii].CompareNoCase("true") == 0 ? true: false;		
		}
		if ( strKey.Find("password") >= 0 )
		{
			bHasPW = true;	
		}
		
	}
	if (!bSecurity) 
	{
		if ( bHasPW )
			return false;
		return true;
	}
	return false;
}
/// END MORE_ON_LOGIN_PROMPT

BOOL db_is_connection_str_has_password(LPCSTR lpcszConnStr, string* pstrUseName, string* pstrDS)// = NULL);
{
	string strConn = lpcszConnStr;
	strConn.TrimLeft(); strConn.TrimRight();
	if(strConn.IsEmpty())
		return false;
	
	vector<string> vsKeys, vsValues, vsKeysLower;
	int nRet = ocu_separate_key_values(strConn, &vsKeys, &vsValues);
	vsKeysLower = vsKeys;
	for(int ii = 0; ii < vsKeys.GetSize(); ii++) 
		vsKeysLower[ii].MakeLower();
	
	/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
	/*
	if(pstrUseName)
	{
		int nn = vsKeysLower.Find("user id");
		if(nn >= 0)
		{
			*pstrUseName = vsValues[nn]; 
		}
	}
	*/
	int nUserID = vsKeysLower.Find("user id");	
	if(pstrUseName)
	{
		if(nUserID >= 0)
		{
			*pstrUseName = vsValues[nUserID]; 
		}
	}
	/// END HANDLE_PASSWORD_IN_CONNECTION
	if(pstrDS)
	{
		int nn = vsKeysLower.Find("data source");
		if(nn >= 0)
		{
			*pstrDS = vsValues[nn]; 
		}
	}		
	int nPwd = vsKeysLower.Find("password");
	
	/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
	//return nPwd >= 0? true:false;
	if ( nPwd < 0 && nUserID >= 0)
	{
		return false;
	}
	return true;
		
	/// END HANDLE_PASSWORD_IN_CONNECTION
}

BOOL db_add_password_to_connection_str(LPCSTR lpcszConnStr, LPCSTR lpcszUser, LPCSTR lpcszPwd, string& strConnStr)
{
	string strConn = lpcszConnStr;
	strConn.TrimLeft(); strConn.TrimRight();
	vector<string> vsKeys, vsValues, vsKeysLower;
	int nRet = ocu_separate_key_values(strConn, &vsKeys, &vsValues);
	vsKeysLower = vsKeys;
	for(int ii = 0; ii < vsKeys.GetSize(); ii++) 
		vsKeysLower[ii].MakeLower();

	int nn = vsKeysLower.Find("user id");
	if(nn >= 0)
		vsValues[nn] = lpcszUser;
	else
	{
		vsKeys.Add("User ID");
		vsValues.Add(lpcszUser);
	}
	nn = vsKeysLower.Find("password");
	if(nn >= 0)
		vsValues[nn] = lpcszPwd;
	else
	{
		vsKeys.Add("Password");
		vsValues.Add(lpcszPwd);
	}
	ocu_make_key_values_str(&strConnStr, &vsKeys, &vsValues);
	return true;
}
///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_load_query(string& strDatasource, string& strQuery, LPCSTR lpcszFileName, string* pstrDSfilename, string* pstrODQfilename)
#else
BOOL db_load_query(string& strDatasource, string& strQuery, LPCSTR lpcszFileName, string* pstrDSfilename, string* pstrODQfilename, string* pstrQueryMode)
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
{
	string strQueryFileName = lpcszFileName;
	//----- CPY 1/21/2008 QA70-10972 LT_CONTROL_OF_DB_ACCESS
	//if (!strQueryFileName.IsFile())
	if(NULL == lpcszFileName)
	//-----
	{
		string strUserPath = okutil_get_origin_path(ORIGIN_PATH_USER);
		StringArray saFiletypes;
		saFiletypes.SetSize( 1 );
		saFiletypes[0] = "[ODQ File (*" + STR_QUERY_FILE_EXT + ")] *" + STR_QUERY_FILE_EXT;//STR_QUERY_FILE_EXT	
		strQueryFileName = GetOpenBox( saFiletypes, strUserPath);
		if ( strQueryFileName.IsEmpty() )
		/// AW 09/15/07 QA80-9625 MORE_ON_CANCEL_LOAD_ODQ
		// rteurn  FALSE;
		return TRUE;
		/// END MORE_ON_CANCEL_LOAD_ODQ
	}
	//----- CPY 1/21/2008 QA70-10972 LT_CONTROL_OF_DB_ACCESS
	else if(!strQueryFileName.IsFile())
		return false;
	//-----
	
	if(pstrODQfilename)
		*pstrODQfilename = strQueryFileName;
	
	if(!db_load_data_source(strQueryFileName, strDatasource, pstrDSfilename))
		return false;
	
	okoc_AddToRecentFileList(strQueryFileName);			 // Fisher 03/10/08 QA80-11219 SUPPORT_FILE_ADO_RECENT_IMPORT
	
	int nRet = okutil_read_file_section(strQueryFileName, STR_QUERY_SQL, &strQuery, NULL);
	if ( nRet < 0 )
		return FALSE;
	if ( pstrDSfilename && pstrDSfilename->GetAt(0) == ';' )			// temp codes result from shulin's wrong code in readsection
		pstrDSfilename->Delete(0,1);
	///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	if ( pstrQueryMode != NULL )
	{
		nRet = okutil_read_file_section(strQueryFileName, STR_QUERY_MODE, pstrQueryMode, NULL);
		//if nRet < 0 means section not found or file not found, nRet is the length of pstrQueryMode, make sure not empty
		//will not return error for backward compatibility.
		ASSERT(nRet > 0); 
		
	}
	
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	return TRUE;
}

static void _check_add_path(string& strFile, int nPath = ORIGIN_PATH_USER)
{
	string strPath = GetFilePath(strFile);
	if(strPath.IsEmpty())
	{
		string strUserPath = okutil_get_origin_path(nPath);
		strFile = strUserPath + strFile;
	}
}
		
///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_save_query(LPCSTR lpcszFileName, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename)
#else
BOOL db_save_query(LPCSTR lpcszFileName, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename, LPCSTR lpcszQueryMode)
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
{
	string strQueryFileName = lpcszFileName;
	_check_add_path(strQueryFileName);
	int nRet = okutil_write_file_section(strQueryFileName, STR_QUERY_CONN, lpcszDatasource, lpcszDSfilename);
	if ( nRet < 0 )
		return FALSE;
	nRet = okutil_write_file_section(strQueryFileName, STR_QUERY_SQL, lpcszQuery, NULL); 
	if ( nRet < 0 )
		return FALSE;

	///Sophy 8/3/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	nRet = okutil_write_file_section(strQueryFileName, STR_QUERY_MODE, lpcszQueryMode, NULL);
	if ( nRet < 0 )
		return FALSE;
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__

	okoc_AddToRecentFileList(strQueryFileName);		 // Fisher 03/10/08 QA80-11219 SUPPORT_FILE_ADO_RECENT_IMPORT
	
	return TRUE;
}

static bool _get_wks_SQL_tree(Worksheet& wks, Tree& tr)
{
	vector<byte> vb;
	if(!wks.GetMemory(STR_ADO, vb))
		return false;
	if(!tr.XML.SetBytes(vb))	
	{
		//---- CPY 11/4/2007 CLEAN_ERR_MSG
		//out_str("failed to get XML info");
		//return false;
		return error_report("_get_wks_SQL_tree failed to get XML info");
		//---
	}
	return true;
}

///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_wks_set_query(Worksheet& wks, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename, bool bAfterImportSuccess)
#else
BOOL db_wks_set_query(Worksheet& wks, LPCSTR lpcszDatasource, LPCSTR lpcszQuery, LPCSTR lpcszDSfilename, bool bAfterImportSuccess, BOOL* pbUseODBC)//NULL, NULL, NULL, false, NULL
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
{
	if ( !wks.IsValid() )
		return FALSE;
	if(NULL == lpcszQuery) // to clear it
	{
		wks.SetMemory(STR_ADO, NULL);
		return true;
	}
	Tree trOld;
	string strLastModified, strLastUsed;
	if(_get_wks_SQL_tree(wks, trOld))
	{
		TreeNode tr1 = trOld.GetNode(STR_LAST_MODIFIED);
		if(tr1)
			strLastModified = tr1.strVal;
		tr1 = trOld.GetNode(STR_LAST_USE);
		if(tr1)
			strLastUsed = tr1.strVal;
	}	
	Tree tr;
	TreeNode trConn = tr.AddTextNode(lpcszDatasource, STR_QUERY_CONN);
	if(lpcszDSfilename)
		trConn.SetAttribute(STR_FILENAME_ATTRIB,lpcszDSfilename);
	TreeNode trSQL = tr.AddTextNode(lpcszQuery, STR_QUERY_SQL);
	//---- setting modified time etc
	double dCurrentTime ;
	SYSTEMTIME systime;
	get_current_time(systime);
	SystemTimeToJulianDate(&dCurrentTime, &systime);
	TreeNode trLM = tr.AddNode(STR_LAST_MODIFIED);
	TreeNode trLU = tr.AddNode(STR_LAST_USE);
	if(bAfterImportSuccess)
	{
		trLM.strVal = strLastModified;
		trLU.dVal = dCurrentTime;
	}
	else
	{
		trLM.dVal = dCurrentTime;
		trLU.nVal = 0;
	}
	//----
	///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	if ( pbUseODBC != NULL )
	{
		TreeNode trQueryMode = tr.AddNode(STR_QUERY_MODE);
		trQueryMode.nVal = *pbUseODBC;
	}
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
	string strXML = tr.XML;
	vector<byte> vb;
	if(!strXML.GetBytes(vb) || !wks.SetMemory(STR_ADO, vb))
	{
		//---- CPY 11/4/2007 CLEAN_ERR_MSG
		//out_str("failed to save SQL info into wks");
		//return false;
		return error_report("db_wks_set_query failed to save SQL info into wks");
		//----
	}
	return true;
}

///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
BOOL db_wks_get_query(Worksheet& wks, string& strDatasource, string& strQuery, string& strDSFilename)
#else
BOOL db_wks_get_query(Worksheet& wks, string& strDatasource, string& strQuery, string& strDSFilename, BOOL* pbUseODBC) //NULL, NULL
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
{
	if ( !wks.IsValid() )
		return FALSE;
	if(strDSFilename) strDSFilename.Empty();
	Tree tr;
	if(!_get_wks_SQL_tree(wks, tr))
		return false;
	
	TreeNode trConn = tr.GetNode(STR_QUERY_CONN);
	if(trConn)
	{
		strDatasource = trConn.strVal;
		string strFilename;
		if(trConn.GetAttribute(STR_FILENAME_ATTRIB, strFilename))
		{
			if(strDSFilename)
				strDSFilename = strFilename;
		}
		///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
		TreeNode trQueryMode = tr.GetNode(STR_QUERY_MODE);
		if ( trQueryMode && pbUseODBC != NULL )
		{
			*pbUseODBC = trQueryMode.nVal;
		}__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
		///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
		TreeNode trSQL = tr.GetNode(STR_QUERY_SQL);
		if(trSQL)
		{
			strQuery = trSQL.strVal;
			return true;
		}
	}
	//---- CPY 11/4/2007 CLEAN_ERR_MSG
	//out_str("failed in getting SQL info from tree");
	//return false;
	return error_report("db_wks_get_query failed in getting SQL info from tree");
	//----
}

/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
//int db_wks_import(Worksheet& wks, int nPreviewLines, int nColBegin, bool bAllowSavePassword)// = 0 = 0;
int db_wks_import(Worksheet& wks, int nPreviewLines, int nColBegin, bool bAllowSavePassword, bool bNeedPassWord, HWND hDlg)// = 0 = 0 = false = true;
/// END HANDLE_PASSWORD_IN_CONNECTION
{
	OWKSDB wksdb(wks);
	return wksdb.Import(nColBegin, nPreviewLines, bAllowSavePassword, bNeedPassWord, hDlg);  /// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION

}

OWKSDB::OWKSDB(Worksheet& wks)
{
	m_wks = wks;
}
OWKSDB::~OWKSDB()
{
}

static bool _close_obj(Object& ors)
{
	if ( ors && ors.State == adStateOpen )
	{
		try 
		{
	    	ors.Close();
		}
		catch(int nError)
		{
			out_str("Failed to close ADO Object");
			return false;
		}
	}
	return true;
}

static int _find_no_case(const string& str, LPCSTR lpcszToFind)
{
	string str1 = str;
	str1.MakeLower();
	string str2 = lpcszToFind;
	str2.MakeLower();
	return str1.Find(str2);
}

/// AW 09/22/07 QA80-9265 MORE_ON_PREVIW
// in SQL Server, the syntax of TOP N is "SELECT TOP N ..."
// in ORACLE SQL, it is "Select .... WHERE ROWNUM <= N"
// in MYSQL, it is "SELECT .... LIMIT N" 
//----- CPY 1/22/2008 QA70-10972 MYSQL_ODBC_PROVIDER_PREVIEW_NOT_SHOW
/*
static bool _makePreviewStr(string& strSQL, int nLines, string strConn)
{
	if ( _find_no_case(strConn, "Oracle" ) >0 )
	{
		string strTemp;
		strTemp.Format(" WHERE ROWNUM <= %d", nLines);
		strSQL += strTemp;
		return TRUE;
	}
	if ( _find_no_case(strConn, "MySQL" ) > 0 )
	{
		string strTemp;
		strTemp.Format(" LIMIT %d", nLines);
		strSQL += strTemp;
		return TRUE;	
	}

	StringArray strTokens;
	strSQL.GetTokens( strTokens);
	if ( strTokens[0].CompareNoCase("SELECT") == 0 )
	{
		string strTemp;
		strTemp.Format(" Top %d", nLines);
		if ( strTokens[1].CompareNoCase("ALL") == 0 || strTokens[1].CompareNoCase("DISTINCT") == 0)
		{
			strTokens[1]+= strTemp;
		}
		else
		{
			strTokens[0]+= strTemp;
		}
		strSQL.SetTokens(strTokens, ' ');
		return TRUE;
		
	}
/// END MORE_ON_PREVIW
	return false;
}*/
enum {
	DB_PROVIDER_UNKNOWN,
	DB_PROVIDER_ORACLE,
	DB_PROVIDER_SQL_SERVER,
	DB_PROVIDER_MYSQL,
	DB_PROVIDER_TOTAL
};
int db_quess_provider(LPCSTR lpcszConn)
{
	string strConn = lpcszConn;
	if ( _find_no_case(strConn, "Oracle" ) >0 )
		return DB_PROVIDER_ORACLE;
	if ( _find_no_case(strConn, "MySQL" ) > 0 )
		return DB_PROVIDER_MYSQL;
	
	if( _find_no_case(strConn, "sqloledb") > 0 || _find_no_case(strConn, "SQL Server") > 0)
		return DB_PROVIDER_SQL_SERVER;
	
	return DB_PROVIDER_UNKNOWN;
}
	// SQL Server, syntax of TOP N is "SELECT TOP N ..."
	// in ORACLE SQL, it is "Select .... WHERE ROWNUM <= N"
	// in MYSQL, it is "SELECT .... LIMIT N" 
static bool _makePreviewStr(string& strSQL, int nLines, int nConnType)
{
	if (DB_PROVIDER_ORACLE == nConnType)
	{
		string strTemp;
		strTemp.Format(" WHERE ROWNUM <= %d", nLines);
		/// AW SOPHY 05/14/2009 QA80-13574-P1 FIX_ORACLE_PREVIEW_ISSUE
		// WHEN strSQL include order by, it does not work well 
		//strSQL += strTemp;
		strSQL = "SELECT * FROM (" + strSQL + ") " + strTemp;
		/// END FIX_ORACLE_PREVIEW_ISSUE
		
		return TRUE;
	}
	if (DB_PROVIDER_MYSQL == nConnType)
	{
		string strTemp;
		strTemp.Format(" LIMIT %d", nLines);
		strSQL += strTemp;
		return TRUE;	
	}
	if(DB_PROVIDER_SQL_SERVER == nConnType)
	{
		StringArray strTokens;
		strSQL.GetTokens( strTokens);
		if ( strTokens[0].CompareNoCase("SELECT") == 0 )
		{
			string strTemp;
			strTemp.Format(" Top %d", nLines);
			if ( strTokens[1].CompareNoCase("ALL") == 0 || strTokens[1].CompareNoCase("DISTINCT") == 0)
			{
				strTokens[1]+= strTemp;
			}
			else
			{
				strTokens[0]+= strTemp;
			}
			strSQL.SetTokens(strTokens, ' ');
			return TRUE;
		}
	}
	return false;
}
///Sophy 5/13/2009 QA80-12574 FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
//static bool _open_recordset(Object& ors, Object& oConn, const string& strQuery, bool bHideErr)
enum CURSORTYPE {
	adOpenUnspecified = -1,
	adOpenForwardOnly = 0,
	adOpenKeyset = 1,
	adOpenDynamic = 2,
	adOpenStatic = 3
};
static bool _open_recordset(Object& ors, Object& oConn, const string& strQuery, bool bHideErr, int nCursorType = adOpenForwardOnly)
///end FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
{
	SetStatusBarText("DB:openning recordset...");
	try
	{
		///Sophy 5/13/2009 QA80-12574 FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
		//ors.Open(strQuery, oConn);//, 0, 1);
		ors.Open(strQuery, oConn, nCursorType);
		///end FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
	}
	catch(int nError)
	{
		if(!bHideErr)
			printf("ADODB.Recordset open failed, err = %X\n", nError);
	
		return false;
	}
	return true;
}
//--------- end MYSQL_ODBC_PROVIDER_PREVIEW_NOT_SHOW


/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
//int OWKSDB::Import(int nColBegin, int nPreviewLines, bool bAllowSavePassword)
int OWKSDB::Import(int nColBegin, int nPreviewLines, bool bAllowSavePassword, bool bNeedPassWord, HWND hDlg) // =0.0, flase, false.
/// END HANDLE_PASSWORD_IN_CONNECTION
{
	waitCursor www;
	/// ML 11/9/2009 QA70-14617 IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
	double	dCursorType;
	LT_get_var("@ADOC", &dCursorType);
	int		nCursorType = dCursorType;
	/// end IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR	
	
#ifndef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__	///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
	Object	oConn;
	oConn = CreateObject("ADODB.Connection");
	if(!oConn)
		return ADO_ERR_CREATE_OBJ;
	
	Object		ors;
	ors = CreateObject("ADODB.Recordset");
	if(!ors)
		return ADO_ERR_CREATE_OBJ;
	
	string strConn, strQuery, strDSfile;
	if ( !db_wks_get_query(m_wks, strConn, strQuery, strDSfile ) )
		return ADO_ERR_GET_SQL_STR;
	string strUser, strDS;
	bool bSavePwdBackToWks = false;
	string strConn0 = strConn;
	
	/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
	/*if(!db_is_connection_str_has_password(strConn, &strUser, &strDS))
	
	{
		GETN_TREE(tr);
		GETN_STR(user,"User Name", strUser)
		GETN_PASSWORD(pwd, "Password", "")
		if(bAllowSavePassword)
		{
			GETN_CHECK(save,"Save Password", 0)
		}
		string strInfo = _L("Data Source: ") + strDS;
		if( GetNBox(tr,"Database Login", strInfo) )
		{
			string strNewConnStr;
			db_add_password_to_connection_str(strConn, tr.user.strVal, tr.pwd.strVal, strNewConnStr);
			if(bAllowSavePassword)
				bSavePwdBackToWks = tr.save.nVal;
			strConn = strNewConnStr;
		}
	}
	*/
	if ( bNeedPassWord )
	/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
	//	bSavePwdBackToWks = DB_GetPassword(hDlg, strDS, strUser, strConn, bAllowSavePassword );
	{
		String strTitle = _L("Importing...");
		if ( nPreviewLines > 0 )
			strTitle = _L("Preview...");	
		bSavePwdBackToWks = DB_GetPassword(hDlg, strDS, strUser, strConn, strTitle, bAllowSavePassword );
		if ( !bSavePwdBackToWks )
		{
			MessageBox(NULL, _L("Need provide password!!!"), "error");
			return ADO_ERROR_OPEN_CONN;
		}
	}
	/// END HANDLE_PASSWORD_IN_CONNECTION
	
	
	/// END HANDLE_PASSWORD_IN_CONNECTION
	try
	{
		SetStatusBarText("DB:openning connection...");
		oConn.Open(strConn);
	}
	catch(int nError)
	{
		printf("ADODB.Connection, err = %X\n", nError);
		return ADO_ERROR_OPEN_CONN;
	}
	string strQuery0 = strQuery;// might be modified by preview
	/// AW 09/22/07 QA80-9265 MORE_ON_PREVIW
	int nProviderType = -1;
	bool bNeedToLoop = false;
	bool bOpenSuccess = false;//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
	for(int ii = DB_PROVIDER_ORACLE; ii < DB_PROVIDER_TOTAL; ii++)
	{
		if(nPreviewLines > 0)
		{
			if(nProviderType < 0)
			{
				nProviderType = db_quess_provider(strConn);
				if(DB_PROVIDER_UNKNOWN == nProviderType)
					bNeedToLoop = true;
			}
			_makePreviewStr(strQuery, nPreviewLines, bNeedToLoop? ii:nProviderType);
		}
		else
			bNeedToLoop = false;
		///Sophy 5/13/2009 QA80-12574 FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
		//if(_open_recordset(ors, oConn, strQuery, bNeedToLoop))
			//bNeedToLoop = false;
		//This is temporary solution, for unknown reason(C# also has this problem), in OC if open as forward-only using Oracle Provider for OLE DB driver \
		//and try to query more than 100 records, we can only traverse all records in the recordset only once, maybe in VC level using MoveFirst as to loop again to fetch data of next field makes no sense.
		//When use Oracle Provider for OLE DB driver, should not open with adOpenForwardOnly flag.
		//Keep default value as adOpenForwardOnly as to improve performance(This is not true for "MS OLE DB Provider for Oracle", as can be confirm in C# code), change CacheSize can slightly improve performance too.
		/// ML 11/9/2009 QA70-14617 IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
		//int nCursorType = adOpenForwardOnly; //default value
		/// end IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
		if ( strConn.Match("Provider=OraOLEDB.Oracle*", true) )
			nCursorType = adOpenKeyset;
		//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
		//if(_open_recordset(ors, oConn, strQuery, bNeedToLoop, nCursorType))
		bOpenSuccess = _open_recordset(ors, oConn, strQuery, bNeedToLoop, nCursorType);
		if(bOpenSuccess)
		//---
			bNeedToLoop = false;
		///end FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
		if(!bNeedToLoop)
			break;
		// try next
		strQuery = strQuery0;
	}
	//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
	//if(bNeedToLoop) // failed after trying all above
	if(bNeedToLoop || !bOpenSuccess)
	//---
	{
		out_str("failed to open Recordset after trying all special Select for preview");
		return ADO_ERROR_OPEN_RECORDSET;
	}

	
	SetStatusBarText("DB:starting to import recordset...");
	/// ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET
	//BOOL bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, 
	//	LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_CLEAR_WKS);
//	BOOL bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX);
	SetStatusBarText("");
	//////
	int			nErr = 0;
	BOOL		bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, 
					LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_CLEAR_WKS, &nErr);
	/// end ADO_EXCEPTION_ON_EMPTY_RECORDSET
	if (bRet)
	{
		try 
		{
	    	ors.Close();
		}
		catch(int nError)
		{
			printf("Failed to close ADO Recordset, err = %X\n", nError);
			return ADO_ERROR_CLOSE_RECORDSET;
		}
		try 
		{
	    	oConn.Close();
		}
		catch(int nError)
		{
			printf("Failed to close ADO Connection, err = %X\n", nError);
			return ADO_ERROR_CLOSE_CONN;
		}
		bool bImport = nPreviewLines == 0? true:false;
		if( bSavePwdBackToWks || bImport )
		{
			if(!bSavePwdBackToWks)
				strConn = strConn0;	
			db_wks_set_query(m_wks, strConn, strQuery0, strDSfile, bImport);
		}
		return bSavePwdBackToWks?ADO_ERROR_CONN_STR_CHANGED : ADO_ERROR_NONE;
	}
	/// ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET
	else
	{
		if (PUTRSET_ERR_EMPTY_RECORDSET == nErr)
		{
			out_str("Empty Recordset.");
			return ADO_ERROR_EMPTY_RECORDSET;
		}
	}
	/// end ADO_EXCEPTION_ON_EMPTY_RECORDSET
	///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#else	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__ defined
	string	strConn, strQuery, strDSfile;
	BOOL	bUseODBC = FALSE;
	if ( !db_wks_get_query(m_wks, strConn, strQuery, strDSfile, &bUseODBC) )
		return ADO_ERR_GET_SQL_STR;
	
	bool	bSavePwdBackToWks = false;
	string	strConn0 = strConn;
	string	strQuery0 = strQuery;
	string	strUser, strDS;
	
	if ( bNeedPassWord )
	{
		String strTitle = _L("Importing...");
		if ( nPreviewLines > 0 )
			strTitle = _L("Preview...");	
		bSavePwdBackToWks = DB_GetPassword(hDlg, strDS, strUser, strConn, strTitle, bAllowSavePassword );
		if ( !bSavePwdBackToWks )
		{
			MessageBox(NULL, _L("Need provide password!!!"), "error");
			return ADO_ERROR_OPEN_CONN;
		}
	}
	
	if ( !bUseODBC ) // use ADO, keep old logic
	{
#ifdef	_DEBUG
		int nStart = GetTickCount();
#endif	//_DEBUG
		Object	oConn;
		oConn = CreateObject("ADODB.Connection");
		if( !oConn )
			return ADO_ERR_CREATE_OBJ;
		
		Object	ors;
		ors = CreateObject("ADODB.Recordset");
		if( !ors )
			return ADO_ERR_CREATE_OBJ;
		
		try
		{
			SetStatusBarText("DB:openning connection...");
			oConn.Open(strConn);
		}
		catch(int nError)
		{
			printf("ADODB.Connection, err = %X\n", nError);
			return ADO_ERROR_OPEN_CONN;
		}
		
		int nProviderType = -1;
		bool bNeedToLoop = false;
		bool bOpenSuccess = false;//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
		for(int ii = DB_PROVIDER_ORACLE; ii < DB_PROVIDER_TOTAL; ii++)
		{
			if(nPreviewLines > 0)
			{
				if(nProviderType < 0)
				{
					nProviderType = db_quess_provider(strConn);
					if(DB_PROVIDER_UNKNOWN == nProviderType)
						bNeedToLoop = true;
				}
				_makePreviewStr(strQuery, nPreviewLines, bNeedToLoop? ii:nProviderType);
			}
			else
				bNeedToLoop = false;
			///Sophy 5/13/2009 QA80-12574 FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
			//if(_open_recordset(ors, oConn, strQuery, bNeedToLoop))
				//bNeedToLoop = false;
			//This is temporary solution, for unknown reason(C# also has this problem), in OC if open as forward-only using Oracle Provider for OLE DB driver \
			//and try to query more than 100 records, we can only traverse all records in the recordset only once, maybe in VC level using MoveFirst as to loop again to fetch data of next field makes no sense.
			//When use Oracle Provider for OLE DB driver, should not open with adOpenForwardOnly flag.
			//Keep default value as adOpenForwardOnly as to improve performance(This is not true for "MS OLE DB Provider for Oracle", as can be confirm in C# code), change CacheSize can slightly improve performance too.
			/// ML 11/9/2009 QA70-14617 IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
			//int nCursorType = adOpenForwardOnly; //default value
			/// end IMPORT_ADO_FAILS_ON_SOME_NONSORTED_QUERIES_DUE_TO_BAD_ADO_CURSOR
			if ( strConn.Match("Provider=OraOLEDB.Oracle*", true) )
				nCursorType = adOpenKeyset;
			//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
			//if(_open_recordset(ors, oConn, strQuery, bNeedToLoop, nCursorType))
			bOpenSuccess = _open_recordset(ors, oConn, strQuery, bNeedToLoop, nCursorType);
			if(bOpenSuccess)
			//---
				bNeedToLoop = false;
			///end FIX_FAIL_TO_IMPORT_ALL_FIELDS_USING_ORACLE_PROVIDER_FOR_OLE_DB_DRIVER
			if(!bNeedToLoop)
				break;
			// try next
			strQuery = strQuery0;
		}
		//---CPY 2/17/10 ADODB_RECORDSET_ERR_IN_SCRIPT_WIN
		//if(bNeedToLoop) // failed after trying all above
		if(bNeedToLoop || !bOpenSuccess)
		//---
		{
			if(bNeedToLoop)
			out_str("failed to open Recordset after trying all special Select for preview");
			return ADO_ERROR_OPEN_RECORDSET;
		}
	//----- end MYSQL_ODBC_PROVIDER_PREVIEW_NOT_SHOW;

		
		SetStatusBarText("DB:starting to import recordset...");
		/// ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET
		//BOOL bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, 
		//	LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_CLEAR_WKS);
		//	BOOL bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX);
		//////
		int			nErr = 0;
		BOOL		bRet = m_wks.PutRecordset(ors, 0, -1, nColBegin, 
								LAYWKGETRECORDSET_SET_COLUMN_LABEL | LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_CLEAR_WKS, &nErr);
								
		SetStatusBarText("");
		/// end ADO_EXCEPTION_ON_EMPTY_RECORDSET
		if (bRet)
		{
			try 
			{
		    	ors.Close();
			}
			catch(int nError)
			{
				printf("Failed to close ADO Recordset, err = %X\n", nError);
				return ADO_ERROR_CLOSE_RECORDSET;
			}
			try 
			{
		    	oConn.Close();
			}
			catch(int nError)
			{
				printf("Failed to close ADO Connection, err = %X\n", nError);
				return ADO_ERROR_CLOSE_CONN;
			}
			bool bImport = nPreviewLines == 0? true:false;
			if( bSavePwdBackToWks || bImport )
			{
				if(!bSavePwdBackToWks)
					strConn = strConn0;	
				db_wks_set_query(m_wks, strConn, strQuery0, strDSfile, bImport, &bUseODBC);
			}
#ifdef	_DEBUG
			int nEnd = GetTickCount();
			printf("Open recordset and read data cause %ld(ms)\n", nEnd - nStart);
#endif	//_DEBUG
			return bSavePwdBackToWks?ADO_ERROR_CONN_STR_CHANGED : ADO_ERROR_NONE;
		}
		/// ML 1/25/2008 QA70-10972-P4 ADO_EXCEPTION_ON_EMPTY_RECORDSET
		else
		{
			if (PUTRSET_ERR_EMPTY_RECORDSET == nErr)
			{
				out_str("Empty Recordset.");
				return ADO_ERROR_EMPTY_RECORDSET;
			}
		}
		/// end ADO_EXCEPTION_ON_EMPTY_RECORDSET
	}
	else
	{
#ifdef	_DEBUG
		int nStart = GetTickCount();
#endif	//_DEBUG
		DWORD	dwOODBC;
		string	strODBCconn;
		if ( _get_odbc_conn_str_from_ado_conn_str(strConn, strODBCconn) )
		{
			dwOODBC = oodbc_connect_ex(strODBCconn, hDlg);
			if ( dwOODBC == 0 )
				return ADO_ERROR_CONN_ODBC_FAIL; //fail to connect to ODBC
		}
		else
			return ADO_ERROR_INVALID_ODBC_DSN; //fail to get ODBC connect string.
	
		int nProviderType = -1;
		bool bNeedToLoop = false;
		for(int ii = DB_PROVIDER_ORACLE; ii < DB_PROVIDER_TOTAL; ii++)
		{
			if(nPreviewLines > 0)
			{
				if(nProviderType < 0)
				{
					nProviderType = db_quess_provider(strConn);
					if(DB_PROVIDER_UNKNOWN == nProviderType)
						bNeedToLoop = true;
				}
				_makePreviewStr(strQuery, nPreviewLines, bNeedToLoop? ii:nProviderType);
			}
			else
				bNeedToLoop = false;

			if ( oodbc_exec_select(dwOODBC, strQuery) )
				bNeedToLoop = false;
			
			if(!bNeedToLoop)
				break;
			// try next
			strQuery = strQuery0;
		}
		if(bNeedToLoop) // failed after trying all above
		{
			out_str("failed to open Recordset after trying all special Select for preview");
			oodbc_disconnect(dwOODBC);
			return ADO_ERROR_OPEN_RECORDSET;
		}
		SetStatusBarText("DB:starting to import data...");
		if ( _db_odbc_wks_import(dwOODBC, m_wks, nColBegin) )
		{
			bool bImport = nPreviewLines == 0 ? true : false;
			if ( bSavePwdBackToWks || bImport )
			{
				if ( !bSavePwdBackToWks )
					strConn = strConn0;
				db_wks_set_query(m_wks, strConn, strQuery0, strDSfile, bImport, &bUseODBC);
			}
		}
		SetStatusBarText("");
		oodbc_disconnect(dwOODBC);
#ifdef	_DEBUG
		int nEnd = GetTickCount();
		printf("Connect ODBC and execute SQL statement and read data cause %ld(ms)\n", nEnd - nStart);
#endif	//_DEBUG
		return bSavePwdBackToWks ? ADO_ERROR_CONN_STR_CHANGED : ADO_ERROR_NONE;
	}
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
	///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
	out_str("PutRecordset failed");
	return FAILED_TO_PUT_RECORD_TO_WKS;		
}

///Sophy 7/31/2009 QA80-14057 SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
#ifdef	__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
//const from sqlext.h
#define	SQL_C_CHAR		1
#define	SQL_C_LONG		4
#define	SQL_C_DOUBLE	8
#define	SQL_C_DATE		9
#define	SQL_C_TIMESTAMP	11
#define	SQL_C_GUID		(-11)
#define	SQL_C_BINARY	(-2)

static	bool	_get_odbc_conn_str_from_ado_conn_str(string& strADOconn, string& strODBCconn)
{
	LPCSTR	lpcszProperties  = "Extended Properties=";
	int nPos = strADOconn.Find(lpcszProperties);
	if ( nPos >= 0 )
	{
		string strRight = strADOconn.Right(strADOconn.GetLength() - (nPos + strlen(lpcszProperties)));
		///Sophy 8/31/2009 QA80-14057-P1 FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
		//strRight.TrimLeft('\"');
		//strRight.TrimRight("\"");
		strRight.Remove('\"');
		///end FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
		vector<string> vsProperties;
		
		if ( strRight.GetTokens(vsProperties, ';') > 0 )
		{
			vector<string>	vsConn(0);
			nPos = vsProperties.Find("DSN=", 0, false, false);
			if ( nPos >= 0 )
				vsConn.Add(vsProperties[nPos]);
			///Sophy 8/31/2009 QA80-14057-P1 FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			else
				return false; //not allow DSN as empty
			///end FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			
			nPos = vsProperties.Find("UID=", 0, false, false);
			if ( nPos >= 0 )
				vsConn.Add(vsProperties[nPos]);
			///Sophy 8/31/2009 QA80-14057-P1 FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			else
				vsConn.Add("UID=");
			///end FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			
			nPos = vsProperties.Find("PWD=", 0, false, false);
			if ( nPos >= 0 )
				vsConn.Add(vsProperties[nPos]);
			///Sophy 8/31/2009 QA80-14057-P1 FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			else
				vsConn.Add("PWD=");
			///end FIX_RUNTIME_WHEN_CONN_STR_CONTAINS_UNMATCHED_QUOTATION_MARKS
			
			strODBCconn.SetTokens(vsConn, ';');
			return true;
		}
	}
		
	return false;
}

static	BOOL	_db_odbc_wks_import(DWORD& dwODBC, Worksheet& wks, int nColBegin)
{
	int nNumFields = oodbc_get_columns_count(dwODBC);
	if ( nNumFields < 0 )
		return FALSE;
	if ( wks.GetNumCols() <= nColBegin + nNumFields )
		wks.SetSize(-1, nColBegin + nNumFields);
	
	int nErr;
	for ( int iField = 0; iField < nNumFields; iField++ )
	{
		Column colObj(wks, iField + nColBegin);
		
		int nSQLCType = oodbc_get_column_datatype(dwODBC, iField, &nErr);
		if ( nErr < 0 )
			return FALSE;
		
		switch(nSQLCType)
		{
		case SQL_C_CHAR:
		case SQL_C_GUID:
		case SQL_C_BINARY:
			///Sophy 3/4/2010 FAIL_TO_SHOW_FULL_CONTEXT_WHEN_TEXT_IS_LONG should keep Text&Numeric to allow show more characters.
			//colObj.SetInternalData(FSI_TEXT);
			///end FAIL_TO_SHOW_FULL_CONTEXT_WHEN_TEXT_IS_LONG
			break;
			
		case SQL_C_DATE:
		case SQL_C_TIMESTAMP:
			colObj.SetInternalData(FSI_DOUBLE);
			colObj.SetFormat(OKCOLTYPE_DATE);
			colObj.SetSubFormat(0); //yyyy-mm-dd
			break;
			
		case SQL_C_LONG:
			colObj.SetInternalData(FSI_LONG);
			break;
			
		case SQL_C_DOUBLE:
			colObj.SetInternalData(FSI_DOUBLE);
			break;
			
		default:
			ASSERT(FALSE);
			return FALSE;
		}
		string strFieldName = oodbc_get_column_name(dwODBC, iField, &nErr);
		colObj.SetLongName(strFieldName);
		
		string strRangeStr;
		colObj.GetRangeString(strRangeStr);
		nErr = oodbc_get_column_data(dwODBC, strRangeStr, iField);
		if ( nErr != 0 )
			return FALSE;
	}
	
	return TRUE;
}
#endif	//__SUPPORT_IMPORT_DATA_BY_ODBC_IN_QUERY_BUILDER__
///end SUPPORT_IMPORTING_INTO_WKS_USING_ODBC_IN_QUERY_BUILDER
//---- CPY 11/4/2007 QUERY_IMPORT_ASK_PASSWORD_NEED_LOCALIZED_AND_PARENT_HWND
/*
/// AW 05/05/2006 HANDLE_PASSWORD_IN_CONNECTION
bool DB_GetPassword(string& strDS, string& strUser, string& strConn, bool bAllowSavePassword) //= false
{
	bool bSavePwdBackToWks = true;
	if(!db_is_connection_str_has_password(strConn, &strUser, &strDS))
	{
		GETN_TREE(tr);
		GETN_STR(user,"User Name", strUser)
		GETN_PASSWORD(pwd, "Password", "")
		if(bAllowSavePassword)
		{
			GETN_CHECK(save,"Save Password", 0)
		}
		/// Nicole 07/05/2007 v8.0655 REMOVE_SPACE_FOR_LOCATION_STRING
		//string strInfo = _L("Data Source: ") + strDS;
		string strInfo = _L("Data Source:") + " " + strDS;
		/// END REMOVE_SPACE_FOR_LOCATION_STRING
		if( GetNBox(tr,"Database Login", strInfo) )
		{
			string strNewConnStr;
			db_add_password_to_connection_str(strConn, tr.user.strVal, tr.pwd.strVal, strNewConnStr);
			if(bAllowSavePassword)
				bSavePwdBackToWks = tr.save.nVal;
			strConn = strNewConnStr;
		}
		
	}
	return bSavePwdBackToWks;
}
/// END HANDLE_PASSWORD_IN_CONNECTION
*/
/// 02/04/08 AW MORE_ON_LOGIN_PROMPT
//bool DB_GetPassword(HWND hWndParent, string& strDS, string& strUser, string& strConn, bool bAllowSavePassword) //= false
bool DB_GetPassword(HWND hWndParent, string& strDS, string& strUser, string& strConn, LPCSTR lpcszDlgTitle, bool bAllowSavePassword) //= false
/// END MORE_ON_LOGIN_PROMPT
{
	bool bSavePwdBackToWks = true;
	if(!db_is_connection_str_has_password(strConn, &strUser, &strDS))
	{
		GETN_TREE(tr);
		GETN_STR(user,_L("User Name"), strUser)
		GETN_PASSWORD(pwd, _L("Password"), "")
		if(bAllowSavePassword)
		{
			GETN_CHECK(save,_L("Save Password"), 0)
		}
		/// Nicole 07/05/2007 v8.0655 REMOVE_SPACE_FOR_LOCATION_STRING
		//string strInfo = _L("Data Source: ") + strDS;
		string strInfo = _L("Data Source:") + " " + strDS;
		/// END REMOVE_SPACE_FOR_LOCATION_STRING
		/// 02/04/08 AW MORE_ON_LOGIN_PROMPT
		//if( GetNBox(tr, _L("Database Login"), strInfo, NULL, NULL, hWndParent) )
		if( GetNBox(tr, lpcszDlgTitle, strInfo, NULL, NULL, hWndParent) )
		/// END MORE_ON_LOGIN_PROMPT
		{
			string strNewConnStr;
			db_add_password_to_connection_str(strConn, tr.user.strVal, tr.pwd.strVal, strNewConnStr);
			if(bAllowSavePassword)
				bSavePwdBackToWks = tr.save.nVal;
			strConn = strNewConnStr;
		}
		/// 02/04/08 AW MORE_ON_LOGIN_PROMPT
		else
			return false;
		/// END MORE_ON_LOGIN_PROMPT
		
	}
	return bSavePwdBackToWks;
}
//------ end QUERY_IMPORT_ASK_PASSWORD_NEED_LOCALIZED_AND_PARENT_HWND


/*
int OWKSDB::Disconnect()
{
	if(!close(m_rs))
		return ADO_ERROR_CLOSE_RECORDSET;
	if(!close(m_Conn))
		return ADO_ERROR_CLOSE_CONN;
	
	return ADO_ERROR_NONE;
}
int OWKSDB::Connect(const string& strConn, const string& strSQL)
{
	if(!m_wks)
		return -1;
	Disconnect();
	if(!_checkCreate(m_Conn)) return -2;
	if(!_checkCreate(m_rs)) return -2;
	try
	{
		m_Conn.Open(strConn);
	}
	catch(int nError)
	{
		out_str("Failed to open  ADODB.Connection");
		return ADO_ERROR_OPEN_CONN;
	}
//	int nErr;
//	if((nErr = openRecordSet(ors, strConn, strSQL)) != ADO_ERROR_NONE)
//		return nErr;
	
//	m_wks.SetADO(oConn, ors);
	return ADO_ERROR_NONE;
}

*/

