> For the complete documentation index, see [llms.txt](https://docs.omnata.com/omnata-product-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.omnata.com/omnata-product-documentation/omnata-connect-for-salesforce/omnata-with-salesforce-apex.md). # Omnata with Salesforce Apex ## Querying data from custom Apex code Omnata provides a native Apex interfaces to your data warehouse. This allows a Salesforce developer to query external data as needed, without needing to build and maintain a custom API integration, or handle the various data type conversions. Only Snowflake is supported. The Apex interface is closer to the wire than Salesforce Connect, so different product APIs return different classes. ### Prerequisites Ensure that package installation and initial connection configuration has been performed. Once complete, data can be queried via custom Apex code by referencing the connection by name. ### Example code (New Snowflake API) ```apex omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection('Snowflake'); List queryBindings = new List(); String testString = 'A000001'; queryBindings.add(new omnata_sf.SnowflakeQueryBinding(testString)); omnata_sf.SnowflakeApiQueryResponse response = connection.executeQueryV2('select * from FINANCIAL_TRANSACTIONS where ACCOUNT_NUMBER=?',queryBindings); Integer totalRows = response.resultSetMetaData.numRows; List> rowset = response.data; List rowtypes = response.resultSetMetaData.rowtype; // If you don't need type conversion, this is the string representation straight from Snowflake List firstRow = rowset.get(0); String firstColumnValueOfFirstRow = firstRow.get(0); // Use this option to get a list of rows, with columns indexed by name List> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns(); // Use this option to get a list of rows, with columns indexed by position List> allRowsWithOrdinalColumns = response.getAllRowsWithOrdinalPositions(); ``` ### Example code (Old Snowflake API) ```apex omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection('Snowflake'); List queryBindings = new List(); String testString = 'A000001'; queryBindings.add(new omnata_sf.SnowflakeQueryBinding(testString)); omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('select * from FINANCIAL_TRANSACTIONS where ACCOUNT_NUMBER=?',queryBindings); Integer totalRows = response.data.total; List> rowset = response.data.rowset; List rowtypes = response.data.rowtype; // If you don't need type conversion, this is the string representation straight from Snowflake List firstRow = rowset.get(0); String firstColumnValueOfFirstRow = firstRow.get(0); // Use this option to get a list of rows, with columns indexed by name List> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns(); // Use this option to get a list of rows, with columns indexed by position List> allRowsWithOrdinalColumns = response.getAllRowsWithOrdinalPositions(); ``` ### Exceptions The followings exceptions may be thrown by the executeQuery method: * `ExternalObjectException` if Snowflake returns an error message * `omnata_sf.UnauthorizedException` if the connection fails due to invalid credentials. ## Mocking for tests The containers that carry API responses are global classes, so that they can be mocked. Below is an example Apex script where the response to a simple Snowflake query is mocked. New Snowflake API ```java omnata_sf.SnowflakeApiQueryResponse mockResponse = new omnata_sf.SnowflakeApiQueryResponse(); mockResponse.resultSetMetaData = new omnata_sf.SnowflakeApiQueryResponse.ResultSetMetaData(); mockResponse.resultSetMetaData.numRows = 2; mockResponse.resultSetMetaData.rowtype = new List(); omnata_sf.SnowflakeApiQueryResponse.Rowtype row1 = new omnata_sf.SnowflakeApiQueryResponse.Rowtype(); row1.name = 'Column1'; row1.type = 'VARCHAR'; omnata_sf.SnowflakeApiQueryResponse.Rowtype row2 = new omnata_sf.SnowflakeApiQueryResponse.Rowtype(); row2.name = 'Column2'; row2.type = 'NUMBER'; mockResponse.resultSetMetaData.rowtype.add(row1); mockResponse.resultSetMetaData.rowtype.add(row2); // data is a 2D matrix of the raw values Snowflake returns mockResponse.data = new List>(); mockResponse.data.add(new List{'AColumn1Value','123.45'}); mockResponse.data.add(new List{'AnotherColumn1Value','456.78'}); // pass in the mock object, instead of a connection name omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection(mockResponse); // execute a query to get your own response object back omnata_sf.SnowflakeApiQueryResponse response = connection.executeQueryV2('doesnt matter what goes here, not sent to Snowflake',null); Integer totalRowsInt = response.resultSetMetaData.numRows; System.assertEquals(2,totalRowsInt); ``` Old Snowflake API ```apex omnata_sf.SnowflakeSchemaResponse mockResponse = new omnata_sf.SnowflakeSchemaResponse(); mockResponse.data = new omnata_sf.SnowflakeSchemaResponse.Data(); mockResponse.data.rowtype = new List(); // Add column metadata omnata_sf.SnowflakeSchemaResponse.Rowtype firstColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype(); firstColumn.name = 'Column1'; firstColumn.type_Z = 'VARCHAR'; // This is the Snowflake column's native data type firstColumn.scale = 100; mockResponse.data.rowtype.add(firstColumn); omnata_sf.SnowflakeSchemaResponse.Rowtype secondColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype(); secondColumn.name = 'Column2'; secondColumn.type_Z = 'FIXED'; firstColumn.scale = 10; firstColumn.precision = 2; mockResponse.data.rowtype.add(secondColumn); omnata_sf.SnowflakeSchemaResponse.Rowtype thirdColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype(); thirdColumn.name = 'Column3'; thirdColumn.type_Z = 'BOOLEAN'; mockResponse.data.rowtype.add(thirdColumn); omnata_sf.SnowflakeSchemaResponse.Rowtype fourthColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype(); fourthColumn.name = 'Column4'; fourthColumn.type_Z = 'TIMESTAMP_LTZ'; mockResponse.data.rowtype.add(fourthColumn); // add row values, these are always just a 2D matrix of the raw values Snowflake returns DateTime dt1 = DateTime.newInstanceGmt(2017,4,9,0,0,0); DateTime dt2 = DateTime.newInstanceGmt(2019,10,9,5,30,15); mockResponse.data.rowset = new List>(); mockResponse.data.rowset.add(new List{'AColumn1Value','123.45','0',(dt1.getTime()/1000)+''}); // Snowflake returns epoch seconds mockResponse.data.rowset.add(new List{'AnotherColumn1Value','456.78','1',(dt2.getTime()/1000)+''}); mockResponse.data.total=2; // pass in the mock object, instead of a connection name omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection(mockResponse); // execute a query to get your own response object back omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('doesnt matter what goes here, not sent to Snowflake',null); Integer totalRowsInt = response.data.total; System.assertEquals(2,totalRowsInt); List> rowset = response.data.rowset; List rowtypes = response.data.rowtype; // Use this option to get a list of rows, with columns indexed by name List> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns(); System.assertEquals('AColumn1Value',allRowsWithNamedColumns.get(0).get('Column1')); System.assertEquals(123.45,allRowsWithNamedColumns.get(0).get('Column2')); System.assertEquals(0,allRowsWithNamedColumns.get(0).get('Column3')); System.assertEquals(dt1,allRowsWithNamedColumns.get(0).get('Column4')); System.assertEquals('AnotherColumn1Value',allRowsWithNamedColumns.get(1).get('Column1')); System.assertEquals(456.78,allRowsWithNamedColumns.get(1).get('Column2')); System.assertEquals(1,allRowsWithNamedColumns.get(1).get('Column3')); System.assertEquals(dt2,allRowsWithNamedColumns.get(1).get('Column4')); System.debug('Completed successfully'); ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.omnata.com/omnata-product-documentation/omnata-connect-for-salesforce/omnata-with-salesforce-apex.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.