Ask Your Question

base SQL to return single value for macro [closed]

asked 2016-07-27 08:32:09 +0200

jvglynnjr gravatar image

If I put a SELECT SQL statement in a Base macro, how can I get it to return a single value to a variable?

In other words, I want to query a value for the unique primary key of a table and get the value from another column of that row.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-09 20:13:20.080670

1 Answer

Sort by » oldest newest most voted

answered 2016-07-27 12:12:54 +0200

peterwt gravatar image

I assume you are running a select query in a Basic routine something like:-

sSQL ="SELECT * FROM ""TestTable1"" WHERE ""CustID"" = n"
result = oStatement.executeQuery(sSQL)

where n is the value of the primary key you are searching for. Running a query in Base in this way creates a result set, result, which will contain all the rows that satisfy the query. In your case this will be one row as you are searching a unique value in the Table. Follow the code above with:-
MyData = result.getString(x) moves to the first row in the result set. MyData is the variable that will be set to the value of the field you want to get where x is the position of the column (I think the first column is 0). This assumes that the field is a string. If it is a different type of field then use:-

result.getInt(x) 'get a Integer value
result.getDouble(x) 'get a Double value
result.getFloat(x) 'get a Float value

and set the variable MyData to the same type.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-07-27 08:32:09 +0200

Seen: 1,135 times

Last updated: Jul 27 '16