Error Searching for Serial Postgresql

Question

Using: c#, windows Forms, postgresql

I’m having trouble putting together a routine which saves data to a table and retrieves the id recorded(serial / Sequence) and updates the log in another table.

The worst of it is that everything works Debugging, but if have it run straight from Exception:

ERROR: 55000: current value of "banco_id_seq" sequence has not yet been defined in this session.

This error happens when going through the "Search id".

I don’t know what could be going on, or how to get around this, because how a thing can work by debugging and running does not work ?

follows below parts of the code:

Record

    protected override void Gravar_Registro()
    {
        using (base.cn = Dados.getInstancia().getConexao())
        {
            cn.Open();
            NpgsqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadCommitted);

            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;

                cmd.CommandText = DAL.Banco.Insert();

                Tela_Para_Banco(cmd);

                cmd.Transaction = tr;
                cmd.ExecuteNonQuery();

                Log_Sistema(tr, Rotina.Usuario_Login, this.Text, cmd);

                tr.Commit();
                cn.Close();
            }
            catch (Exception ex)
            {
                tr.Rollback();
                throw new Exception("Servidor SQL Erro: " + ex.Message);
            }
        }

        beCodigo.IdTable = Buscar_ID("banco").ToString();
        beCodigo.Text = beCodigo.IdTable;
    }

LOG

    private void Log_Sistema(NpgsqlTransaction tr, string strUsuario, string strTela, NpgsqlCommand cCommand)
    {
        using (NpgsqlConnection cn = Dados.getInstancia().getConexao())
        {
            try
            {
                cn.Open();

                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.Connection = cn;

                cmd.CommandText = "insert into log (data, usuario, tela, sql) values (current_timestamp, @usuario, @tela, @sql);"; 

                cmd.Parameters.AddWithValue("@usuario", strUsuario);
                cmd.Parameters.AddWithValue("@tela", strTela);

                string tTexto = "";

                for (int i = 0; i < cCommand.CommandText.Length - 1; i++)
                {
                    tTexto = tTexto + cCommand.CommandText.Substring(i, 1);

                    if ((cCommand.CommandText.Substring(i, 1) == ",") || (cCommand.CommandText.Substring(i, 1) == " ") || (cCommand.CommandText.Substring(i, 1) == ")"))
                    {
                        foreach (NpgsqlParameter p in cCommand.Parameters)
                        {
                            string sParametro_Nome = p.ParameterName.ToString();
                            string sParametro_Valor = Rotina.ReturnEmptyIfNull(p.Value);

                            if (tTexto.IndexOf(sParametro_Nome) > 0)
                                if (tTexto.Substring(tTexto.IndexOf(sParametro_Nome), (tTexto.Length - 1) - tTexto.IndexOf(sParametro_Nome)) == sParametro_Nome)
                                {
                                    tTexto = tTexto.Replace(sParametro_Nome, sParametro_Valor);
                                    break;
                                }
                        }
                    }
                }
                if (tTexto != "")
                    cmd.Parameters.AddWithValue("@sql", tTexto);

                cmd.Transaction = tr;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Servidor SQL Erro: " + ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
    }

Recovers ID

    public static string Buscar_ID(string sTabela)                                                                                 
    {                                                                                                                              
        using (NpgsqlConnection cn = Dados.getInstancia().getConexao())                                                            
        {                                                                                                                          
            try                                                                                                                    
            {                                                                                                                      
                cn.Open();                                                                                                         
                NpgsqlCommand cmd = new NpgsqlCommand((String.Format("select currval('public.{0}_id_seq');", sTabela)), cn);
                NpgsqlDataReader dr = cmd.ExecuteReader();                                                                         

                dr.Read();                                                                                                         

                if (dr.HasRows)                                                                                                    
                    if (dr.IsDBNull(0))                                                                                            
                        return "1";                                                                                                
                    else                                                                                                           
                        return dr[0].ToString();                                                                                   
                else                                                                                                               
                    return "1";                                                                                                    
            }                                                                                                                      
            catch (Exception ex)                                                                                                   
            {                                                                                                                      
                throw new Exception(ex.Message);                                                                                   
            }                                                                                                                      
            finally                                                                                                                
            {                                                                                                                      
                cn.Close();                                                                                                        
            }                                                                                                                      
        }                                                                                                                          
    }                                                                                                                              

Answer 1

A suggestion to retrieve the inserted Quence id is to use the clause RETURNING in Insert, you also need to change Executenonquery() which returns the number of rows affected by Executescalar qu according to the documentation return only the first column and row of a query.

cmd.CommandText = "INSERT INTO log (data, usuario, tela, sql)
VALUES (current_timestamp, @usuario, @tela, @sql) RETURNING NOME_DO_CAMPO_SERIAL;"

....
ultimo_id = cmd.ExecuteScalar();

Answer 2

The function CURRVAL returns the last value of the sequence generated in your session. This error occurs when calling the function CURRVALS without first having advanced to SEQUENCE.

In your case, the problem is that the method Buscar_ID is being called out of the transaction. Try so:

Record:

protected override void Gravar_Registro()
{
     using (base.cn = Dados.getInstancia().getConexao())
     {
         cn.Open();
         NpgsqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadCommitted);

         try
         {
             NpgsqlCommand cmd = new NpgsqlCommand();
             cmd.Connection = cn;
             cmd.CommandType = CommandType.Text;

             cmd.CommandText = DAL.Banco.Insert();

             Tela_Para_Banco(cmd);

             cmd.Transaction = tr;
             cmd.ExecuteNonQuery();

             Log_Sistema(tr, Rotina.Usuario_Login, this.Text, cmd);

             // Chamado antes de terminar a transação
             beCodigo.IdTable = Buscar_ID("banco", cn).ToString();

             tr.Commit();
             cn.Close();
         }
         catch (Exception ex)
         {
             tr.Rollback();
             throw new Exception("Servidor SQL Erro: " + ex.Message);
         }
     }

     beCodigo.Text = beCodigo.IdTable;
}

Buscar_id:

private static string Buscar_ID(string sTabela, NpgsqlConnection conexao)                                                                                 
{                                                                                                                              
    var cmd = new NpgsqlCommand((String.Format("select currval('public.{0}_id_seq');", sTabela)), conexao);
    using(var dr = cmd.ExecuteReader())
    {    
       if (dr.Read())                                                                                                    
           if (dr.IsDBNull(0))                                                                                            
               return "1";                                                                                                
           else                                                                                                           
               return dr[0].ToString();                                                                                   
       else                                                                                                               
           return "1";                                                                                                    
   }
}