This question is locked. New answers and comments are not allowed.
Hi Telerik Team,
I am experiencing difficulties with Open Access when I try to retrieve big data from PostgreSQL database.
It seems the Buffer got overloaded and it produces System.InvalidOperationException Invalid attempt to read from column ordinal '23'. With CommandBehavior.SequentialAccess, you may only read from column ordinal '24' or greater.
Please can you look into this and provide me with a solution?
See below:
DB server: PostgreSQL 9.3.15 (could also be 9.4.1)
Creating test table
CREATE TABLE audit.test (
id BIGSERIAL,
c00 TEXT, c01 TEXT, c02 TEXT, c03 TEXT, c04 TEXT, c05 TEXT, c06 TEXT, c07 TEXT, c08 TEXT, c09 TEXT, c10 TEXT, c11 TEXT, c12 TEXT, c13 TEXT, c14 TEXT, c15 TEXT, c16 TEXT, c17 TEXT, c18 TEXT, c19 TEXT, c20 TEXT,
c21 TEXT, c22 TEXT, c23 TEXT, c24 TEXT, c25 TEXT, c26 TEXT, c27 TEXT, c28 TEXT, c29 TEXT, c30 TEXT, c31 TEXT, c32 TEXT, c33 TEXT, c34 TEXT, c35 TEXT, c36 TEXT, c37 TEXT, c38 TEXT, c39 TEXT, c40 TEXT,
c41 TEXT, c42 TEXT, c43 TEXT, c44 TEXT, c45 TEXT, c46 TEXT, c47 TEXT, c48 TEXT, c49 TEXT,
CONSTRAINT test_pkey PRIMARY KEY(id)
)
WITH (oids = false);
Creating function that creates test data in the test table.
CREATE OR REPLACE FUNCTION audit.test_f (
)
RETURNS void AS
$body$
DECLARE
i integer;
BEGIN
FOR i IN 1..1500000
LOOP
INSERT INTO audit.test ("c00", "c01", "c02", "c03", "c04", "c05", "c06", "c07", "c08", "c09", "c10", "c11", "c12", "c13", "c14", "c15", "c16", "c17", "c18", "c19", "c20", "c21", "c22", "c23", "c24", "c25", "c26", "c27", "c28", "c29", "c30", "c31", "c32", "c33", "c34", "c35", "c36", "c37", "c38", "c39", "c40", "c41", "c42", "c43", "c44", "c45", "c46", "c47", "c48", "c49")
VALUES ('c00', 'c01', 'c02', 'c03', 'c04', 'c05', 'c06', 'c07', 'c08', 'c09', 'c10', 'c11', 'c12', 'c13', 'c14', 'c15', 'c16', 'c17', 'c18', 'c19', 'c20', 'c21', 'c22', 'c23', 'c24', 'c25', 'c26', 'c27', 'c28', 'c29', 'c30', 'c31', 'c32', 'c33', 'c34', 'c35', 'c36', 'c37', 'c38', 'c39', 'c40', 'c41', 'c42', 'c43', 'c44', 'c45', 'c46', 'c47', 'c48', 'c49');
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
Test Methods calling the function and querying the data.
using System;
using System.Diagnostics;
using CRMModels;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Linq;
using System.Linq.Dynamic;
namespace UnitTests_CRMModels
{
[TestClass]
public class UnitTest
{
[TestMethod]
public void Test_CreateTestData()
{
using (var db = new CRMEntitiesModel ())
{
db.ExecuteNonQuery("audit.test_f", System.Data.CommandType.StoredProcedure);
db.SaveChanges();
}
}
[TestMethod]
public void Test_LoadLargeData()
{
using (var db = new CRMEntitiesModel())
{
var q = db.Tests.Select("new (Id, C00, C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25, C26, C27, C28, C29, C30, C31, C32, C33, C34, C35, C36, C37, C38, C39, C40, C41, C42, C43, C44, C45)");
var test = q.Cast<object>().ToArray(); // <= InvalidOperationException
Assert.IsNotNull(test);
Debug.WriteLine(test.Length);
}
}
}
}
I am experiencing difficulties with Open Access when I try to retrieve big data from PostgreSQL database.
It seems the Buffer got overloaded and it produces System.InvalidOperationException Invalid attempt to read from column ordinal '23'. With CommandBehavior.SequentialAccess, you may only read from column ordinal '24' or greater.
Please can you look into this and provide me with a solution?
See below:
DB server: PostgreSQL 9.3.15 (could also be 9.4.1)
Creating test table
CREATE TABLE audit.test (
id BIGSERIAL,
c00 TEXT, c01 TEXT, c02 TEXT, c03 TEXT, c04 TEXT, c05 TEXT, c06 TEXT, c07 TEXT, c08 TEXT, c09 TEXT, c10 TEXT, c11 TEXT, c12 TEXT, c13 TEXT, c14 TEXT, c15 TEXT, c16 TEXT, c17 TEXT, c18 TEXT, c19 TEXT, c20 TEXT,
c21 TEXT, c22 TEXT, c23 TEXT, c24 TEXT, c25 TEXT, c26 TEXT, c27 TEXT, c28 TEXT, c29 TEXT, c30 TEXT, c31 TEXT, c32 TEXT, c33 TEXT, c34 TEXT, c35 TEXT, c36 TEXT, c37 TEXT, c38 TEXT, c39 TEXT, c40 TEXT,
c41 TEXT, c42 TEXT, c43 TEXT, c44 TEXT, c45 TEXT, c46 TEXT, c47 TEXT, c48 TEXT, c49 TEXT,
CONSTRAINT test_pkey PRIMARY KEY(id)
)
WITH (oids = false);
Creating function that creates test data in the test table.
CREATE OR REPLACE FUNCTION audit.test_f (
)
RETURNS void AS
$body$
DECLARE
i integer;
BEGIN
FOR i IN 1..1500000
LOOP
INSERT INTO audit.test ("c00", "c01", "c02", "c03", "c04", "c05", "c06", "c07", "c08", "c09", "c10", "c11", "c12", "c13", "c14", "c15", "c16", "c17", "c18", "c19", "c20", "c21", "c22", "c23", "c24", "c25", "c26", "c27", "c28", "c29", "c30", "c31", "c32", "c33", "c34", "c35", "c36", "c37", "c38", "c39", "c40", "c41", "c42", "c43", "c44", "c45", "c46", "c47", "c48", "c49")
VALUES ('c00', 'c01', 'c02', 'c03', 'c04', 'c05', 'c06', 'c07', 'c08', 'c09', 'c10', 'c11', 'c12', 'c13', 'c14', 'c15', 'c16', 'c17', 'c18', 'c19', 'c20', 'c21', 'c22', 'c23', 'c24', 'c25', 'c26', 'c27', 'c28', 'c29', 'c30', 'c31', 'c32', 'c33', 'c34', 'c35', 'c36', 'c37', 'c38', 'c39', 'c40', 'c41', 'c42', 'c43', 'c44', 'c45', 'c46', 'c47', 'c48', 'c49');
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
Test Methods calling the function and querying the data.
using System;
using System.Diagnostics;
using CRMModels;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Linq;
using System.Linq.Dynamic;
namespace UnitTests_CRMModels
{
[TestClass]
public class UnitTest
{
[TestMethod]
public void Test_CreateTestData()
{
using (var db = new CRMEntitiesModel ())
{
db.ExecuteNonQuery("audit.test_f", System.Data.CommandType.StoredProcedure);
db.SaveChanges();
}
}
[TestMethod]
public void Test_LoadLargeData()
{
using (var db = new CRMEntitiesModel())
{
var q = db.Tests.Select("new (Id, C00, C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25, C26, C27, C28, C29, C30, C31, C32, C33, C34, C35, C36, C37, C38, C39, C40, C41, C42, C43, C44, C45)");
var test = q.Cast<object>().ToArray(); // <= InvalidOperationException
Assert.IsNotNull(test);
Debug.WriteLine(test.Length);
}
}
}
}