Skills of operation BLOB data in PowerBuilder: parthenolide issued Time: BLOB (Binary Large Object) data type 2002.02.25PowerBuilder provided can be used to handle large data, including images, large text, Word documents, binary files and other multimedia Data, its length can be 0 ~ 2GB bytes, and we use the BLOB type variable to deliver data to the database in large fields. However, use the usual data window technology to add BLOB type data to the database, and you cannot extract the data of the corresponding fields in the database, even if the SQL statement such as UPDATE and SELECT is used directly in the program, it is not possible to operate the BLOB type data. . The following writers will introduce the skills of operating BLOB type data in PowerBuilder 7.0.
Program design principle
In the PB, the library and query of the BLOB type data are implemented. It is mainly used to use two special SQL commands --updateblob and selectblob. First, you can first locate records to operate, and then use the updateblob command to store the BLOB type variable. Data is passed to the database, and using selectblob is the transfer of large field data in the specified record to the BLOB variable. Using the BLOB type variable to get the data content of the external file, you must also use the visualizing OLE control object provided by the PB, by displaying the specified file content into the corresponding object (such as a BMP picture), double-click it to activate the corresponding OLE The server application to edit the contents of the modified object, but also read the data content of the object (using the ObjectData attribute of the OLE control) to assign the BLOB type variable. The following is a simple program example, telling how to handle the BLOB data type. In this example, the BMP picture will be stored as a large field in the database. We choose Microsoft Access 2000 as a background database, which contains the "OLE object" type in its field type (if you select MS SQL Server as the Database Server, you can use the text or Image type field to store large field data), which is to manage multimedia data. Big files and pictures provide support.
Operate the implementation of the blob field
We create a new database file in Access, the file name is bmp.mdb, defined in the BMP image data table BMPTABLE as follows: Other settings of the fields (such as field width, etc.), all in the default form.
The column name field type can be empty note bmpno number No photo numbers (keyword) bmpname text Energy picture name BMPData OLE Objects (BMP)
One-step preparation that needs to be made before programming is to establish a connection to the BMP.mdb database in the ODBC configuration of the operating system, named BMPTABLE. The following program fragments implements the database connection, insert, update, delete, and query. Considering the parameters of the article, this article only lists the program source code related to the subject.
1. Connect Access Database
SQLCA.DBMS = "ODBC" // SQLCA global transaction transaction variables SQLCA.AutoCommit = FalseSQLCA.DBParm = "Connectstring = 'DSN = bmptable; UID =; PWD ='" CONNECT USING SQLCA;. If SQLCA SQLCode <> 0 ThenMessageBox ("Database Error", "Connection Fails!") Halt Close; End IF
2. Deposit BMP image into the database
Integer bmpno, ICOUNT / / Variable BMPNO Store gived a given picture number String FilePath, FileNameBlob BLB_TMP / / ... This is assigned to the picture bmpno, such as bmpno = 101; can be implemented for pop-up dialog window to provide a picture number /// Query the specified image number already already exists ICOUNT = 0select count () Into: iCountFrom Bmptablewhere BMPNOUSING SQLCA; / /: Bmpno is a given picture number if iF iCount> 0 TheenMessageBox ("Query Results", String BMPNO) "Image already exists, please enter a new picture number") Returnend IF // Insert BMP image file in OLE control object OLE_1 GetFileOpenName ("Please select a BMP picture file you need to insert", FilePath, FileName, "BMP", "BMP image file (.BMP), BMP") if len (filepath) = 0 tellTurnend IFIF OLE_1.INSERTFILE (FilePath) <> 0 THEN // OLE Error Returnend IF // New Picture string sqlsql = "INSERT INTO bmptable (bmpno, bmpdata, bmpname) && VALUES (" && string (bmpno) ", '', '" && filename "')" EXECUTE IMMEDIATE: sql; If SQLCA.SQLDBCode = 0 ThenCOMMIT USING SQLCA; // commit the transaction ElseMessageBox ( "database error", "insert failed") rOLLBACK USING SQLCA; // transaction rollback ReturnEnd IfBlb_tmp = ole_1.ObjectDataUPDATEBLOB bmptable SET bmpdata =: blb_tmpWHERE bmptable.bmpno =: bmpnoUSING SQLCA; // Update the field if Sqlca.sqldbcode = 0 ThenCommit using sqlca; // Submit transaction MessageBox ("Insert Success", "Picture Board Success") ElseMessageBox ("Database Error", "Update Image Failed") Rollback Using Sqlca; // Transaction Roll Roll Returnend IF3. Remove BMP Pictures from Database
Update bmptable set bmpdata = '' where bmptable.bmpno =: bmpnousing sqlca; // bmpno is the image number IF SQLCA.SQLDBCODE = 0 ThenCommit using sqlca; // Submit transaction ElseMessageBox ("Database Error", "Update Failed" ) rOLLBACK USING SQLCA; // transaction rollback ReturnEnd IfDELETE FROM bmptableWHERE bmptable.bmpno =: bmpnoUSING SQLCA; If SQLCA.SQLDBCode = 0 ThenCOMMIT USING SQLCA; // commit the transaction MessageBox ( "deleted successfully", "Image deleted successfully") ElseMessageBox ("Database Error", "Delete Fail") Rollback Using Sqlca; // Transaction Roll Roll Returnend IF4. Press the image number to query the image information
// No need to provide a picture stored in the variable bmpno in SetNull (blb_tmp) SELECTBLOB bmptable.bmpdata INTO: blb_tmpFROM bmptableWHERE bmptable.bmpno =: bmpnoUSING SQLCA; If Not IsNull (blb_tmp) Thenole_1.ObjectData = blb_tmp // Double-click the wake OLE OLE control Server can be edited by End IF
When using PB programming, pay attention to check the return result after the database is operated, to ensure the reliability of the program. The above procedure is just a key technique for operational BLOB data types, and a complete CLIENT / Server-based database application can be formed by drawing the corresponding user interface. Note: The author does not solve a picture handling problem greater than 32K.
