questionable API behavior SQLErrorEvent.ERROR

[Guest]

I'm getting an error when I SELECT on a value which is not present in the database (expected). But it's not being caught by the errorHandler function.

I would like to note that my selectStmt (function readFromDatabase()) is identical to that on Adobe livedocs, so I feel confident that the code is correct.

Let's say, for simplicity, that my database table has columns authors.lName and authors.fName.

If I enter a letter into my txtIn.text (which is being copied to a string variable which is part of the SELECT statement) and the SELECT function can't find an exact match in the table, I get a TypeError: Error #1009. I understand why. But the SQLErrorEvent.Error is not capturing the event.

Apparently, not finding a value in the table is not a SQLErrorEvent?

What is the value of this: var numResults:int = result.data.length; if there is no data? Null? Tracing for a null value doesn't seem to work.

The errorHandler works fine if the the app fails to make a connection. Just not if it fails to find data.

I can't use code to handle this exception because I can't capture it.

And another question; and this one really gets my goat...

Why are there no tutorials...ANYWHERE...on how to handle errors? Tons of tutorials on how to connect to a db, select statements, entering data, etc, etc, etc. But NOTHING on error handling???

Here's my code:

import flash.data.SQLConnection;

import flash.data.SQLMode;

import flash.data.SQLResult;

import flash.events.SQLEvent;

import flash.filesystem.File;

import fl.data.DataProvider;

import fl.controls.List;

import flash.events.KeyboardEvent;

import flash.events.Event;

import flash.events.MouseEvent;

import flash.net.URLRequest;

import flash.display.Loader;

import flash.display.NativeWindow;

import flash.system.Capabilities;

import flash.text.TextField;

import flash.errors.SQLError;

import flash.events.SQLErrorEvent;

var appWindow:NativeWindow = this.stage.nativeWindow;

appWindow.x = (Capabilities.screenResolutionX - appWindow.width) / 2;

appWindow.y = (Capabilities.screenResolutionY - appWindow.height) / 2;

var conn:SQLConnection = new SQLConnection();

conn.addEventListener(SQLEvent.OPEN, readFromDatabase);

conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);

var dbFile:File = File.desktopDirectory.resolvePath("bookLibrary.db");

conn.openAsync(dbFile, SQLMode.UPDATE);

txtIn.addEventListener(Event.CHANGE, changeHandler);

var txtIn:TextField;

txtIn.stage.focus = txtIn;

var txtOut:String = "a";

function readFromDatabase(event:Event):void

{

var selectStmt:SQLStatement = new SQLStatement();

selectStmt.sqlConnection = conn;

// register listeners for the result and failure (status) events

selectStmt.addEventListener(SQLEvent.RESULT, readResultHandler);

selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);

// define the SQL text;

selectStmt.text = "SELECT * FROM authors WHERE lName LIKE '" + txtOut + "%'";

// execute the statement

selectStmt.execute();

}

function readResultHandler(event:SQLEvent):void

{

var result:SQLResult = event.target.getResult();

var numResults:int = result.data.length;

                              //crashes on var numResults:int = result.data.length since it contains no record.

for (var i:int = 0; i < numResults; i++)

{

var row:Object = result.data;

var dataOut:String;

dataOut = row.lName + ", " + row.fName;

myList.addItem({label:dataOut});

addChild(myList);

authorName.text = row.lName;

}

}

function changeHandler(e:Event):void

{

if (0 < txtIn.text.length)

{

myList.removeAll();

txtOut = txtIn.text;

readFromDatabase(e);

}

else

{

myList.removeAll();

}

}

function errorHandler(event:SQLErrorEvent):void

{

trace("Error message: ", event.error.message);

trace("Details: ", event.error.details);

}

Reply

[chris.campbell · Accepted solution]

Hi Kristin,

It's normal behavior for a select statement to return 0 rows of data.  This is not a SQL error, which is why there is no SQLError to catch.

If you take a look at the docs for the SQLResult.data property

The last line of the docs states:

If a statement does not return any data this property is null. This is the case if the statement is not a SELECT statement, or if it is a SELECT statement that returns 0 rows.

So, in order to validate if data was returned before trying to access any actual data, you just need to check if .data is null.

in your code:

var result:SQLResult = event.target.getResult();
var numResults:int = result.data.length;
//crashes on var numResults:int = result.data.length since it contains no record.

You might want to change to be something like:

var result:SQLResult = event.target.getResult();
var numResults:int;
if (result.data != null) 
{
    numResults = result.data.length;
} 
else 
{
    numResults = 0;
}

Thanks,

Chris

[Guest]

Yes, absolutely. That worked. Thank you very much.