Data Types
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Data Types
My reading of the SQL example seems to say that only integer, text and boolean are allowed.
If true, how does one handle floats?
If true, how does one handle floats?
Re: Data Types
Buongiorno venanzio,
of course LiveCode supports all datatypes that the used database engine (MySQL, SQLite etc.) supports!
Which DOES include floats!
How did you come to think this?
Best
Klaus
of course LiveCode supports all datatypes that the used database engine (MySQL, SQLite etc.) supports!
Which DOES include floats!
How did you come to think this?
Best
Klaus
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types
Klaus,
In the example given for the SQLite, they say that only integer text and boolean are available.
and from sqllite they have
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.
I have an excel spreadsheet with VBA that I want to convert and my numbers range from 1e3 to 1e-23 and was wondering how to handle it either with sqlite or datagrids.
I can't find the livecode documentation that real is ok and I find lots all over that states that everything is saved as string anyway. very confusing
In the example given for the SQLite, they say that only integer text and boolean are available.
and from sqllite they have
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.
I have an excel spreadsheet with VBA that I want to convert and my numbers range from 1e3 to 1e-23 and was wondering how to handle it either with sqlite or datagrids.
I can't find the livecode documentation that real is ok and I find lots all over that states that everything is saved as string anyway. very confusing
-
- VIP Livecode Opensource Backer
- Posts: 1236
- Joined: Sat Apr 08, 2006 1:10 pm
- Location: Zurich
- Contact:
Re: Data Types
SqLite does not really have datatypes, similar to LiveCode itself. The type designators are mostly for Unique Identifiers (integers), and sorting. So you can store anything you want in your SQLite db, just like a variable in LiveCode can contain anything you want.
Various teststacks and stuff:
http://bjoernke.com
Chat with other RunRev developers:
chat.freenode.net:6666 #livecode
http://bjoernke.com
Chat with other RunRev developers:
chat.freenode.net:6666 #livecode
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types example sqlite
does anyone have an example (simple) that works?
I can't anything to work. the database always is 0 kilobytes. the demo off of runrev does not work well and can't figure out where they are getting the data to feed to it.
thanks in advance
I can't anything to work. the database always is 0 kilobytes. the demo off of runrev does not work well and can't figure out where they are getting the data to feed to it.
thanks in advance
-
- VIP Livecode Opensource Backer
- Posts: 1236
- Joined: Sat Apr 08, 2006 1:10 pm
- Location: Zurich
- Contact:
Re: Data Types
parts of some code of mine. Note that you'd need to fill "theData" to get anything useful out of this
Code: Select all
put revOpenDatabase("sqlite", theSQLPath,,,) into theID
if theID is not a number then
answer "database open problem:" && theID
exit to top
end if
put "reference,file,name,type,description,categories,date" into fieldList
replace comma with quote & comma & quote in fieldList
put quote & fieldList & quote into fieldList
revExecuteSQL theID, "CREATE TABLE myDatabase (id INTEGER PRIMARY KEY AUTOINCREMENT,"& fieldList & ")"
if the Result is not an integer then
answer theID, "SQL table creation failed:" && the Result
exit to top
end if
put 0 into x
repeat for each line theLine in the keys of theData
add one to x
put quote & theLine & quote & comma after theFields
put ":" & x & comma after theIndex
put theData[theLine] into tempData[x]
end repeat
put "INSERT INTO myDatabase" && "(" & theFields & ")" && "VALUES(" & theIndex & ")" into theSQL
revExecuteSQL theID, theSQL, "tempData"
if the Result is not an integer then
answer theID, "SQL insert failed:" && the Result
exit to top
else
answer theSQL
end if
revCloseDatabase theID
Various teststacks and stuff:
http://bjoernke.com
Chat with other RunRev developers:
chat.freenode.net:6666 #livecode
http://bjoernke.com
Chat with other RunRev developers:
chat.freenode.net:6666 #livecode
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types
put "CREATE TABLE users(mgfcode text, model text, mode text(2))" into tSQL
put "INSERT INTO 'users' values( 'EAGLE', 'APU', 'FI')" into tSQL
can someone tell me why the create statement seems to work and the insert new data into the table does not work?? that is I get no compilation or run errors and I get no data added to table either.
thanks
put "INSERT INTO 'users' values( 'EAGLE', 'APU', 'FI')" into tSQL
can someone tell me why the create statement seems to work and the insert new data into the table does not work?? that is I get no compilation or run errors and I get no data added to table either.
thanks
Re: Data Types
Insert data... in which columns ?venanzio@mac.com wrote: put "INSERT INTO 'users' values( 'EAGLE', 'APU', 'FI')" into tSQL
can someone tell me why the create statement seems to work and the insert new data into the table does not work?? that is I get no compilation or run errors and I get no data added to table either.
I mean.... if you try to use SQL... what don't you peruse the gazillons of web ressources that describe the SQL syntax ?
Code: Select all
put "INSERT INTO 'users' (mgfcode,model,mode) values( 'EAGLE', 'APU', 'FI')" into tSQL
Furthermore, don't use "space" in your column names.
Re: Data Types
Actually, as long as the data to be entered is in the order of the columns specified in the table creation, it is valid to omit the destination column names in the insert statement. http://www.w3schools.com/sql/sql_insert.asp
I don't *like* doing it that way. But it is acceptable.
I think the big problem will be spaces in the column names.
I don't *like* doing it that way. But it is acceptable.
I think the big problem will be spaces in the column names.
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types
Actually I have and did and do peruse. The format that I got was from doing so. I understand that it might not be best practice, but since this is ALL that I have found, I am not sure what best practice is. Even the SQL.org site says to do it this way.
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types
this is what worked
put "INSERT into instrument_details VALUES ('EAGLE', 'APU', 'FI', '10V/100mA', '10', '-10', '0.1', '0.00000305', '0.0000125', '0.001', '0.0000004', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0');" into tSQL
put "INSERT into instrument_details VALUES ('EAGLE', 'APU', 'FI', '10V/10mA', '10', '-10', '0.01', '0.000000305', '0.00000125', '0.001', '0.00000004', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0');" after tSQL
and there never ever where any spaces in the column names.
the problem with it not working was the into and the after
just happened on that one. documentation is not really too clear
put "INSERT into instrument_details VALUES ('EAGLE', 'APU', 'FI', '10V/100mA', '10', '-10', '0.1', '0.00000305', '0.0000125', '0.001', '0.0000004', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0');" into tSQL
put "INSERT into instrument_details VALUES ('EAGLE', 'APU', 'FI', '10V/10mA', '10', '-10', '0.01', '0.000000305', '0.00000125', '0.001', '0.00000004', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0');" after tSQL
and there never ever where any spaces in the column names.
the problem with it not working was the into and the after
just happened on that one. documentation is not really too clear
Re: Data Types
You should be more specific. What is the error message you receive ?
Anyway. I see at least 2 points that could make a problem :
-1- datatype : are you sure that your columns can handle a data like "0.00000305" for instance ?
-2- the problem of ";" and into/after
Here basically, you have 2 solutions :
Solution 1:
One insert + one execution
Solution 2 : multiple insert (it will be faster if you a large number of records to insert)
Three insert + one execution
Note the use of comma, not ";"
With such a structure, you can use a repeat / end repeat
Anyway. I see at least 2 points that could make a problem :
-1- datatype : are you sure that your columns can handle a data like "0.00000305" for instance ?
-2- the problem of ";" and into/after
Here basically, you have 2 solutions :
Solution 1:
Code: Select all
put "INSERT into instrument_details (col1,col2) VALUES ('YES','NO')" into tSQL
revExecuteSQL ..........
Solution 2 : multiple insert (it will be faster if you a large number of records to insert)
Code: Select all
put "INSERT into instrument_details (col1,col2) VALUES ('YES','NO'),('YES1','NO1'),('YES2','NO2')" into tSQL
revExecuteSQL ..........
Note the use of comma, not ";"
With such a structure, you can use a repeat / end repeat
Code: Select all
put "INSERT into instrument_details (col1,col2) VALUES " into tSQL
repeat with i = 1 to 10
put "('YES"&i&"','NO"&i&"')," after tSQL
end repeat
delete last char of tSQL
revExecuteSQL .........
-
- Posts: 10
- Joined: Wed May 25, 2011 9:17 am
Re: Data Types
I can't be more specific. NO ERROR MESSAGE. Original did not populate and produced no error message. When I found into and after, worked fine.