Problem with ROUND() function

[johnmiller1950]

Greetings All,

I am setting up the following variable in a line of code which is then used to access my database:

amount = one of my database column names

put "SELECT round(amount,2) FROM prlabordist" into dbCommand

When I run my code, I get the following error message: Expected lexical element not found: FROM

If I use: put "SELECT amount FROM prlabordist" into dbCommand, then I get a list showing the "amount" column from my database.

Am I missing something obvious here?

Thanks for your help.

John

[Thierry]

Hi John,

Did you tried this:

Code: Select all

put "SELECT " & round(amount,2) & " FROM prlabordist" into dbCommand

I've started working with SQLITE in LC since few days, so dont' believe me

HTH,

Thierry

[sturgis]

try :

Code: Select all

put "select round(amount,2) as amount from prlbordist" into dbCommand

From: http://www.w3schools.com/sql/sql_func_round.asp

[Thierry]

From: http://www.w3schools.com/sql/sql_func_round.asp

Hi,

thanks for the link, exactly what I need right now

ps: I thought round() was a LC function

Joyeux Noel !

Thierry

[johnmiller1950]

Sturgis,

Thanks for the link. I tried the code you suggested, but I still get the same error.

Any other ideas.

John

[phaworth]

Hi John,
Could you post your LC code that executes the command please. I've tried this here and all works fine (different column name of course!). Only other thing I can think of is that somehow an undisplayable character made it's way into your dbCommand string.
Pete

[Simon]

Hi John,
This is quotation crazy.
In your first post:

Code: Select all

put "SELECT round(amount,2) FROM prlabordist" into dbCommand

You should see:
SELECT round(amount,2) FROM prlabordist in the dbCommand var as the quotations make it a string.
Thierry's makes more sense, lets say amount = 10.3

Code: Select all

put "SELECT " & round(amount,2) & " FROM prlabordist" into dbCommand

would give you SELECT 10.3 FROM prlabordist in dbCommand
But I am guessing that is not what you wanted?

Also check out http://www.fourthworld.com/embassy/arti ... style.html for naming conventions of your variables. It makes it much easier to read and helps others decipher.

Simon

[sturgis]

Hmm if you tried my example using cut and paste, I had a typo in there for the table name. DOH. Might check again to make sure you didn't duplicate my typo.

[phaworth]

Simon,
In the he original post he tried a SELECT of "amount" without the round function and it worked so amount is a column in his database not an LC variable.
Pete

[Mark]

Hi John,

Are you absolutely sure that the datatype of amount is float?

Kind regards,

Mark

[johnmiller1950]

Thanks everybody for your interest.

Here is my script phaworth:

put revOpenDatabase("ODBC","Company 0",,"User Name","password") into dbID -- databaseID
put "SELECT ROUND(amount,2) FROM prlabordist" into dbCommand -- I get error msg
put numtochar(13) into PP
put revDataFromQuery (TAB,PP,dbID,dbCommand) into XX
revCloseDatabase(dbID)
put XX

error msg: "Expected lexical element not found: FROM"

substituting the code below works fine:
put "SELECT amount FROM prlabordist" into dbCommand

Mark, I'm not sure what you mean by "datatype" and "float." Maybe this is where we need to start.

[sturgis]

It sounds like the database you're hitting may not support the round function? What database is on the backside so we can perhaps locate the syntax it expects.

Also, for this particular problem it might be easiest to just grab the data and round it on the livecode side.

Just curious also, is your odbc interface the codebase reduced odbc interface addon? The one discussed on this page..: http://www.codebase.com/products/odbc/technical.htm

Either way, as I mentioned it would most likely be easier to do the round inside lc.

[johnmiller1950]

Sturgis,

Actually, I am trying to update my database using the ROUND() function. I couldn't get ROUND() to work with the UPDATE command so I back off and tried SELECT.
To let LC do the rounding, I would have to . . .
get the amount list (which I know how to do)
have LC round each line in the list (I can do this also)
replace the items in the database (I'm not sure how to do this)

A little help would be appreciated. Thanksd

[sturgis]

Here is a really quick example of how you might do it with lc doing the round.

In this case, (my example) there are only 2 fields for my testing, so I just select * all of them.

Code: Select all

   put revdatafromquery(tab,cr,dbID,"select *  from prlabordist") into tData -- initial query
-- if its empty, don't bother to run do anything else   
if tData is not empty then 

-- set up a string to update the right field based on the primary key (id)
      put "UPDATE prlabordist SET amount = [[tAmount]] WHERE id=[[tId]]" into tSql
-- since the query was done using a tab as one of the delimiters, specify it here
      set the itemdelimiter to tab

-- to speed things up if there is a huge number or rows you can do a begin and commit segment.
-- the only issue would be that if one update in the list fails, they all fail. 
-- But its HUGELY faster to do it this way if there are lots of rows
      revexecutesql dbID,"BEGIN" 

-- loop through the data, 1 line at a time, parse the data, and execute an update for each. 
      repeat for each line tLine in tData
         put item 1 of tLine into tId -- split the data up
         put round(item 2 of tLine,2) into tAmount -- round the value and put it in a var
         revexecutesql dbID,merge(tSql) -- merge the current values into the sql string and execute
      end repeat
      revexecutesql dbID,"COMMIT" -- all lines have been sent, go ahead and commit.   

-- as mentioned you could drop the BEGIN and COMMIT lines and it would still work, but would not be as fast. 
   end if
end mouseUp

Hope this helps. It would surely be nicer though if you could do it all in one select/update and make the db do all the work.

[Mark]

John,

It looks like you are using a SQL database and the database can round a value by itself. You don't need to do this in two steps using LiveCode as an intermediate. You need to make sure that the field in the database is of datatype float. You have to open your database manager, check the datatype of the field, make sure that it is float and not e.g. text or varchar. When you have corrected this, I'd expect the round() function to work.

Kind regards,

Mark