DatabaseRecord

From Xojo Documentation

Revision as of 18:55, 19 November 2009 by 10.0.1.5 (talk) (Examples)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
You are currently browsing the old Xojo documentation site. Please visit the new Xojo documentation site!


Description

Used to create new Database records. The methods are used to populate the fields in a record. Call it once per column in the record.


Super Class

Object

Methods

Name Parameters Return Type Description
BlobColumn Name as String String Sets the specified field to the specified blob. The parameter is the column name. Use Blob columns to work with pictures in the REALSQLdatabase.
BooleanColumn Name as String Boolean Sets the specified field to the specified boolean value. The parameter is the column name.

The REALSQLdatabase inserts 0 for False and 1 for True.

Column Name as String String Sets the field specified by name to the specified String value.
Column
Introduced 2008r5
Index as Integer String Gets a column's value by the passed Index.
CurrencyColumn Name as String Currency Sets the specified field to the specified Currency value. The parameter is the column name.
DateColumn Name as String Date Sets the specified field to the specified Date value. The parameter is the column name.
DoubleColumn Name as String Double Sets the specified field to the specified Double value. The parameter is the column name.
Int64Column Name as String Int64 Sets the specified field to the specified 64-bit integer value. The parameter is the column name.

Support for 64-bit integers is provided at the framework level. Plug-ins must be updated to support 64-bit integers.

IntegerColumn Name as String Integer Sets the specified field to the specified Integer value. The parameter is the column name.
JPEGColumn Name as String Picture Sets the specified field to the specified JPEG image. The parameter is the column name. The REALSQLdatabase does not use JPEGColumn for pictures. Store a picture in a Blob Column instead.
MacPictColumn Name as String Picture Sets the specified field to the specified Macintosh PICT image. The parameter is the column name. The REAL SQL database does not use MacPictColumn to store pictures.Store a picture in a BlobColumn instead.


Notes

Assignments via the DateColumn method store the time as well as the date to support the SQL TimeStamp and Time field types (as well as Date). Note that if the date part is January 1, 0001, then this is converted to SQL as only a time (e.g., "18:54:00"), whereas if the date is anything else, it converts to SQL in full form (e.g., "2000-5-30 18:54:00").

Not all field types supported by the DatabaseRecord class are supported by all data sources. Check whether your data source supports the data type returned by the method you are using.

In the case of the REAL SQL Database, you should use the BlobColumn method to store images, not the JPEGColumn or MacPICTColumn methods.


Examples

Please see the example "Database Example" in the Examples folder that was installed on disk when you installed REALbasic. It is a fully worked orders-inventory-customers relational database that uses REALSQLDatabase as its data source.

The following example creates a new REALSQLdatabase, an employees table, and adds a record to the table.

Dim dbFile as FolderItem
Dim mydate as New Date
Dim rec as DatabaseRecord
Dim myBool as Boolean
dbFile = GetFolderItem("Pubs")
db.DatabaseFile = dbFile
If db.CreateDatabaseFile Then
db.SQLExecute("create table employees(id integer, name varchar," _
+"jobtitle varchar, DOB date, Salary double)")
rec = New DatabaseRecord

rec.IntegerColumn("id") = 1
rec.Column("name") = "Lois Lane"
rec.Column("jobtitle")="Pundit"
mybool=ParseDate("1Jan1950",mydate)
rec.DateColumn("DOB")=mydate
rec.DoubleColumn("Salary")=105000
db.InsertRecord("employees",rec)
If (db.Error= False) then
db.commit
If db.error = False then
MsgBox "Record successfully added!"
Else
MsgBox "Record NOT added!"
End if
else
db.RollBack
if db.Error=False then
MsgBox "Record could not be added but was rolled back."
Else
MsgBox "Record could not be added nor save rolled back."
End if
End if
End If


The following example adds and displays an image in a blob field in a REALSQLdatabase. The window has the properties currentImage as String and db as REALSQLdatabase.

Sub LoadImage

//open the image and store it in a String
f = GetOpenFolderItem("image/Jpeg") //defined as a file type
If f <> Nil then
currentImage=b.Read(b.Length)
b.Close
end if

// Add Image To The Database **

Dim comment As String

comment="my great image"

rec = New DatabaseRecord
rec.BlobColumn("picture") = currentImage
rec.Column("comment")=comment

db.InsertRecord ("images", rec)
If (db.Error= False) then
db.commit
if db.error = False then
MsgBox "Image correctly added!"
Else
MsgBox "Image not correctly added!"
End if
Else
db.rollback
if db.error = False then
MsgBox "Image was not saved. The record was rolled back!"
else
MsgBox "Image was not saved and the rollback failed"
End if
End if

This method reads the image from the database record. The record contains the two fields shown in the previous example. The contents of the Comment field is displayed in a TextArea and the picture is displayed in a Canvas.


// Display only the first image
rs = db.SQLSelect("Select * from images where rowid=1")
// Set the comment field
commentField.Text=rs.Field("comment").GetString
// Set the picture field

f=SpecialFolder.Temporary.Child("temp.jpg")
b=BinaryStream.Create(f,False)
b.Write rs.Field("picture").NativeValue
b.Close
Canvas1.Backdrop=f.OpenAsPicture

See Also

Database, DatabaseField, MySQLCommunityServer, MySQLEnterpriseServer, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, REALSQLdatabase, RecordSet classes.