Example code to drop & create table, and create record in REALbasic.

Here's a snippet of REALbasic code when you want to create a bogus table to experiment.

I wrote this in REAL Studio 2011 Release 1, but it should work in older versions too.


// Purpose: Create a table in Postgres, insert a row, and retrieve that row.
// Shows a bug in the Postgres database driver of REAL Studio 2011 Release 1
// where dates ('timestamp with time zone' at least) are nil in the RecordSet.

// © 2011 Basil Bourque. This source code may be used freely by anyone taking full responsibility for doing so.

dim db as PostgreSQLDatabase = new PostgreSQLDatabase
dim sql as String
dim rs as RecordSet

// ---------------- Prepare connection.
db.Host = "127.0.0.1"
db.port = 5432 // 5432 is default port for PostgreSQL.
//db.Encryption = 128
db.UserName = "postgres" // "postgres" is the default admin user name.
db.Password = "YourPasswordGoesHere"
db.DatabaseName = "YourDatabaseNameGoesHere" // Postgres supports multiple databases. Which one do you want? Default is named the same name as user, such as 'postgres'.

// ---------------- Try connecting.
dim status as String
if( db.Connect ) then
status = "Success connecting to db server"
else // Error connecting
status = "Error occurred when connecting to database server. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
return
end if

// ---------------- Create a table named "bogus_spacetime_".
sql = sql + "DROP TABLE IF EXISTS bogus_spacetime_ ;" + EndOfLine
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if

// Assumes you have enabled the UUID generation feature in PostgreSQL.
// If not, kill or edit 2 lines below ('pkey_' and 'CONSTRAINT').
sql = "CREATE TABLE bogus_spacetime_" + EndOfLine
sql = sql + "(" + EndOfLine
sql = sql + " pkey_ uuid NOT NULL DEFAULT uuid_generate_v1mc()," + EndOfLine
sql = sql + " moment_ timestamp with time zone NOT NULL DEFAULT clock_timestamp()," + EndOfLine
sql = sql + " CONSTRAINT bogus_spacetime_primary_key_ PRIMARY KEY (pkey_)" + EndOfLine
sql = sql + ")" + EndOfLine
sql = sql + "WITH (" + EndOfLine
sql = sql + " OIDS=FALSE" + EndOfLine
sql = sql + ");" + EndOfLine
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if

sql = "ALTER TABLE bogus_spacetime_ OWNER TO postgres;"
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if

// ---------------- Create a record
sql = "INSERT INTO bogus_spacetime_ DEFAULT VALUES;"
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if

// ---------------- Execute a "SELECT *"
sql = "SELECT * FROM bogus_spacetime_;"
rs = db.SQLSelect( sql)
if(db.Error) then
status = "Error when running a SELECT. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
else // Else normal.
if( rs = nil ) then
status = "ERROR - RecordSet is nil"
else
status = "Record count = " + str( rs.RecordCount ) // Replace with more useful code.
// Access the timestamp value from the 2nd column.
dim f as DatabaseField = rs.IdxField(2) // Despite name, "IdxField" is 1-based.
// BUG = The DatabaseField object contains a nil value rather than a date.
dim d as Date = f.DateValue // Crash.
MsgBox d.SQLDateTime
end if
end if

db.Close

// ---------------- Catch any exception thrown by code above.
Exception
if( db <> nil) then
db.Close
end if

// ---------------- End of code.