Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error: CLI0115E Invalid cursor state. SQLSTATE=24000 while querying programmatically

Status
Not open for further replies.

THEROL

Programmer
Jul 6, 2021
1
0
0
DE
Hello.

I am a C# developer and we have the requirement to read rows from a DB2 table and write it to a csv file. I've implemented it with the .net package "IBM.Data.DB2.Core". Connection is working fine but there are sometimes problems with the reading. The exception then is : "Error: CLI0115E Invalid cursor state. SQLSTATE=24000"

Here's the relvant code snippet:


Code:
 using (var connection = new DB2Connection(connectionString))
                {
                    connection.Open();
                    
                    using (var db2Command = new DB2Command(query, connection))
                    using (var reader = db2Command.ExecuteReader(mode))
                    using (var memoryStream = new MemoryStream())
                    {
                        string[] columnHeaders = GetColumnHeaders(reader).ToArray();


                        var headerLine = string.Join(";", columnHeaders) + Environment.NewLine;
                        
                        memoryStream.Write(Encoding.UTF8.GetBytes(headerLine), 0, headerLine.Length);


                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string[] columnValues =
                                    Enumerable.Range(0, columnHeaders.Length)
                                                .Select(x => reader.GetValue(x).ToString())
                                                .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                                                .ToArray();




                                var valueLine = string.Join(";", columnValues) + Environment.NewLine;
                                LoggingHelper.Log($"Values: {valueLine}");


                                using (StreamWriter writer = new StreamWriter(memoryStream, Encoding.GetEncoding("UTF-8"), leaveOpen: true))
                                {
                                    writer.Write(valueLine);
                                }
                            }
                        }

                        LoggingHelper.Log($"Reading done...");

                        reader.Close();


                        csvData = memoryStream.GetBuffer();
                        db2Command.Dispose();


                        memoryStream.Close();
                    }
                    
                    connection.Dispose();
                    connection.Close();
}

What could be the problem here? A driver problem or is something wrong in the code?

Thank you!

Tobias

P.S.
here some none-working queries:

Code:
Select * FROM ZUOC_OL.V_tcms_13_Vehicle_Base LIMIT 5
Select * FROM ZUOC_OL.V_tcms_13_Vehicle_Base FETCH FIRST 5 ROWS ONLY
Select ORDERNUMBER FROM ZUOC_OL.V_tcms_13_Vehicle_Base FETCH FIRST 5 ROWS ONLY
Select * FROM ZUOC_OL.V_tcms_13_Steuergeraetedaten FETCH FIRST 1 ROWS ONLY

these are working:
Code:
Select HEX(HASH(VEHICLE_FIN, 2)), * FROM ZUOC_OL.V_tcms_13_Vehicle_Base FETCH FIRST 100 ROWS ONLY
Select 
                HEX(HASH(a.VEHICLESTATE_ACTUAL_FIN, 2)) as VEHICLESTATE_ACTUAL_FIN_HASH
                ,HEX(HASH(a.VEHICLESTATE_ACTUAL_VIN,2)) as VEHICLESTATE_ACTUAL_VIN_HASH
                ,a.DIOGENES_BK
                ,a.HARDWARE_OBJECTNUMBER
                ,a.SOFTWARE_OBJECTNUMBER
                ,a.CUS_SCNIDENTIFICATION
                ,a.CUS_SCNIDENTIFICATIONINITIAL
                ,a.CUS_CVNCODING
                ,a.CONTROLUNIT_ID
                ,a.VALIDSINCE_CONTROLUNIT
                ,a.LOAD_DATE
                ,a.DIOGENESSHORTNAME
FROM ZUOC_OL.V_tcms_13_Steuergeraetedaten a
inner join ZUOC_OL.V_tcms_13_Vehicle_Base b on b.VEHICLE_FIN = a.VEHICLESTATE_ACTUAL_FIN
where b.DATE_TECHNICALAPPROVAL >= '2018-01-01'
FETCH FIRST 100 ROWS ONLY

Hint: the last query doesn't work with 10000 rows limitation but with 1000000 (and 100 as shown).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top