C / C++ / MFC

"The pointy end goes in the other man." - Antonio Banderas (Zorro, 1998)

"The pointy end goes in the other man." - Antonio Banderas (Zorro, 1998)

int Export(BSTR szServer, BSTR szDB, BSTR szUser, BSTR szPassword, BSTR szSQL, int nExportType)
{
	/*notes--
			Author: Diamond ringold (2004-03-22)
			Purpose: accepts a query string along with server name, dbname, user, password
					and gets a result set from a MySQL database.  Then allows user to select name and
					directory of a new dbase table to export queried info to
	*/
	SQLRETURN			sr;
	int						numOfRows=0;							//Counter
	int						numOfFields=0;						//Counter
	int						connected=0;							//status flag
	MYSQL					*dbHandle;								//Connection handle
	MYSQL_RES			*res;											//Result struct
	MYSQL_FIELD		*field;										//Field struct
	MYSQL_ROW			row;											//row struct
	char					errmsg[512];
	
	hDbConn =0;
	hOdbcEnv = 0;
	// Create the mysql object 
	dbHandle=mysql_init((MYSQL*)0);

	CString szDBFName;
	CString strPath,strTblName;

	try
	{
		CString sFileFilter;
		if(nExportType)
			sFileFilter = "Excel Spreadsheet (*.xls)|*.xls||";
		else
			sFileFilter = ".dbf files (*.dbf)|*.dbf||";
		//open file save dialog
		CFileDialog dlg(FALSE,".DBF",NULL,OFN_ENABLESIZING | OFN_HIDEREADONLY,sFileFilter);
		if(dlg.DoModal() == IDOK)
		{
			szDBFName = dlg.GetPathName();
			strTblName = dlg.GetFileName();
			//if no name in filename box
			if((szDBFName.GetLength() || strTblName.GetLength() ) == 0)
			{
				AfxMessageBox("Export Canceled",MB_OK | MB_ICONINFORMATION);
				return 0;
			}
		}
		else //if user pressed cancel
		{
			AfxMessageBox("Export Canceled",MB_OK | MB_ICONINFORMATION);
			return 0;
		}
		
		int nPos = szDBFName.Find(strTblName,0);
		strPath = szDBFName.Left(nPos);

		//connect to MySQL db
		if(mysql_real_connect(dbHandle,			// Handle to mysqlobject 
									(char*)szServer,			// Host - default=localhost
									(char*)szUser,				// Username
									(char*)szPassword,		// Password
									(char*)szDB,					// Databasename
									MYSQL_PORT,						// Port of connection
									NULL,									// Used by Unixsocket
									0))										// client_flag
		{

			connected=1;
		}
		else //failed to connect
		{
			sprintf(errmsg, "\nConnection Error: %s\n", mysql_error(dbHandle));
			AfxMessageBox(errmsg);
			connected = 0;
		}

		//select the default db
		if(mysql_select_db(dbHandle,(char*)szDB))
		{
			sprintf(errmsg, "\nSelect DB Error: %s\n", mysql_error(dbHandle));
			AfxMessageBox(errmsg);
			return 0;
		}

		//execute query
		if(mysql_query(dbHandle,(char*)szSQL))
		{
			sprintf(errmsg, "\nError: %s\n", mysql_error(dbHandle));
		AfxMessageBox(errmsg);
		return 0;
		
		}

		//store the query results
		res=mysql_store_result(dbHandle);
		
		CString			strConnect;

		//connect to dbase directory chosen by user
		
		if(nExportType)
			strConnect.Format("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DefaultDir=%s;",strPath); 
		else //driverid 533 = dbase 5.0; 277 = dbase IV
			strConnect.Format("Driver={Microsoft dBase Driver (*.dbf)};DriverID=533;Dbq=%s;",strPath);
			
		
		if(hDbConn != SQL_NULL_HANDLE)
			SQLFreeHandle(SQL_HANDLE_DBC, hDbConn); //free connection
		
		if(hOdbcEnv != SQL_NULL_HANDLE)
			SQLFreeHandle(SQL_HANDLE_ENV,hOdbcEnv); 
				//return code for odbc calls
		
		//allocate environment
		sr = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hOdbcEnv);
		SR_CHECK
		//set the app's odbc version
		sr = SQLSetEnvAttr(hOdbcEnv, SQL_ATTR_ODBC_VERSION	,(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
		SR_CHECK

		//allocate connection
		sr = SQLAllocHandle(SQL_HANDLE_DBC, hOdbcEnv, & hDbConn);
		SR_CHECK

		//set connect timeout
		sr = SQLSetConnectAttr(hDbConn,SQL_ATTR_LOGIN_TIMEOUT,(void*)5,0);
		SR_CHECK
		//connect to data source
		char szConnectString[512];
		sprintf(szConnectString,"%s",strConnect);
		
		char szDescription[1024];
		SQLSMALLINT				length;
		sr = SQLDriverConnect(hDbConn,NULL , (UCHAR*)szConnectString, 512,
			(UCHAR*)szDescription,1023,&length,SQL_DRIVER_COMPLETE_REQUIRED);
		
		SR_CHECK
				
		CString sColumns;
		numOfRows = (int) mysql_num_rows(res);
		numOfFields= (int) mysql_num_fields(res);
		
		if(numOfRows > 255)
		{
			AfxMessageBox("Too many fields for export.");
			return 0;
		}
		//dynamic arrays based on number of fields to hold field types and lengths
		char *			bNeedTics = new char[numOfFields] ;
		int *				nFieldLen = new int[numOfFields];
		CString			sColumnsToInsert;	
		sColumnNames = new CString[numOfFields];

		for(int i = 0; i < numOfFields; i++)
		{
			bNeedTics[i] = 'N';
		}
		CString* sStatements = new CString[numOfRows];

		//this FOR loop build string for create table statement	
		
		for (i =0;i < numOfFields;i++)
		{
			field=mysql_fetch_field(res);
			CString		sTemp = field->name;
			sColumnsToInsert += sTemp + ",";
			int				nType = field->type;
			int nLen = (int)field->length;
			CString					sCol;
			nFieldLen[i] = nLen;
			sTemp = CheckFieldname(sTemp,i);
			sColumnNames[i] = sTemp;

			switch(nType)
			{			
				case FIELD_TYPE_ENUM:
				case FIELD_TYPE_TINY:
				case FIELD_TYPE_SHORT:
					sCol.Format("%s %s,",sTemp,"INTEGER ");
					break;
				case FIELD_TYPE_LONG:
					sCol.Format("%s %s,",sTemp,"LONG ");
					break;
				case FIELD_TYPE_FLOAT:
				case FIELD_TYPE_DECIMAL:
					sCol.Format("%s %s,",sTemp,"FLOAT ");
					break;
				case FIELD_TYPE_DOUBLE:
					sCol.Format("%s %s,",sTemp,"DOUBLE ");
					break;
				case FIELD_TYPE_NULL:
					break;
				case FIELD_TYPE_LONGLONG:
				case FIELD_TYPE_INT24:
					sCol.Format("%s %s,",sTemp,"LONG ");
					break;
				case FIELD_TYPE_YEAR:
				case FIELD_TYPE_DATE:
				case FIELD_TYPE_NEWDATE:
					bNeedTics[i] = 'D';
					sCol.Format("%s %s,",sTemp,"DATE ");
					break;
				case FIELD_TYPE_TIME:
				case FIELD_TYPE_DATETIME:
				case FIELD_TYPE_TIMESTAMP:
					bNeedTics[i] = 'D';
					sCol.Format("%s %s,",sTemp,"DATETIME ");
					break;
				case FIELD_TYPE_SET:
					sCol.Format("%s %s,",sTemp,"int ");
					break;
				case FIELD_TYPE_TINY_BLOB:
				case FIELD_TYPE_MEDIUM_BLOB:
				case FIELD_TYPE_LONG_BLOB:
				case FIELD_TYPE_BLOB:
					bNeedTics[i] = 'T';
					sCol.Format("%s %s,",sTemp,"MEMO ");
					break;
				case FIELD_TYPE_VAR_STRING:
				case FIELD_TYPE_STRING:
					bNeedTics[i] = 'T';
					sCol.Format("%s TEXT(%d),",sTemp,nLen);
					break;
					
			}
			
			sColumns += sCol;
		
		}
		sColumnsToInsert = sColumnsToInsert.Left(sColumnsToInsert.GetLength() - 1);
		sColumns = sColumns.Left(sColumns.GetLength() - 1);
		sColumns = "CREATE TABLE " + strTblName + " (" + sColumns + ")";

		char tmp1[10000];
		sprintf(tmp1,"%s",sColumns);
		SQLCHAR*				SQL1 = (SQLCHAR*)tmp1;
		if(!nExportType) 
			executeSQL(SQL1);  //only do this if .dbf
		SQLSMALLINT				cols = 0;


		CString			sTic;
		CString			sTemp;
		CString			sValues,sInsertString;
		SQLDisconnect(hDbConn);
		CDBFRecordset* m_Set = 0;
		CString str;


		if(!nExportType)
		{	
			m_Set =  new CDBFRecordset;
			m_Set->Open(szDBFName);
			
			for(i=0;i<numOfRows;i++)
			{
   			row=mysql_fetch_row( res );
				m_Set->AddNew();
				sValues = "";
				for (int col=0;col<numOfFields;col++)
				{	
					field=mysql_fetch_field(res);
					str = row[col];
					CString sYear,sMonth,sDay,sHour,sMinute,sSecond;
					COleDateTime * t;
					BOOL bDate = FALSE;

					if(bNeedTics[col] == 'D')
					{
						
							str.Remove('-');  //take out dashes in case it's an empty date
							int len = str.GetLength();
							sYear = str.Mid(0,4);
							sMonth = str.Mid(4,2);
							sDay = str.Mid(6,2);
							//if more than 8 characters then column also has time info
							if(len > 8)
							{
								sHour = str.Mid(8,2);
								sMinute = str.Mid(10,2);
								sSecond = str.Mid(12,2);
							}
							else
							{
								sHour = sMinute = sSecond = "0";
							}
							bDate = TRUE;
							 t = new COleDateTime(atoi(sYear),
								 atoi(sMonth),atoi(sDay),atoi(sHour),atoi(sMinute),atoi(sSecond));
						
					}
					COleVariant fVar;
					if(bDate)
						fVar = *t;
					else
						fVar = str;
					
					m_Set->SetFieldValue(col,fVar);
				}

			m_Set->Update();

			}   //end of rows loop
			m_Set->Close();
			delete m_Set;
		}

mysql_free_result( res ) ;	//free the result set
		dbHandle->free_me;