Should ODBC drivers be written?

Hi,
   I have a client server application[server is written in c and client is
   written using MFC].

   Server maintains the database in flat files. Client can request the
   following operations from the server.
   a. add a record,
   b. modify a record,
   c. query,
   d. list the records....

   Now there is a request to make the application ODBC compliant.
   As i have understood, it means the server should be able to talk to
   other SQL databases
   
   So how should i implement it?
   Should i write ODBC drivers for the present database,
   and make server use ODBC calls?
   If this is the right approach, what books/urls should i refer?

   Is there any other solution?
   i.e is there any other way of making this application ODBC compliant?

   Thanking you,
Akhila
 
akhila75Asked:
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.

david_levineCommented:
Well, from the way I read your posting, the server accesses the database(s) and not the client. Is that correct?

There are ODBC drivers today for most databases and even for pure text files. You could ODBC enable your app today to access the flat files you already have on the server.

Not sure what else your server app is doing, so it's hard to make recommendations, but it should be fairly trival.

Install the appropraite ODBC driver on the server and create a DSN (connection) to it via the ODBC Control Panel. In your server app, you'd connect to the ODBC control at startup and disconnect on exit (which is probably never other then a shutdown). And then you'd replace your reads/writes to the flat files with ODBC SQL calls.

You'd probably want to convert to a database at some point, even an Access one. As your flat files get bigger, performance will start to suffer substantially. And if your Access table definitions mimic your flat file, the only change would be to modify the DSN to point to your Access "database" instead of the flat files. No code changes should be required.

David
0
Ron WarshawskyCommented:

The best way to make you current application ODBC comlient is to install ODBC Microsoft Text driver.

When the Text driver is used, you can use the Define Text Format dialog box to define the format for columns in a selected file. This dialog box enables you to specify the schema for each data table. This information is written to a SCHEMA.ini file in the data source directory. A separate SCHEMA.ini is created for each text data source directory.

Note   The same default file format applies to all new text data tables. All files created by the CREATE TABLE statement inherit those same default format values, which are set by selecting file format values in the Define Text Format dialog box with <default> chosen in the Tables list box. The Text driver does not change the format of an existing text file to match the format defined in this dialog box, but returns an error when it uses the format, such as when it attempts to retrieve data from the text file.

Control      Information
Add      Adds a column using the values in the Data Type, Name, and Width fields from the dialog box, and if applicable, the Date Separator value from SCHEMA.ini.
Characters      ANSI or OEM. OEM specifies a non-ANSI character set. This defaults to OEM if the format of the item selected in the Tables list has not been previously defined by this dialog box
Column NameHeader      Indicates whether the columns of the first row of the selected table are to be used as column names. Either TRUE or FALSE. This defaults to FALSE if the format of the item selected in the Tables list has not been previously defined by this dialog box.
Columns      Contains a list of the column names for each column in the selected table. The order of the columns reflects the order of the columns in the table. This list is enabled if a file has been selected in the Tables list.
Data Type      Can be BIT, BYTE, CHAR, CURRENCY, DATE, FLOAT, INTEGER, LONGCHAR, SHORT, or SINGLE. Date data types can be in the following formats: “dd-mmm-yy”, “mm-dd-yy”, “mmm-dd-yy”, “yyyy-mm-dd”, or “yyyy-mmm-dd”. “mm” denotes numbers for months; “mmm” denotes letters for months.
Delimiter      Specifies the custom delimiter character to be used to separate columns. Enabled when the “Custom Delimited” format is selected. The delimiter can only be one character in length, and double quotations marks (“) cannot be used as the delimiter character. (Note that the delimiter cannot be specified in hexadecimal or decimal format.)
Format      Either delimited or fixed length. If delimited, indicates the type of delimiter used: comma (CSV), tab, or special character (custom). This defaults to CSV Delimited if the format of the item selected in the Tables list has not been previously defined by this dialog box.If Format is fixed-length and Column Name Header is TRUE, the first line must be comma-delimited.
Guess      Automatically generates the column's data type, name, and width values for the columns in the selected table by scanning the table's contents according to the Format list box selection. Enabled when the table format is delimited. Any previously defined columns in the Columns list are cleared and replaced with new entries. If Column Name Header is not checked, column names are generated automatically as “F1”, “F2”, and so on. Note that no default value is shown in the Data Type box.This functionality only works on columns that are less than 64,513 bytes.
Modify      Modifies the selected column using the values in the Data Type, Name, and Width fields.
Name      Displays the name of the selected column. May be used to specify a new column name for either an existing column or a new column.If Column Name Header is TRUE, the column name displayed is ignored.
Remove      Deletes the selected column.
Rows to Scan      The number of rows that Setup or the driver will scan when setting the columns and column data types based upon existing data.You may enter a number from 1 to 32767 for the number of rows to scan. This defaults to 25 if the format of the item selected in the Tables list has not been previously defined by this dialog box. (A number outside the limit will return an error.)
Tables      Contains a list of all files in the directory selected in the Text Setup dialog box that match the list of extensions specified.When <default> is selected, and one of the following is true, then the values of the table attributes in the Tables group are written to schema.ini. No other entries in schema.ini are touched.·      There is no schema.ini in the specified directory.·      The schema.ini file exists, but there is no section in schema.ini for one of the Text files (with the specified extension) in the directory.·      The section for a Text file exists in schema.ini, but the body is empty.When <default> is selected, the “Columns” group is disabled.
Width      The width of the column may be changed for CHAR or LONGCHAR columns. The width defaults to 1 if the format of the item selected in the Tables list has not been previously defined by this dialog box.For other data types, the width control is disabled, and no value is displayed.

