Data Types

[venanzio@mac.com]

My reading of the SQL example seems to say that only integer, text and boolean are allowed.
If true, how does one handle floats?

[Klaus]

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

[venanzio@mac.com]

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

[BvG]

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.

[venanzio@mac.com]

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

[BvG]

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

[venanzio@mac.com]

thanks!!

[venanzio@mac.com]

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

[bangkok]

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.

Insert data... in which columns ?

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

http://www.tutorialspoint.com/mysql/mys ... -query.htm

Furthermore, don't use "space" in your column names.

[SparkOut]

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.

[venanzio@mac.com]

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.

[venanzio@mac.com]

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

[bangkok]

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:

Code: Select all

put "INSERT into instrument_details (col1,col2) VALUES ('YES','NO')" into tSQL
revExecuteSQL ..........

One insert + one execution

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 ..........

Three insert + one execution
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 .........

[venanzio@mac.com]

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.