ODBC DSN-less connection

I am trying to connect directly to my visual FoxPro database without using a datasource.
With a datasource it worked fine, but I can not get it to work without one. Can someone check my connection code line here:

Code:

SQLDriverConnect(hdbc, NULL, (unsigned char*)"Driver={Microsoft FoxPro VBF Driver}; SourceType=DBF; SourceDB=data/CATADA.DBF; Exclusive=No \0", SQL_NTS, NULL, 0, NULL, 0);

or give me exact code to connect to a database without a datasource. I went to a number of MSDN pages but none of them helped me figure it out.

thanks a lot,
esaptonor

What error return are you getting?
What does GetLastError() return?

I just get my apps to create their own DSN at runtime.



Is your path correct?
Shouldn't it be, for a DB file 'CATADA.dbf' in the subfolder 'data' off the working folder;

Code:

SQLDriverConnect(hdbc, NULL, (unsigned char*)"Driver={Microsoft FoxPro VBF Driver}; SourceType=DBF; SourceDB=.\\data\\CATADA.DBF; Exclusive=No \0", SQL_NTS, NULL, 0, NULL, 0);

SDIT:
I suggest printing the 'InConnectionString' to a string / char to ensure that it contains what you think it does....

hmm...well for starters I found it was a .dbc not .dbf :|
But that didn't solve the problem. I continued to search around and try different things, but I just can not seem to get it working.

Can you explain how your application created its own DSN at runtime? that sounds like a good alternative.

thanks,
esaptonor

something like

Code:

iRet=SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN, (LPSTR) "SQL Server",
						 (LPSTR)"DSN=TestingMyDSN\0"
						 "Server=192.168.0.1\0"
						 "Database=MY_DB_NAME\0"
						 "Trusted_Connection=NO\0"
						 "Description=This is the DSN for my app\0");
if(!iRet)
{
          //error check
          iRet=GetLastError();

//remove DSN when finished?
SQLConfigDataSource(NULL,ODBC_REMOVE_SYS_DSN,NULL,(LPSTR)"TestingMyDSN");

You will have to change the parameters to meet your DB settings and type (ie not SQLServer, file DSN ect).
I assume you know where to check the DSN settings under Administrative Tools.
I assume you know that some corp machines will have security to block these changes to the registry.

EDIT:
Each section of the string must be null terminated and so the end of the string will be double null terminated.
I have a loop that adds the params to a string as the user will change/supply the DSN settings in the app during runtime.

Code:

char		szAttrib[1028]={'\0'};
char		*pAttrib=NULL,*pParam=NULL;

_snprintf(szDSN,127,"DSN=%s",szSuppliedDSN);
_snprintf(szServer,127,"Server=%s",szSuppliedServer);
_snprintf(szDatabase,127,"Database=%s",szSuppliedDatabase);
_snprintf(szConnection,127,"Trusted_Connection=NO");
_snprintf(szDescription,127,"This is the DSN For %s",szAppName);
pAttrib=szAttrib;
for(i=0;i<5;i++)
{
	switch(i)
	{
		case 0:
			pParam=szDSN;
			iLen=lstrlen(szDSN);
		break;
		case 1:
			pParam=szServer;
			iLen=lstrlen(szServer);
		break;
		case 2:
			pParam=szDatabase;
			iLen=lstrlen(szDatabase);
		break;
		case 3:
			pParam=szConnection;
			iLen=lstrlen(szConnection);
		break;
		case 4:
			pParam=szDescription;
			iLen=lstrlen(szDescription);
		break;
	}
	//null terminate
	pParam[iLen]='\0';
	//add to the attrib string
	strcpy(pAttrib,pParam);
	pAttrib+=iLen+1;
}

thanks, I will look into this

hmm...its returning error 11. No idea what that is, I shall investigate further.

Its worth noting that your code works fine, its just this visual fox pro thing that doesn't work.

Its something to do with the database location... When I just have

Code:

rc=SQLConfigDataSource(hwnd,ODBC_ADD_DSN,(LPSTR)"Microsoft FoxPro VFP Driver (*.dbf)",
						 (LPSTR)"DSN=cadatatest\0\0");

it opens a dialog box asking for the additional information, but if I use

Code:

rc=SQLConfigDataSource(hwnd,ODBC_ADD_DSN,(LPSTR)"Microsoft FoxPro VFP Driver (*.dbf)",
					(LPSTR)"DSN=cadatatest\0Database=C:\\data\\CADATA.DBC\0\0");

it just fails and returns error code 11, which corresponds to ODBC_ERROR_REQUEST_FAILED.


EDIT: The problem was I had to write DB= instead of Database =

Originally Posted by esaptonor

EDIT: The problem was I had to write DB= instead of Database =

Hmmmmm......MS being inconsistent?

I checked my code again and for Access and SQL Server I use 'Database'.

Glad you got it to work (you did get it to work?)

yes it all works great now, thanks a lot!

For the record, Visual Foxpro databases require DNS=, SourceType=, Description=, and DB= in that string. That was half the problem right there!