HP 3000 Manuals HP 3000 Manuals
Putting Data into a LONG Column with a [BULK] INSERT Command
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.
Example.
/* 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 */
.
.
.
Example Data File.
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
.
.
.