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