마우스 오른쪽 도스창 띄우는 명령 추가하기
&& 이후는 비쥬얼 도스 커맨드의 배치파일을 실행하게 한 것이다. (/k 와 && 에 대한 옵션 설명은 윈도우즈 도움말 참고. 바탕화면에서 F1 ) |
일반 cmd에서 dumpbin.exe 가 실행되도록 하기 위해서 만들었음.
Hard path라서 Visual Studio 버전이 다르면 수정해야 함.
아래는 윈도우즈7 64비트, Visual Studio 2008 환경에 맞게 수정한 파일입니다.
cd 에서 /d 옵션도 추가하였습니다. /d 옵션은 cd 명령시 드라이브도 함께 변경되도록 하여 다른 드라이브에서도 정상적으로 동작하도록 만들어 줍니다.
아이콘을 추가한 새로운 버전입니다. (참고:http://unremote.org/?p=839)
아래는 옛날 버전
Visual C++ Debugging 관련 MSDN 정리
삽입한 코드 디버깅 3
최적화된 코드 디버깅 3
디버그 빌드 구성에서 최적화를 설정하는 방법 3
DebugBreak 3
Assertion 3
CRT Assertion 4
printf 바꾸기 5
Heap 손상 확인 5
포인터 유효성 확인 5
Memory Block 확인 6
MFC Assertion 6
MFC ASSERT_VALID 및 CObject::AssertValid 6
논리 오류 찾기 9
결과 확인 9
오류 조건 테스트 9
Memory Leak Detection and Isolation 9
메모리 누수 탐지 기능 사용 10
_CrtSetDbgFlag 11
CRT 보고서 모드 설정 11
메모리 블록 형식 해석 11
메모리 할당 번호에 중단점 설정 11
조사식 창에서 메모리 할당 중단점을 설정 12
메모리 상태 비교 12
메모리 누수 및 잘못된 양수 13
ATL Debugging 14
COM+ 1.0 구성 요소 디버깅 14
QueryInterface 호출 디버깅 15
참조 횟수 추적 16
MFC Debugging 기술 16
AfxDebugBreak 16
TRACE 매크로 16
MFC의 메모리 누수 탐지 16
메모리 할당 추적 16
메모리 진단 사용 17
메모리 스냅샷 보기 18
메모리 통계 보기 19
개체 덤프 19
개체 덤프 해석 21
개체 덤프 사용자 지정 22
MFC 디버그 빌드 크기 줄이기 24
선택한 모듈의 디버그 정보로 FMC 응용 프로그램 빌드 24
CRT 디버깅 기술 26
CRT 디버그 라이브러리 사용 26
보고서 매크로 26
CRT 디버그 힙 27
메모리 관리 및 디버그 힙 27
디버그 힙의 블록 형식 29
디버그 힙 30
C++에서 디버그 힙 사용 32
힙 상태 보고 함수 33
힙 할당 요청 추적 34
디버그 후크 함수 작성 35
클라이언트 블록 후크 함수 35
할당 후크 함수 36
할당 후크 및 C 런타임 메모리 할당 37
보고서 후크 함수 38
Visual C++ 디버깅에 대한 질문과 대답 38
포인터가 메모리 주소를 손상시키는지 어떻게 알 수 있습니까? 38
포인터가 변경된 위치를 어떻게 찾을 수 있습니까? 39
함수를 수백 번 호출하는 경우 어떤 호출이 실패했습니까? 39
프로그램에서 단계별로 실행하는 경우 어떻게 포커스를 유지할 수 있습니까? 39
전경 프로그램을 디버깅 하는 동안 디버거 창을 어떻게 사용합니까? 39
NT 기호가 있는 Windows API 함수를 어떻게 디버깅 할 수 있습니까? 39
References 40
좌표공간과 변환
좌표공간그 그 공간간의 변환을 문서로 정리함. (영문)
키워드 정리 :
Coordinates Spaces and Transformation; World-space; Page-space; Device-space; Physical Device;
Translation; Scaling; Rotation; Shear; Reflection; SetWorldTransform; MM_ANISOTROPIC; M_ISOTROPIC;
MM_HIENGILSH; MM_LOENGLISH; MM_HIMETRIC; MM_LOMETRIC; MM_TWIPS; MM_TEST; Viewpost;
OffsetWindowOrgEx; OffsetViewportOrgEx; LPtoDP; DPtoLP; GM_ADVANCED; SetGraphicsMode;
SetWorldTransform;
크리티컬 섹션 초간단 클래스
class CCriticalSection { public: void Lock() { EnterCriticalSection(&m_sec); } void Unlock() { LeaveCriticalSection(&m_sec); } CCriticalSection() { InitializeCriticalSection(&m_sec); } ~CCriticalSection() { DeleteCriticalSection(&m_sec); } CRITICAL_SECTION m_sec; }; 생성자에서 바로 EnterCriticalSection 하는게 좀 걸리긴 하지만... 저 함수가 실패할리는 없겠지... 출처가 MSDN이라 그냥 써도 될듯함. From MSDN : |
정규 DLL Implicit, Explicit 링킹에 대한 몇가지 잡담
==============================================================================
1. DEF 파일을 사용하여 EXPORT
==============================================================================
// 헤더 파일
#ifdef __cplusplus
extern "C" { // 반드시 C 형태로 선언해야 한다.
#endif
void WINAPI func(); // WINAPI는 FAR PASCAL의 매크로로 CALLBACK과 같다.
#ifdef __cplusplus
}
#endif
// 소스 파일
void WINAPI func()
{
AFX_MANAGE_STATE(AfxGetStaticModuleState()); // DLL에서 MFC를 사용한다면..
TRACE("Hello dll\n");
}
// DEF 파일
EXPORTS
; 명시적 내보내기를 여기에 사용할 수 있습니다.
func @2
와 같이 한 후,
Dll을 사용하는 프로그램에서,
프로젝트 종속성을 지정하여 위에서 만든 DLL의 라이브러리를 링크하든,
어쨋든 라이브러리 링크가 필요하다.
그리고, 위 DLL 생성시 선언한 헤더파일을 include 하든, 아래와 같이 선언하면 DLL에서
export 한 함수를 사용할 수 있다.
extern "C" void WINAPI func(void);
void CUseDllDlg::OnBnClickedButton1()
{
// TODO: 여기에 컨트롤 알림 처리기 코드를 추가합니다.
func();
}
==============================================================================
2. __decl(dllexport) 사용하기
==============================================================================
// 헤더 파일
#define DLLEXPORT __declspec(dllexport)
#ifdef __cplusplus
extern "C" {
#endif
DLLEXPORT void WINAPI func();
#ifdef __cplusplus
}
#endif
// 소스파일
... 1과 동일
DEF 파일은 건들지 않고,
extern "C" void WINAPI func(void);
void CUseDllDlg::OnBnClickedButton1()
{
// TODO: 여기에 컨트롤 알림 처리기 코드를 추가합니다.
func();
}
또는
extern "C" __declspec(dllimport) void WINAPI func(void);
void CUseDllDlg::OnBnClickedButton1()
{
// TODO: 여기에 컨트롤 알림 처리기 코드를 추가합니다.
func();
}
와 같이 사용할 수 있다.
만약 헤더 파일을 사용한다면,
아래와 같이 정의하면,,좀더 편하게 import, export 할 수 있다.
#ifdef _EXPORTING
#define CLASS_DECLSPEC __declspec(dllexport)
#else
#define CLASS_DECLSPEC __declspec(dllimport)
#endif
==============================================================================
3. EXPLICIT LINKING
==============================================================================
위 두방법은 모두 implicit 링킹으로 DLL과 함께 생성된 LIB 파일이랑 함수 선언이 반드시
필요하다. 이제 이런 Lib와 헤더파일이 필요없는 Explicit 링킹을 해보자.
LoadLiblary("test.dll")
GetProAddress(...)
를 이용하여 함수 포인터를 얻어서 사용하면 된다.
==============================================================================
확장 DLL에 대해서 또 알아보자.
==============================================================================
==> 확장 DLL은 MFC 프로젝트에서만 이용할 수 있으며, (위 3가지 방법은 아무데서나 사용가능)
클래스를 사용할 수 있기 때문에, MFC만으로 개발하는 프로젝트에서는 확장 DLL을 사용하는 것이
좀더 편리할 것이다.
==============================================================================
FAR에 관해서
==============================================================================
Any data pointers used in the API are explicit FAR pointers. Again, FAR, is not really necessary for Win32, but is
useful if you plan to compile the code for 16-bit Windows sometime in the future
==> 첨부는 정규 DLL 예제(DLLScreenCap.zip)와 확장 DLL 예제(dllhusk.zip)
Using Layered Windows
Using Layered Windows
To have a dialog box come up as a translucent window, first create the dialog as usual. Then, on
// Set WS_EX_LAYERED on this window SetWindowLong(hwnd, GWL_EXSTYLE, GetWindowLong(hwnd, GWL_EXSTYLE) | WS_EX_LAYERED); // Make this window 70% alpha SetLayeredWindowAttributes(hwnd, 0, (255 * 70) / 100, LWA_ALPHA);
Note that the third parameter of SetLayeredWindowAttributes is a value that ranges from 0 to 255, with 0 making the window completely transparent and 255 making it completely opaque. This parameter mimics the more versatile
To make this window completely opaque again, remove the WS_EX_LAYERED bit by calling
// Remove WS_EX_LAYERED from this window styles SetWindowLong(hwnd, GWL_EXSTYLE, GetWindowLong(hwnd, GWL_EXSTYLE) & ~WS_EX_LAYERED); // Ask the window and its children to repaint RedrawWindow(hwnd, NULL, NULL, RDW_ERASE | RDW_INVALIDATE | RDW_FRAME | RDW_ALLCHILDREN);
Most DLL developers have faced the challenge of detecting a HMODULE/HINSTANCE handle within the module you're running in. It may be a difficult task if you wrote the DLL without a DLLMain() function or you are unaware of its name. For example:
Your DLL was built without ATL/MFC, so the DLLMain() function exists, but it's hidden from you code and you cannot access the hinstDLL parameter. You do not know the DLL's real file name because it could be renamed by everyone, so GetModuleHandle() is not for you.
This small code can help you solve this problem:
#if _MSC_VER >= 1300 // for VC 7.0 // from ATL 7.0 sources #ifndef _delayimp_h extern "C" IMAGE_DOS_HEADER __ImageBase; #endif #endif HMODULE GetCurrentModule() { #if _MSC_VER < 1300 // earlier than .NET compiler (VC 6.0) // Here's a trick that will get you the handle of the module // you're running in without any a-priori knowledge: // http://www.dotnet247.com/247reference/msgs/13/65259.aspx MEMORY_BASIC_INFORMATION mbi; static int dummy; VirtualQuery( &dummy, &mbi, sizeof(mbi) ); return reinterpret_cast<HMODULE>(mbi.AllocationBase); #else // VC 7.0 // from ATL 7.0 sources return reinterpret_cast<HMODULE>(&__ImageBase); #endif }
MSDN에 보면,
Handle to a module. The value is the base address of the module.This type is declared in WinDef.h as follows:
typedef HINSTANCE HMODULE;
이라고 나와 있다.
출처 : Codeguru
Connector/ODBC Reference
23.1.6. Connector/ODBC Reference
This section provides reference material for the Connector/ODBC API, showing supported functions and methods, supported MySQL column types and the corresponding native type in Connector/ODBC, and the error codes returned by Connector/ODBC when a fault occurs.
23.1.6.1. Connector/ODBC API Reference
This section summarizes ODBC routines, categorized by functionality.
For the complete ODBC API reference, please refer to the ODBC Programer's Reference at http://msdn.microsoft.com/library/en-us/odbc/htm/odbcabout_this_manual.asp.
An application can call SQLGetInfo
function to obtain conformance information about Connector/ODBC. To obtain information about support for a specific function in the driver, an application can call SQLGetFunctions
.
Note
For backward compatibility, the Connector/ODBC 3.51 driver supports all deprecated functions.
The following tables list Connector/ODBC API calls grouped by task:
Connecting to a data source:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLAllocHandle |
Yes | ISO 92 | Obtains an environment, connection, statement, or descriptor handle. |
SQLConnect |
Yes | ISO 92 | Connects to a specific driver by data source name, user ID, and password. |
SQLDriverConnect |
Yes | ODBC | Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialog boxes for the user. |
SQLAllocEnv |
Yes | Deprecated | Obtains an environment handle allocated from driver. |
SQLAllocConnect |
Yes | Deprecated | Obtains a connection handle |
Obtaining information about a driver and data source:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLDataSources |
No | ISO 92 | Returns the list of available data sources, handled by the Driver Manager |
SQLDrivers |
No | ODBC | Returns the list of installed drivers and their attributes, handles by Driver Manager |
SQLGetInfo |
Yes | ISO 92 | Returns information about a specific driver and data source. |
SQLGetFunctions |
Yes | ISO 92 | Returns supported driver functions. |
SQLGetTypeInfo |
Yes | ISO 92 | Returns information about supported data types. |
Setting and retrieving driver attributes:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLSetConnectAttr |
Yes | ISO 92 | Sets a connection attribute. |
SQLGetConnectAttr |
Yes | ISO 92 | Returns the value of a connection attribute. |
SQLSetConnectOption |
Yes | Deprecated | Sets a connection option |
SQLGetConnectOption |
Yes | Deprecated | Returns the value of a connection option |
SQLSetEnvAttr |
Yes | ISO 92 | Sets an environment attribute. |
SQLGetEnvAttr |
Yes | ISO 92 | Returns the value of an environment attribute. |
SQLSetStmtAttr |
Yes | ISO 92 | Sets a statement attribute. |
SQLGetStmtAttr |
Yes | ISO 92 | Returns the value of a statement attribute. |
SQLSetStmtOption |
Yes | Deprecated | Sets a statement option |
SQLGetStmtOption |
Yes | Deprecated | Returns the value of a statement option |
Preparing SQL requests:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLAllocStmt |
Yes | Deprecated | Allocates a statement handle |
SQLPrepare |
Yes | ISO 92 | Prepares an SQL statement for later execution. |
SQLBindParameter |
Yes | ODBC | Assigns storage for a parameter in an SQL statement. |
SQLGetCursorName |
Yes | ISO 92 | Returns the cursor name associated with a statement handle. |
SQLSetCursorName |
Yes | ISO 92 | Specifies a cursor name. |
SQLSetScrollOptions |
Yes | ODBC | Sets options that control cursor behavior. |
Submitting requests:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLExecute |
Yes | ISO 92 | Executes a prepared statement. |
SQLExecDirect |
Yes | ISO 92 | Executes a statement |
SQLNativeSql |
Yes | ODBC | Returns the text of an SQL statement as translated by the driver. |
SQLDescribeParam |
Yes | ODBC | Returns the description for a specific parameter in a statement. |
SQLNumParams |
Yes | ISO 92 | Returns the number of parameters in a statement. |
SQLParamData |
Yes | ISO 92 | Used in conjunction with SQLPutData to supply parameter data at execution time. (Useful for long data values.) |
SQLPutData |
Yes | ISO 92 | Sends part or all of a data value for a parameter. (Useful for long data values.) |
Retrieving results and information about results:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLRowCount |
Yes | ISO 92 | Returns the number of rows affected by an insert, update, or delete request. |
SQLNumResultCols |
Yes | ISO 92 | Returns the number of columns in the result set. |
SQLDescribeCol |
Yes | ISO 92 | Describes a column in the result set. |
SQLColAttribute |
Yes | ISO 92 | Describes attributes of a column in the result set. |
SQLColAttributes |
Yes | Deprecated | Describes attributes of a column in the result set. |
SQLFetch |
Yes | ISO 92 | Returns multiple result rows. |
SQLFetchScroll |
Yes | ISO 92 | Returns scrollable result rows. |
SQLExtendedFetch |
Yes | Deprecated | Returns scrollable result rows. |
SQLSetPos |
Yes | ODBC | Positions a cursor within a fetched block of data and allows an application to refresh data in the rowset or to update or delete data in the result set. |
SQLBulkOperations |
Yes | ODBC | Performs bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark. |
Retrieving error or diagnostic information:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLError |
Yes | Deprecated | Returns additional error or status information |
SQLGetDiagField |
Yes | ISO 92 | Returns additional diagnostic information (a single field of the diagnostic data structure). |
SQLGetDiagRec |
Yes | ISO 92 | Returns additional diagnostic information (multiple fields of the diagnostic data structure). |
Obtaining information about the data source's system tables (catalog functions) item:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLColumnPrivileges |
Yes | ODBC | Returns a list of columns and associated privileges for one or more tables. |
SQLColumns |
Yes | X/Open | Returns the list of column names in specified tables. |
SQLForeignKeys |
Yes | ODBC | Returns a list of column names that make up foreign keys, if they exist for a specified table. |
SQLPrimaryKeys |
Yes | ODBC | Returns the list of column names that make up the primary key for a table. |
SQLSpecialColumns |
Yes | X/Open | Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. |
SQLStatistics |
Yes | ISO 92 | Returns statistics about a single table and the list of indexes associated with the table. |
SQLTablePrivileges |
Yes | ODBC | Returns a list of tables and the privileges associated with each table. |
SQLTables |
Yes | X/Open | Returns the list of table names stored in a specific data source. |
Performing transactions:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLTransact |
Yes | Deprecated | Commits or rolls back a transaction |
SQLEndTran |
Yes | ISO 92 | Commits or rolls back a transaction. |
Terminating a statement:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLFreeStmt |
Yes | ISO 92 | Ends statement processing, discards pending results, and, optionally, frees all resources associated with the statement handle. |
SQLCloseCursor |
Yes | ISO 92 | Closes a cursor that has been opened on a statement handle. |
SQLCancel |
Yes | ISO 92 | Cancels an SQL statement. |
Terminating a connection:
Function name | C/ODBC 3.51 | Standard | Purpose |
SQLDisconnect |
Yes | ISO 92 | Closes the connection. |
SQLFreeHandle |
Yes | ISO 92 | Releases an environment, connection, statement, or descriptor handle. |
SQLFreeConnect |
Yes | Deprecated | Releases connection handle |
SQLFreeEnv |
Yes | Deprecated | Releases an environment handle |
23.1.6.2. Connector/ODBC Data Types
The following table illustrates how driver maps the server data types to default SQL and C data types:
Native Value | SQL Type | C Type |
bigint unsigned |
SQL_BIGINT |
SQL_C_UBIGINT |
bigint |
SQL_BIGINT |
SQL_C_SBIGINT |
bit |
SQL_BIT |
SQL_C_BIT |
bit |
SQL_CHAR |
SQL_C_CHAR |
blob |
SQL_LONGVARBINARY |
SQL_C_BINARY |
bool |
SQL_CHAR |
SQL_C_CHAR |
char |
SQL_CHAR |
SQL_C_CHAR |
date |
SQL_DATE |
SQL_C_DATE |
datetime |
SQL_TIMESTAMP |
SQL_C_TIMESTAMP |
decimal |
SQL_DECIMAL |
SQL_C_CHAR |
double precision |
SQL_DOUBLE |
SQL_C_DOUBLE |
double |
SQL_FLOAT |
SQL_C_DOUBLE |
enum |
SQL_VARCHAR |
SQL_C_CHAR |
float |
SQL_REAL |
SQL_C_FLOAT |
int unsigned |
SQL_INTEGER |
SQL_C_ULONG |
int |
SQL_INTEGER |
SQL_C_SLONG |
integer unsigned |
SQL_INTEGER |
SQL_C_ULONG |
integer |
SQL_INTEGER |
SQL_C_SLONG |
long varbinary |
SQL_LONGVARBINARY |
SQL_C_BINARY |
long varchar |
SQL_LONGVARCHAR |
SQL_C_CHAR |
longblob |
SQL_LONGVARBINARY |
SQL_C_BINARY |
longtext |
SQL_LONGVARCHAR |
SQL_C_CHAR |
mediumblob |
SQL_LONGVARBINARY |
SQL_C_BINARY |
mediumint unsigned |
SQL_INTEGER |
SQL_C_ULONG |
mediumint |
SQL_INTEGER |
SQL_C_SLONG |
mediumtext |
SQL_LONGVARCHAR |
SQL_C_CHAR |
numeric |
SQL_NUMERIC |
SQL_C_CHAR |
real |
SQL_FLOAT |
SQL_C_DOUBLE |
set |
SQL_VARCHAR |
SQL_C_CHAR |
smallint unsigned |
SQL_SMALLINT |
SQL_C_USHORT |
smallint |
SQL_SMALLINT |
SQL_C_SSHORT |
text |
SQL_LONGVARCHAR |
SQL_C_CHAR |
time |
SQL_TIME |
SQL_C_TIME |
timestamp |
SQL_TIMESTAMP |
SQL_C_TIMESTAMP |
tinyblob |
SQL_LONGVARBINARY |
SQL_C_BINARY |
tinyint unsigned |
SQL_TINYINT |
SQL_C_UTINYINT |
tinyint |
SQL_TINYINT |
SQL_C_STINYINT |
tinytext |
SQL_LONGVARCHAR |
SQL_C_CHAR |
varchar |
SQL_VARCHAR |
SQL_C_CHAR |
year |
SQL_SMALLINT |
SQL_C_SHORT |
23.1.6.3. Connector/ODBC Error Codes
The following tables lists the error codes returned by the driver apart from the server errors.
Native Code | SQLSTATE 2 | SQLSTATE 3 | Error Message |
500 | 01000 | 01000 | General warning |
501 | 01004 | 01004 | String data, right truncated |
502 | 01S02 | 01S02 | Option value changed |
503 | 01S03 | 01S03 | No rows updated/deleted |
504 | 01S04 | 01S04 | More than one row updated/deleted |
505 | 01S06 | 01S06 | Attempt to fetch before the result set returned the first row set |
506 | 07001 | 07002 | SQLBindParameter not used for all parameters |
507 | 07005 | 07005 | Prepared statement not a cursor-specification |
508 | 07009 | 07009 | Invalid descriptor index |
509 | 08002 | 08002 | Connection name in use |
510 | 08003 | 08003 | Connection does not exist |
511 | 24000 | 24000 | Invalid cursor state |
512 | 25000 | 25000 | Invalid transaction state |
513 | 25S01 | 25S01 | Transaction state unknown |
514 | 34000 | 34000 | Invalid cursor name |
515 | S1000 | HY000 | General driver defined error |
516 | S1001 | HY001 | Memory allocation error |
517 | S1002 | HY002 | Invalid column number |
518 | S1003 | HY003 | Invalid application buffer type |
519 | S1004 | HY004 | Invalid SQL data type |
520 | S1009 | HY009 | Invalid use of null pointer |
521 | S1010 | HY010 | Function sequence error |
522 | S1011 | HY011 | Attribute can not be set now |
523 | S1012 | HY012 | Invalid transaction operation code |
524 | S1013 | HY013 | Memory management error |
525 | S1015 | HY015 | No cursor name available |
526 | S1024 | HY024 | Invalid attribute value |
527 | S1090 | HY090 | Invalid string or buffer length |
528 | S1091 | HY091 | Invalid descriptor field identifier |
529 | S1092 | HY092 | Invalid attribute/option identifier |
530 | S1093 | HY093 | Invalid parameter number |
531 | S1095 | HY095 | Function type out of range |
532 | S1106 | HY106 | Fetch type out of range |
533 | S1117 | HY117 | Row value out of range |
534 | S1109 | HY109 | Invalid cursor position |
535 | S1C00 | HYC00 | Optional feature not implemented |
0 | 21S01 | 21S01 | Column count does not match value count |
0 | 23000 | 23000 | Integrity constraint violation |
0 | 42000 | 42000 | Syntax error or access violation |
0 | 42S02 | 42S02 | Base table or view not found |
0 | 42S12 | 42S12 | Index not found |
0 | 42S21 | 42S21 | Column already exists |
0 | 42S22 | 42S22 | Column not found |
0 | 08S01 | 08S01 | Communication link failure |
출처 : MySQL 5.0 Reference Manaual
Connector/ODBC Programming
23.1.5.7. Connector/ODBC Programming
With a suitable ODBC Manager and the Connector/ODBC driver installed, any programming language or environment that can support ODBC should be able to connect to a MySQL database through Connector/ODBC.
This includes, but is certainly not limited to, Microsoft support languages (including Visual Basic, C# and interfaces such as ODBC.NET), Perl (through the DBI module, and the DBD::ODBC driver).
23.1.5.7.1. Using Connector/ODBC with Visual Basic Using ADO, DAO and RDO
This section contains simple examples of the use of MySQL ODBC 3.51 Driver with ADO, DAO and RDO.
23.1.5.7.1.1. ADO: rs.addNew
, rs.delete
, and rs.update
The following ADO (ActiveX Data Objects) example creates a table my_ado
and demonstrates the use of rs.addNew
, rs.delete
, and rs.update
.
Private Sub myodbc_ado_Click() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim fld As ADODB.Field Dim sql As String 'connect to MySQL server using MySQL ODBC 3.51 Driver Set conn = New ADODB.Connection conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_ & "SERVER=localhost;"_ & " DATABASE=test;"_ & "UID=venu;PWD=venu; OPTION=3" conn.Open 'create table conn.Execute "DROP TABLE IF EXISTS my_ado" conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _ & "txt text, dt date, tm time, ts timestamp)" 'direct insert conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')" conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')" conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')" Set rs = New ADODB.Recordset rs.CursorLocation = adUseServer 'fetch the initial table .. rs.Open "SELECT * FROM my_ado", conn Debug.Print rs.RecordCount rs.MoveFirst Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-") For Each fld In rs.Fields Debug.Print fld.Name, Next Debug.Print Do Until rs.EOF For Each fld In rs.Fields Debug.Print fld.Value, Next rs.MoveNext Debug.Print Loop rs.Close 'rs insert rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic rs.AddNew rs!Name = "Monty" rs!txt = "Insert row" rs.Update rs.Close 'rs update rs.Open "SELECT * FROM my_ado" rs!Name = "update" rs!txt = "updated-row" rs.Update rs.Close 'rs update second time.. rs.Open "SELECT * FROM my_ado" rs!Name = "update" rs!txt = "updated-second-time" rs.Update rs.Close 'rs delete rs.Open "SELECT * FROM my_ado" rs.MoveNext rs.MoveNext rs.Delete rs.Close 'fetch the updated table .. rs.Open "SELECT * FROM my_ado", conn Debug.Print rs.RecordCount rs.MoveFirst Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-") For Each fld In rs.Fields Debug.Print fld.Name, Next Debug.Print Do Until rs.EOF For Each fld In rs.Fields Debug.Print fld.Value, Next rs.MoveNext Debug.Print Loop rs.Close conn.Close End Sub
23.1.5.7.1.2. DAO: rs.addNew
, rs.update
, and Scrolling
The following DAO (Data Access Objects) example creates a table my_dao
and demonstrates the use of rs.addNew
, rs.update
, and result set scrolling.
Private Sub myodbc_dao_Click() Dim ws As Workspace Dim conn As Connection Dim queryDef As queryDef Dim str As String 'connect to MySQL using MySQL ODBC 3.51 Driver Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC) str = "odbc;DRIVER={MySQL ODBC 3.51 Driver};"_ & "SERVER=localhost;"_ & " DATABASE=test;"_ & "UID=venu;PWD=venu; OPTION=3" Set conn = ws.OpenConnection("test", dbDriverNoPrompt, False, str) 'Create table my_dao Set queryDef = conn.CreateQueryDef("", "drop table if exists my_dao") queryDef.Execute Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _ & "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT)") queryDef.Execute 'Insert new records using rs.addNew Set rs = conn.OpenRecordset("my_dao") Dim i As Integer For i = 10 To 15 rs.AddNew rs!Name = "insert record" & i rs!Id2 = i rs.Update Next i rs.Close 'rs update.. Set rs = conn.OpenRecordset("my_dao") rs.Edit rs!Name = "updated-string" rs.Update rs.Close 'fetch the table back... Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic) str = "Results:" rs.MoveFirst While Not rs.EOF str = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2 Debug.Print "DATA:" & str rs.MoveNext Wend 'rs Scrolling rs.MoveFirst str = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2 Debug.Print str rs.MoveLast str = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2 Debug.Print str rs.MovePrevious str = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2 Debug.Print str 'free all resources rs.Close queryDef.Close conn.Close ws.Close End Sub
23.1.5.7.1.3. RDO: rs.addNew
and rs.update
The following RDO (Remote Data Objects) example creates a table my_rdo
and demonstrates the use of rs.addNew
and rs.update
.
Dim rs As rdoResultset Dim cn As New rdoConnection Dim cl As rdoColumn Dim SQL As String 'cn.Connect = "DSN=test;" cn.Connect = "DRIVER={MySQL ODBC 3.51 Driver};"_ & "SERVER=localhost;"_ & " DATABASE=test;"_ & "UID=venu;PWD=venu; OPTION=3" cn.CursorDriver = rdUseOdbc cn.EstablishConnection rdDriverPrompt 'drop table my_rdo SQL = "drop table if exists my_rdo" cn.Execute SQL, rdExecDirect 'create table my_rdo SQL = "create table my_rdo(id int, name varchar(20))" cn.Execute SQL, rdExecDirect 'insert - direct SQL = "insert into my_rdo values (100,'venu')" cn.Execute SQL, rdExecDirect SQL = "insert into my_rdo values (200,'MySQL')" cn.Execute SQL, rdExecDirect 'rs insert SQL = "select * from my_rdo" Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect) rs.AddNew rs!id = 300 rs!Name = "Insert1" rs.Update rs.Close 'rs insert SQL = "select * from my_rdo" Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect) rs.AddNew rs!id = 400 rs!Name = "Insert 2" rs.Update rs.Close 'rs update SQL = "select * from my_rdo" Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect) rs.Edit rs!id = 999 rs!Name = "updated" rs.Update rs.Close 'fetch back... SQL = "select * from my_rdo" Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect) Do Until rs.EOF For Each cl In rs.rdoColumns Debug.Print cl.Value, Next rs.MoveNext Debug.Print Loop Debug.Print "Row count="; rs.RowCount 'close rs.Close cn.Close End Sub
23.1.5.7.2. Using Connector/ODBC with .NET
This section contains simple examples that demonstrate the use of Connector/ODBC drivers with ODBC.NET.
23.1.5.7.2.1. Using Connector/ODBC with ODBC.NET and C# (C sharp)
The following sample creates a table my_odbc_net
and demonstrates its use in C#.
/**
* @sample : mycon.cs
* @purpose : Demo sample for ODBC.NET using Connector/ODBC
* @author : Venu, <myodbc@lists.mysql.com>
*
* (C) Copyright MySQL AB, 1995-2006
*
**/
/* build command
*
* csc /t:exe
* /out:mycon.exe mycon.cs
* /r:Microsoft.Data.Odbc.dll
*/
using Console = System.Console;
using Microsoft.Data.Odbc;
namespace myodbc3
{
class mycon
{
static void Main(string[] args)
{
try
{
//Connection string for Connector/ODBC 3.51
string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=test;" +
"UID=venu;" +
"PASSWORD=venu;" +
"OPTION=3";
//Connect to MySQL using Connector/ODBC
OdbcConnection MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
Console.WriteLine("\n !!! success, connected successfully !!!\n");
//Display connection information
Console.WriteLine("Connection Information:");
Console.WriteLine("\tConnection String:" +
MyConnection.ConnectionString);
Console.WriteLine("\tConnection Timeout:" +
MyConnection.ConnectionTimeout);
Console.WriteLine("\tDatabase:" +
MyConnection.Database);
Console.WriteLine("\tDataSource:" +
MyConnection.DataSource);
Console.WriteLine("\tDriver:" +
MyConnection.Driver);
Console.WriteLine("\tServerVersion:" +
MyConnection.ServerVersion);
//Create a sample table
OdbcCommand MyCommand =
new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",
MyConnection);
MyCommand.ExecuteNonQuery();
MyCommand.CommandText =
"CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
MyCommand.ExecuteNonQuery();
//Insert
MyCommand.CommandText =
"INSERT INTO my_odbc_net VALUES(10,'venu', 300)";
Console.WriteLine("INSERT, Total rows affected:" +
MyCommand.ExecuteNonQuery());;
//Insert
MyCommand.CommandText =
"INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
Console.WriteLine("INSERT, Total rows affected:" +
MyCommand.ExecuteNonQuery());
//Insert
MyCommand.CommandText =
"INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
Console.WriteLine("INSERT, Total rows affected:" +
MyCommand.ExecuteNonQuery());
//Update
MyCommand.CommandText =
"UPDATE my_odbc_net SET id=999 WHERE id=20";
Console.WriteLine("Update, Total rows affected:" +
MyCommand.ExecuteNonQuery());
//COUNT(*)
MyCommand.CommandText =
"SELECT COUNT(*) as TRows FROM my_odbc_net";
Console.WriteLine("Total Rows:" +
MyCommand.ExecuteScalar());
//Fetch
MyCommand.CommandText = "SELECT * FROM my_odbc_net";
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) {
//Supported only by Connector/ODBC 3.51
Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
MyDataReader.GetString(1) + " " +
MyDataReader.GetInt64(2));
}
else {
//BIGINTs not supported by Connector/ODBC
Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
MyDataReader.GetString(1) + " " +
MyDataReader.GetInt32(2));
}
}
//Close all resources
MyDataReader.Close();
MyConnection.Close();
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
for (int i=0; i < MyOdbcException.Errors.Count; i++)
{
Console.Write("ERROR #" + i + "\n" +
"Message: " +
MyOdbcException.Errors[i].Message + "\n" +
"Native: " +
MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
"Source: " +
MyOdbcException.Errors[i].Source + "\n" +
"SQL: " +
MyOdbcException.Errors[i].SQLState + "\n");
}
}
}
}
}
23.1.5.7.2.2. Using Connector/ODBC with ODBC.NET and Visual Basic
The following sample creates a table my_vb_net
and demonstrates the use in VB.
' @sample : myvb.vb ' @purpose : Demo sample for ODBC.NET using Connector/ODBC ' @author : Venu,<myodbc@lists.mysql.com>
' ' (C) Copyright MySQL AB, 1995-2006 ' ' ' ' build command ' ' vbc /target:exe ' /out:myvb.exe ' /r:Microsoft.Data.Odbc.dll ' /r:System.dll ' /r:System.Data.dll ' Imports Microsoft.Data.Odbc Imports System Module myvb Sub Main() Try 'Connector/ODBC 3.51 connection string Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=test;" & _ "UID=venu;" & _ "PASSWORD=venu;" & _ "OPTION=3;" 'Connection Dim MyConnection As New OdbcConnection(MyConString) MyConnection.Open() Console.WriteLine("Connection State::" & MyConnection.State.ToString) 'Drop Console.WriteLine("Dropping table") Dim MyCommand As New OdbcCommand() MyCommand.Connection = MyConnection MyCommand.CommandText = "DROP TABLE IF EXISTS my_vb_net" MyCommand.ExecuteNonQuery() 'Create Console.WriteLine("Creating....") MyCommand.CommandText = "CREATE TABLE my_vb_net(id int, name varchar(30))" MyCommand.ExecuteNonQuery() 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(10,'venu')" Console.WriteLine("INSERT, Total rows affected:" & _ MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')" Console.WriteLine("INSERT, Total rows affected:" & _ MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')" Console.WriteLine("INSERT, Total rows affected:" & _ MyCommand.ExecuteNonQuery()) 'Insert MyCommand.CommandText = "INSERT INTO my_vb_net(id) VALUES(30)" Console.WriteLine("INSERT, Total rows affected:" & _ MyCommand.ExecuteNonQuery()) 'Update MyCommand.CommandText = "UPDATE my_vb_net SET id=999 WHERE id=20" Console.WriteLine("Update, Total rows affected:" & _ MyCommand.ExecuteNonQuery()) 'COUNT(*) MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_vb_net" Console.WriteLine("Total Rows:" & MyCommand.ExecuteScalar()) 'Select Console.WriteLine("Select * FROM my_vb_net") MyCommand.CommandText = "SELECT * FROM my_vb_net" Dim MyDataReader As OdbcDataReader MyDataReader = MyCommand.ExecuteReader While MyDataReader.Read If MyDataReader("name") Is DBNull.Value Then Console.WriteLine("id = " & _ CStr(MyDataReader("id")) & " name = " & _ "NULL") Else Console.WriteLine("id = " & _ CStr(MyDataReader("id")) & " name = " & _ CStr(MyDataReader("name"))) End If End While 'Catch ODBC Exception Catch MyOdbcException As OdbcException Dim i As Integer Console.WriteLine(MyOdbcException.ToString) 'Catch program exception Catch MyException As Exception Console.WriteLine(MyException.ToString) End Try End Sub
출처 : MySQL 5.0 Reference Manual
Using Connector/ODBC with Crystal Reports
23.1.5.6. Using Connector/ODBC with Crystal Reports
Crystal Reports can use an ODBC DSN to connect to a database from which you to extract data and information for reporting purposes.
Note
There is a known issue with certain versions of Crystal Reports where the application is unable to open and browse tables and fields through an ODBC connection. Before using Crystal Reports with MySQL, please ensure that you have update to the latest version, including any outstanding service packs and hotfixes. For more information on this issue, see the Business) Objects Knowledgebase for more information.
For example, to create a simple crosstab report within Crystal Reports XI, you should follow these steps:
-
Create a DSN using the
Data Sources (ODBC)
tool. You can either specify a complete database, including username and password, or you can build a basic DSN and use Crystal Reports to set the username and password.For the purposes of this example, a DSN that provides a connection to an instance of the MySQL Sakila sample database has been created.
-
Open Crystal Reports and create a new project, or an open an existing reporting project into which you want to insert data from your MySQL data source.
-
Start the Cross-Tab Report Wizard, either by clicking on the option on the Start Page. Expand the Create New Connection folder, then expand the ODBC (RDO) folder to obtain a list of ODBC data sources.
You will be asked to select a data source.
-
When you first expand the ODBC (RDO) folder you will be presented the Data Source Selection screen. From here you can select either a pre-configured DSN, open a file-based DSN or enter and manual connection string. For this example, the Sakila DSN will be used.
If the DSN contains a username/password combination, or you want to use different authentication credentials, click
to enter the username and password that you want to use. Otherwise, click to continue the data source selection wizard. -
You will be returned the Cross-Tab Report Creation Wizard. You now need to select the database and tables that you want to include in your report. For our example, we will expand the selected Sakila database. Click the
city
table and use the button to add the table to the report. Then repeat the action with thecountry
table. Alternatively you can select multiple tables and add them to the report.Finally, you can select the parent Sakila resource and add of the tables to the report.
Once you have selected the tables you want to include, click
to continue. -
Crystal Reports will now read the table definitions and automatically identify the links between the tables. The identification of links between tables enables Crystal Reports to automatically lookup and summarize information based on all the tables in the database according to your query. If Crystal Reports is unable to perform the linking itself, you can manually create the links between fields in the tables you have selected.
Click
to continue the process. -
You can now select the columns and rows that you wish to include within the Cross-Tab report. Drag and drop or use the
buttons to add fields to each area of the report. In the example shown, we will report on cities, organized by country, incorporating a count of the number of cities within each country. If you want to browse the data, select a field and click the button.Click
to create a graph of the results. Since we are not creating a graph from this data, click to generate the report. -
The finished report will be shown, a sample of the output from the Sakila sample database is shown below.
Once the ODBC connection has been opened within Crystal Reports, you can browse and add any fields within the available tables into your reports.
출처 : MySQL 5.0 Reference Manual