Questions about database connector

Question

question

Hao Zhou asked May 17 2018 at 11:09 PM Hao Zhou commented May 18 2018 at 7:46 PM

Questions about database connector

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[i] 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

Software Version:

FlexSim 18.1.1

database connector

· 4

Phil BoBo ♦♦ commented · May 18 2018 at 3:26 PM

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.

1 ·

Hao Zhou Phil BoBo ♦♦ commented · May 18 2018 at 5:52 PM

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?

0 ·

Phil BoBo ♦♦ Hao Zhou commented · May 18 2018 at 5:57 PM

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.

1 ·

Show more comments

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2018-05-18T15:01:37Z

Mischa Spelt answered May 18 2018 at 3:01 PM Phil BoBo edited May 18 2018 at 3:34 PM

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;

· 1

question