-
ADO or ODBC?
Hi there.
I have some experience with Win 32 and MFC, but I have never programmed with data base. I have task to write a program which is written in MFC and use access data base, so ask you which i better ADO or ODBC? And which is easier, so I can learn it
fast enought.
Thank you in advance.
-
MFC has a decent enough DAO wrapper.
For Ado you need to either use straight COM or the MFC wrappers for activeX objects.
I'd go with ADO over ODBC as it's better supported and you can do more with it
-
Thank you, but do you know where I can find resources on this topic?
-
-
Thank you. But I finally chose ODBC.
I wrote "odbc c++: in Google, as you told me to do with "ado c++" :))
I learned the basics with the MFC classes for ODBC. But I don't know if I
copy my program to another pc, which files I must give? The .mdb file, the .exe
and .dll-files, Is there something more that I must copy?
Is any registration of the DB required, as I did to make datasource?
-
Depends if the Dll is COM related - if not then you shouldnt need registration.
Some commercial apps I know of that use ODBC distribute the MDAC redistributable file that can be found on MSDN - this should have everything needed for ODBC (as well as OLEDB and ADO).
-
What about datasource, which I made from
Start Menu->Control Panel->Administrative Tools->Data Sources
(ODBC) ? How can I make it on another machine automatically?
-
Not sure...should be part of the ODBC Api...
That's one of the reasons I like ADO...you can load the MSJET (or whatever) driver on the fly without any need for datasources
-
-
I cant find one of my examples of using ADO using C++, so I knocked this together
It works with VC++. You need to pass the FULL PATH of the database (as provided) as a parameter once you have built the exe file
-
>>What about datasource, which I made from
Start Menu->Control Panel->Administrative Tools->Data Sources
(ODBC) ? How can I make it on another machine automatically?
Like so
Code:
CString Attrib;
int iRet=0;
Attrib.Format("%s","DSN=DamageMod;DBQ=C:\\databases\\DatabaseName.mdb;DESCRIPTION=Test;READONLY=FALSE;EXCLUSIVE=FALSE;");
//this adds a SYSTEM DSN for the database to use
iRet=::SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"Microsoft Access Driver (*.mdb)",Attrib);
if(!iRet)
{
iRet=GetLastError();
-
OK. I learned how to use CDatabase and CRecordset.
But I have question. How much of the SQL standart is supported
by this classes. Can I use LEFT/RIGHT JOIN in CRecordset::Open()
and nested queries in CDatabase::ExecuteSQL().
And if this functionality is not supported directly how can I use it?
-
-
I tried today to use one INSERT query like this:
Code:
INSERT INTO Sometable(PersonID,...) VALUES(
(SELECT Id FROM People WHERE Name='some name'),...)
And it says:
Syntax error in INSERT INTO statement.
And I don't know weather or not my error is in the syntax
or the CDatabase don't support nested queries.
-
Here is a good JET SQL reference.
As you can see, you need to lose the VALUES() for a multiple record insert.
-
I don't want to make a multiple record insert, but to insert one row in the table.
With the nested SELECT query I want to get an Id from another table.
-
Did you read the link I posted? The multiple record syntax can insert one or more records.
If you have to insert values from another table as well as literal values you will have to get creative:
Code:
INSERT INTO SomeTable (PersonID, FavouriteFood, Location)
SELECT ID, 'Fried Carrots', 'Jail' FROM People WHERE Name='some name';
or maybe:
Code:
INSERT INTO SomeTable (PersonID, FavouriteFood, Location)
SELECT ID, "Fried Carrots" AS FavouriteFood, "Jail" AS Location FROM People WHERE Name='some name';
-
Sorry, I took a look, but I obviously missed this. Now I understand. Thank you.
This question could be a little bit sily, but in the documentation
doesn't say anything about:
What about if I want to run SELECT query on two or more
tables to get Id's?
-
Well, you should be able to use the normal table joining syntax (INNER JOIN, simple WHERE join, etc). How do you need to combine the two tables?
-
OK. I have three tables:
1)Reasons with Id AUTOINCREMENT and Reason TEXT
2)People with Id AUTOINCREMENT, names TEXT, address TEXT
3)Orders with Id AUTOINCREMENT,PersonId INT, ReasonId INT, Value FLOAT and OrderDate Date
I want to INSERT a record in Orders like this:
Code:
INSERT INTO Orders(PersonID,ReasonID,Value,OrderDate)
VALUES((SELECT Id FROM People WHERE Names='some name'),
(SELECT Id FROM Reasons WHERE Reason='some reason'),1.50,
'1/2/2004');
This query works with Postgres SQL, but not with Access throught
ODBC. I guess that nested queries are not allowed and do it with
separate queries, but is there any way with one SQL statement?
And I need LEFT/RIGHT JOIN to query the Orders table after that.
(To get the Names throught PersonId, for example)
-
You can probably just use a table combine. When you don't provide a join condition jet combines all possible combinations. You can then add the where clause to only get the row you are interested in.
For example if you have table 1 with data items '1a', '1b', '1c' and table 2 with data items '2a' '2b' jet creates from:
Code:
Select table1.col1, table2.col1 FROM table1, table2
something like this:
Code:
1a 2a
1a 2b
1b 2a
1b 2b
1c 2a
1c 2b
All the possible combinations are created! If you don't use a WHERE clause you could, in theory, get a massive result set. A two column result set created from two tables with 10000 records each would give you a hundred million records.
Anyway, with this behaviour in mind, you would use something like this:
Code:
INSERT INTO Orders(PersonID, ReasonID, Value, OrderDate)
SELECT People.Id, Reasons.Id, 1.5, '1/2/2004'
FROM People, Reasons WHERE People.Names='some name' AND Reasons.Reason='some reason';