ODBC - text files - schema.ini

[PeterK]

I know that the schema.ini file is used when accessing text files through ODBC. The schema.ini file is created by using Control Panel then going into ODBC.

Is there some way to programmically create the schema.ini file? I just need the basic entries to show up. SQLConfigDataSource does not appear to be the way to create this file, although many of the schema.ini settings are present in SQLConfigDataSource.

Any help would be appreciated.

[Sam Hobbs]

I am working on a similar thing. I have tentatively decided that we have two choices. Either create and maintain schema.ini ourselves independent of ODBC or use SQL to do that. I think that SQL will do what we want with things like create schema. The Microsoft documentation is bleak in this area and I do not know how I was able to realize that SQL is probably the "right" way. I do not know how I realized that there is such a thing as create schema but I did.

I have been looking for the correct SQL syntax. Microsoft documentation does not seem to have the SQL syntax documentation. I tried getting it from ANSI. I am still researching.

I think that ODBC might have some utility functions that might help maintaning schema.ini but I would rather avoid that.

I will be impressed if we get assistance from someone that has already done this type of thing. I feel like I am trying to blaze a trail to a new land that no one else has ever gone before.

[PeterK]

Thanks for the input.

Would the create schema SQL command actually create the schema.ini file?

I was looking for an ODBC call that would create the schema.ini file but if there is another way I'd like to hear about it.

You can do this on the fly. No need to muck around with the schema.ini file. If you create a data source using SQLConfigDataSource(), and then use SQL to create, populate and drop tables, the ODBC driver will take care of schema.ini for you. See the following example:

#include "stdafx.h"

#include <iostream.h>
#include <iomanip.h>
#include <odbcinst.h>

int main()
{
/////////////////////////////////////////////////////////////////////
// get path to the temp directory
/////////////////////////////////////////////////////////////////////

cout << "Getting path to temp directory...";

CString strPath;

GetTempPath(_MAX_PATH, strPath.GetBuffer(_MAX_PATH));
strPath.ReleaseBuffer();

strPath.TrimRight();

if (strPath.Right(1) == '\\')
strPath = strPath.Left(strPath.GetLength() - 1);

cout << " Temporary directory is \"" << strPath << "\"." << endl;


/////////////////////////////////////////////////////////////////////////
// create the new data source
/////////////////////////////////////////////////////////////////////////

cout << "Creating temporary data source...";

CString strDriver = "Microsoft Text Driver (*.txt; *.csv)"; // exactly the same name as in the ODBC-Manager

char dsn[256];
int i;

wsprintf(dsn,
"DSN=TmpTextDSN\n"
"Description=Temporary text file DSN\n"
"DefaultDir=%s\n", strPath);

for (i = 0; dsn[i] != '\0'; i++)
{
if (dsn[i] == '\n')
dsn[i] = '\0';
}

if (!SQLConfigDataSource(NULL, ODBC_ADD_DSN, strDriver, dsn))
{
cout << " Error creating data source" << endl;
exit(1);
}

cout << " Temporary data source created." << endl;

/////////////////////////////////////////////////////////////////////////
// open the temporary data source
/////////////////////////////////////////////////////////////////////////

cout << "Opening temporary data source...";

CDatabase dbTmp;

try
{
dbTmp.OpenEx("ODBC;UID=;PWD=;DSN=TmpTextDSN;", CDatabase::noOdbcDialog);
}

catch(CDBException* e)
{
cout << " Database error opening temporary data source." << endl;
cout << e->m_strError;
e->Delete();
}

catch(CMemoryException* e)
{
cout << " Memory error opening temporary data source." << endl;
e->Delete();
}

if (!dbTmp.IsOpen())
{
cout << " Couldn't open temporary data source." << endl;
exit(2);
}

cout << " Temporary data source is open." << endl;


/////////////////////////////////////////////////////////////////////////
// build and populate new tables in the temporary data source
/////////////////////////////////////////////////////////////////////////

cout << "Creating and populating tables..." << endl;

CString str, strSQL;

strSQL = "CREATE TABLE Text1#txt (TEXT_COL TEXT, NUM_COL NUMBER);";

try
{
dbTmp.ExecuteSQL(strSQL);
}

catch (CDBException* e)
{
cout << endl << e->m_strError;
e->Delete();
exit(3);
}

for (i = 0; i < 10; i++)
{
str.Format("Entry #%d", i+1);
strSQL.Format("INSERT INTO Text1.txt (TEXT_COL,NUM_COL) VALUES ('%s',%d);",
str, i+1);
try
{
dbTmp.ExecuteSQL(strSQL);
}

catch (CDBException* e)
{
cout << endl << e->m_strError;
e->Delete();
exit(4);
}
}

cout << "All tables created and populated." << endl;

/////////////////////////////////////////////////////////////////////////
// Close data sources
/////////////////////////////////////////////////////////////////////////

cout << "Closing all data sources...";

dbTmp.Close();

cout << " All data sources closed." << endl;

/////////////////////////////////////////////////////////////////////////
// remove the temporary data source
/////////////////////////////////////////////////////////////////////////

cout << "Deleting temporary data source...";

wsprintf(dsn, "DSN=TmpTextDSN\n");

for (i = 0; dsn[i] != '\0'; i++)
{
if (dsn[i] == '\n')
dsn[i] = '\0';
}

if (!SQLConfigDataSource(NULL, ODBC_REMOVE_DSN, strDriver, dsn))
{
cout << " Error deleting data source" << endl;
exit(12);
}

cout << " Temporary data source deleted." << endl;

/////////////////////////////////////////////////////////////////////////

return 0;
}

This results in the following files being created in my TEMP directoty:

schema.ini

[Text1.txt]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
Col1=TEXT_COL Char Width 255
Col2=NUM_COL Float

text1.txt

"TEXT_COL","NUM_COL"
"Entry #1",1.00
"Entry #2",2.00
"Entry #3",3.00
"Entry #4",4.00
"Entry #5",5.00
"Entry #6",6.00
"Entry #7",7.00
"Entry #8",8.00
"Entry #9",9.00
"Entry #10",10.00

You can execute DROP TABLE SQL statements through the CDatabase to delete the entries in the schema.ini file, and delete the text files.

Hope this helps.