database.sarang.net
UserID
Passwd
Database
ㆍDBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DBMS Q&A 1052 게시물 읽기
No. 1052
C언어로 access연동인데..
작성자
김준성
작성일
2004-11-30 21:24
조회수
12,475

#include <stdio.h>
#include <conio.h>
#include <string.h>
#include <stdlib.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

HENV henv;
HDBC hdbc;
HSTMT hstmt;


int ConnecToDB ( unsigned char *id, unsigned char *pwd, unsigned char *dsn )
{
RETCODE retcode;

retcode = SQLAllocEnv(&henv);
if(retcode == SQL_SUCCESS) {
retcode = SQLAllocConnect(henv,&hdbc);
if(retcode == SQL_SUCCESS)
{
SQLSetConnectOption(hdbc,SQL_LOGIN_TIMEOUT,5);

retcode = SQLConnect(hdbc,dsn,SQL_NTS,id,SQL_NTS,pwd,SQL_NTS);
if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
return 1;
}
return 0;
}
return 0;
}
return 0;
}


int DisconnectDB()
{
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
return 1;
}

void insert_rec(void)
{
char name[20],addr[40],tel[20],mobile[20],email[20];
char query[1048];
RETCODE retcode;

printf("\n>>>사용자로부터 레코드를 입력받아 데이터베이스에 저장\n\n");

printf("Enter name :");
gets(name);

printf("Enter address :");
gets(addr);

printf("Enter telephone number :");
gets(tel);

printf("Enter telephone mobile :");
gets(mobile);

printf("Enter e-amil :");
gets(email);

printf("\n입력 레코드 : %s %s %s %s %s\n\n",name,addr,tel,mobile,email);

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

sprintf(query,"insert into address values ('%s','%s','%s','%s','%s')",name,addr,tel,mobile,email);
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,query,SQL_NTS );

if( retcode == SQL_SUCCESS ) printf("입력 성공\n\n");
else printf("입력에 실패 하였습니다.\n\n");

DisconnectDB();


}

void search_all_rec(void)
{
char query[1048];
RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

printf("\n>>>전체 레코드를 출력\n\n");

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

strcpy(query,"select * from address");
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();


}


search_by_title(void)
{
char name[20];
char query[1048];
RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

printf("\n>>>사용자로부터 입력받은 검색어를 통해 만족하는 레코드 출력\n\n");

printf("Name : ");
gets(name);

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

sprintf(query,"select * from address where name like'%s%%'",name);
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();

}

 

delete_by_name(void)
{
char name[20];
char query[1048];
RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

printf("\n>>>사용자로부터 입력받은 검색어를 통해 삭제 실행\n\n");

printf("Name : ");
gets(name);

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

sprintf(query,"delete * from address where name like'%s%%'",name);
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();
}


search_all_record(void)
{
char query[1048];
RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

printf("\n>>>사용자로부터 레코드를 통해 오름차순 정렬\n\n");

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

strcpy(query,"select * from address order by name asc");
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();


}

search_by_attribute(void)
{
char ch;
char name[20],addr[40],tel[20],mobile[20],email[20];
char query[1048];
RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

if(ConnecToDB("","","mydsn" ) == 0)
{
printf("db접속 에러");
return;
}

do{
printf("어떤식으로 검색할지 메뉴를 정해주세요\n");
printf("Command : (1)이름 (2) 주소 (3) 전화번호 (4)E-mail(5)핸드폰번호(6)검색종료\n");

ch = getchar();

fflush(stdin);

switch(ch) {

case '1' :
printf("Name : ");
gets(name);
break;
case '2' :
printf("ADDRESS : ");
gets(addr);
break;
case '3' :
printf("TEL : ");
gets(tel);

break;
case '4' :
printf("E-mail : ");
gets(email);
break;
case '5' :
printf("Mobile: ");
gets(mobile);
break;
case '6' : break;
default : printf("메뉴가 없습니다.\n");

}

}while(ch != '6');

sprintf(query,"select * from address where name like'%s%%'",name);
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();


}

searh_exe(void)
{

RETCODE retcode;
SQLCHAR szA[1048];
SQLINTEGER cbA ;
int nColCnt;

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();
}

void main(void)
{
char ch;
do{
printf("***************************************\n");
printf("* 주 소 록*\n");
printf("****************************************\n");
printf("해당메뉴를 입력하고 리턴을 치시오\n");
printf("Command : (1) Insert (2) Search_ALL (3) Search_by_name (4)delete_by_name(5)search_all_record(6)serach_by_attribute(7) Quit > ");

ch = getchar();

fflush(stdin);

switch(ch) {

case '1' : insert_rec();
break;
case '2' : search_all_rec();
break;
case '3' : search_by_title();
break;
case '4' : delete_by_name();
break;
case '5' : search_all_record();
break;
case '6' : search_by_attribute();
case '7' :
printf("프로그램이 종료됩니다.\n");
break;

default : printf("메뉴가 없습니다.\n");

}

}while(ch != '7');

}
/*메인 프로그램 끝*/

 

 

검색조건에 맞게 결과를 뿌려주고 싶은데

 

일일히 while(ch != '6');

sprintf(query,"select * from address where name like'%s%%'",name);
SQLAllocStmt(hdbc,&hstmt);
retcode = SQLExecDirect(hstmt,(unsigned char *)query, SQL_NTS);

if( retcode != SQL_SUCCESS)
{
printf("질의에 오류가 있습니다.\n\n");
return;
}

while(!SQLFetch(hstmt) )
{
nColCnt = 1;

while(1)
{
retcode = SQLGetData(hstmt,nColCnt,SQL_CHAR,&szA,1048,&cbA);
if(retcode == -1) break;
printf("%s\t",szA);
nColCnt++;
}
printf("\n");
}

DisconnectDB();


}

이부분을 스위치구문에다가 주게되면 소스가 상당히 길어지는데 좋은방법이 없을까요?

[Top]
No.
제목
작성자
작성일
조회
1055다시 질문드립니다.. [3]
수니
2004-12-05
10898
1054무선인터넷과 연동되어 사용할수 있는 Database에는 어떤것들이 있나요? [1]
수니
2004-12-03
11815
1053commit 시점을 어떻게 해야할 지... [3]
윤명욱
2004-12-01
11640
1052C언어로 access연동인데..
김준성
2004-11-30
12475
1050[질문] 데이터베이스의 종류가 뭐가있쬬? [1]
지훈쏭
2004-11-26
12582
1049오라클 mysql mssql....서로의관계 [2]
오희원
2004-11-25
11761
1046오라클7버전의 DB서버과 오라클 9.2 버전의 DB서버의 Db Link 방법문의 [1]
김희정
2004-11-24
12163
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다