Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Adobe AIR Development

Reply
Developer
EraserX
Posts: 168
Registered: ‎01-13-2011
My Device: Not Specified

Inexplicable SQLite Error!!

I've been racking my brain for hours trying to figure this out and I can't so I'm hoping someone here might be able to help me.

 

I'll start off by saying that I use an SQLite database in all of my apps so I am familiar with them.  I can't figure out why I am getting an error when I try to update a value from a particular table in my db.

 

Here is my table structure:

 

CREATE TABLE Units (unitID INTEGER PRIMARY KEY, categoryID NUMERIC, unitNameString TEXT, unitSymbol TEXT, unitValue NUMERIC, favorite NUMERIC, updateDate NUMERIC);

 

 

Here is my update function:

 

sqlStatement.clearParameters();
var sql:String = "UPDATE Units SET favorite = :favorite " +
		"WHERE unitID = :unitID ";
sqlStatement.parameters[":favorite"] = favorite;
sqlStatement.parameters[":unitID"] = unitID;
sqlStatement.text = sql;
sqlStatement.execute();
sqlResult = sqlStatement.getResult();

 

 

favorite and unitID are both integers.  I have confirmed that they are passing properly. I get the following error:

 

 

SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.', operation:'execute', detailID:'2300'

 

 

I have tried putting in literal values and I get the same error!

 

"UPDATE Units SET favorite = 1 " +
		"WHERE unitID = 1 ";

 

SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.', operation:'execute', detailID:'2300'

Even STRANGER I tried updating a String in my Units table, even without a WHERE clause and I get the same error!

"UPDATE Units SET unitNameString = 'test' ";

SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.', operation:'execute', detailID:'2300'

I'll note that I use data from my Units table throughout my app without error.  Anyways, so now convinced something screwy must be going on, I rebuild my database... the error persists.

 

Has anyone seen something like this?

Please use plain text.
Developer
JRab
Posts: 2,462
Registered: ‎11-04-2010
My Device: Bold 9700

Re: Inexplicable SQLite Error!!

hmm you're right, i really cant find the error. whats more weird is that i always thought SQLite was ok with datatype mismatches and wasnt so strict when putting in the wrong datatype. this shows the complete opposite.

J. Rab (Blog) (Twitter)
--
1. If you liked my post or found it useful please click on the thumbs up and provide a Like!
2. If my post solved your problem please click on the Accept as Solution button. Much appreciated!

Approved Apps: OnTrack | ssShots | Hangman
Please use plain text.
Developer
EraserX
Posts: 168
Registered: ‎01-13-2011
My Device: Not Specified

Re: Inexplicable SQLite Error!!

I've been trolling the internet for answers, and I think I may know the cause.  Turns out SQLite in AIR is EXTREMELY picky about enforcing data types.   See http://chrisgriffith.wordpress.com/2011/02/03/error-3132-in-air-and-sqlite/  The author of that article had an invalid value somewhere in his db and when he tried to update a column, it was not until then that AIR decided to complain about it.

 

My db has 600+ rows so I'm working on figuring out if one of the values is the culprit. 

 

I'll report back.

Please use plain text.
Developer
EraserX
Posts: 168
Registered: ‎01-13-2011
My Device: Not Specified

Re: Inexplicable SQLite Error!!

I got it!  I imported my data from a CSV.  My "updateDate" column was all just empty.  Apparently AIR wasn't happy about that, because when I inserted a dummy value (-1) into that column, the stars aligned and BAM my db update statement was working again.

 

So here's the lesson.  If you are getting mismatch errors, take a close look at the data in your columns.  It is probably the culprit.

Please use plain text.
Developer
JRab
Posts: 2,462
Registered: ‎11-04-2010
My Device: Bold 9700

Re: Inexplicable SQLite Error!!

man i gotta stop making up my own SQLite rules then! good job on hunting down your error though hah

J. Rab (Blog) (Twitter)
--
1. If you liked my post or found it useful please click on the thumbs up and provide a Like!
2. If my post solved your problem please click on the Accept as Solution button. Much appreciated!

Approved Apps: OnTrack | ssShots | Hangman
Please use plain text.
Developer
peter9477
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Inexplicable SQLite Error!!

SQLite itself is totally lax when it comes to enforcing types.  The problem is specifically the AIR API for SQLite in flash.data.  Interesting, and good to know.


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Please use plain text.
Regular Visitor
gaveho
Posts: 1
Registered: ‎07-03-2012
My Device: Samsung Galaxy SII - GT-I9100
My Carrier: Digitel

Re: Inexplicable SQLite Error!!

I had the same problem. It was the data on my table. It was empty and needed a dummy value like 0.

Please use plain text.