Just another quickie

[dulux1309]

Is there a quick method of returning the contents of a query back to an object? At the moment, I am querying the database for all rows and would like to print the results to a box on the layout as opposed to a debug window, although code to do this would also be appreciated. Once I have this handled, I'll be out of your hair for a while, I promise...:lol

[Worm]

You can use QueryToTable, then use:

TableName.Data[Row]["FieldName"]

to display the info held in that field


Member_num = Input.GetText ("Input1");

--if Member_num is a text field use this
tblResult=SQLite.QueryToTable(db,"SELECT * FROM MEMBERS WHERE Member_num = '" .. Member_num .. "'")

--if Member_num is a numeric field use this
tblResult=SQLite.QueryToTable(db,"SELECT * FROM MEMBERS WHERE Member_num = " .. Member_num)

if tblResult ~= nil then
for Row=1, tblResult.Rows do
Dialog.Message("Row"..nRow, tblResult.Data[n]["Member_num"] .. " " .. tblResult.Data[n]["first_name"] .. " " .. tblResult.Data[n]["last_name"])
end
end

[dulux1309]

Great - thanks for the effort. I'm nearly there!

I have used your code, but am getting an error stating that I am trying to concatenate a null value in 'Row' - is this because there is nothing in the database?

I have included the code again if it makes things easier. I really appreciate the help.

--GET USER INPUTS
member_num = Input.GetText ("Input1");
first_name = Input.GetText ("Input2");
last_name = Input.GetText ("Input3");

--OPEN HANDLE TO DATABASE
db = SQLite.Open("retain\\databases\\retain.db");
--syntax from Worm -write from inputted fields to database..
SQLite.Query(db,"insert into Members values('".. member_num .."','".. first_name .."','".. last_name .."')");

--CREATE QUERY IDENTIFIERS
querynum = "select * from MEMBERS WHERE member_num = "..member_num;
querytxt = "select * from MEMBERS WHERE last_name = '"..last_name.."'";
queryall = "select * from MEMBERS";

--READ QUERY RESULTS INTO TABLE
tblResult = SQLite.QueryToTable(db,queryall);
if tblResult ~= nil then
for Row=1, tblResult.Rows do
Dialog.Message("Row"..nRow, tblResult.Data[n]["member_num"].." "..tblResult.Data[n]["first_name"].." "..tblResult.Data[n]["last_name"]);
end
end
-------------------------------------------------------

I am also intending as the next step to read the returned data back into the input boxes (calling up the record). Can this be done?

Thanks again, you guys are sound...
SQLite.Close(db);

[Worm]

Sheesh, can you tell I'm not testing this code :)


--GET USER INPUTS
member_num = Input.GetText ("Input1");
first_name = Input.GetText ("Input2");
last_name = Input.GetText ("Input3");

--OPEN HANDLE TO DATABASE
db = SQLite.Open("retain\\databases\\retain.db");
--syntax from Worm -write from inputted fields to database..
SQLite.Query(db,"insert into Members values('".. member_num .."','".. first_name .."','".. last_name .."')");

--CREATE QUERY IDENTIFIERS
querynum = "select * from MEMBERS WHERE member_num = "..member_num;
querytxt = "select * from MEMBERS WHERE last_name = '"..last_name.."'";
queryall = "select * from MEMBERS";

--READ QUERY RESULTS INTO TABLE
tblResult = SQLite.QueryToTable(db,queryall);
if tblResult ~= nil then
for n=1, tblResult.Rows do
Dialog.Message("Row"..n, tblResult.Data[n]["member_num"].." "..tblResult.Data[n]["first_name"].." "..tblResult.Data[n]["last_name"]);
end
end

[Worm]

To set the Query results back into the Input objects.


--This sets the input boxes to the values in the
--first record of the query results
Input.SetText("Input1", tblReturn.Data[1]["member_num"]);
Input.SetText("Input2", tblReturn.Data[1]["first_name"]);
Input.SetText("Input3", tblReturn.Data[1]["last_name"]);

--This sets the input boxes to the values in the
--second record of the query results
Input.SetText("Input1", tblReturn.Data[2]["member_num"]);
Input.SetText("Input2", tblReturn.Data[2]["first_name"]);
Input.SetText("Input3", tblReturn.Data[2]["last_name"]);

[dulux1309]

You're gonna have to send me your bank details...:lol

I'll play with this - once again you're saving my bacon. Do you know any good resourses for sqlite? I know most of it is just sql, which I am learning at the moment, but I understand there are some differences...