----------------------------

Understanding Schema.ini Files
Schema.ini files provide schema information about the records in a text file. Each Schema.ini entry specifies one of five characteristics of the table:

·      The text file name
·      The file format
·      The field names, widths, and types
·      The character set
·      Special data type conversions

The following sections discuss these characteristics.
Specifying the File Name
The first entry in Schema.ini is always the name of the text source file enclosed in square brackets. The following example illustrates the entry for the file Sample.txt:

[Sample.txt]

Specifying the File Format
The Format option in Schema.ini specifies the format of the text file. The Text IISAM can read the format automatically from most character-delimited files. You can use any single character as a delimiter in the file except the double quotation mark (“). The Format setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis. The following table lists the valid values for the Format option.

Format specifier      Table format
TabDelimited      Fields in the file are delimited by tabs.
CSVDelimited      Fields in the file are delimited by commas (comma-separated values).
Delimited(*)      Fields in the file are delimited by asterisks. You can substitute any character for the asterisk except the double quotation mark.
FixedLength      Fields in the file are of a fixed-length.
For example, to specify a comma-delimited format, you would add the following line to Schema.ini:

Format=CSVDelimited

Specifying the Fields
You can specify field names in a character-delimited text file in two ways:

·      Include the field names in the first row of the table and set ColNameHeader to True.
·      Specify each column by number and designate the column name and data type.

You must specify each column by number and designate the column name, data type, and width for fixed-length files.

Note   The ColNameHeader setting in Schema.ini overrides the FirstRowHasNames setting in the Windows Registry on a file-by-file basis.

The data types of the fields can also be determined. Use the MaxScanRows option to indicate how many rows should be scanned when determining the column types. If you set MaxScanRows to 0, the entire file is scanned. The MaxScanRows setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis.
The following entry indicates that Microsoft Jet should use the data in the first row of the table to determine field names and should examine the entire file to determine the data types used:

ColNameHeader=True
MaxScanRows=0

The next entry designates fields in a table by using the column number (Coln) option, which is optional for character-delimited files and required for fixed-length files. The example shows the Schema.ini entries for two fields, a 10-character CustomerNumber text field and a 30-character CustomerName text field:

Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30

The syntax of Coln is:

