Problem with ROUND() function
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 113
- Joined: Mon Apr 30, 2007 3:33 pm
- Location: Dalton, Ohio
Problem with ROUND() function
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
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
Re: Problem with ROUND() function
Hi John,
Did you tried this:
I've started working with SQLITE in LC since few days, so dont' believe me ![Smile :)](./images/smilies/icon_smile.gif)
HTH,
Thierry
Did you tried this:
Code: Select all
put "SELECT " & round(amount,2) & " FROM prlabordist" into dbCommand
![Smile :)](./images/smilies/icon_smile.gif)
HTH,
Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!
Re: Problem with ROUND() function
try :
From: http://www.w3schools.com/sql/sql_func_round.asp
Code: Select all
put "select round(amount,2) as amount from prlbordist" into dbCommand
Re: Problem with ROUND() function
Hi,sturgis wrote:
From: http://www.w3schools.com/sql/sql_func_round.asp
thanks for the link, exactly what I need right now
![Smile :)](./images/smilies/icon_smile.gif)
ps: I thought round() was a LC function
![Sad :(](./images/smilies/icon_sad.gif)
Joyeux Noel !
Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!
-
- Posts: 113
- Joined: Mon Apr 30, 2007 3:33 pm
- Location: Dalton, Ohio
Re: Problem with ROUND() function
Sturgis,
Thanks for the link. I tried the code you suggested, but I still get the same error.
Any other ideas.![Confused :?](./images/smilies/icon_confused.gif)
John
Thanks for the link. I tried the code you suggested, but I still get the same error.
Any other ideas.
![Confused :?](./images/smilies/icon_confused.gif)
John
Re: Problem with ROUND() function
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
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
-
- VIP Livecode Opensource Backer
- Posts: 3901
- Joined: Sat Mar 24, 2007 2:54 am
- Location: Palo Alto
Re: Problem with ROUND() function
Hi John,
This is quotation crazy.
In your first post:
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
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
This is quotation crazy.
In your first post:
Code: Select all
put "SELECT round(amount,2) FROM prlabordist" into dbCommand
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
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
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!
Re: Problem with ROUND() function
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. ![Smile :)](./images/smilies/icon_smile.gif)
![Smile :)](./images/smilies/icon_smile.gif)
Re: Problem with ROUND() function
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
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
Re: Problem with ROUND() function
Hi John,
Are you absolutely sure that the datatype of amount is float?
Kind regards,
Mark
Are you absolutely sure that the datatype of amount is float?
Kind regards,
Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
-
- Posts: 113
- Joined: Mon Apr 30, 2007 3:33 pm
- Location: Dalton, Ohio
Re: Problem with ROUND() function
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.
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.
Re: Problem with ROUND() function
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.
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.
-
- Posts: 113
- Joined: Mon Apr 30, 2007 3:33 pm
- Location: Dalton, Ohio
Re: Problem with ROUND() function
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
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
Re: Problem with ROUND() function
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.
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.
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
Re: Problem with ROUND() function
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
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
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode