DatabaseRecord

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 db as REALSQLdatabase

db=New REALSQLdatabase

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

Dim b As BinaryStream

Dim f as FolderItem

//open the image and store it in a String

f = GetOpenFolderItem("image/Jpeg") //defined as a file type

If f <> Nil then

b=BinaryStream.Open(f,False)

currentImage=b.Read(b.Length)

b.Close

end if

// Add Image To The Database **

Dim comment As String

Dim rec As DatabaseRecord

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.

Dim b As BinaryStream

Dim rs as RecordSet

Dim f as FolderItem

// 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.