Feature request: display select * result as JSON

[hasan3ysf]

Hi Alex, for displaying the output of the select * statement, I forced to write "print("${resultset.getInt(i)} for all the parameters I've, i.e. ${resultset.getString(1)} ${resultset.getString(2)} ${resultset.getString(3)} ... a shown in the attached.

I'm looking for the following:

1. number of columns retrieved with 'select * from ...'
2. total number of rows
3. having output as json, so instead of using ${resultset.getString(2)} and having in mind that the second parameter is lastname, I want to use result.lastname(i)

thanks, Hasan

[aam]

Hi Hasan,

In 0.0.8 I added access to fields by name(getStringByName, getIntByName,...)

test('Test getStringByName, getIntByName', () {
    OracleResultset resultset =
        connection.select("select empno, ename from emp where EMPNO='7369'");
    resultset.next();
    expect(resultset.getString(1), equals("7369"));
    expect(resultset.getStringByName("EMPNO"), equals("7369"));
    expect(resultset.getInt(1), equals(7369));
    expect(resultset.getIntByName("EMPNO"), equals(7369));
    expect(resultset.getString(2), equals("SMITH"));
    expect(resultset.getStringByName("ENAME"), equals("SMITH"));
  });

and access to number of columns via resultset.getMetadataVector().getSize():

test('Test getMetadataVector', () {
    OracleResultset resultset =
        connection.select("select empno, ename from emp");
    OracleMetadataVector metadata =
        resultset.getMetadataVector();
    expect(metadata.getSize(), equals(2))

Regarding number of rows - I don't think it is possible to easily figure out number of rows in resultset. I think generally speaking you would want to do 'select count(*) from emp' and then take getInt(1) from result of that query to figure out how many rows are in emp table.

Please let me know if this helps.

[hasan3ysf]

Hi Alex, I run this code, and got the below error:

  var resultset = oracleConnection.select("select * from ppl where firstname='Maha'");

  while(resultset.next()) {
        print(i++);
        print("${resultset.getString(3)} ${resultset.getStringByName('firstname')}");

      } 

output:

0 Dart_IntegerToInt64 expects argument 'integer' to be non-null.

[hasan3ysf]

Hi Alex, I tried another code, and got a loonger error, I tried this code, and got the error below:

  var resultset = oracleConnection.select("select firstname from ppl where firstname='Maha'");

  while(resultset.next()) {
        print(i++);
        print("${resultset.getString(3)} ${resultset.getStringByName('firstname')}");

      } 

the output I got is: 0 Uncaught Error: ORA-32109: invalid column or parameter position

Stack Trace:

0 _OracleResultset.getString (package:oracledart/oracledart_synchronous_extension.dart:100:65)

1 main. (file:///D:/fonixApps/DartApps/App01/web/app01.dart:23:41)

2 _rootRunUnary (dart:async/zone.dart:730)

3 _RootZone.runUnary (dart:async/zone.dart:864)

4 _Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:488)

5 _Future._propagateToListeners (dart:async/future_impl.dart:571)

6 _Future._complete (dart:async/future_impl.dart:317)

7 Future.Future. (dart:async/future.dart:118)

8 _createTimer. (dart:async-patch/timer_patch.dart:11)

9 _handleTimeout (dart:io/timer_impl.dart:292)

10 _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:124)

Unhandled exception: ORA-32109: invalid column or parameter position

0 _rootHandleUncaughtError.. (dart:async/zone.dart:713)

1 _asyncRunCallbackLoop (dart:async/schedule_microtask.dart:23)

2 _asyncRunCallback (dart:async/schedule_microtask.dart:32)

3 _asyncRunCallback (dart:async/schedule_microtask.dart:36)

4 _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:128)

[aam]

Hi Hasan,

try putting column names in all captial letters:

print("${resultset.getString(3)} ${resultset.getStringByName('FIRSTNAME')}");

I believe Oracle convention is to uppercase all identifiers.

[hasan3ysf]

Hi Ales, the first one worked, the second one still give same error.

i,e, select *, worked when the field name had been capitalized

  var resultset = oracleConnection.select("select * from ppl where firstname='Maha'");

  while(resultset.next()) {
        print(i++);
        print("${resultset.getString(3)} ${resultset.getStringByName('FIRSTNAME')}");

      } 

but the second one, with select firstname ... did not work var resultset = oracleConnection.select("select FIRSTNAME from ppl where firstname='Maha'");

thanks

[hasan3ysf]

Hi Alex, Can you provide the output in the form of text, like: resultset.output = {"firstName":"Hasan","lastName":"Yousef"}

then we can get the use https://pub.dartlang.org/packages/json_object to extract the JSON data.

thanks