ADODB

등급 : 초급

ADO oledb를 이용한 간단한 DB조작에 관련된 강좌입니다.

아시겠지만 DB조작은 ODBC를 이용하거나 OLEDB 등을 이용해 가능합니다.

ODBC는 배포시 ODBC설정등을 해줘야(코드로 대체할 수 있습니다.) 하므로 귀찮기에 전 VB건 ASP건 VC건 모두 OLEDB로 처리하는 편입니다.

짧은 경험상 책을 통한 지식(클래스를 이용한 DB조작)은 딱딱하기 그지 없습니다.

이는 위에 나열한 언어와 관련된 책 모두 동일했습니다.

(특히 VB로 처음 DB조작이란걸 경험했을때 DAO 컨트롤을 통해 레코드를 이동시키며 공부했을때의 그 막막함이란..ㅡㅡ

Join이란 녀석 자체도 몰랐기에 테이블 만큼 컨트롤 생성하고 억지로 이것 저것..ㅡㅡ)

여튼.. 잡설은 그만두고.. 간단한 사용 예를 들어볼까 합니다.

[사전인지사항]

MyAdodb.h, MyAdodb.cpp : DB관련 변수와 초기화 연결등을 담당합니다.

SimpleAdoDlg.h SimpleAdoDlg.cpp : 실질적인 DB조작을 담당합니다.

[Step 1 : MyAdodb.h, MyAdodb.cpp]

1.프로젝트 하나를 생성합니다.

2.StdAfx.h를 열어 #import “C:\Program Files\Common Files\System\ado\msado15.dll” no_namespace rename(“EOF”, “adoEOF”)를

  복사해 넣습니다.

3.COM 라이브러리를 초기화해야 하니 CoInitialize(0);를 생성자에 포함시킵니다.

   이와 반대로 소멸자엔 CoUninitialize();를 삽입합니다. 

4. 커넥션과 커멘드 그리고 레코드셋 변수를 생성해줍니다.

    _ConnectionPtr m_pConn; // 커넥션은 말 그대로 DB와의 연결을 담당합니다. DB의 Open, Close…

    _RecordsetPtr m_pRs;      // 레코드셋 또한 말 그대로 조회결과인 레코드들을 담당합니다. 이녀석을 통하여 조회된 결과를 Movenext등을 이용해 원하는 결과물을 얻어낼 수 있습니다.

    _CommandPtr m_pComm;// 커멘드는 이 역시 명령과 관련된 녀석입니다. Update를 해라던지…

5.DB와 연결을 합니다.

BOOL CMyAdodb::InitDB(CString strUserId, CString strPassWord, CString strDbIp, CString strDbName, CString strPort)

{  

    CString strConn = “”;

    strConn.Format(“User ID=%s;Password=%s;Data Source=%s;”

                    “Initial Catalog=%s;Network Address=%s,%s;”

                    “Network Library=dbmssocn”,

                    strUserId, strPassWord, strDbIp, strDbName, strDbIp, strPort);

    m_pConn = new _ConnectionPtr(“ADODB.Connection”);

    m_pRs = new _RecordsetPtr(“ADODB.Recordset”);

    m_pRs->CursorType = adOpenDynamic;

    m_pComm = NULL;

    m_pConn->Provider = “SQLOLEDB”;

    m_pConn->ConnectionString = strConn.GetBuffer(0);  // 접속 스트링 설정.

    try

    {

        m_pConn->Open(“”,””,””, adConnectUnspecified);

    }catch(…)

    {

        return FALSE;

    }

    m_pComm.CreateInstance(__uuidof(Command));

    m_pComm->ActiveConnection = m_pConn;

    m_pComm->CommandType = adCmdText;

    return TRUE;

}

여기 까지 했다면 일단 사전 준비단계는 끝난거라봐도 무관합니다.

이제부턴 실질적인 연결작업과 조회 수정등을 해 볼 차례입니다.

[Step 2 SimpleAdo.h, SimpleAdo.cpp]

1.MyAdodb.h를 SimpleAdoDlg.h에 포함시키고 클래스를 만듭니다.

#include “MyAdodb.h”

CMyAdodb m_MyAdodb;

2.SimpleAdoDlg.cpp에서 DB연결 및 조작을 해봅니다.

  먼저 DB연결 문자열을 생성시킵니다.

  쿼리를 날렸을때 이녀석이 어떤 DB에 어떤 정보로 로긴등을 하여 조작이 가능한지에 대한 문자열입니다.

  잘 모르신다면 아래 강좌를 참조하시면 쉽게 설명되어있습니다.

