SQLCHAR* and const char

[freddie1]

I got looking at the printed output from my printf where I'm outputting Sql.szCnOut, which is the string the database driver returns after successfully connecting, and I noticed a difference between my ingoing connection string and the outgoing one. There are '{' and '}' brackets on each side of the driver description that DSTR3A's code has, but my connectiion string building method isn't putting in. In other words this...

DRIVER=Microsoft Access Driver (*.mdb, *.accdb);

instead of this

Driver={Microsoft Access Driver (*.mdb, *.accdb)};

Something like this could be making a difference Lamblion. It is not unusual for one version of a driver to let something 'pass' that another one won't. I mean, it works for me either way, but it could be Access 2010 requires the brackets?

I updated my MakeConnectionString() class method to put the brackets on either side of the driver description. Here that is...

void SQL::MakeConnectionString(void)
{
 if(strDriver=="SQL Server")
 {
    if(strDBQ=="")
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";";
    }
    else
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";"+ \
       "DATABASE=" + strDatabase + ";" + "DBQ=" + strDBQ + ";";
    }
 }
 else if(strDriver=="Microsoft Access Driver (*.mdb, *.accdb)")
 {
    strConnectionString="DRIVER={";
    strConnectionString=strConnectionString+strDriver+"};"+"DBQ="+strDBQ+";";
 }
 else if(strDriver=="Microsoft Excel Driver (*.xls)")
 {
    strConnectionString="DRIVER=";
    strConnectionString=strConnectionString+strDriver+";"+"DBQ="+strDBQ+";";
 }
}

[Lamblion]

freddie1: Do you mind if I post your code on the MS VC++ Language Forum? Here's the link --

http://social.msdn.microsoft.com/forums/en-US/vclanguage/threads/

I was thinking about posting your main.cpp code and see if anyone there can tell us what's going on.

[naraku9333]

Have you tried the sample code from http://archive.msdn.microsoft.com/odcac2010ta/Release/ProjectReleases.aspx?ReleaseId=4908 I run VS2010 and Office2010, opened the direct odbc project, converted it to 2010 changed the path to example dbase. Intellisence gives an "error" about const char* conversion to SQLCHAR* but the project still runs, and worked fine.

[freddie1]

No problem, go ahead Lamblion. I want to know the problem too.

Did you try my bracket thingie I described above? That's worth a shot. Otherwise I'm
thinking perhaps Access 2010 needs more data in the connection string than I'm giving
it with my DRIVER= and DBQ=. In DSTR3A's connection string I believe he has a UUID
or PWD or something. Given the increased emphasis on security over the years it isn't
unreasonable to think Microsoft might require more of that.

There really are two issues you are having. Is the iCreateDB() function still failing
for you or did you get that working? I'm sure you looked at my code and saw that if
iCreateDB() fails there won't even be an attempt to Connect?

I just looked at and downloaded the code naraku provided a link to. The conclusion
one must come to from that and from DSTR3S's code is that ODBC indeed works with
Microsoft's 2010 products. Unfortunately, from my end there isn't too much more I can
do to solve this problem because everything is working for me. There are two other
folks in my office who are presently out but my trial of my code on their machines
wouldn't solve anything because they have the same software setups I have.

[Lamblion]

Yes!!! Thank you for that code. I compiled with VC++ 2010 without warnings or error, and it opened the Northwind database and displayed its content.

I have to figure out how to create a database as freddie1 has done, though. But at least we're making progress.

BTW, DSTR3A, your code also compiled and ran with VC++ 2010.

[Lamblion]

Yeah, I tried freddie1. I may post your main.cpp over there a bit later. Right now I've got to break away and do some other things. But like I said, we're making progress!

Thank for all your very hard work. And that goes for the rest you who have helped as well.

I think this forum is getting its workout on this thread!!!

[Lamblion]

freddie1: Monitor the forum link I gave you, as I posted your main.cpp code there with an explanation. I suspect someone will see what's wrong.

[freddie1]

Hello, Lamblion!

If you aren't completely wore out fighting software another possible issue just occurred
to me. The way it occurred to me is I started to provide a quickie little program for you
that just illustrated how simple it is to create an access database with SQLConfigDataSource()
if everything is working as it should. SQLConfigDataSource is the ODBC function that actually
creates the database, and it is the key component of iCreateDB(). As soon as I started to write the code I immediately realized that I hadn't updated the creation string we were feeding into SQLConfigDataSource() to reflect that second '*.accdb' file extension we were putting in the driver description string. Recall I had modified this...

