I would like to add a database to my program. How can I do this.

Hi experts,
I have no experience with databases concerning c++ (some MySQL and PHP).
I am working on a cross-platform application (Windows, UNIX).
Now that it is working I would like to add database support to my app in both,
UNIX and Win. There is an Oracle database on the UNIX server.
How can I connect to a database using MFC, ANSI C++, ... ??
Is it necessary to locally set-up the database, too?
I am missing vocabluary in the context, so please give me as much ideas,
as possible. Maybe also a tutorial or two.
Thanks,
Jens
LVL 5
allmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkrCommented:
I'd suggest checking out Oracle's site at http://www.oracle.com/technology/software/products/ids/index.html ("Oracle Developer Suite") where you can download their 'SDK' and will find downloads, documentation, samples etc. for a variety of platforms. Also, http://www.oracle.com/technology/sample_code/tutorials/index.html ("Tutorials") seems to be a good starting poijnt.
0
Jaime OlivaresSoftware ArchitectCommented:
0
allmerAuthor Commented:
I don't seem to understand what I need to get me started.
I guess I would need to install the database application itself.
Would there be something that tells me step by step what to
do in order to set everything up to use Visual Studio .net for
the programming with a recent oracle database?
Cheers,
Jens
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

AxterCommented:
Check out the following link for free downloads of Oracle 10g:
http://www.oracle.com/technology/software/products/database/oracle10g/index.html

Here is a link for documentation on all 10g software including the installation guides.

http://otn.oracle.com/documentation/database10g.html

0
itsmeandnobodyelseCommented:
You could use ODBC interface for both platforms:

  - Install Oracle ODBC client Windows and UNIX (comes with Oracle installation)
  - Create a DataSource (Windows using odbcad32.exe, UNIX check ODBC client)
  - Use include file sqlext.h (Windows/Unix?), sql.h (Unix?)
  - libraries in Windows are odbc32.lib and odbccp32.lib

Tell me, if you need more information.

Regards, Alex
0
allmerAuthor Commented:
Nice,
I am downloading two "CDs" from oracle at the moment.
They are supposed to contain the developer's suite.
I guess I'll need some more help when done with the installation.
Thanks so far,
Jens
0
eranifCommented:
i would use ODBC on Windows - just install the Oracle driver and then create a DSN (Data Source Name).

IN the DSN you define your schema password and the service name.

EI



0
allmerAuthor Commented:
Good morning everyone,
So I installed oracle 10g for developers.
If I now start the Oracle Discoverer Administrator it asks a password and login
which I don't have.
Where do I turn to setup these?
0
allmerAuthor Commented:
Ok,
I have a database, Username and a login now.
Of course I also have a space where the stuff is located.
Where do I go from here?
I guess I need some classes there,
where do I find them?
Jens
0
allmerAuthor Commented:
Can I have some more information on ODBC?
Can I connect to an access database using that?
0
allmerAuthor Commented:
If I use these two headers:
#include <sql.h>
#include <sqlext.h>
it sure compiles under windows. But won't compile in UNIX.
Which headers do I have to use there?
Do they contain the same library?
-Jens
0
itsmeandnobodyelseCommented:
>> Which headers do I have to use there?

That differs between unix platforms. AFAIK, you need sql.h, sqlext.h, sqlunix.h and sqlopt.h. At AIX it is sql2.h (because of DB2 database).

>> Do they contain the same library?

Same library as in Windows? No, of course not. Check your library path. The name of the lib should be either be prefixed by 'odbc' or 'sql'.

I found the following link with google:

   www.dbmaker.com/download/manual/odbc.pdf

that seems to provide the information you are looking for.

Regards, Alex


0
allmerAuthor Commented:
Very nice tutorial Alex.
One thing they all fail to explain is how to convert data types.
Maybe I don't know how the SQL statement will look like at programming time.
So I end up with something like a list<string> where each string is an SQL command.
Then I try something like:

bool CPeptideDatabase::ExecuteSQL(list<string> lst) {
      string q;
      char text[1024];
                //char *text = new char[1024];
      for(list<string>::iterator i=lst.begin(); i!=lst.end(); i++) {
            q = *i;
            sprintf(text,"%s\0",q.c_str());
            SQLTCHAR query[] = _T(text);
            retcode = SQLPrepare(hstmt,query,sizeof(query));
            if(retcode == SQL_ERROR)
                  return(false);
      }
      return(true);
}

