Go Back   Northcode Support > André Goliath Software & Solutions
FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
Old 2011-02-09, 07:25 AM   #1
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
Question SQLite plugin and Flex development problems

I have a few questions regarding the SQLite plugin.

We have purchased the plugin and used it on a project built with Flash (this was before I joined the company, developed by someone else).

We are starting a new project using Flex 4 and would like to use the plugin again. We would normally use stardard Air/Flex when developing a desktop app but our client is a large corporate and has locked-down PCs, making this option troublesome, so SWF Studio is a good alternative.

I have created a couple of test apps in Flex, built them in SWF studio with some problems as follows (full code of the test app is below):

1. (Not a problem as such) the SQLite class must be inside a Flex app package.

2. There is an error when compiling the swf:
Code:
Encountered errors or warnings while building project sqltest.mxml.
SQLite.as: Incorrect number of arguments.  Expected 2.
This seems to relate to the function 'fireError', where it is being called multiple times throughout the class without the second parameter. By changing the declaration from
Code:
private function fireError(errorNumber, err_obj) {
to
Code:
private function fireError(errorNumber, err_obj = null) {
I can suppress the error, but I'm not sure this is a long term solution

3. Once compiled and built in SWF Studio successfully, no new database is created (C:\test.db)

4. If I manually create the DB using SqLite Maestro, then run the app, the table is NOT created

Has anyone used the plugin in a Flex/SWF Studio project successfully. Can anyone provide some pointers as to how to proceed with this?

Otherwise I fear it will have to be Air...

Thanks in advance

Paul

Code:
<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" 
			   xmlns:s="library://ns.adobe.com/flex/spark" 
			   xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600"
			   creationComplete="init()">
	<fx:Script>
		<![CDATA[
			public function init():void
			{
				ssDebug.trace('Starting SQLite');
				var sqlite = new SQLite({defaultDB:"C:\\test.db"});
				var sql = "CREATE TABLE phonebook (name varchar, phonenumber int);";
				var query_obj = sqlite.query({command:sql });
				if (!query_obj.success) {
					ssDebug.trace("Query Failed:\r"+query_obj.Error.description+" (Code "+query_obj.Error.code+")");
				}
			}			
		]]>
	</fx:Script>
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
</s:Application>
dexadrine is offline   Reply With Quote
Old 2011-02-09, 10:57 AM   #2
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
The AS class that came with the connector was an AS2 class.
While (good) AS2 classes looks pretty similar to AS3 classes there are a few differences,
you found one with the fireError declaration for example.

Try the attached class instead, this is native AS3 and see if that works better for you.
It works fine in a Flash IDE project at least, I don´t think I ever tried it in Flex though, but
can´t see why it should not work there as well.
Attached Files
File Type: as SQLite.as (24.1 KB, 339 views)

Last edited by AGo; 2011-07-18 at 04:10 AM. Reason: The as file now contains the fix mentioned below
AGo is offline   Reply With Quote
Old 2011-02-09, 11:01 AM   #3
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
here is a little sample code that shows the main difference, the way to access the resultsets as dataproviders ("result" is a instance of a datagrid component)

ActionScript Code:
import fl.data.DataProvider;

//Creates an Instance of the SQLite Class and register the Error Callback
var sqlite:SQLite = new SQLite({onError:onError});
//
//Error Callback
function onError(error_obj) {
    ssDebug.trace(error_obj.Error.description+" (Code "+error_obj.Error.code+")");
}
sqlite.setDefaultDB({DB:"startdir://World.db"});
ssDebug.trace("Found Tables: "+sqlite.getTables().result.join(" "));
var select_obj:Object = sqlite.select({command:"select * from City"});
result.dataProvidernew DataProvider(select_obj.result);
AGo is offline   Reply With Quote
Old 2011-02-09, 11:40 AM   #4
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
Question

Thanks for your prompt reply.

I have tried the updated sqlite.as file and this works better (no compile errors).

It now creates the database if it doesnt exist and reads the tables in it (using your sample code).

However, when I try to use a CREATE TABLE query, nothing happens. Here is my test code init() function:

Code:
public var sqlite:SQLite;
public function init():void
{
	ssDebug.trace('Starting SQLite');				
	//Creates an Instance of the SQLite Class and register the Error Callback
	sqlite = new SQLite({onError:onError});
	//Error Callback
	function onError(error_obj) {
		ssDebug.trace(error_obj.Error.description+" (Code "+error_obj.Error.code+")");
	}
	sqlite.setDefaultDB({DB:"startdir://new.db"});
	var query_obj = sqlite.query({command: "CREATE TABLE phonebook (name varchar, phonenumber int);"});
	ssDebug.trace("Query success: " + query_obj.success);
	if (!query_obj.success) {
		ssDebug.trace("Query Failed: "+query_obj.Error.description+" (Code "+query_obj.Error.code+")");
	}
	ssDebug.trace("Found Tables: "+sqlite.getTables().result.join(" "));
}
I get the initial trace 'Starting SQLite' then no more, no errors.
dexadrine is offline   Reply With Quote
Old 2011-02-09, 11:47 AM   #5
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
first try to put your error callback into your main scope, and do not declare it inside the init function.

Then, if you dont get any more trace results after the first I bet you´ll run into some kind of runtime error. Compile your SWF Studio exe with the Flash Debug Player and use the debug binary produced by Flex, that should give you some decent error messages we can work with.
AGo is offline   Reply With Quote
Old 2011-02-09, 12:22 PM   #6
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
Question

I have moved the error callback into the main scope as you suggest, but still same result.

Code:
public var sqlite:SQLite;
			public function init():void
			{
				ssDebug.trace('Starting SQLite');
				sqlite = new SQLite({onError:onError});				
				sqlite.setDefaultDB({DB:"startdir://new.db"});
				var query_obj = sqlite.query({command: "CREATE TABLE phonebook (name varchar, phonenumber int);"});
				ssDebug.trace("Query success: " + query_obj.success);
				if (!query_obj.success) {
					ssDebug.trace("Query Failed: "+query_obj.Error.description+" (Code "+query_obj.Error.code+")");
				}
				ssDebug.trace("Found Tables: "+sqlite.getTables().result.join(" "));
			}
			//Error Callback
			function onError(error_obj) {
				ssDebug.trace(error_obj.Error.description+" (Code "+error_obj.Error.code+")");
			}
When I run it in Debug Player, this is the output:
Code:
TypeError: Error #1010: A term is undefined and has no properties.
	at sqltest/init()
	at sqltest/___sqltest_Application1_creationComplete()
	at flash.events::EventDispatcher/dispatchEventFunction()
	at flash.events::EventDispatcher/dispatchEvent()
	at mx.core::UIComponent/dispatchEvent()
	at mx.core::UIComponent/set initialized()
	at mx.managers::LayoutManager/doPhasedInstantiation()
	at mx.managers::LayoutManager/doPhasedInstantiationCallback()
dexadrine is offline   Reply With Quote
Old 2011-02-09, 12:40 PM   #7
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
My guess is that the function is now marked private since it does not have an access modifier to it, so sqlite.as is not allowed to call it, make it look like this

ActionScript Code:
public var sqlite:SQLite;

public function onError(error_obj:Object) {
    ssDebug.trace(error_obj.Error.description+" (Code "+error_obj.Error.code+")");
            }

            public function init():void
            {
                ssDebug.trace('Starting SQLite');
                sqlite = new SQLite({onError:onError});    
                sqlite.setDefaultDB({DB:"startdir://new.db"});
                var query_obj = sqlite.query({command: "CREATE TABLE phonebook (name varchar, phonenumber int);"});
                ssDebug.trace("Query success: " + query_obj.success);
                if (!query_obj.success) {
                    ssDebug.trace("Query Failed: "+query_obj.Error.description+" (Code "+query_obj.Error.code+")");
                }
                ssDebug.trace("Found Tables: "+sqlite.getTables().result.join(" "));
            }


If I remember correctly from my Flex days the compiler should give you your main.swf and something like main-debug.swf (or you need to enable it somewhere, havent used flex for ages)
If you use that swf as your main movie the Flash debug player gives you the exact lines where the error is raised, that should shed some light what goes wrong.
AGo is offline   Reply With Quote
Old 2011-02-09, 01:10 PM   #8
mbd
Registered User
 
mbd's Avatar
 
Join Date: Jun 2002
Location: Ottawa
Posts: 4,430
AGo asked me to comment, since I've got Flex Builder ready to go on my system. Here's a link to debugging a SWF Studio app from Flash (IDE) and Flex Builder:
http://www.northcode.com/forums/show...33&postcount=4

For Flex Builder the real benefit is in knowing what line of code is causing the issue, and getting any trace messages showing up in the Flex Console panel.

NOTE: the instructions were for Flex Builder 2. While most of the procedure is unchanged for FB3 I make no guarantees for FB4. One significant change going to FB3 is the naming of the SWF. FB2 produced 2 SWFs, as AGo mentioned: one debug and one non-debug. In FB3 it produces only one, and the naming will not include "_debug", unless you've named your project that way. The default is to produce a debug SWF in a folder called bin-debug. If you want a non-debug version you need to add -debug=false in the compiler option for the project.

So, step 3 in the above link is not necessary for FB3 (and I'm guessing FB4), since your main movie's name doesn't have to change.
__________________

Derek Vadneau (mbd) - Northcode Support

Home | Download Free Trial | Buy / Upgrade
How does SWF Studio Work? | Online Help | Knowledge Base (FAQs) | Examples | Known Issues

ActionScript API:
Code Wizard - New to SWF Studio V3? Step through this wizard and sample code is displayed.

FAQ:
How do I enable synchronous commands?
mbd is offline   Reply With Quote
Old 2011-02-10, 04:33 AM   #9
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
Lightbulb

First off, thanks to both of you for your help with this issue. You will be glad to hear I have tracked down the cause of the issues.

I was thinking that if the 'select' function worked ok, why doesnt the 'query' function? As 'select' uses the 'query' function internally.

Firstly I used a similar function call to that which happens when you use 'select':

Code:
var sql:String = "CREATE TABLE phonebook (name varchar, phonenumber int);";
var query_obj:Object = sqlite.query({caller:"query", rowSeparator:"\n", fieldSeparator:"|", singleTransaction:false, DB:"startdir://new.db", command: sql, withHeaders:"false"});
I found that this works OK...

So by process of elimination I found that the param 'withHeaders' was causing the issue. If this was omitted from the function call, the function would not work. Looking at the 'query' function declaration (SQLite.as Line 152) if we change the test condition to a boolean test (which it should be, strictly speaking):
From:
Code:
if (param_obj.withHeaders.toLowerCase() == "true" || param_obj.withHeaders == true) {
	options += " -header ";
} else {
	options += " -noheader ";
}
to:
Code:
if (param_obj.withHeaders) {
	options += " -header ";
} else {
	options += " -noheader ";
}
The function call will now work and the sql will be executed.
dexadrine is offline   Reply With Quote
Old 2011-02-10, 04:38 AM   #10
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
Thanks for investigating!

Indeed that´s a flaw I oversaw at the time I was rewriting the class from AS2 to AS3.

The problem arises if you ommit the parameter or pass a boolean,
in that case you´ll hit a Null Reference exception at the "toLowercase" call.
I´ll fix that in the as as soon as I have the time to do so.
AGo is offline   Reply With Quote
Old 2011-02-10, 08:11 AM   #11
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
Exclamation

Further to the issues above, I've found that CREATE TABLE IF NOT EXISTS commands return an error:
Code:
SQL error: near "NOT": syntax error
 (Code 98)
CREATE TABLE IF NOT EXISTS retailers ( retailer_id  int PRIMARY KEY NOT NULL DEFAULT 0, created datetime NOT NULL, updated datetime NOT NULL, name varchar(100) NOT NULL,active tinyint NOT NULL DEFAULT '1', updated_by int NOT NULL DEFAULT '0', created_by int NOT NULL DEFAULT '0');
The sql above works fine if run in SQLite Maestro.

It works OK if the query is a CREATE TABLE type. Any ideas?
dexadrine is offline   Reply With Quote
Old 2011-02-10, 08:21 AM   #12
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
The SQLite Connector uses SQLite 3.2 which has no support for the IF NOT EXISTS statement. So to emulate that behaviour you should check getTables before executing the sql (or simply ignore the error returned by CREATE TABLE)
AGo is offline   Reply With Quote
Old 2011-02-10, 08:57 AM   #13
dexadrine
Registered User
 
Join Date: Feb 2011
Posts: 10
OK, thanks for the clarification.
dexadrine is offline   Reply With Quote
Old 2011-06-23, 04:53 AM   #14
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
A class without warnings

@AGo Hope you don't mind me asking but have you ever thought about getting rid of the warnings on this class? I tried myself but realised it would take a while for someone who doesn't know the code well.

Kind regards,

Chris
ChrisGrigg is offline   Reply With Quote
Old 2011-06-23, 05:01 AM   #15
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
d'oh I just realized I never uploaded the fix described in this thread to the AS3 version, Ill do so as soon as I get to the office. Thanks for the heads-up.

Chris, are you running into any other warnings or errors? Currently I´m not aware of any other issues with the AS3 version.
AGo is offline   Reply With Quote
Old 2011-08-25, 06:01 AM   #16
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
Ago, the class works well for me, but in the Flash Builder 4 IDE there are about 70 warnings. It simply means it's harder to see if my code has any warnings or isn't strict.

Regards
ChrisGrigg is offline   Reply With Quote
Old 2011-08-25, 06:17 AM   #17
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
Thanks for the headsup. Unfortunately I do not own FB4, but if you could send me the raw output of the compiler and the warnings I´ll happily fix them
AGo is offline   Reply With Quote
Old 2011-08-25, 07:39 AM   #18
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
Ok I have copied all the warnings and included them in an attached document.

Thanks
Attached Files
File Type: doc WarningsOnSQLLite.doc (33.5 KB, 246 views)
ChrisGrigg is offline   Reply With Quote
Old 2011-09-27, 09:58 AM   #19
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
@Ago how was that list of compiler warnings for you?

Regards

Chris
ChrisGrigg is offline   Reply With Quote
Old 2011-10-03, 08:28 AM   #20
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
d'oh sorry, I totally forgot about this...

Try this attached class, you should be able to drop it in and it should just work without warnings.

That beeing said, I have not tested it. 99% of the warnings where missing datatype annotations so it should not change anything semantically but you never know,...
Let me know if you still get errors (this time you'll get a prompter response, I promise )
Attached Files
File Type: as SQLite.as (24.5 KB, 210 views)
AGo is offline   Reply With Quote
Old 2011-11-25, 10:36 AM   #21
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
@Ago, thanks for the new file, there are no warnings displayed

Do you have an example file that invokes this re-factored sqlite.as file, for example I was given a folder of example files allowing AS and SS to interact with an sqlite db?
ChrisGrigg is offline   Reply With Quote
Old 2011-11-26, 03:34 AM   #22
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
the method signatures have not changed (except for now beeing strong typed) so you should really be able to simply drop in the new class and it should work just fine. Do you get any errors?
AGo is offline   Reply With Quote
Old 2011-11-28, 06:50 AM   #23
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
I had to change this line:

this._query_obj = this._sqlite.query("query", query_txt, false, "", "", "", true);

to this:

this._query_obj = this._sqlite.query({caller:"query", rowSeparator:"", fieldSeparator:"", singleTransaction:true, DB:"", command: query_txt, withHeaders:"false"});

Maybe there's something I am doing wrong here?

The error is occurring because the code is looping infinitely between:

// check if the DB is readable, don´t forget to specify the fireEvents:FALSE flag here!
this._readOK_obj = this._sqlite.DBreadable({fireEvents:false});

var fireEvents:Boolean = !(TRIM(param_obj.fireEvents).toLowerCase() == "false");
if (fireEvents) {
fireEvent({event:"onOpen", DB:queryDB, command:"DBreadable"});
}

private function fireEvent(event_obj:Object):void {
if (this[event_obj.event] !== undefined && this[event_obj.event] !== null) {
this[event_obj.event].apply(null, [event_obj]);
}
}

Thanks
ChrisGrigg is offline   Reply With Quote
Old 2011-12-07, 06:26 AM   #24
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
I get no errors, but there is an infinite loop occurring, the trace statements are as follows:

[SQLRepository.onOpenDB] - DBreadable is going to open Database Metrics.db!
[SQLite.validateDB3] = Metrics.db
[SQLite.validateDB4] = Metrics.db
[SQLite.DBreadable] = Metrics.db
[SQLite.DBreadable2] = true
[SQLite.DBreadable3] = Metrics.db
[SQLite.fireEvent] = onOpen
[SQLite.fireEvent2] = onOpen
[SQLRepository.onOpenDB] - DBreadable is going to open Database Metrics.db!
[SQLite.validateDB3] = Metrics.db
[SQLite.validateDB4] = Metrics.db
[SQLite.DBreadable] = Metrics.db
[SQLite.DBreadable2] = true
[SQLite.DBreadable3] = Metrics.db
[SQLite.fireEvent] = onOpen
[SQLite.fireEvent2] = onOpen

So the fireEvent method goes no further?

Thanks
ChrisGrigg is offline   Reply With Quote
Old 2011-12-08, 02:17 PM   #25
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
hm it´s hard to tell from the traces what´s going wrong. Can you show the code that produces this?

Do your event listeners try to work with the DB that would in turn fire the listener again?
AGo is offline   Reply With Quote
Old 2011-12-09, 04:47 AM   #26
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
holy crap, I totally oversaw the post before, sorry for that!

The line you marked red seems to be the issue, change

var fireEvents:Boolean = !(TRIM(param_obj.fireEvents).toLowerCase() == "false");

to

var fireEvents:Boolean = param_obj.fireEvents;

that should fix it in the first place, I will have a look at the code again at the weekend and make sure no more similar bugs exists. This bug was introduced due to the strict typing I introduced with the rewriting to get rid of the warnings.

I´m really sorry for the inconveniences caused...
AGo is offline   Reply With Quote
Old 2011-12-12, 09:45 AM   #27
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
That has fixed the problem, thanks for that.
ChrisGrigg is offline   Reply With Quote
Old 2011-12-14, 05:18 AM   #28
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
Not sure if your getting a little tired of my posts @Ago but I have another question :-)

I am attempting to allow all DB transactions to execute at once so the journal file is invoked once rather than on each transaction to speed up DB processing. I got this tip from:

http://notes.theorbis.net/2010/02/ba...n-android.html

So am hoping this is relevant. I attempting to implement this myself, but thought you may have useful thoughts on this?

Thanks again

Chris
ChrisGrigg is offline   Reply With Quote
Old 2011-12-14, 05:40 AM   #29
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
from the help of the "query" command...

Quote:
You should use “singleTransaction” whenever possible. It will increase the speed of the transaction up to 500% sometimes, especially on INSERTs and UPDATEs. See http://www.sqlite.org/cvstrac/wiki?p...ceTuning#trans for more details.
However, the default is false because it can break some commands,
including some dot-commands.
It has the same effect as including your commands in a
BEGIN TRANSACTION;
...
COMMIT TRANSACTION;
shell.
So, yes, if you do multiple inserts just do them all at once with the query command within a single transaction, it should work nicely.
(dont rely on the singleTransaction parameter though, just add the BEGIN/COMMIT statements around your sql by yourself)
AGo is offline   Reply With Quote
Old 2011-12-15, 04:59 AM   #30
ChrisGrigg
Registered User
 
Join Date: Apr 2011
Posts: 20
Cool

Just to try to conclude, my understanding is because the ssCore.Shell.Execute code is utilised, it is not possible to open the db connection, utilise batch processing by looping through all SQL commands whilst the db connection is still open, then close the DB connection?

Thanks
ChrisGrigg is offline   Reply With Quote
Old 2011-12-16, 09:26 AM   #31
AGo
Plugin Developer
 
Join Date: Jun 2002
Location: Germany
Posts: 2,415
Right, but you can perform multiple queries while the DB is open using sqlite.query. Just pile all your commands up in a single string with ";" inbetween. That´s the point where you should surround your commands with
"BEGIN ; ... ; COMMIT" to get the desired effect.
AGo is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes