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.
1 Like
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:-
result.next
MyData = result.getString(x)Result.next 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.