ODBC Programming Overview
From CometWiki
(New page: '''ODBC Programming Overview''' As mentioned above, ODBC is implemented as an API. This means that ODBC commands are integrated into the application programming language. In Internet Basi...) |
|||
| (21 intermediate revisions not shown) | |||
| Line 5: | Line 5: | ||
Here is the syntax for the CONTROL statement with ODBC commands: | Here is the syntax for the CONTROL statement with ODBC commands: | ||
| - | <Result> = CONTROL(<Lun>, <CommandString>) | + | '''<Result> = CONTROL(<Lun>, <CommandString>)''' |
where: | where: | ||
| - | <Lun> is the logical unit number of the ODBC gateway | + | '''<Lun>''' is the logical unit number of the ODBC gateway |
| - | <CommandString> is an ODBC command followed by optional parameters separated by a semi-colon. | + | '''<CommandString>''' is an ODBC command followed by optional parameters separated by a semi-colon. |
| - | <Result> contains the returned data and/or error string. | + | '''<Result>''' contains the returned data and/or error string. |
| - | • If a command is successful, <Result> begins with a “+”followed by any other returned information. | + | • If a command is successful, <Result> begins with a “+”followed by any other returned information. |
| - | • If a command fails, <Result> begins with a “-“ followed by additional error information. | + | • If a command fails, <Result> begins with a “-“ followed by additional error information. |
| - | + | ||
| - | + | ||
| + | '''Example 1:''' | ||
| + | |||
Here is how to open the sample Northwind Traders database. Your program must use the dbOpen command and specify the data source name. The Internet Basic statements would look like this: | Here is how to open the sample Northwind Traders database. Your program must use the dbOpen command and specify the data source name. The Internet Basic statements would look like this: | ||
| - | Length 254 & Local Result$ | + | 100 Length 254 & Local Result$ |
| - | Set ODBC = 1 | + | 200 Set ODBC = 1 |
| - | + | 300 open (ODBC) “G00” ! open ODBC gateway | |
| - | + | 400 Result$ = CONTROL(ODBC,”dbOpen:DSN=Northwind”) | |
| - | + | ||
| - | + | ||
| - | Example 2: | + | '''Example 2:''' |
Here is how to set the database options to report StateNativeOrigin error codes and use the cursor library. | Here is how to set the database options to report StateNativeOrigin error codes and use the cursor library. | ||
| - | Length 254 & Local Result$ | + | 100 Length 254 & Local Result$ |
| - | Set ODBC = 1 | + | 200 Set ODBC = 1 |
| - | + | 300 open (ODBC) “G00” ! open ODBC gateway | |
| - | + | 400 result$ = Control(ODBC, "dbSetOptions:ErrorCodes;UseCursorLib") | |
| - | + | 500 If (Sub(Result$, 1, 1) EQ “-“) | |
| - | + | 600 print “Error:”;Result$8 | |
| - | + | 700 Goto Error Return | |
| - | + | 800 EndIf | |
| - | + | ||
| - | + | ||
Some ODBC commands must be followed immediately by a Internet Basic WRITE in order to supply additional information to the driver. An example is the dbDefineRecordset command, where the recordset definition map (i.e., record layout) must be provided to the ODBC driver immediately after the dbDefineRecordset command is issued. | Some ODBC commands must be followed immediately by a Internet Basic WRITE in order to supply additional information to the driver. An example is the dbDefineRecordset command, where the recordset definition map (i.e., record layout) must be provided to the ODBC driver immediately after the dbDefineRecordset command is issued. | ||
| Line 49: | Line 45: | ||
A recordset definition map consists of the field names and field sizes (as they exist in the ODBC database) that will be used in the Internet Basic program. The syntax is: | A recordset definition map consists of the field names and field sizes (as they exist in the ODBC database) that will be used in the Internet Basic program. The syntax is: | ||
| - | fieldname, size; fieldname, size; fieldname, size (etc.) | + | '''fieldname, size; fieldname, size; fieldname, size (etc.)''' |
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | The following example shows a recordset definition map for three database fields,<BR/> | |
| + | FirstName (25 characters), LastName (25 characters), and Age (numeric, 3.0).<BR/> | ||
| + | Note that this information is written to the ODBC driver immediately after the recordset has been defined. | ||
| - | + | ''' Example 3:''' | |
| - | If (Sub(Result$, 1, 1) EQ "+") ! Successful | + | 100 ! This FORMAT contains the recordset definition map |
| - | + | 200 RSDefinition: FORMAT “FirstName, 25; LastName, 25 ; Age, 3.0” | |
| - | Write (Lun, RSDefinition) Excp = BadDefinition | + | 300 Length 254 & Local Result$ |
| - | EndIf | + | 400 Set ODBC = 1 |
| + | 500 Open (ODBC) “G00” ! open ODBC gateway | ||
| + | 600 ! Define a recordset named “MyRecordset” | ||
| + | 700 Result$ = Command(Lun, “dbDefineRecordset:MyRecordset”) | ||
| + | 800 If (Sub(Result$, 1, 1) EQ "+") ! Successful | ||
| + | 900 ! Definition map MUST be provided immediately | ||
| + | 1000 Write (Lun, RSDefinition) Excp = BadDefinition | ||
| + | 1100 EndIf | ||
Latest revision as of 09:33, 29 May 2009
ODBC Programming Overview
As mentioned above, ODBC is implemented as an API. This means that ODBC commands are integrated into the application programming language. In Internet Basic, all ODBC commands are invoked via the CONTROL statement.
Here is the syntax for the CONTROL statement with ODBC commands:
<Result> = CONTROL(<Lun>, <CommandString>)
where:
<Lun> is the logical unit number of the ODBC gateway
<CommandString> is an ODBC command followed by optional parameters separated by a semi-colon.
<Result> contains the returned data and/or error string.
• If a command is successful, <Result> begins with a “+”followed by any other returned information. • If a command fails, <Result> begins with a “-“ followed by additional error information.
Example 1:
Here is how to open the sample Northwind Traders database. Your program must use the dbOpen command and specify the data source name. The Internet Basic statements would look like this:
100 Length 254 & Local Result$ 200 Set ODBC = 1 300 open (ODBC) “G00” ! open ODBC gateway 400 Result$ = CONTROL(ODBC,”dbOpen:DSN=Northwind”)
Example 2:
Here is how to set the database options to report StateNativeOrigin error codes and use the cursor library.
100 Length 254 & Local Result$ 200 Set ODBC = 1 300 open (ODBC) “G00” ! open ODBC gateway 400 result$ = Control(ODBC, "dbSetOptions:ErrorCodes;UseCursorLib") 500 If (Sub(Result$, 1, 1) EQ “-“) 600 print “Error:”;Result$8 700 Goto Error Return 800 EndIf
Some ODBC commands must be followed immediately by a Internet Basic WRITE in order to supply additional information to the driver. An example is the dbDefineRecordset command, where the recordset definition map (i.e., record layout) must be provided to the ODBC driver immediately after the dbDefineRecordset command is issued.
A recordset definition map consists of the field names and field sizes (as they exist in the ODBC database) that will be used in the Internet Basic program. The syntax is:
fieldname, size; fieldname, size; fieldname, size (etc.)
The following example shows a recordset definition map for three database fields,
FirstName (25 characters), LastName (25 characters), and Age (numeric, 3.0).
Note that this information is written to the ODBC driver immediately after the recordset has been defined.
Example 3:
100 ! This FORMAT contains the recordset definition map 200 RSDefinition: FORMAT “FirstName, 25; LastName, 25 ; Age, 3.0” 300 Length 254 & Local Result$ 400 Set ODBC = 1 500 Open (ODBC) “G00” ! open ODBC gateway 600 ! Define a recordset named “MyRecordset” 700 Result$ = Command(Lun, “dbDefineRecordset:MyRecordset”) 800 If (Sub(Result$, 1, 1) EQ "+") ! Successful 900 ! Definition map MUST be provided immediately 1000 Write (Lun, RSDefinition) Excp = BadDefinition 1100 EndIf
