 |
» |
|
|
|
As with any column, use the INSERT command to initially put data
into a LONG column.
At the time of the insert, all input devices must be on the system in the
locations you have specified.
Should your insert operation fail, nothing is inserted,
a relevant error message is returned to the program, and the transaction
continues.
Depending on your application, you might want to write a verification
routine that reads a portion of each specified input device to make certain
valid data exists prior to using the INSERT command. The next examples are based on the
PartsTable
created and altered in the previous section, "Defining LONG Columns with
CREATE TABLE or ALTER TABLE."
Additional examples of LONG column I/O string usage are found in the ALLBASE/SQL Reference Manual . Insert Using Host Variables for LONG Column I/O Strings | |
When inserting a single row, use a version of the LONG Column I/O String
for each LONG column following the
VALUES clause, as below.
INSERT INTO PartsTable VALUES (
'bracket',
200,
:PartPictureIO,
:PartModuleIO)
|
An example of the values that might be stored in the host variables, :PartPictureIO and :Part ModuleIO, are shown in the last two fields of a hypothetical record
in the Example Data File that appears later in this chapter.
In the above example, the values, bracket and 200, are coded as constants,
rather than coming from the data file.
The following represents a record could be read into host variables in
preparation for the above INSERT statement. The constants, bracket and 200, in the statement
could alternately be read into host variables.
bracket 200 0'<bracket.tools >hammer' 0'<mod88.module > mod88' 0
|
Bulk Insert Using Host Variables for LONG Column I/O Strings | |
The following example illustrates
how to define
and use an appropriate host variable for a BULK INSERT into PartsTable.
Define an entry in your host variable
array for each LONG column I/O string.
/* This code segment reads a data file into a host variable array, one line */
/* at a time. It parses the buffer and displays each record as it's */
/* read and loaded. Then a BULK INSERT to an ALLBASE/SQL database table is */
/* performed. Maximum number of records per BULK INSERT is 25. */
#include <stdio.h>
#define MAXSIZE 25
.
.
.
/* PartNum is used to read in the PartNumber as a string. */
char PartNum[7];
/* Note that all of the columns allow null values, and an indicator */
/* variable has been defined for each. */
EXEC SQL BEGIN DECLARE SECTION;
struct {
char PartName[11];
sqlind PartNameInd;
int PartNumber;
sqlind PartNumberInd;
char PartPictureIO[31]; /* IO string for LONG column PartPicture */
sqlind PartPictureInd;
char PartModuleIO[31]; /* IO string for LONG column PartModule */
sqlind PartModuleInd;
} PartsTableRows[25]; /* INSERT up to 25 rows at a time */
char SQLMessage[133];
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
.
.
.
|
int InsertRows() /* function to insert rows in PartsTable */
{
StartIndex = 1;
NumberOfRows = counter1;
EXEC SQL BULK INSERT INTO PartsTable
(PartName,
PartNumber,
PartPicture,
PartModule)
VALUES (:PartsTableRows,
:StartIndex,
:NumberOfRows);
if (sqlca.sqlcode != 0) {
SQLStatusCheck();
}
} /* End of InsertRows Function */
/* Here you could accept data from the user or from a file. */
/* For this example, a file is used. */
int main() /* function to initialize host variable array from */
/* a file */
{
char s[225];
int i = 0;
FILE *ptr;
ptr = fopen("data_file","r");
while (fgets(s,225,ptr) & (i < MAXSIZE)) {
sscanf (s, "%10c %2d%2c %2d%30c %2d%30
PartsTableRows[i].PartName, &PartsTableRows[i].PartNameInd,
PartNum, &PartsTableRows[i].PartNumberInd,
PartsTableRows[i].PartPictureIO,
&PartsTableRows[i].PartPictureInd,
PartsTableRows[i].PartModuleIO, &PartsTableRows[i].PartModuleInd);
|
/* We read PartNumber as a string, but assign it as an integer. */
/* atoi converts the ascii to integer. */
PartsTableRows[i].PartNumber = atoi(PartNum);
printf ("%10s %2d %2d %2d %30s %2d %30s %2d\n",
PartsTableRows[i].PartName, PartsTableRows[i].PartNameInd,
PartsTableRows[i].PartNumber, PartsTableRows[i].PartNumberInd,
PartsTableRows[i].PartPictureIO,
PartsTableRows[i].PartPictureInd,
PartsTableRows[i].PartModuleIO, PartsTableRows[i].PartModuleInd);
i;
}
} /* end of initialize function */
.
.
.
|
The file, in this case named data_file, that your program reads might look
something like this. Note that it is limited to 80 characters per record
to facilitate documentation.
hammer 011 0'<hammer.tools >hammer' 0'<mod11.module > mod11' 0
file 022 0'<file.tools >file' 0'<mod22.module > mod22' 0
saw 033 0'<saw.tools > saw' 0'<mod33.module > mod33' 0
wrench 044 0'<wrench.tools >wrench' 0'<mod44.module > mod44' 0
lathe 055 0'<lathe.tools >lathe' 0'<mod55.module > mod55' 0
drill 066 0'<drill.tools >drill' 0'<mod66.module > mod66' 0
pliers 077 0'<pliers.tools >pliers' 0'<mod77.module > mod77' 0
.
.
.
|
|