Coln=ColumnName type [Width #]

The following table describes each part of the Coln entry.

Parameter      Description
ColumnName      The text name of the column. If the column name contains embedded spaces, you must enclose it in double quotation marks.
type      Data types are:Microsoft Jet data types
Bit
Byte
Short
Long
Currency
Single
Double
DateTime
Text
MemoODBC data types
Char (same as Text)
Float (same as Double)
Integer (same as Short)
LongChar (same as Memo)
Date date format
Width      The literal string value Width. Indicates that the following number designates the width of the column (optional for character-delimited files, required for fixed-length files).
#      The integer value that designates the width of the column (required if Width is specified).
Selecting a Character Set
You can select from two character sets: ANSI and OEM. The following example shows the Schema.ini entry for an OEM character set. The CharacterSet setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis. The following example shows the Schema.ini entry that sets the character set to ANSI:

CharacterSet=ANSI

Specifying Data Type Formats and Conversions
The Schema.ini file contains a number of options that you can use to specify how data is converted or displayed. The following table lists each of these options.

Option       Description
DateTimeFormat      Can be set to a format string indicating dates and times. You should specify this entry if all date/time fields in the import/export are handled with the same format. All of the Microsoft Jet formats except A.M. and P.M. are supported. In the absence of a format string, the Windows Control Panel short date picture and time options are used.
DecimalSymbol      Can be set to any single character that is used to separate the integer from the fractional part of a number.
NumberDigits      Indicates the number of decimal digits in the fractional portion of a number.
NumberLeadingZeros      Specifies whether a decimal value less than 1 and greater than –1 should contain leading zeros; this value can either be False (no leading zeros) or True.
CurrencySymbol      Indicates the currency symbol to be used for currency values in the text file. Examples include the dollar sign ($) and Dm.
CurrencyPosFormat      Can be set to any of the following values:·      Currency symbol prefix with no separation ($1)·      Currency symbol suffix with no separation (1$)·      Currency symbol prefix with one character separation ($ 1)·      Currency symbol suffix with one character separation (1 $)
CurrencyDigits      Specifies the number of digits used for the fractional part of a currency amount.
CurrencyNegFormat      Can be one of the following values:·      ($1)·      – $1·      $–1·      $1–·      (1$)·      – 1$·      1–$·      1$–·      – 1 $·      – $ 1·      1 $–·      $ 1–·      $ –1·      1– $·      ($ 1) ·      (1 $)This example shows the dollar sign, but you should replace it with the appropriate CurrencySymbol value in the actual program.
CurrencyThousandSymbol      Indicates the single-character symbol to be used for separating currency values in the text file by thousands.
CurrencyDecimalSymbol      Can be set to any single character that is used to separate the whole from the fractional part of a currency amount.
Note   If you omit an entry, the default value in the Windows Control Panel is used.

----------------

The ODBC Text driver supports both delimited and fixed-width text files. A text file consists of an optional header line and zero or more text lines.
Although the header line uses the same format as the other lines in the text file, the ODBC text driver interprets the header line entries as column names, not data.
A delimited text line contains one or more data values separated by delimiters: commas, tabs, or a custom delimiter. The same delimiter must be used throughout the file. Null data values are denoted by two delimiters in a row with no data between them. Character strings in a delimited text line can be enclosed in double quotation marks (“”). No blanks may occur before or after delimited values.

The width of each data entry in a fixed-width text line is specified in a schema. Null data values are denoted by blanks.
Tables are limited to a maximum of 255 fields. Field names are limited to 64 characters, and field widths are limited to 32,766 characters. Records are limited to 65,000 bytes.
A text file can be opened only for a single user. Multiple users are not supported.
The following grammar, written for programmers, defines the format of a text file that can be read by the ODBC text driver. Non-italics represent characters that must be entered as shown, italics represent arguments that are defined elsewhere in the grammar, brackets ([]) represent optional items, braces ({}) delimit a list of mutually exclusive choices, vertical bars (|) separate these choices, and ellipses (...) represent items that can be repeated one or more times.

The format of a text file is:
text-file ::=
      [delimited-header-line] [delimited-text-line]... end-of-file |
      [fixed-width-header-line] [fixed-width-text-line]... end-of-file
delimited-header-line ::= delimited-text-line
delimited-text-line ::=
      blank-line |
      delimited-data [delimiter delimited-data]... end-of-line
fixed-width-header-line ::= fixed-width-text-line

fixed-width-text-line ::=
      blank-line |
      fixed-width-data [fixed-width-data]... end-of-line
end-of-file ::= <EOF>
blank-line ::= end-of-line
delimited-data ::= delimited-string | number | date | delimited-null
fixed-width-data ::= fixed-width-string | number | date | fixed-width-null
The width of each column in a fixed width text file is specified in the SCHEMA.INI file.

end-of-line ::= <CR> | <LF> | <CR><LF>
delimited-string ::= unquoted-string | quoted-string
unquoted-string ::= [character | digit] [character | digit | quote-character]...
quoted-string ::=
      quote-character
      [character | digit | delimiter | end-of-line | embedded-quoted-string]...
      quote-character
embedded-quoted-string ::=
      quote-character quote-character
      [character | digit | delimiter | end-of-line]
      quote-character quote-character

fixed-width-string ::= [character | digit | delimiter | quote-character] ...
character ::=  any character except:
      delimiter
      digit
      end-of-file
      end-of-line
      quote-character
digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
delimiter ::= , | <TAB> | custom-delimiter
custom-delimiter ::= any character except:
      end-of-file
      end-of-line
      quote-character
The delimiter in a custom-delimited text file is specified in the SCHEMA.INI file.

quote-character ::= "
number ::= exact-number | approximate-number
exact-number ::= [+ | -] {unsigned-integer[.unsigned-integer] |
      unsigned-integer. |
      .unsigned-integer}
approximate-number ::= exact-number{e | E}[+ | -]unsigned-integer
unsigned-integer ::= {digit}...
date ::=
      mm date-separator dd date-separator yy |
      mmm date-separator dd date-separator yy |
      dd date-separator mmm date-separator yy |
      yyyy date-separator mm date-separator dd |
      yyyy date-separator mmm date-separator dd

mm ::= digit [digit]
dd ::= digit [digit]
yy ::= digit digit
yyyy ::= digit digit digit digit
mmm ::= Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
date-separator ::= - | / | .
delimited-null ::=
For delimited files, a NULL is represented by no data between two delimiters.
fixed-width-null ::= <SPACE>...
For fixed width files, a NULL is represented by spaces.

regards,

  rwarsh
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
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
Databases

From novice to tech pro — start learning today.