Thanks

[dulux1309]

I'm now having a problem with the line below -

Input.SetText("Input4", tblResult.Data[1]["..first_name.."]);

It produces an error regarding argument 2 not being a string.

When I try the line below instead, I get the result, but I also get the tblResult.Data[1] before it.

Input.SetText("Input4", "tblResult.Data[1]["..first_name.."]");

Any ideas?

[Worm]

Don't enclose tblResult in quotes.

Input.SetText("Input4", tblResult.Data[1]["first_name"]);

[dulux1309]

That doesn't work. I get the '2nd argument not a string' message.

If I try and move the quote marks around, I get numerous results, but never just the actual returned data...

[Worm]

You must have something wrong then.

I threw this together real quickly, I'm better with samples :)

[dulux1309]

Thanks, I'll pick it over. This has me baffled for the moment!

[Worm]

On parts of this I'm kind of stabbing in the dark because I don't know the structure of your table. If you posted the structure of the table (it's field names and types) I could offer code that was geared toward your table. As it is now, I'm guessing that the structure is like this,


Field Name Type
Member_num integer primary key
first_name text
last_name text

[dulux1309]

That's it in a nutshell - same fields and types. I understand that by assigning an Integer as a primary key it becomes an autonumber in Sqlite. You have the table dead right, though.

[dulux1309]

I have cut and pasted the code into my project and am getting the same error message about the 2nd argument not being a string on the InputSetText - should I start a ticket about this?

[Worm]

Well that's not my call...

My guess is that the record is nil or the field is nil or something along that line. Without having the actual Database, and code, I can't tell.

Make sure you do something like this before setting the text
[code]
if tblResult ~= nil then
Input.SetText("Input1", tblReturn.Data[1]["member_num"]);
Input.SetText("Input2", tblReturn.Data[1]["first_name"]);
Input.SetText("Input3", tblReturn.Data[1]["last_name"]);
else
Dialog.Message("Error", "The query did not return any results.")
end

[dulux1309]

Same thing - error regarding the InputSetText syntax and strings in the second argument, this one on line 27, the first use of the action.

I am entering into all 3 fields into the input boxes, so there shouldn't be any nulls going in there.

For completeness, here is the code I am using to create the database:
---code follows

db = SQLite.Open("retain\\databases\\retain.db");
SQLite.Query(db, "create table MEMBERS(MEMBERSHIPNO integer primary key,NAME text,SURNAME text)", nil);
SQLite.Close(db);
--code ends

I have started a ticket as I am running out of ideas. If this works on your system, I can only start looking at my installation of AMS. Version is Pro 5.0.0.4, which is the latest to my knowledge...

Thanks for the help, though.

[Worm]

Ahhhh!

You need to use the name of the FIELD you created in the database in the code. Think of an Excel Spreadsheet, across the top you have A-Z for columns, and Rows are 1 - Whatever.

The code tblReturn.Data[1]["A"] would give me the value in Row 1, Column A. tblReturn.Data[51]["M"] would give me the value in Row 52, Column M. When referencing the table/database you need to use the names of the FIELD you created so it knows which Columns to look in.


if tblResult ~= nil then
Input.SetText("Input1", tblReturn.Data[1]["MEMBERSHIPNO"]);
Input.SetText("Input2", tblReturn.Data[1]["NAME"]);
Input.SetText("Input3", tblReturn.Data[1]["SURNAME"]);
else
Dialog.Message("Error", "The query did not return any results.")
end

[dulux1309]

Feeling slightly foolish, here:oops

I was confused by the following -

tblResult = SQLite.QueryToTable(db, "Select * from MEMBERS");

Is this reading the entire table into variable 'tblresult', including the names of the fields? I understand that the variable is a table, is this in memory?

This is now working - THANK YOU **HIGH FIVE** !!

I have a lot to learn here, but hopefully this thread will help anyone else just starting out with this fabulous and powerful package.

Worm - you the man.

[Worm]

Yes, to the best of my knowlege, the table resides in memory. I'm sure if it's too big it gets cached out to the hard drive.

SELECT * FROM MEMBERS

Selects ALL records from the MEMBERS table (when using QueryToTable) in AMS. You can then point to the data using the table, row number, and field name. Table.Data[Row]["FieldName"]

SELECT NAME, SURNAME from MEMBERS would only select Name and Surname into the table. If you tried to reference MEMBERSHIPNO, you'd get a nil value.

I'm sure there are way to get around that, but like you, I'm new to this plugin and I'm still learning