Ask Your Question
0

base SQL to return single value for macro

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 close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 809 times

Last updated: Jul 27 '16