DRIVER="Microsoft Access Driver (*.mdb);"

to this...

DRIVER="Microsoft Access Driver (*.mdb, *.accdb);"

...in the hope that that *.accdb was the addition Access 2010 needed for my program to work. So anyway, here is a real little program for you to try whenever you are interested in this again. I'm hoping it might finally create the database for you so we could get to the next step which is connecting. In the code below iInstallerError was never called because it worked for me but whether it will work for you I don't know but I hope! Just to avoid any possible confusion, all this program should do is create the TestData.mdb database. Also, if it doesn't work the first time, try modifying the database name to TestData.accdb. We hadn't tried that either. I really suspect there is some really small thing going on here the fixing of which will get the whole thing working.

//Link with MS - odbccp32.lib ;   GNU MinGW - libodbccp32.a 
#include <windows.h>
#include <stdio.h>
#include <odbcinst.h>

unsigned int iInstallerError()
{
 DWORD pErr;
 char szErrMsg[512];
 WORD  cbMsgBuffer=512;
 WORD  cbRet;
 WORD  wErrNum=1;

 while(SQLInstallerError(wErrNum,&pErr,szErrMsg,cbMsgBuffer,&cbRet)!=SQL_NO_DATA)
 {
  printf("wErrNum    = %d\t",wErrNum);
  printf("szErrMsg = %s\n",szErrMsg);
  wErrNum++;
 };

 return (unsigned int)pErr;
}

int main()
{
 char szBuffer[512],szCreate[512];

 strcpy(szCreate,"CREATE_DB=");        //SQLConfigDataSource() just basically takes a few
 GetCurrentDirectory(512,szBuffer);    //parameters the most important of which is the
 strcat(szCreate,szBuffer);            //path and name of the database you want created
 strcat(szCreate, "\\TestData.mdb");   //and it creates the file.
 printf("%s\n",szCreate);                                             //  <!!!>
 if(SQLConfigDataSource(0,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb, *.accdb)",szCreate))
    printf("SQLConfigDataSource() returned TRUE!\n");
 else
    printf("iInstallerError() = %d\n",iInstallerError());
 getchar();

 return 0;
}

/*
Output:
========================================================
CREATE_DB=C:\Code\CodeBlks\ConfigDataSource\TestData.mdb
SQLConfigDataSource() returned TRUE!
*/

[Lamblion]

Thanks again, freddie1, but unfortunately I still get this output --

CREATE_DB=C:\Programming\VS 2008\Win Console\create_database 01\Debug\TestData.m
db
wErrNum    = 1  szErrMsg = Driver's ConfigDSN, ConfigDriver, or ConfigTranslator
 failed
iInstallerError() = 11 

I think it has to do with the info in the string. Look at this string and compare it, for this string is the one given by MS to connect to their Northwind DB. Maybe you can see if there's any relevance because I don't know enough to tell --

SQLCHAR szDSN[256] = 
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\Programming\\Win Console Apps\\Database\\directODBC\\Debug\\Northwind.accdb;PWD=1L0v3Acce55;";

I know that connecting and creating are two different things, but perhaps the above will tell you something.

[freddie1]

I'm pretty sure 11 is the code returned if TestData.mdb already exists at that location. In other words, the database was successfully created at some point and the function succeeded. Did you open the directory in Explorer and see if TestData.mdb was there?

Yes, creation of the database has absolutely nothing to do with connecting.

[freddie1]

Yep! Just double checked. 11 is the number returned by SQLInstallerError when an attempt is made to create a database is made and the database already exists at that location. In that other complex GUI example I had posted over in Jose Roca's Forum several years ago I based program logic on that number. If 11 was returned from the error handler my program added four new records to whatever number existed in the database, and the table creation step wasn't carries out (the DB and table were already there).

[Lamblion]

Nope, there has never once been a database created. I open the folder where the exe is and run it straight from there, so I'm positive that the DB is not being created.

I'm going to bed, but if you get it worked out, I'll check it first thing in the morning. Thanks again for the hard work.

[freddie1]

Can't seem to get off the problem, although I'd better try soon. I've other things to do. I just recently posted a description of the problem over in the PowerBASIC forum with a PowerBASIC program calling SQLConfigDataSource() and a request for someone to test it who only has Access 2010 installed....

http://www.powerbasic.com/support/pbforums/showthread.php?t=48830

I've a one track mind and can only deal with one problem at a time but there are two problems...

1) Why is SQLConfigDataSource failing when Access 2010 is installed?
2) Why is my connection string failing.

