PostgreSQL array data type

CREATE TABLE test_arrays ( "array2D" smallint[][], ... ) // Populate "array2D" as 5x7 matrix INSERT INTO test_arrays("array2D",...) VALUES('{{3,16,9,22,15,0,100},{20,8,21,14,2,0,100},{7,25,13,1,19,0,100},{24,12,5,18,6,0,100},{11,4,17,10,23,0,100}}',...)

string sConnectionStr = "Provider=PGNP.1;..."; OleDbConnection conn = new OleDbConnection(sConnectionStr); conn.Open(); OleDbCommand dbcmd = new OleDbCommand("SELECT \"array2D\" FROM test_arrays", conn); OleDbDataReader dr = dbcmd.ExecuteReader(); while (dr.Read()) { object o = dr.GetValue(0); if (o is DBNull) Console.WriteLine("&#91;NULL&#93;"); else { Array array = o as Array; switch (lang_req.Rank) { case 1: for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++) { object x = lang_req.GetValue(i); Console.WriteLine(String.Format("{0}>{1}", i, x)); } break; case 2: for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++) { for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++) { object x = lang_req.GetValue(i, j); Console.WriteLine(String.Format("({0},{1}) > {2}", i, j, x)); } } break; case 3: for (int k = lang_req.GetLowerBound(2); k < lang_req.GetLowerBound(2) + lang_req.GetLength(2); k++) { for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++) { for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++) { object x = lang_req.GetValue(i, j, k); Console.WriteLine(String.Format("({0},{1},{2}) > {3}", i, j, k, x)); } } } break; } } }

(1,1) > 3 (2,1) > 16 (3,1) > 9 (4,1) > 22 (5,1) > 15 (6,1) > 0 (7,1) > 100 (1,2) > 20 (2,2) > 8 (3,2) > 21 (4,2) > 14 (5,2) > 2 (6,2) > 0 (7,2) > 100 (1,3) > 7 (2,3) > 25 (3,3) > 13 (4,3) > 1 (5,3) > 19 (6,3) > 0 (7,3) > 100 (1,4) > 24 (2,4) > 12 (3,4) > 5 (4,4) > 18 (5,4) > 6 (6,4) > 0 (7,4) > 100 (1,5) > 11 (2,5) > 4 (3,5) > 17 (4,5) > 10 (5,5) > 23 (6,5) > 0 (7,5) > 100

short array2D&#91;7,5&#93;; // it can be confusing because PostgreSQL array is 5x7!

#include <stdio.h> #include <tchar.h> #include <iostream> #include <string> using namespace std; #import "msado15.dll" no_namespace rename("EOF", "adoEOF") void MyArrayPrint(const _variant_t& v); int _tmain(int argc, _TCHAR* argv&#91;&#93;) { CoInitialize (NULL); _ConnectionPtr pConn; try { HRESULT hr = pConn.CreateInstance (__uuidof (Connection)); if (FAILED (hr)) { cout << "Can't create Connection instance" << endl; return 1; } hr = pConn->Open (_bstr_t ("Provider=PGNP.1;Password=12345;User ID=postgres;Initial Catalog=postgres;Data Source=localhost;Extended Properties=\"\""), _bstr_t (""), _bstr_t (""), adModeUnknown); if (FAILED(hr)) { cout << "Can't open datasource" << endl; return 2; } _CommandPtr pCommand; hr = pCommand.CreateInstance (__uuidof (Command)); if (FAILED (hr)) { cout << "Can't create an instance of Command" << endl; return 3; } pCommand->ActiveConnection = pConn; pCommand->CommandText = "select \"array2D\"from test_arrays"; _RecordsetPtr pRecordset; hr = pRecordset.CreateInstance (__uuidof (Recordset)); if (FAILED (hr)) { cout << "Can't create an instance of Recordset" << endl; return 4; } pRecordset->CursorLocation = adUseClient; pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenForwardOnly, adLockUnspecified, adCmdUnknown); if (!pRecordset->GetadoEOF ()) { _variant_t v = pRecordset->Fields->GetItem("array2D")->GetValue(); MyArrayPrint(v); } pRecordset->Close (); } catch ( _com_error &e ) { cout << e.Description() << endl; } catch (...) { cout << "*** Unhandled Exception ***" << endl; } if ((pConn->State & adStateOpen) == adStateOpen) pConn->Close(); CoUninitialize(); return 0; } void PrintArrayElement(VARTYPE vt, LPVOID pvData, LONG i) { switch (vt) { default: cout << "?unknown vartype"; break; case VT_I2: case VT_UI2: cout << ((short*)pvData)&#91;i&#93;; break; case VT_I4: case VT_UI4: cout << ((int*)pvData)&#91;i&#93;; break; case VT_BSTR: { _bstr_t bstrTemp(((BSTR*)pvData)&#91;i&#93;, true); cout << LPCSTR(bstrTemp); } break; } } void MyArrayPrint(const _variant_t& v) { if (0 == (v.vt & VT_ARRAY)) { cout << "Not array, vt=" << v.vt; return; } VARTYPE vt = v.vt & ~VT_ARRAY; // element type switch (v.parray->cDims) { case 1: for (LONG i = v.parray->rgsabound&#91;0&#93;.lLbound; i < v.parray->rgsabound&#91;0&#93;.lLbound + v.parray->rgsabound&#91;0&#93;.cElements; i++) { cout << i << ">"; PrintArrayElement(vt, v.parray->pvData, i-v.parray->rgsabound&#91;0&#93;.lLbound); // the implementation requires zero-based indexes cout << endl; } break; case 2: for (LONG i = v.parray->rgsabound&#91;0&#93;.lLbound; i < v.parray->rgsabound&#91;0&#93;.lLbound + v.parray->rgsabound&#91;0&#93;.cElements; i++) { for (LONG j = v.parray->rgsabound&#91;1&#93;.lLbound; j < v.parray->rgsabound&#91;1&#93;.lLbound + v.parray->rgsabound&#91;1&#93;.cElements; j++) { cout << "(" << i << "," << j << ")>"; PrintArrayElement(vt, v.parray->pvData, (i-v.parray->rgsabound&#91;0&#93;.lLbound) * v.parray->rgsabound&#91;1&#93;.cElements + (j-v.parray->rgsabound&#91;1&#93;.lLbound)); // the implementation requires zero-based indexes cout << endl; } } break; case 3: for (LONG i = v.parray->rgsabound&#91;0&#93;.lLbound; i < v.parray->rgsabound&#91;0&#93;.lLbound + v.parray->rgsabound&#91;0&#93;.cElements; i++) { for (LONG j = v.parray->rgsabound&#91;1&#93;.lLbound; j < v.parray->rgsabound&#91;1&#93;.lLbound + v.parray->rgsabound&#91;1&#93;.cElements; j++) { for (LONG k = v.parray->rgsabound&#91;2&#93;.lLbound; k < v.parray->rgsabound&#91;2&#93;.lLbound + v.parray->rgsabound&#91;2&#93;.cElements; k++) { cout << "(" << i << "," << j << "," << k << ")>"; PrintArrayElement(vt, v.parray->pvData, ((i-v.parray->rgsabound&#91;0&#93;.lLbound) * v.parray->rgsabound&#91;1&#93;.cElements + (j-v.parray->rgsabound&#91;1&#93;.lLbound)) * v.parray->rgsabound&#91;2&#93;.cElements + (k-v.parray->rgsabound&#91;2&#93;.lLbound)); // the implementation requires zero-based indexes cout << endl; } } } break; } }