select query not give result in certain cases

[beautybird]

On same database, same table,same row and same connection : The row contains 21 columns ..

The following select query has 2 parametarized values, it gives a result & also can run on pgPool.run & pgPoo.runTx (i.e, pgPool.queueSize = 1)

       await pgPool.runTx( (c) async {
        try {
           loginUserResult = await c.query(
            'select emaildb, passdb from usersData where emaildb = @emailValue AND
            passdb = @passwordValue order by iddb',
            substitutionValues: {'emailValue': email,  'passwordValue': password
            },
            allowReuse: false,
            timeoutInSeconds: 60);

         }catch(){
    .....
        }

But in following select query (retrieve data from DB) :

1- It gives null despite me trying 3 select String variants

2- When run pgPool.run I get 0 connection (i.e, pgPool.queueSize = 0):

  await pgPool.runTx((c) async {
    try{
    dataResult = await c.query(
     'select col1,citydb from usersData where citydb = @cityValue order by iddb', // 1st variant
     'select * from userData where citydb = @cityValue order by iddb',  // 2nd variant
     'select col1,col2,col3,col4 ..up till ..,citydb from usersData where
      citydb = @cityValue  order by iddb', // 3rd variant

        substitutionValues: {'cityValue': city},
        allowReuse: true,
        timeoutInSeconds: 60);
    print('dataResult isEmpty : ${dataResult.isEmpty}'); // here I get empty result 
    }catch(err){
        ....
    }

When connecting pgAdmin3 to the database...above 3 variants queries strings gave results . On the application neither return a result !!

I'm puzzled..is there any limitations applied on the " c.query " function ?

Just so you know , Postgresql version 10, emaildb column is a varchar Primary key , while citydb just a varchar , and I'm getting the pgPool from : final pgPool = PgPool(pgEndpoint, settings: pgPoolSettings);

[isoos]

@beautybird: package:postgres_pool does not add any restriction or change the connections. It does, however, add an extra connection check query sometimes ("SELECT 1"), unlikely to cause issues.

Maybe an unrelated note: the 2nd variant you are using userData instead of usersData.

A few things that comes to my mind:

• maybe city (or the database field) contains an extra space somewhere
• maybe city (or the database field) is differently cased

Please check the above with extra debugging, and if nothing helps, please try to create a reproducible use case, I'll happy to debug and fix it.

[beautybird]

I ran debug with 4 break point :

     List<List<dynamic>> cityAllShopsDataList;
     PostgreSQLResult cityAllShopsDataResult;
    Future<List<List<dynamic>>> cityAllShopsData( String city, String category) async {
     final pgPool =  Persist_Entries_PG()._pgPoolConn();   // break point 1
    try {
         await pgPool.runTx((c) async {           // break point 2
         cityAllShopsDataResult = await c.query(   // break point 3
           'select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,'
            'col13,col14,col15,col6,col17,col18,col19,col20,citydb '
            'from data.usersData where citydb = @cityValue order by iddb',
             substitutionValues: {'cityValue': city},
             allowReuse: true,
             timeoutInSeconds: 60);
    cityAllShopsDataList = await _sortingShopsDataRows(cityAllShopsDataResult, category);  // 
    break point 4
  });
    } catch (cityAllShopsDataError) {
        cityAllShopsDataList = null;
    } finally {
        Timer(Duration(minutes: 1),()=> pgPool.close());
    }
     return cityAllShopsDataList;
  }

debugging break point 3(before break point 4) get me below report , seems to me the issue is with the substitutionValues .

 pgPool = {PgPool} 
 c = {_PgExecutionContextWrapper} 
  connectionId = 1
  _delegate = {_TransactionProxy} 
   _queue = {QueryQueue} []
    _inner = {_GrowableList} size = 0
    _isCancelled = false
   _beginQuery = {Query} BEGIN
    onlyReturnAffectedRowCount = true
    statementIdentifier = null
    statement = "BEGIN"
    substitutionValues = {_InternalLinkedHashMap} size = 0   // this is strange !!
    transaction = {_TransactionProxy} 
      _queue = {QueryQueue} []
      _inner = {_GrowableList} size = 0  // this is strange !!
      _isCancelled = false
     _beginQuery = {Query} BEGIN
      onlyReturnAffectedRowCount = true
      statementIdentifier = null
      statement = "BEGIN"
      substitutionValues = {_InternalLinkedHashMap} size = 0   // this is strange !!
      transaction = {_TransactionProxy} 
       _queue = {QueryQueue} []
       _beginQuery = {Query} BEGIN
       _completer = {_AsyncCompleter} 
       _connection = {PostgreSQLConnection} 
       executionBlock = {_Closure} 
       commitTimeoutInSeconds = null
       _hasFailed = false
       _hasRolledBack = false
      connection = {PostgreSQLConnection} 
      _specifiedParameterTypeCodes = null
      rows = {_GrowableList} size = 0
      cache = null
      _onComplete = {_SyncCompleter} 
      _fieldDescriptions = null
     _completer = {_AsyncCompleter} 
      future = {_Future} 
       _awaiter = {_Closure}              // ??
       _state = 0
       _zone = {_CustomZone} 
       _resultOrListeners = {_FutureListener} 
        _nextListener = null
        result = {_Future} 
         _awaiter = null
         _state = 0
         _zone = {_CustomZone} 
          _run = {_RunNullaryZoneFunction} 
          _runUnary = {_RunUnaryZoneFunction} 
          _runBinary = {_RunBinaryZoneFunction} 
          _registerCallback = {_RegisterNullaryZoneFunction} 
          _registerUnaryCallback = {_RegisterUnaryZoneFunction} 
          _registerBinaryCallback = {_RegisterBinaryZoneFunction} 
          _errorCallback = {_ZoneFunction} 
          _scheduleMicrotask = {_ZoneFunction} 
          _createTimer = {_ZoneFunction} 
          _createPeriodicTimer = {_ZoneFunction} 
          _print = {_ZoneFunction} 
          _fork = {_ZoneFunction} 
          _handleUncaughtError = {_ZoneFunction} 
          _delegateCache = null
          parent = {_RootZone} 
          _map = {_HashMap} {}
         _resultOrListeners = null
        state = 19
        callback = {_Closure} 
        errorCallback = {_Closure} 
     _connection = {PostgreSQLConnection} 
     executionBlock = {_Closure} 
     commitTimeoutInSeconds = null
     _hasFailed = false
     _hasRolledBack = false
    connection = {PostgreSQLConnection} 
    _specifiedParameterTypeCodes = null
    rows = {_GrowableList} size = 0     // this is strange !!
    cache = null
    _onComplete = {_SyncCompleter} 
     future = {_Future} 
      _awaiter = null
      _state = 4
      _zone = {_CustomZone} 
      _resultOrListeners = 0
    _fieldDescriptions = null
   _completer = {_AsyncCompleter} 
    future = {_Future} 
     _awaiter = {_Closure} 
     _state = 0
     _zone = {_CustomZone} 
     _resultOrListeners = {_FutureListener} 
      _nextListener = null
      result = {_Future} 
       _awaiter = null
       _state = 0
       _zone = {_CustomZone} 
       _resultOrListeners = null
      state = 19
      callback = {_Closure} 
      errorCallback = {_Closure} 
   _connection = {PostgreSQLConnection} 
   executionBlock = {_Closure} 
   commitTimeoutInSeconds = null
   _hasFailed = false
   _hasRolledBack = false
  sessionId = null
  traceId = null
  _eventSink = {_AsyncBroadcastStreamController} 
 city = "Pasco"
 this = {Persist_Entries_PG} 
 category = "body_building"

[isoos]

That empty substitutionValue is for start of the transaction (see statement = "BEGIN" a bit further up). That query is without parameters.

On top of the print/log-based debugging, since you are using PgPool, there is an ability to log the queries through PgPool.events. Unfortunately it does not have the returned row count in it yet, but it can add another sanity check.

I'd encourage you to create a reproducible environment (e.g. CREATE TABLE, INSERT statements and then the code that fails).

[beautybird]

Hi, for insert statement (new user registration)..I do select query to check the user id not registered already then I do the insert query(c.execute)...it works fine . Same when a user want login..the select query works fine . When user insert details in the 19 columns...all works fine. it's only when a retrieving the data from the 19 columns with select ...things goes blind :) Will update to you if I solve this dilemma. Anyway...Thank you for you time..I appreciate to you.

[isoos]

@beautybird: another thing to debug if you are on it: what happens if you select 10 columns? 15? 16? 17?