And of course it fails to compile.
How is the conversion done?
Must be very tough since otherwise it should be explained somewhere.
Jens
0
itsmeandnobodyelseCommented:
First, i would say, forget the _T and SQLTCHAR stuff. AFAIK, neither ORACLE nor MYSQL do anything with wide strings (it could have changed in recent times, but 2000 only SQLSERVER could stor UNICODE strings).

Next, if you want to execute SQL statements, i would use SQLExecDirect rather than SQLPrepare. SQLPrepare is used tp *prepare* an SQL statement that has placeholders like in

       "UPDATE Parts SET Price = ? WHERE PartID = ?"

Then, you could replace the '?' terms at runtime using SQLBindParameter(..). However, you have to hold the statement handle passed to SQLPrepare and use SQLExecute to finally excute the prepared statement. All that you can avoid by uing SQLExecDirect instead (it is a little bit slower because it has to parse the SQL statement, but that shouldn't count on modern hardware).

MSDN says:

------------------------------------ MSDN -----------------------------------------------
Prepared Execution
Prepared execution is an efficient way to execute a statement more than once. The statement is first compiled, or prepared, into an access plan. The access plan is then executed one or more times at a later time. For more information about access plans, see &#8220;Processing an SQL Statement&#8221; in Chapter 2, &#8220;An Introduction to SQL and ODBC.&#8221;

Prepared execution is commonly used by vertical and custom applications to repeatedly execute the same, parameterized SQL statement. For example, the following code prepares a statement to update the prices of different parts. It then executes the statement multiple times with different parameter values each time.

SQLREAL   Price;
SQLUINTEGER PartID;
SQLINTEGER  PartIDInd = 0, PriceInd = 0;

// Prepare a statement to update salaries in the Employees table.
SQLPrepare(hstmt, "UPDATE Parts SET Price = ? WHERE PartID = ?", SQL_NTS);

// Bind Price to the parameter for the Price column and PartID to
// the parameter for the PartID column.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
       &Price, 0, &PriceInd);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 10, 0,
       &PartID, 0, &PartIDInd);

// Repeatedly execute the statement.
while (GetPrice(&PartID, &Price)) {
 SQLExecute(hstmt);
}
-------------------------------- MSDN --------------------------------------------------------------



>> And of course it fails to compile.

Can you post the compiler errors? Maybe its only a SQL_NTS that means *null terminated string* instead of sizeof(query) that has to be changed?

>>     if(retcode == SQL_ERROR)
>>            return(false);

You need to implement a better error handling, like that:

//////////////////////////////////////////////////////////////////////////
Bool OdbcEnvironment::checkSqlReturn(RETCODE ret, String* pInfo, Bool bGetInfo, Bool bUpdate)
{
   if (ret == SQL_SUCCESS || (ret == SQL_SUCCESS_WITH_INFO && !bGetInfo))
      return True;

   if (pInfo == NULL)
      return False;

   SQLHSTMT& hStmt = bUpdate? m_hStmtUpd : m_hStmt;
   UChar szSqlState[10];
   UChar szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
   Long  nativeError;
   Short nLenErrorMsg;

   String&  info = *pInfo;
   String   newLine;

   if (ret == SQL_SUCCESS_WITH_INFO)
      info = "SQL_SUCCESS_WITH_INFO " + info;
   else
      info = "SQL_ERROR " + info;

   for (;;)
   {
       if (SQLError(m_hEnv, m_hDbc, hStmt, szSqlState, &nativeError,
          szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &nLenErrorMsg) != SQL_SUCCESS)
          break;


       info << newLine << " | " << String((CharPtr)szSqlState) <<
                        " : " << String(nativeError) << " | " << String((CharPtr)szErrorMsg);
       if (newLine.isEmpty())
          newLine = "\n\t";
   }
   return False;
}

Note, that is only a sample code that won't compile on your system. I used some special typedefs like UInt, Bool, CharPtr, ...  and a string class that was capable to act like a string stream. But, i think it is not difficult to adopt it to STL.
     

Regards, Alex

P.S.  Could you post some SQL statements as well?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allmerAuthor Commented:
Sorry, that I come back to this question so late.
After some research I did, I found that there are easier ways for me to get my data to a database.
I am going to write XML formated text now. Which can easily be uploaded to the database I am using.
Furthermore there are ways to use xml files as a database.
Thanks for your help.
Jens
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.