Using variable in ORACLDART

[hasan3ysf]

Hi Alex,
I want to execute SQL statement using variables, how can I do it using oracldart.

The below SQL had been rum fine:

.then((_) => oracleConnection.select("""
INSERT INTO USERS_MAIN
(UserID,Alias,Password,FirstName,LastName,Email,Roles)
VALUES(
USERS_sequence.nextval,
'Admin',
'FonixAdmin',
'Hasan',
'Yousef',
'hasan@fonix.tk',
'[admin, masterData, purchaser]'
)
"""))

I tried running it using VARIABLES, as below:

var users = <Map>[];

users.add({
     'Alias':'Admin',
     'Password':'FonixAdmin',
     'FirstName':'Hasan',
     'LastName':'Yousef',
     'Email':'hasan@fonix.tk',
     'Roles':'[admin, masterData, purchaser]'
});

        .then((_) => oracleConnection.select("""
                        INSERT INTO USERS_MAIN 
                                          (UserID,Alias,Password,FirstName,LastName,Email,Roles)
                                    VALUES(
                                            USERS_sequence.nextval,                                     
                                            ${users['Alias']},
                                            ${users['Password']},
                                            ${users['FirstName']},
                                            ${users['LastName']},
                                            ${users['Email']},
                                            ${users['Roles']}
                                           )
                """))

then I got this error:

Unhandled exception:
Uncaught Error: type 'String' is not a subtype of type 'int' of 'index'.
Stack Trace:
#0 List.
#1 main.. (file:///G:/fonix-DBcreation/bin/users.dart:81:56)
#2 _RootZone.runUnary (dart:async/zone.dart:1082)
#3 _Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:488)
#4 _Future._propagateToListeners (dart:async/future_impl.dart:571)
#5 _Future._completeWithValue (dart:async/future_impl.dart:331)
#6 _Future._asyncComplete. (dart:async/future_impl.dart:393)
#7 _asyncRunCallbackLoop (dart:async/schedule_microtask.dart:41)
#8 _asyncRunCallback (dart:async/schedule_microtask.dart:48)
#9 _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:126)
#0 _rootHandleUncaughtError. (dart:async/zone.dart:820)
#1 _asyncRunCallbackLoop (dart:async/schedule_microtask.dart:41)
#2 _asyncRunCallback (dart:async/schedule_microtask.dart:48)
#3 _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:126)

[aam]

Hasan,

you want to use OracleStatement setInt(), setString() to specify variable values.
Take a look at following line in oracledart test https://github.com/aam/oracledart/blob/master/test/oracledart_sync_extension_test.dart#L78.

Is that what you are after?

[hasan3ysf]

Exactly, setString is what I need, the below code run perfectly with me. Thanks

.then((_) {
OracleStatement stmt =
oracleConnection.createStatement("""
INSERT INTO USERS_MAIN
(Alias,FirstName,LastName,Password,Email,Roles)
VALUES(:1,:2,:3,:4,:5,:6)
""");
stmt.setString(1, theData['Alias']);
stmt.setString(2, theData['FirstName']);
stmt.setString(3, theData['LastName']);
stmt.setString(4, theData['Password']);
stmt.setString(5, theData['Email']);
stmt.setString(6, theData['Roles'].toString()); // I'm here converting a List into String
OracleResultset resultset = stmt.executeQuery();
})