http://www.devpia.com/Forum/BoardView.aspx?no=6840&page=1&Tpage=1&forumname=vc_lec&stype=&ctType=&answer=&KeyR=nameid&KeyC=

MS-SQL을 기준으로

User ID : DB에 로그인할 아이디 (SA정도가 됩니다.)

Password : 로그인할 아이디의 비밀번호

Data Source : MS-SQL이니 특정 아이피가 들어갑니다. 만약 MDB라면 해당 로컬 경로가 들어가있을겁니다.

Initial Catalog : DB의 이름입니다.

Network Address : MS-SQL이니 특정 아이피가 들어갑니다. 만약 MDB라면 해당 로컬 경로가 들어가있을겁니다. 특정 포트로 DB가 설정이 되어있지 않다면 기본포트인 1433이 입력됩니다.

Network Library : 모르겠군요. 책을 보면 나와있을겁니다. 죄송^^

Ex)

User ID=USERID;Password=PASSWORD;Data Source=XXX.XXX.XXX.XXX;Initial Catalog=DBNAME;Network Address=XXX.XXX.XXX.XXX,1433;Network Library=dbmssocn

위 정보를 토대로 Step1의 5번에서 만든 DB연결 함수를 호출합니다.

    if(m_MyAdodb.InitDB(“USERID”, “PASSWORD”, “XXX.XXX.XXX.XXX”, “DBNAME”, “1433”) == FALSE)

    {

        MessageBox(“디비접속에러입니다. 작업을 진행할 수 없습니다.”, “SIMPLEDB”, MB_ICONSTOP);

        GetParentOwner()->PostMessage(WM_CLOSE);

        return;

    }  

3.DB와의 연결이 성공하였으니 가장 기본이 되는 조회(Select)를 해보겠습니다.

    // 알맞은 쿼리를 대입하십시요. join이든 union이든.. (당연하지만..ㅡㅡ)

    m_MyAdodb.m_pComm->CommandText = “Select * From Test_Tbl where 어쩌고 저쩌고”;

    try

    {

        m_MyAdodb.m_pRs = m_MyAdodb.m_pComm->Execute(NULL,NULL,adCmdText);  // 저장된 select문을 수행합니다.

    }

    catch(…)

    {   // 에러일 경우 메세지 박스를 출력하고 조회 작업을 종료해 버립니다.(에러가 날 경우는 거의 없습니다.)

        MessageBox(“조회 오류!”, “SIMPLEDB”, MB_ICONSTOP);

        return;

    }

    // 결과가 존재하지 않음

    if(m_MyAdodb.m_pRs->adoEOF)    

    {

        m_MyAdodb.m_pRs->Close();   // 레코드셋을 닫는다.(항상 레코드셋 오픈 후엔 닫아주는 작업을 수행해야 합니다.)

        MessageBox(“조건에 맞는 데이터가 없습니다.”, “SIMPLEDB”, MB_ICONINFORMATION);

        return;

    }  

    _variant_t Field_1;     // 필드명입니다.(알맞은 필드명을 나열하십시요)

    _variant_t Field_2;     // 필드명입니다.

    char szField1[10+1] 
        = {‘\0’,};  // 필드명과 1:1대응하게 변수를 선언하십시요

    char szField2[10+1]         = {‘\0’,};

    // 조회된 레코드의 끝까지 루핑을 돌며 데이터를 확인합니다.

    while(!m_MyAdodb.m_pRs->adoEOF)

    {

        Field_1         = m_MyAdodb.m_pRs->Fields->GetItem(“field_1”)->GetValue();  // 해당 필드를 선언한 variant변수에 저장합니다.

        Field_2         = m_MyAdodb.m_pRs->Fields->GetItem(“field_2”)->GetValue();

        strcpy(szField1,    (char*)((_bstr_t)Field_1));                             // 저장된 variant변수를 조작이 편한 char변수에 복사합니다.

        strcpy(szField2,    (char*)((_bstr_t)Field_2));

        // 리스트 컨트롤을 추가하셨거나 화면에 출력하실 생각이 있으시다면

        // 루핑을 돌리며 값을 채워넣는 작업을 이부분에서 하시면 됩니다.

        m_MyAdodb.m_pRs->MoveNext();    // 레코드셋을 다음으로 이동시킵니다.

    }

    m_MyAdodb.m_pRs->Close();           // 레코드셋을 닫아줍니다.

그림으로 해보면..

