Retrieve all the rows of resultset without knowing the length

[Heychsea]

Hello,

I've been trying to use the "Row limit" parameter in my SQL properties to get the full result of my query (simulate an array), but it has the same behavior.

How does it work and how am I supposed to get several results ?

[bcivel]

Hello @Heychsea, For the SQL Properties, the row limit will determine the max number of row to get from the database. Then, regarding the nature (Static / Random....etc), it pick one date from this resultset. The other limit of the sql directly in the property is that it can retrieve only 1 data (1 row / 1 column)

If you need to use an array of data, I would recommend to use a datalib. On the datalib, you can specify an SQL Query, and map data you retrieve from resultset to an object (defining subdata). Then, in the testcase, you create a property (exemple PRODUCT) that is getFromDatalib (for exemple AVAILABLE_PRODUCT that contains subdata EAN, COLOR, SIZE)

Then, in the testcase, you can use it with this syntaxe : %PRODUCT.2.COLOR% (Which basically mean PROPERTY.ROW.SUBDATA)

[Heychsea]

Hello,

Well, thanks for the answer. Unfortunately, I don't really understand how the datalib works.

I created the datalib "TEST" with its query and its subdatas (ID, DAT, DECI) each corresponding to the columns of my table.

Then, I tried to call the property, but nothing happens.

Where am I doing things the wrong way ?

[bcivel]

On the subdata, you must feed a column name for the first line. That will be considered as the key. You can put ID again if ID is the key of your table.

Then I would suggest to use : calculateProperty > property.test (Without % because calculateProperty is the only action that don't need % mark)

Then, on other action or control, use %property.test.1.ID% should work fine.

[Heychsea]

I tried what you've said and I managed to calculate the Property.

The first issue is the number of row extracted, which is not variable, I need to set it in the "Length" param if I want several results, and if the "Length" is not the same as what I can get from the Query, the calculation fail.

The second issue is about storage, where does my results go ? I've noticed a return message where my query results are in a JSON format, but the values are not updated anywhere and the %PROPERTY.ROW.SUBDATA% is not usable.

[bcivel]

About your first issue, we designed it that way : If it's defined that you need 100 row of that data, and finally, you can only get 40, your test can't be executed because you don't have enough data.

About your second issue, did you define subdata called KEY? It looks like no. Basic usage is : %property.test% will give the value of the key (the subdata with no name). %property.test.2% will give the value of the key for the second row. %property.test.1.ID% will give the value of the subdata ID for the first row.

is that more clear? The full result is stored as a json document. the key for each row is stored in database. But the full result is kept in memory during the execution, and all of them can be called in any action/control

[Heychsea]

Ok, the second issue has been solved, I misunderstood how the key worked.

Getting back to the first issue, is it possible to set an "unknown" length, given that I don't know how much data I will get ?

And lastly, the Length option is not present in the getFromDataLib type, I need to switch back to text to change it every-time.

[bcivel]

OK, I fixed the display of the Length when property is getFromDatalib.

As you know now how to retrieve array of result, I suggest to rename the issue into "Retrieve all the rows of resultset without knowing the length".

Let's think a bit on it before proposing the best implementation. Could you tell a bit more about the usage of this things?

[Heychsea]

Let's say I have X clients. I must execute a test on each client ID. So I want to retrieve the IDs of my X clients so I can test each of them. In this case, I can't predict how many rows I will get.

[vertigo17]

In fact, I don't really understand the need of testing all clients. If 1 client is working or failing on that test, why would the other clients in the list provide a different result ? I would more suggest that you secure that the client tested is representative to the test controls and test only 1. That would speedup the test result and allow you to test more cases of over client where the expected behaviour is different. Thought, if you want to test more client on the same testcase, you can use the nature (defined at property level) in order to force each execution to take a new value RANDOM and/or RANDOMNEW and trigger multiple executions.

Did I understand right ?

[Heychsea]

Not exactly, in my case, I need to test a value for each client (one query by client), I just don't know the number of client I would get with my first query (change often).

[vertigo17]

1 query by client ? I though that it was 1 query returning a certain number of client and you wanted to loop on each of them to test all of them ? Again why would 1 client work and the next one fail if the controls are the same. I guess the reason if that client query is not specific enought in order to test the right case.

[Heychsea]

I have the first query with the undetermined number of clients, and I loop over this list.

Ex: list = "SELECT client_id FROM clients" foreach id of list execute query "SELECT status FROM table WHERE client_id = id"

At this time, I still need to check the number of clients before creating the datalib (length) and my loop condition.

[vertigo17]

Don't understand why you use 2 query why don't you: SELECT client_id, status FROM clients JOIN table ON table.client_id = clients.client_id

Also still don't understand why you need to loop on all of them.

We will probably have to share that conversing together. Can you get in touch with me on my email ?

[Heychsea]

Not a problem

[vertigo17]

Let's connect on linkedin https://www.linkedin.com/in/benoit-dumont-82666a3a/