2011-03-17

Communicating Between Apps

Folks on the REAL Studio forums and mailing list have been asking about how to get their REALbasic apps to talk to other apps such as Perl or PHP server-side apps. There is no magic answer. REAL Studio produces apps like any other language does, and has a good TCP/IP socket class. So your RS apps can reach out and touch other apps in the same way as other languages. Here's a few of those ways.

Socket

Open a TCP socket directly to one another. REAL Studio has good classes to make a socket connection, either for listening (server) or initiating (client). Nearly every language has such libraries either built-in or available.

You then pass any series of octets you want. This is totally flexible, but you have the burden of figuring out your own protocols for packaging and shipping data back and forth. This process of exchanging data is known as marshaling or serialization.

This approach works even if both apps are running on the same machine. Two local apps can make a socket connection even without a network. Simply use the internet/host address "127.0.0.1", or domain name "localhost", both of which means "this computer".

HTTP

Either app can act as a web server (an HTTP server), returning plain text rather than HTML in response to certain URLs requested by the other app. Super simple & easy approach for getting single or few pieces of data.

Instead of a web browser making the HTTP request, the app uses a socket or HTTP-client library to make the HTTP request.

Two good books on HTTP include these from O'Reilly.
HTTP Pocket Reference
HTTP: The Definitive Guide

Remember: HTTP <> HTML
HTTP is the usual way we deliver HTML, but the two are not bound together. You can make requests and responses over HTTP that have nothing to do with HTML and web pages. Likewise, you can deliver HTML content without HTTP, such as "rich text" email messages.

Example:
http://www.Example.com/get_sales_for_today/
…returns a single line of plain text rather than a bunch of HTML making up a web page:
435.72

Web Service

Either app may be able to act as a Web Service server, while the other makes a SOAP, REST, or XML-RPC call. XML-RPC is the simpler and saner predecessor to SOAP, as described well in this this book.

Or both apps can read and write data via a 3rd server's services.

This idea is similar to the bullet above, "HTTP", but formalizes how to make requests with arguments, and how to represent data.

Files

One app writes a file in a location to be read by the other app.

Database

Either app reads or writes data to a database server such as Postgres.

You can even handle requests and responses this way. Create a table of 'requests' or 'to_do_items', where either app creates a row in that table to ask for work to be done, and the other app is in a loop scanning for such rows being inserted.

Message Queue

Both apps can use a 3rd party message queue service.

You can even use email for this, where each app gets their own email account on your email system so they can send and receive messages on their own.

2011-03-15

Get All Column Names of Table In Postgres 9

If you need to get a list of the column names in a table in Postgres, here's a line of REALbasic code with the SQL SELECT statement:

sql = "SELECT attname, attnum FROM pg_attribute, pg_class WHERE attrelid = pg_class.oid AND relname = " + DBUtils.escapeAndAddQuotes(tableName) + " AND attnum > 0 ;"

You can see that a join is required across two tables of Postgres' meta-data. I've not studied Postgres' meta-data tables, but worked out this code with some googling and experimenting.

The call to DBUtils.escapeAndAddQuotes is unrelated. That is my own method for 3 things:
  • Verify the string is in UTF-8 encoding.
  • Replace any occurrence of a single quotation mark (Unicode APOSTROPHE-QUOTE) with two such characters, to make the string safe for SQL execution.
  • Add single quote marks around the string for use as a literal in the SQL execution.

An alternative approach suggested by others is this:
"select * from tablename where 0=1"
Obviously that is a hack, though it may be an efficient and effective one. Instead I chose the other approach.

2011-03-08

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.

2011-03-04

Subclassing a Window in REALbasic

In REAL Studio 2010, a window cannot inherit from another window. But a window can inherit from a class whose "Super" is set to "Window".

For example, if you want to track each individual window, you could add assign a UUID to each window instance. That UUID value could be stored as a hex string in a property on each window. Rather than create the property on each window definition (Customer window, Purchase Order window, etc.), let those window definitions inherit the UUID property.

(1) Add a class (not a Window) named "UuidWindow".

(2) On that class, set it's "Super" property to "Window".

(3) On that class, add a property named "UUID" of type String.

(4) Open each of your window definitions (Customer window, Purchase Order window, etc.) and change their "Super" property to "UuidWindow".

Now each of those windows can refer to "me.UUID".