[Lamblion]

I confess, I've been playing with it all morning myself. I even hard-coded the path into it instead of using GetCurrentDirectory() and I still can't get it to work.

I still believe the driver string I gave you earlier may provide the clue, but you are the one with the knowledge to make sense out of it. I can't, as of yet.

[Lamblion]

Just to make sure we are complete on the same page, here the EXACT code I'm using in VS 2008 --

//Link with MS - odbccp32.lib ;   GNU MinGW - libodbccp32.a 
#include <windows.h>
#include <stdio.h>
#include <odbcinst.h>

unsigned int iInstallerError()
{
 DWORD pErr;
 char szErrMsg[512];
 WORD  cbMsgBuffer=512;
 WORD  cbRet;
 WORD  wErrNum=1;

 while(SQLInstallerError(wErrNum,&pErr,szErrMsg,cbMsgBuffer,&cbRet)!=SQL_NO_DATA)
 {
  printf("wErrNum    = %d\t",wErrNum);
  printf("szErrMsg = %s\n",szErrMsg);
  wErrNum++;
 };

 return (unsigned int)pErr;
}

int main()
{
 if(SQLConfigDataSource(NULL,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb, *.accdb)","CREATE_DB=C:\\Programming\\VS 2008\\Win Console\\create_database 01\\Debug\\TestData.mdb"))
    printf("SQLConfigDataSource() returned TRUE!\n");
 else
    printf("iInstallerError() = %d\n",iInstallerError());
 getchar();

 return 0;
}

[freddie1]

I just created that exact directory structure on my work laptop which is a Win 7 32 bit, i.e.,

C:\Programming\VS 2008\Win Console\create_database 01\Debug

and I compiled and ran the exact program you have above twice. The first time I ran it from C:\Code\CodeBlks\ConfigDataSource, which is where I created the Code::Blocks project, and the second time I ran it from that exact directory by copying it there and executing it with a shortcut. IT FAILED BOTH TIMES.

I really, really, really, really, really, really believe, you need to try running it from a location without spaces in the directory path.

[freddie1]

Just made another test. I created this directory structure...

C:\Code\I Deplore Spaces In File Names

...and I tried to run my program (the one with GetCurrentDirectory()) from it, and it failed. Then I replaced the spaces with underscores and it worked perfectly, i.e.,

C:\Code\I_Deplore_Spaces_In_File_Names\ConfigDataSource.exe

[Lamblion]

I created a directory "C:Test", put the exe in it, ran it, and here is the output --

wErrNum    = 1  szErrMsg = Driver's ConfigDSN, ConfigDriver, or ConfigTranslator
 failed
iInstallerError() = 11 

So the spaces have nothing to do with on my system.

OOPS: UPDATE

When I copied the exe, and vs 2008 project files into the folder it WORKED!!!

But I have no idea why the spaces would affect this??? That's a problem, I would think.

[freddie1]

That's a problem, I would think.

You better believe its a problem. But a known one.


Under no circumstances, not under penalty of death by torture, being boiled in oil, or any other aweful circumstances, would I ever put my databases in directory structures with spaces in them.

Not if paid to do it, threatened with death by firing squad, being subjected to non-stop rap music, nothing, would cause me to do that.

Oh! And did I mention I wouldn't put my databases in directory structures with spaces in them? If I haven't, then I'll mention it. I wouldn't do that or advise doing it.

Windows over the years has gotten better and better at handling the situation of spaces in file names, but its still not perfect. It really doesn't surprise me that SQLConfigDataSource() is sensitive to that because that is a function introduced in the very earlies days of ODBC I'd guess in the early 90s when DOS and short file names were still widespread. My guess is that function hasn't seen a lot of work by MS in a very long time.

Does that solve the rest of your problem?

[Lamblion]

Yes, it solves my problem in that from now on I'm going to be as fanatical as you with regard to spaces in folders and file name with regard to ANYTHING I'm coding!!!