Questions about database connector

[hao_zhou]

[ FlexSim 18.1.1 ]

Hi,

I have a few questions about database connector:

1. Is there a way of setting cursor to the front of result set? For example, in MySQL C++ connector, they have function like

resultSet->beforeFirst(); // Put cursor to the beginning

2. Is there a way of getting metadata from result set? For example, in MySQL C++ connector, if we want to print all the column name from result set, we can

sql::ResultSetMetaData *metaData = resultSet->getMetaData();
int nrCols = metaData->getColumnCount();
for (int i = 1; i <= nrCols; i++)
{
     pt(metaData->getColumnName(i));
     pt(", ");
}

3. How do I know the value returned from result set is an int or double? The value we get from resultSet is a Variant. We are able to know if the value is a number or not. But we do not know if the number is a int or double.

Thanks,

Hao

[mischa_spelt · Accepted solution]

It seems that the Database.ResultSet class does not support random access, just forward iteration, and getting metadata like column names or data types is not possible. As an answer to your first two questions then, it is probably best if you just dump the result set to a table using the cloneTo method, and then use the Table interface to get the information you want.

This would be a useful addition, so I would support your request for this, though I am not sure how hard it is to implement. FlexSim uses a C++ library that is able to connect to various databases so presumably they can only add this functionality if that library supports it. In your post you compare to the C++ MySQL-specific interface, but in general it is difficult to make things work consistently across all the database flavors out there.

As for the third question, that would be solved by allowing you to request the data type of a column in the result set; but for now you could do it the "old-fashioned" way - like you do it with any Variant variable, e.g. any of

Variant val = 42;
isInt = Math.frac(val) == 0;
isInt = val == Math.floor(val);
isInt = val - Math.trunc(val) == 0;

[philboboADSK]

"This would be a useful addition, so I would support your request for this"

We purposefully locked down the ResultSet to not support random access. The old db commands hid how they worked from the user, which enabled users to write loops that caused it to run exceptionally slow. The new database api forces the user to do it a certain way so that it performs well with minimal memory and cpu overhead.

If you don't mind the memory overhead required for random access, clone the result set to a table and then access the table, as you explained.

[philboboADSK]

1. No. See the Database.ResultSet documentation for a list of its properties and methods. They are purposefully minimal.

2. In C++, the Database.ResultSet class has a getFieldName(int fieldNum) method on it. I'm not sure why this method wasn't exposed to FlexScript. I'll need to discuss with @anthony.johnson to find out.

3. It never was an "int or double". Previously, it was one of many different database field datatypes. Now it is a Variant. Once the value has been put into a Variant, whether it was originally a bool, short, ushort, long, ulong, double, numeric, interval, etc is gone. It is now just a number. The datatype of the database field where the number came from is not accessible once it has been converted to a Variant. How you cast that number from then on is up to you.

[hao_zhou]

In terms of question 3, to be able to cast the Variant properly, I need to know the original column data type in the database. I noticed that FlexSim has already implemented data type for database writing. Any plan in the future to let user query original column data type?

[philboboADSK]

At the point where we convert the data into a Variant, the original column datatype has already been lost. We use a third-party API to interface with multiple databases. That API has already converted it from the original database datatype into its own set of datatypes that we then convert into a Variant.

We might be able to convert from the API datatypes into our Database.DataTypes (that are used to specify PreparedStatement parameter bindings) before we cast the value into a Variant. I suppose we might be able to return that and let you store it somewhere if you wanted to use it later when binding those values as part of a PreparedStatement.

I suppose something like ResultSet.getFieldType() could be added that returns Database.DataTypes. Again, I would need to discuss with @anthony.johnson.

If you are only concerned with using double or int in FlexScript, then the Variant is storing all numbers as a double, just like number nodes in the tree. Use a double local variable by default unless you know that it is supposed to be a value that is an integer within the int range. If that's the case, cast it to an int.

You have this same issue with all Variant number access in FlexSim, such as label access:

int fred = 6;
current.labelName = fred;

Reading that label later in other code:

double bob = current.labelName;

Whether bob should be a double or an int is left to the writer of the code. The label doesn't tell you whether you "should" cast to an int. You know what to do by nature of what you stored.

The Variant is purposefully number-type agnostic.

[hao_zhou]

Thanks for the detailed explanation Phil.