working on it ...

Filters

Explore Public Snippets

Sort by

Found 59 snippets matching: resultset

    public by cghersi  3123  1  6  1

    How to create a RecordSet in VBA to execute a SELECT query

    This simple method creates and returns a Recordset containing the results of the given SQL query. Note: the caller is in charge to close the recordset and release the resources once done with DB data.
    Public Function ExecQuery(ByVal sql As String) As ADODB.Recordset
    On Error GoTo ErrorCmd
    
      Dim conn As ADODB.Connection
    
      'Init db objects
      'Connection is the current DB one
      If exception_flag Then On Error Resume Next
      Set conn = CurrentProject.Connection
      Set ExecQuery = New ADODB.Recordset
    
      'Exec the query
      Set ExecQuery = CurrentProject.Connection.Execute(sql)
    
    Exit_go: Exit Function
    ErrorCmd: Set ExecQuery = Nothing
    End Function

    public by cghersi  2596  0  6  0

    How to consume a RecordSet in VBA with a single row

    If you want to read just a single value from a table, this can be helpful. the connection to the database is the current one, builtin inside MS Access.
    Public Function GetStringFromDB(ByVal id As Integer) As String
    On Error GoTo ErrorQuery
    
      Dim objRS As ADODB.Recordset
      Dim cmd As ADODB.Command
      Dim sql As String
          
      'Init db objects
      'Connection is the current DB one
      Set cmd = New ADODB.Command
      Set objRS = New ADODB.Recordset
      
      sql = "SELECT MyField FROM MyTable WHERE ID=" & id
      Set objRS = CurrentProject.Connection.Execute(sql)
      GetStringFromDB = objRS("MyField")
      objRS.Close
      Set objRS = Nothing
            
    Exit_go: Exit Function
    ErrorQuery: GetStringFromDB = ""
    End Function

    public by cghersi  2290  0  6  0

    How to consume a RecordSet in VBA

    This is a simple example of how to consume a recordset in VBA. It relies on the ExecQuery method explained in the related snippet.
    Dim rd As ADODB.Recordset
    Dim sql As String, res As String
    
    res = ""
    sql = "SELECT Something FROM MyTable WHERE Year=2014"
    Set rd = ExecQuery(sql)
    If (rd Is Nothing) Then GoTo ErrorQuery
    While Not rd.EOF
      res = res + rd("Something")
      rd.MoveNext
    Wend
    
    MsgBox(res)
    
    Exit
    ErrorQuery: MsgBox("Something wrong here")

    public by cghersi  4286  0  6  1

    How to execute an SQL statement in VBA

    This simple method allows to execute the given SQL query, returning true if there was no error, or false otherwise. The SQL statement is not supposed to return resultsets, like an INSERT, UPDATE or DELETE
    Public Function ExecCmd(ByVal sql As String) As Boolean
    On Error GoTo ErrorCmd
    
      Dim conn As ADODB.Connection
      Dim cmd As ADODB.Command
          
      'Init db objects
      'Connection is the current DB one
      Set conn = CurrentProject.Connection
      Set cmd = New ADODB.Command
        
      With cmd
          .ActiveConnection = conn
          .CommandText = sql
          .CommandType = adCmdText
          .Execute
      End With
    
      ExecCmd = True
        
    Exit_go: Exit Function
    ErrorCmd:
      MsgBox Err.Description
      ExecCmd = False
    End Function

    public by msdn  1731  0  7  0

    SetFromTask: Transfers the result of a Task to the TaskCompletionSource.

    Transfers the result of a Task to the TaskCompletionSource. Specifies the type of the result. The TaskCompletionSource. The task whose completion results should be transfered.
    /// <summary>Transfers the result of a Task to the TaskCompletionSource.</summary>
    /// <typeparam name="TResult">Specifies the type of the result.</typeparam>
    /// <param name="resultSetter">The TaskCompletionSource.</param>
    /// <param name="task">The task whose completion results should be transfered.</param>
    public static void SetFromTask<TResult>(this TaskCompletionSource<TResult> resultSetter, Task<TResult> task)
    {
        SetFromTask(resultSetter, (Task)task);
    }

    public by msdn  1409  0  7  0

    TrySetFromTask: Attempts to transfer the result of a Task to the TaskCompletionSource.

    Attempts to transfer the result of a Task to the TaskCompletionSource. Specifies the type of the result. The TaskCompletionSource. The task whose completion results should be transfered. Whether the transfer could be completed.
    /// <summary>Attempts to transfer the result of a Task to the TaskCompletionSource.</summary>
    /// <typeparam name="TResult">Specifies the type of the result.</typeparam>
    /// <param name="resultSetter">The TaskCompletionSource.</param>
    /// <param name="task">The task whose completion results should be transfered.</param>
    /// <returns>Whether the transfer could be completed.</returns>
    public static bool TrySetFromTask<TResult>(this TaskCompletionSource<TResult> resultSetter, Task<TResult> task)
    {
        return TrySetFromTask(resultSetter, (Task)task);
    }

    external by James Newman  265  0  3  0

    Get free jdbi ResultSetMapper if your class has a constructor from a ResultSet

    Get free jdbi ResultSetMapper if your class has a constructor from a ResultSet: ConstructorResultSetMapperFactory.java
    import java.lang.reflect.Constructor;
    import java.lang.reflect.InvocationTargetException;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Optional;
    import lombok.extern.slf4j.Slf4j;
    import org.skife.jdbi.v2.ResultSetMapperFactory;
    import org.skife.jdbi.v2.StatementContext;
    import org.skife.jdbi.v2.tweak.ResultSetMapper;
    
    /**
     * Automatically create a ResultSetMapper if the type has a constructor that accepts a ResultSet
     */
    @Slf4j
    public class ConstructorResultSetMapperFactory implements ResultSetMapperFactory {
    
      private static Optional<Constructor<?>> getConstructor(Class type) {
        try {
          return Optional.of(type.getConstructor(ResultSet.class));
        } catch (NoSuchMethodException | SecurityException ex) {
          return Optional.empty();
        }
      }
    
      
      private static class ConstructorResultSetMapper implements ResultSetMapper {
    
        private final Constructor constructor;
        
        public ConstructorResultSetMapper(Constructor constructor) {
          this.constructor = constructor;
        }
        
        @Override
        public Object map(int i, ResultSet rs, StatementContext sc) throws SQLException {
          try {
            return constructor.newInstance(rs);
          } catch(InvocationTargetException ex) {
            Throwable cause = ex.getCause();
            // Don't mangle useful exceptions!
            if(cause != null && cause instanceof SQLException) {
              throw (SQLException) cause;
            } else {
              throw new RuntimeException(ex);
            }
          } catch (InstantiationException | IllegalAccessException | IllegalArgumentException ex) {
            throw new RuntimeException(ex);
          }
        }
        
      }
      
      @Override
      public boolean accepts(Class type, StatementContext sc) {
        return getConstructor(type).isPresent();
      }
    
      private static ResultSetMapper fromConstructor(Constructor constructor) {
        log.debug("Automatically creating ResultSetMapper<{}> from constructor.", constructor.getDeclaringClass().getSimpleName());
        return new ConstructorResultSetMapper(constructor);
      }
    
      @Override
      public ResultSetMapper mapperFor(Class type, StatementContext sc) {
        return getConstructor(type)
          .map(ConstructorResultSetMapperFactory::fromConstructor)
          .orElseThrow(RuntimeException::new);
      }
    }
    
    
    

    external by roe3p  91  0  1  0

    T-SQL procedure to get a JSON object based on the resultset of a SQL query

    T-SQL procedure to get a JSON object based on the resultset of a SQL query: pGetJSONfromQuery.sql
    //Gets the result of the query @ParameterSQL and returns it as a single-dimension JSON object
    //If variable @VarName is passed, the resultant output assigns the object to that variable
    
    CREATE PROCEDURE [dbo].[pGetJSONFromQuery] @ParameterSQL NVARCHAR(4000), @VarName VARCHAR(255) = NULL
    AS
    BEGIN
    
    	DECLARE @SQL NVARCHAR(1000)	
    	DECLARE @XMLString VARCHAR(MAX)
    	DECLARE @XML XML;
    	DECLARE @Paramlist NVARCHAR(1000)
    
    	SET @Paramlist = N'@XML XML OUTPUT'
    
    	SET @SQL = 'WITH PrepareTable (XMLString)'
    	SET @SQL = @SQL + 'AS('
    	SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS '
    	SET @SQL = @SQL + ')'
    	SET @SQL = @SQL + ' SELECT @XML=[XMLString] FROM [PrepareTable] '
    
    	EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
    	SET @XMLString=CAST(@XML AS VARCHAR(MAX))
    	  
    	DECLARE @JSON VARCHAR(MAX)
    	DECLARE @Row VARCHAR(MAX)
    	DECLARE @RowStart INT
    	DECLARE @RowEnd INT
    	DECLARE @FieldStart INT
    	DECLARE @FieldEnd INT
    	DECLARE @KEY VARCHAR(MAX)
    	DECLARE @Value VARCHAR(MAX)
    	  
    	DECLARE @StartRoot VARCHAR(100) = '<row>'
    	DECLARE @EndRoot VARCHAR(100) = '</row>'
    	DECLARE @StartField VARCHAR(100) = '<'
    	DECLARE @EndField VARCHAR(100) = '>'
    	  
    	SET @RowStart = CHARINDEX(@StartRoot,@XMLString,0)
    	SET @JSON = N''
    	WHILE @RowStart > 0
    	BEGIN
    		SET @RowStart = @RowStart + LEN(@StartRoot)
    		SET @RowEnd = CHARINDEX(@EndRoot, @XMLString, @RowStart)
    		SET @Row = SUBSTRING(@XMLString, @RowStart, @RowEnd - @RowStart)
    		SET @JSON = @JSON + '{'
    	  
    		-- for each row
    		SET @FieldStart = CHARINDEX(@StartField, @Row, 0)
    		WHILE @FieldStart > 0
    		BEGIN
    			-- parse node key
    			SET @FieldStart = @FieldStart + LEN(@StartField)
    			SET @FieldEnd = CHARINDEX(@EndField,@Row,@FieldStart)
    			SET @KEY = SUBSTRING(@Row, @FieldStart, @FieldEnd-@FieldStart)
    			SET @JSON = @JSON + '"' + @KEY + '":'
    			-- parse node value
    			SET @FieldStart = @FieldEnd + 1
    			SET @FieldEnd = CHARINDEX('</', @Row, @FieldStart)
    			SET @Value = SUBSTRING(@Row, @FieldStart, @FieldEnd-@FieldStart)
    			SET @JSON = @JSON + '"' + @Value + '", '
    	  
    			SET @FieldStart = @FieldStart + LEN(@StartField)
    			SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart)
    			SET @FieldStart = CHARINDEX(@StartField, @Row, @FieldEnd)
    		END    
    		IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON))
    		SET @JSON=@JSON+'},'+ CHAR(13) + CHAR(10)
    		--/ for each row
    	  
    		SET @RowStart=CHARINDEX(@StartRoot,@XMLString,@RowEnd)
    	END
    	IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON,0,LEN(@JSON)-2)
    	SET @JSON='['+@JSON+']'
    	
        SELECT  Json = ISNULL('var ' + @VarName + ' = ', N'') + @JSON, lEN(@JSON) AS Length;
    
    END
    
    GO
    
    

    external by adrian1358  80  0  1  0

    Foreach through MongoDB resultset and update each

    Foreach through MongoDB resultset and update each: gistfile1.txt
    db.KompareOffer.find().forEach(function (offer) {
       offer.sharedData.userData.bonus = offer.sharedData.huoData.huoCheckPolicyData.bonusMalus;
       db.offer.save(offer); 
    });
    
    

    external by Marcello Lopes  34  0  1  0

    ResultSet to Jtable

    ResultSet to Jtable: Display resultset on jTable(incomplete)
    public static DefaultTableModel buildTableModel(ResultSet rs)
            throws SQLException {
    
        ResultSetMetaData metaData = rs.getMetaData();
    
        // names of columns
        Vector<String> columnNames = new Vector<String>();
        int columnCount = metaData.getColumnCount();
        for (int column = 1; column <= columnCount; column++) {
            columnNames.add(metaData.getColumnName(column));
        }
    
        // data of the table
        Vector<Vector<Object>> data = new Vector<Vector<Object>>();
        while (rs.next()) {
            Vector<Object> vector = new Vector<Object>();
            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                vector.add(rs.getObject(columnIndex));
            }
            data.add(vector);
        }
    
        return new DefaultTableModel(data, columnNames);
    
    }
    
    public static void main(String[] args) throws Exception {
        // The Connection is obtained
    
        ResultSet rs = stmt.executeQuery("select * from product_info");
    
        // It creates and displays the table
        JTable table = new JTable(buildTableModel(rs));
    
        // Closes the Connection
    
        JOptionPane.showMessageDialog(null, new JScrollPane(table));
    
    }
    
    
    • Public Snippets
    • Channels Snippets