4.Update를 해보겠습니다.

    _variant_t LRowCount;   // Update가 적용된 레코드의 로우수

    long lrowcount = 0;     // 적용된 variant형을 레코드의 로우수 저장

    char CommandString[400+1]={‘\0’,};

    strcpy(CommandString, “Update Test_Tbl set Field_1 =’01’, Field_2=’02’ Where User=’senosora'”);    // 알맞는 update쿼리를 적용하십시요.

    m_MyAdodb.m_pComm->CommandText = CommandString;

    try

    {

        m_MyAdodb.m_pComm->Execute(&LRowCount,NULL,adCmdText); // Update 실행

    }

    catch(…)

    {

        // 에러일 경우 메세지 박스를 출력하고 update 작업을 종료해 버립니다.(에러가 날 경우는 update문 오류.)

        MessageBox(“Update 오류!”, “SIMPLEDB”, MB_ICONSTOP);

        return;

    }

    lrowcount = atol((char*)((_bstr_t)LRowCount));  // variant형을 long형에 저장

    if(lrowcount == 0) // 업데이트된게 없다.

    {

        MessageBox(“Update 적용된 레코드 없음!”, “SIMPLEDB”, MB_ICONSTOP); 

        return;

    }

여기까지입니다.

제 지식이 DB에 관해 command객체는 어떠어떠하고 레코드셋은 어떠하며 속도가 어떻다라 논할 실력이 된다면야 좋겠지만

그렇다고 책을 그대로 베껴놓을수도 없고.. 해서 배경지식은 적지 못했습니다.

틀린점이 있다면 바로잡아 주시면 감사하겠습니다.

첨부되는 파일은 실행하면 DB가 없으니..에러가 납니다.

그냥 눈으로 보시면 이해가 빠르지 않을까 싶어 첨부합니다.

테스트 하시려면 DB구성부터 하셔야 합니다.

근데.. 주절 주절 해놓고 보니 어째 더 복잡해보이는군요.ㅡㅡ

MYSQL을 사용하시려면 DB연결부분만 MYSQL로 변경해주시면 동일하게 사용이 가능합니다.

추가 : 대충 이런식으로 알고 있습니다. mysql을 잘 모르는데.. localhost라면 mysql은 pwd를 안넣더군요..

        // MySql

        strcpy(ConStr,”Driver={MySQL};server=”);

        strcat(ConStr,DBIP);

        if(strcmp(DBIP, “localhost”) == 0)

        {      

            strcat(ConStr,”;Uid=root;pwd=;Database=yourdbname”);

        }

        else

        {      

            strcat(ConStr,”;Uid=root;pwd=yourpassword;Database=yourdbname”);

        }

            pConn = _ConnectionPtr(“ADODB.Connection”);

            pRS =  _RecordsetPtr(“ADODB.Recordset”);

            pRS->CursorType = adOpenDynamic;

            pComm = NULL;

            pConn->ConnectionString = ConStr;  // 접속 스트링 설정.

            try

            {

                pConn->Open(“”,””,””, adConnectUnspecified);

            }catch(_com_error &e)

            {

_bstr_t bstrSource(e.Source());

_bstr_t bstrDescription(e.Description());

                AfxMessageBox(“MySQL DB 접속 에러”);

                exit(1);

            }

            pComm.CreateInstance(__uuidof(Command));

            pComm->ActiveConnection = pConn;

            pComm->CommandType = adCmdText;

        }

