Re: [flexcoders] Using TOTAL and AS in the SQL of a Flex app

[Maciek Sakrejda]

Just a hunch, but are you sure that you're allowed to set column aliases
("AS :qtySum") with parameters? That seems a little fishy. Can you try
taking that out but leaving in the other parameters? Do you still get
the same error?

-Maciek


-----Original Message-----
From: bredwards358 <[EMAIL PROTECTED]>
Reply-To: flexcoders@yahoogroups.com
To: flexcoders@yahoogroups.com
Subject: [flexcoders] Using TOTAL and AS in the SQL of a Flex app
Date: Mon, 11 Aug 2008 14:40:44 -0000

I really don't know where to post this, as it involves both SQLite and
Flex 3, but, I'll try here first. Here's what I have in FlexBuilder:

 

sqlText = "SELECT Up18RODetail.ProductID, TOTAL(Up18RODetail.Qty)
AS :qtySum " + 

                                   "FROM Up18RODetail LEFT JOIN
Up18ROHeader ON Up18RODetail.ROID = Up18ROHeader.ROID " + 

                                   "WHERE Up18ROHeader.DateUploaded
Between :startDate AND :endDate " + 

                                   "GROUP BY Up18RODetail.ProductID " + 

                                   "HAVING Up18RODetail.ProductID
= :prodProdID";

                                   

                        transactionStatement = new SQLStatement();

                        conn = new SQLConnection();

                        transactionStatement.sqlConnection = conn;

                        conn.open(dbFile);


transactionStatement.addEventListener(SQLEvent.RESULT,
calcUsePercentResult);


transactionStatement.addEventListener(SQLErrorEvent.ERROR,
errorHandler);

                        transactionStatement.text = sqlText;

                        transactionStatement.parameters[":startDate"] =
startDate;

                        transactionStatement.parameters[":endDate"] =
endDate;

                        transactionStatement.parameters[":qtySum"] =
qtySum; //String variable

                        transactionStatement.parameters[":prodProdID"] =
prodDataArray[i].ProductID

                        transactionStatement.execute();

Now, the SQL actually works in the SQLite database browser, however it
throws a SQL syntax error when I try to run it in my flex app and I'm
not sure why. The error reads SQLError: 'Error #3115: SQL Error.',
details:'near ":qtySum": syntax error', operation:'execute'. I could
sure use a hand here, thanks in advance…

Brian Ross Edwards

Tech-Connect LLC