Programming Guide : 8. Working with Images and Text Strings : How You Can Work with Text Strings : How You Can Store Strings in the Database
 
Share this page                  
How You Can Store Strings in the Database
To store strings in a database, use OpenROAD to add them to a table. OpenROAD automatically creates a special string storage table, called ii_stored_strings. However, you can create your own storage table if you want to improve distribution of the strings in the database and avoid transaction bottlenecks (see How You Can Create a String Storage Table).
Each string in the storage table is associated with a handle, called a DBHandle, which you use to access the string. For example, to display a string stored in the database, set the value of the DBHandle attribute to the DBHandle for that string. OpenROAD then displays the string that is associated with that particular DBHandle. You must store the DBHandles for the strings in a separate table from the strings themselves.
The strings that you load into the database can come from text files, from the user (who enters them into multiline entry fields), or from StringObject variables in your 4GL code.
How You Can Create a String Storage Table
If you need to store string objects in your database, you may be able to use a system catalog or you may need to create your own string storage table. This depends on if you are using Enterprise Access Microsoft SQL Server and whether the database is Unicode-enabled.
Note:  If your database is Enterprise Access Microsoft SQL Server and Unicode-enabled:
• You must create your own string storage table (see following procedure).
• The name of this table must be passed to the methods associated with writing and reading a string object stored in a database. Supply the name of the predefined table using the tablename parameter on the InsertIntoDB method of the StringObject. For more information, see the Language Reference Guide.
• We strongly recommend using NFC normalization format databases (-i option in the createdb command).
Determing Whether a Database Is Unicode-enabled
You can determine whether a database is Unicode-enabled by using the following query:
SELECT   cap_value
FROM   iidbcapabilities
WHERE  cap_capability = 'NATIONAL_CHARACTER_SET'
If the value of cap_value is ‘N’, then the database is not Unicode-enabled. If the value of cap_value is ‘Y’, then the database is Unicode-enabled.
Creating Your Own String Storage Table
Note:  This procedure applies to Ingres 9.1.1 or higher. Typically, you store strings in the database that contains the other data for the application. Someone with authority to grant the user permission must own this storage table, which must have exactly the following columns and data types:
string_id
Data Type: i4 not null
Specifies the ID of the string in the table. This value is a sequential number, starting from 1, that was established when the string was inserted into the database. In the DBHandle that refers to this string, this ID appears in textual form after the table name.
row_sequence
Data Type: i4 not null
Specifies the sequence number for text in the string, beginning with 1
text_total
Data Type: i4 not null
Specifies the total number of bytes in the full text string
text_value
For Ingres and any non-Enterprise Access Microsoft SQL Server database, the data type for this column must be the following:
Data Type: varchar(1786) not null
For Enterprise Access Microsoft SQL Server databases:
• If the database is not Unicode-enabled, the data type for this column must be the following:
Data Type: varchar(1786) not null
• If the database is Unicode-enabled, the data type for this column must be the following:
Data Type: nvarchar(893) not null
Create this table with a B-tree structure (compressed or not) with the unique key values on string_id and row_sequence. Be sure to grant the correct permissions on the table.
How You Can Load a String into the Database
Use the InsertIntoDB method to add a string to the table you specify or, if you do not specify a table, to the system table.
Note:  To use a string storage table other than the system table, you must set it up as described in How You Can Create a String Storage Table.
To load a string into the database
1. Define a varchar(76) column to hold the DBHandles in the table that contains the data for the application.
2. Decide whether to store the strings in the system table or your own table.
3. If the text string is in a text file, set the FileHandle attribute of the StringObject to the file name. (If the user has entered the text into an entry field, the string is already stored in the StringObject object and it is not necessary to set the FileHandle attribute.)
For this step, if there is an entry field on your form, you can use the StringObject object associated with the entry field. If there is no entry field, declare a variable of type StringObject to use for loading the text.
4. Use the InsertIntoDB method to insert the string in the StringObject object into the database table.
After this method completes, OpenROAD stores the new DBHandle for the string in the DBHandle attribute.
5. Add the current value of the DBHandle attribute to your database table so that you can access the string the next time you need it.
6. Commit the changes to the database.
For more information about error handling for the InsertIntoDB method, see Working with Images and Text Strings.
The string is now stored in the database. You can display the string time by setting the DBHandle attribute, and you can replace or delete the string if necessary with the UpdateInDB and DeleteFromDB methods respectively. These methods are described in How You Can Display a String from a Database and How You Can Update Strings in a Database.