“ADODB”의 139개의 댓글

  1. Spot on with this write-up, I absolutely think this amazing site needs
    much more attention. I’ll probably be returning to read through
    more, thanks for the advice!

  2. I loved as much as you’ll receive carried out
    right here. The sketch is attractive, your authored subject matter stylish.
    nonetheless, you command get got an shakiness over that
    you wish be delivering the following. unwell unquestionably come more formerly again since exactly the same nearly a lot often inside case you shield this
    hike.

  3. Wonderful article! That is the kind of information that are supposed
    to be shared across the net. Shame on Google for no longer positioning this put up higher!
    Come on over and discuss with my web site . Thanks =)

  4. I really like your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
    Plz answer back as I’m looking to design my own blog and would like to know where u got this from.

    cheers

  5. I just want to tell you that I am just newbie to blogging and site-building and seriously liked you’re web blog. Almost certainly I’m want to bookmark your website . You actually come with excellent posts. Thanks a lot for sharing with us your webpage.

  6. What i do not realize is actually how you’re no longer really a lot more well-favored than you may be right now. You are very intelligent. You already know therefore considerably on the subject of this topic, produced me personally consider it from so many varied angles. Its like men and women aren’t fascinated until it is something to accomplish with Lady gaga! Your individual stuffs outstanding. Always deal with it up!

  7. I simply want to mention I’m beginner to blogging and truly enjoyed this blog. Almost certainly I’m likely to bookmark your website . You actually have outstanding stories. Thanks a lot for sharing your webpage.

  8. Hello There. I discovered your blog the usage of msn. That is an extremely well written article. I’ll be sure to bookmark it and return to read extra of your helpful info. Thank you for the post. I will certainly return.

  9. One thing I want to discuss is that weight loss program fast is possible by the suitable diet and exercise. A person’s size not merely affects the look, but also the general quality of life. Self-esteem, melancholy, health risks, in addition to physical skills are influenced in weight gain. It is possible to make everything right but still gain. In such a circumstance, a condition may be the perpetrator. While an excessive amount food and never enough body exercise are usually guilty, common medical ailments and key prescriptions might greatly add to size. Thx for your post right here.

  10. I’m now not certain the place you are getting your information, but good topic. I needs to spend a while studying much more or figuring out more. Thank you for excellent info I was on the lookout for this information for my mission.

  11. I just want to tell you that I’m all new to blogging and actually enjoyed this website. Almost certainly I’m going to bookmark your site . You really come with fabulous article content. Thank you for revealing your web-site.

  12. I seriously love your blog.. Great colors & theme. Did you develop this website yourself? Please reply back as I’m wanting to create my own personal blog and would love to know where you got this from or exactly what the theme is called. Appreciate it!

  13. I’ve been browsing online greater than 3 hours as of late, yet I by no means found any fascinating article like yours. It is lovely worth sufficient for me. In my view, if all web owners and bloggers made good content as you did, the web shall be much more useful than ever before.

  14. Oh my goodness! an amazing article dude. Thanks Nonetheless I’m experiencing problem with ur rss . Don’t know why Unable to subscribe to it. Is there anybody getting similar rss downside? Anyone who is aware of kindly respond. Thnkx

  15. An impressive share! I’ve just forwarded this onto a colleague who was doing a little research on this. And he in fact bought me breakfast due to the fact that I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanx for spending time to discuss this subject here on your web page.

  16. Simply want to say your article is as surprising. The clarity on your put up is just excellent and i can assume you’re a professional in this subject. Well with your permission let me to take hold of your feed to keep updated with imminent post. Thank you one million and please keep up the gratifying work.

  17. It is in reality a nice and useful piece of info. I¡¦m glad that you simply shared this useful information with us. Please stay us up to date like this. Thank you for sharing.

  18. I actually wanted to compose a word so as to express gratitude to you for those precious recommendations you are giving out on this site. My time-consuming internet research has at the end been compensated with reliable knowledge to share with my friends and family. I would assert that many of us website visitors are unequivocally lucky to exist in a fabulous community with so many special professionals with good basics. I feel somewhat lucky to have seen your entire website page and look forward to many more enjoyable moments reading here. Thanks a lot again for all the details.

  19. I’ve been exploring for a little for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I ultimately stumbled upon this website. Studying this information So i’m satisfied to convey that I’ve a very excellent uncanny feeling I found out exactly what I needed. I most unquestionably will make certain to don’t omit this website and provides it a glance a continuing.

  20. I would like to thank you for the efforts you have put in penning this blog. I’m hoping to view the same high-grade blog posts from you in the future as well. In fact, your creative writing abilities has inspired me to get my own, personal website now 😉

  21. Comfortably, the news post is during truthfulness a hottest on this subject well known subject matter. I agree with ones conclusions and often will desperately look ahead to your updates . Saying thanks a lot will not just be sufficient, for ones wonderful ability in your producing. I will immediately grab ones own feed to stay knowledgeable from any sort of update versions. Fantastic get the done and much success with yourbusiness results!

  22. I’ve been exploring for a bit for any high quality articles or blog posts in this sort of area . Exploring in Yahoo I finally stumbled upon this web site. Reading this information So i’m satisfied to convey that I have an incredibly just right uncanny feeling I came upon just what I needed. I most undoubtedly will make sure to don’t fail to remember this site and provides it a glance regularly.

  23. Oh my goodness! an incredible write-up dude. Thanks a ton However We are experiencing problem with ur rss . Don’t know why Struggling to sign up for it. Is there everyone finding identical rss problem? Anybody who knows kindly respond. Thnkx

  24. Just wish to say your article is as amazing. The clarity in your post is just excellent and i can assume you are an expert on this subject. Well with your permission allow me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please continue the gratifying work.

  25. An interesting discussion is worth comment. I think that you should write extra on this topic, it may not be a taboo topic but typically people are not sufficient to speak on such topics. To the next. Cheers

  26. It’s appropriate time to make a few plans for the longer term and it is time to be happy. I have learn this put up and if I may I desire to counsel you some fascinating issues or tips. Perhaps you can write subsequent articles regarding this article. I want to learn even more issues about it!

  27. Hi this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding knowledge so I wanted to get guidance from someone with experience. Any help would be greatly appreciated!order now

  28. Nice post. I learn one thing more challenging on totally different blogs everyday. It will at all times be stimulating to learn content material from different writers and apply just a little one thing from their store. I’d desire to make use of some with the content material on my blog whether or not you don’t mind. Natually I’ll offer you a link in your web blog. Thanks for sharing.

  29. Hello just wanted to give you a quick heads up and let you know a few of the images aren’t loading properly. I’m not sure why but I think its a linking issue. I’ve tried it in two different browsers and both show the same results.

  30. There are actually a whole lot of details like that to take into consideration. That may be a great point to deliver up. I supply the ideas above as basic inspiration but clearly there are questions like the one you convey up where a very powerful factor will probably be working in honest good faith. I don?t know if greatest practices have emerged round things like that, but I am certain that your job is clearly identified as a good game. Each girls and boys really feel the impact of only a second’s pleasure, for the rest of their lives.

  31. Can I just say what a comfort to discover somebody that truly knows what they are talking about on the web. You certainly know how to bring an issue to light and make it important. A lot more people ought to look at this and understand this side of the story. It’s surprising you’re not more popular because you certainly possess the gift.

  32. After looking into a handful of the blog articles on your web site, I truly like your way of writing a blog. I saved it to my bookmark webpage list and will be checking back soon. Take a look at my web site as well and tell me your opinion.

  33. I must say, as a lot as I enjoyed reading what you had to say, I couldnt help but lose interest after a while. Its as if you had a wonderful grasp on the subject matter, but you forgot to include your readers. Perhaps you should think about this from far more than one angle. Or maybe you shouldnt generalise so considerably. Its better if you think about what others may have to say instead of just going for a gut reaction to the subject. Think about adjusting your own believed process and giving others who may read this the benefit of the doubt.Jobs offered

  34. Someone essentially help to make seriously articles I would state. This is the very first time I frequented your web page and thus far? I amazed with the research you made to make this particular publish amazing. Magnificent job!

  35. Yesterday, while I was at work, my sister stole my iphone and tested to see if it can survive a 40 foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views. I know this is completely off topic but I had to share it with someone!

  36. Pretty great post. I simply stumbled upon your blog and wished to mention that I have truly loved surfing around your blog posts. After all I will be subscribing for your rss feed and I’m hoping you write once more soon!

  37. My developer is trying to convince me to move to .net from PHP. I have always disliked the idea because of the costs. But he’s tryiong none the less. I’ve been using Movable-type on a number of websites for about a year and am concerned about switching to another platform. I have heard fantastic things about blogengine.net. Is there a way I can import all my wordpress posts into it? Any kind of help would be really appreciated!

  38. I liked up to you’ll obtain performed right here. The comic strip is tasteful, your authored subject matter stylish. nevertheless, you command get bought an edginess over that you would like be delivering the following. sick no doubt come further beforehand again since exactly the same nearly a lot incessantly inside case you shield this hike.

  39. Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I am going to revisit once again since i have saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to help other people.

  40. Having read this I believed it was really enlightening. I appreciate you spending some time and effort to put this content together. I once again find myself personally spending way too much time both reading and posting comments. But so what, it was still worthwhile!

  41. Hi there! I know this is somewhat off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having problems finding one? Thanks a lot!

  42. Thanks for sharing superb informations. Your website is very cool. I’m impressed by the details that you have on this site. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found simply the info I already searched everywhere and just couldn’t come across. What a perfect web site.

  43. Thanks for the marvelous posting! I actually enjoyed reading it, you’re a great author.I will remember to bookmark your blog and will come back down the road. I want to encourage that you continue your great writing, have a nice afternoon!

  44. Hey! This is my first visit to your blog! We are a group of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done a outstanding job!

  45. I do agree with all of the ideas you’ve presented in your post. They are really convincing and will certainly work. Still, the posts are too short for starters. Could you please extend them a little from next time? Thanks for the post.

  46. Oh my goodness! Amazing article dude! Thank you, However I am encountering problems with your RSS. I don’t know why I cannot subscribe to it. Is there anybody else having similar RSS problems? Anyone that knows the answer will you kindly respond? Thanx!

  47. This is the right website for anybody who wants to understand this topic. You know so much its almost hard to argue with you (not that I really would want to…HaHa). You certainly put a brand new spin on a subject that’s been discussed for years. Wonderful stuff, just wonderful.

  48. Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your blog posts. Any way I’ll be subscribing to your augment and even I achievement you access consistently rapidly.

댓글 남기기