Apache Ignite 2.17 - View Data in SQL table vs Cache

I am using Apache Ignite 2.17 thin client C++ to write records.
I created a schema and a table with a cache called RESULTS. Initially, I was using SQLFieldsQuery, and the Data was available. However, I had to switch to the Key Value API to use putAll. Now, the cache size increases, but the table is empty.

My question/issue is that I want the data to be available within the tables as well. Is it not interoperable? I read in the documentation that both are just representations, and internally, they are the same.

I think this is happening because the data is being put as a string with a key via the Key value API and is not compatible with the TABLE structure. At this point, I am very confused as to how I can make this work. I found that the binary Object API is not available in C++, with which I could register the object template.

Final questions:

Should I revert to SQLFieldsQuery, but then how do I deal with the cursor issue I mentioned below?What's the right way to close a cursor? All of the cursor calls are within try catch, so shouldn't it destroy them as soon as it returns
Key Value API has putAll, which seems to be a better approach, but then how do I make it in a way that data still appears in the Tables?

Implementation Details

Through the C++ client, I was inserting records in a loop: About 80k records using SqlFieldsQuery

try
                {
          ignite::thin::cache::query::SqlFieldsQuery sqlFieldsQuery(query);
            tableCache.Query(sqlFieldsQuery);
            totalRows++;
        }
        catch (const std::exception &e)
        {
            std::cerr << "[IgniteDBHandler] Failed to execute query: " << query << "\nError: " << e.what() << std::endl;

                }

I started getting the error:
Error processing job: Too many open cursors (either close other open cursors or increase the limit through ClientConnectorConfiguration.maxOpenCursorsPerConnection) [maximum=128, current=128]

To me, 128 cursor limit seems ok. I was not sure why the cursors weren't being closed.

I decided to switch the insertion method to Key Value API so I can use PutAll and do batch inserts. See snippet below:

 std::vector<std::vector<std::string>> allData = readDataFromCsv(full_csv_path);
            if (allData.empty())
            {
                std::cerr << "[IgniteDBHandler] No data found in CSV file: " << full_csv_path << std::endl;
                return -1;
            }

            int totalRows = 0;

            const size_t BATCH_SIZE = 1000;
            std::map<std::string, std::string> batch;

            for (const auto &values : allData)
            {
                boost::uuids::uuid uuid = boost::uuids::random_generator()();
                std::string uniqueJobId = groupId + "_" + boost::uuids::to_string(uuid);

                // Build key and value
                std::string key, value;

                    key = uniqueJobId + "_" + values[0];
                    value = uniqueJobId + "," + values[0] + "," + values[1] + "," + values[2] + "," + values[3] + "," +
                            values[4] + "," + values[5] + "," + values[6] + "," + values[7] + "," + values[8] + "," +
                            values[9] + "," + values[10] + "," + values[11] + "," + values[12];
                

                batch[key] = value;
                totalRows++;

                if (batch.size() >= BATCH_SIZE)
                {
                    try
                    {
                        tableCache.PutAll(batch);
                    }
                    catch (const std::exception &e)
                    {
                        std::cerr << "[IgniteDBHandler] Failed to PutAll batch: " << e.what() << std::endl;
                        throw;
                    }
                    batch.clear();
                }
            }

This works but now the table I created called results is empty, and I cant query it tried it via gridgain control agent console and Dbeaver. The table is empty but when I check the size of the cache ,the values seem to be going there and cache size is increasing.

Java code for table creation:

ignite.query(new SqlFieldsQuery(
        "CREATE TABLE IF NOT EXISTS npv_results (" +
                "unique_job_id VARCHAR, " +
                "trade_id VARCHAR, " +
                "trade_type VARCHAR, " +
                "maturity VARCHAR, " +
                "maturity_time DOUBLE, " +
                "npv DOUBLE, " +
                "npv_currency VARCHAR, " +
                "npv_base DOUBLE, " +
                "base_currency VARCHAR, " +
                "notional DOUBLE, " +
                "notional_currency VARCHAR, " +
                "notional_base DOUBLE, " +
                "netting_set VARCHAR, " +
                "counter_party VARCHAR, " +
                "PRIMARY KEY (unique_job_id, trade_id)) " +
                "WITH \"CACHE_NAME=SQL_PUBLIC_NPV_RESULTS\"")
        .setSchema("PUBLIC")).getAll();

Solution

The problem is that you're creating your table in SQL with fourteen columns of various types and then inserting data into it in a completely different format (two strings).

So the data is present in Ignite, and can be accessed using the key-value API. But the SQL engine doesn't understand it as it's in the wrong format.

(Yes, this is valid. Maybe it shouldn't be. It works differently in Ignite 3, for instance.)

The trick is to insert the data in the correct format.

Define your table in SQL by adding the key and value type
Create C++ classes with the same type names and matching column types. So nvp_result_key would have two strings (unique_job_id and trade_id), and nap_result_value would have all the other properties
In your PutAll, you'd use those classes rather than strings

More information here: https://www.gridgain.com/docs/latest/developers-guide/SQL/sql-key-value-storage