|
|
Friday, May 31, 2013
This article will explain how to connect to any of the RSSBus SQLBrokers from PHP. While the example will use the SQLBroker for QuickBooks, the same process can be followed for any of the RSSBus SQLBrokers. - Step 1: Download and install the SQLBroker for QuickBooks from RSSBus.
- Step 2: Next configure the SQLBroker for QuickBooks to connect to your QuickBooks company file or online account. If you browse to the Help file in the Windows start menu, there is a link to the Getting Started Guidewhich walks you through setting up the SQLBroker for QuickBooks. Once you have installed the SQLBroker for QuickBooks and configured the connection to QuickBooks, you are ready to begin querying data from PHP.
- Step 3: Now open the connection to the SQLBroker from PHP. Invoking the mysql_connect method will connect with the RSSBus SQLBroker server in PHP. As parameters to the mysql_connect method, you will need to supply the connection info to your RSSBus SQLBroker instance. This will contain the following:
- The remote host location and port where the server is running. In this case "localhost" is used for the remote host setting since it is running on the local machine and the port is 3307.
- The username in this case is root.
- The password for the specified user.
<?php
$con = mysql_connect("localhost:3307","root","my_password");
?>
- Step 4: We are now ready to query our QuickBooks data. In this example, we will query the data from the Customers table in our QuickBooks database. The following steps will walk you through the example:
- First, we must tell the RSSBus SQLBroker which database we want to connect to. In our example, we have configured the database name to be "QuickBooksSqlBroker".
- Now we will use the mysql_query method to query the table. The results will be stored in the $result object.
- Finally, we can walk through each row and column, printing the values to display in our PHP page.
<?php
mysql_select_db("QuickBooksSqlBroker", $con);
$result = mysql_query("SELECT * FROM Customers");
while($row = mysql_fetch_assoc($result)) {
foreach ($row as $k=>$v) {
echo "$k : $v";
echo "<br />";
}
}
?>
- Step 5: Finally close the connection to RSSBus SQLBroker when we are finished querying our data source.
<?php
mysql_close($con);
?>
Wednesday, February 13, 2013
The ODBC protocol is used by a wide variety of Business Intelligence (BI) and reporting tools to get access to data from different databases. The RSSBus ODBC Drivers bring the same power and ease of use to non-traditional data sources such as Salesforce, Microsoft CRM, QuickBooks etc. Here we will use the RSSBus Salesforce Driver to import data into PowerPivot.
Importing Table Data
- Step 1: Download and install both the RSSBus ODBC Driver for Salesforce and PowerPivot.
- Step 2: Open Excel and go to the PowerPivot tab, and click on the 'PowerPivot Window' button, this will open PowerPivot.
- Step 3: Click the 'External Data from Other Sources' button, this will open the 'Table Import Wizard'.
- Step 4: Now select the data source type, in our case this will be the OLEDB/ODBC source.
- Step 5: Here you can give a name for this connection and set the connection string. The connection string is usually of the form: "Provider=MSDASQL.1;Persist Security Info=False;DSN=RSSBus Salesforce Source". If you don't know your DSN name (the only piece of information that changes in this connection string), you can also use the "Build..." button to generate the same.
Note: If you use the 'Build...' button to generate the connection string, make sure you select the 'Microsoft OLEDB Provider for ODBC Drivers' in the Provider tab. The Connection tab will then allow you to select a DSN available on your machine. If you don't see RSSBus Salesforce Source here, make sure the RSSBus Salesforce Driver is installed and a DSN has been configured.
- Step 6: On continuing with the wizard, you will be prompted to choose how to import the data. Select the option to list the tables and you will see the list of tables available from Salesforce. You can then select the tables that you want to import.
- Step 7: After closing the wizard, the data from your chosen tables will be available in PowerPivot.
Custom Query Import
Besides choosing the table to be imported, you can also specify a query to import specific columns from a table, you can even use 'WHERE' clauses to import just the relevant piece of information. To do this, instead of continuing in the wizard to the table list, choose to specify a query. This choice was mentioned in Step 6 above.
- Step 1: Choose to specify a query.
- Step 2: Here you can type any query you want, for example 'SELECT BillingStreet, Phone FROM Account'. You can validate the query and import data based on it. You can also start with a query like 'SELECT * from Account' and design the rest of it using the wizard from the Design button. This wizard allows you to modify the query and see the results it will produce.
- Step 3: Click 'Finish' to import the data for your chosen query.
The RSSBus JDBC Drivers can be used in any environment that supports loading a JDBC Driver. In this tutorial we will explore using the RSSBus Salesforce JDBC Driver from within ColdFusion.
To begin, this tutorial will assume that you have already installed the RSSBus Salesforce JDBC Driver as well as Adobe ColdFusion v10, and that you already have a Salesforce account and access token (See our forum for more details about getting an access token).
-
Add the RSSBus JDBC Driver to ColdFusion's lib directory.
Copy the Salesforce JDBC Driver and lic file from "C:\Program Files\RSSBus\RSSBus JDBC Driver for Salesforce V3\lib" to "C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib".
rssbus.jdbc.salesforce.jar
rssbus.jdbc.salesforce.lic Note: If you do not copy the .lic file with the jar, you will see a licensing error that indicates you do not have a valid license installed. This is true for both the trial and full versions.
-
Add the RSSBus Salesforce JDBC Driver as a data source.
From the ColdFusion administrator interface, choose "Data Sources" from "Data & Services". Here you will want to "Add New Data Source". The data source name can be any name, provided it conforms to the ColdFusion variable naming conventions. For driver, choose "other", then click the "Add" button.
-
Populate the driver properties.
JDBC URL will need to be in the format: jdbc:salesforce:<connectionString>. For Salesforce, the connection string must include User, Password, and AccessToken, for example:
jdbc:salesforce:User=username@test.com;Password=password;Access Token=AbCxYz
Driver Class is: rssbus.jdbc.salesforce.SalesforceDriver
The driver name is used to recognize the data source in the ColdFusion administration console.
-
Test the connection to the data source
You can now test the connection by pressing the check mark to the left of the RSSBusSalesforce data source you just created.
The data source should report an "OK" status, and should now be ready for use.
-
Creating the ColdFusion Markup File.
Next, create a new ColdFusion Markup file (.cfm) and place it in the wwwroot ("C:\ColdFusion10\cfusion\wwwroot") directory for ColdFusion.
The following code can be used to query the data source:
<cfquery name="SalesforceQuery" dataSource="RSSBusSalesforce">
SELECT * FROM Account
</cfquery> And a CFTable can be used to quickly output the table in HTML:
<cftable
query = "SalesforceQuery"
border = "1"
colHeaders
colSpacing = "2"
headerLines = "2"
HTMLTable
maxRows = "500"
startRow = "1"/>
<cfcol header="<b>ID</b>" align="Left" width=2 text="#Id#"/>
<cfcol header="<b>Name</b>" align="Left" width=15 text="#Name#"/>
<cfcol header="<b>Phone</b>" align="Center" width=12 text="#Phone#"/>
<cfcol header="<b>Billing Street</b>" align="Center" width=25 text="#Billingstreet#"/>
<cfcol header="<b>Billing City</b>" align="Center" width=15 text="#Billingcity#"/>
<cfcol header="<b>Billing State</b>" align="Center" width=5 text="#Billingstate#"/>
</cftable> Full code, including the HTML portion is available below:
<html>
<head><title>Hello World</title></head>
<body>
<cfoutput>#ucase("hello world")#</cfoutput>
<cfquery name="SalesforceQuery" dataSource="RSSBusSalesforce">
SELECT * FROM Account
</cfquery>
<cftable
query = "SalesforceQuery"
border = "1"
colHeaders
colSpacing = "2"
headerLines = "2"
HTMLTable
maxRows = "500"
startRow = "1">
<cfcol header="<b>ID</b>" align="Left" width=2 text="#Id#"/>
<cfcol header="<b>Name</b>" align="Left" width=15 text="#Name#"/>
<cfcol header="<b>Phone</b>" align="Center" width=12 text="#Phone#"/>
<cfcol header="<b>Billing Street</b>" align="Center" width=25 text="#Billingstreet#"/>
<cfcol header="<b>Billing City</b>" align="Center" width=15 text="#Billingcity#"/>
<cfcol header="<b>Billing State</b>" align="Center" width=5 text="#Billingstate#"/>
</cftable>
</body>
</html>
-
Run the code!
Running this code in the browser should produce the following output:
If you have any questions, comments, or feedback regarding this tutorial, please contact us at support@rssbus.com.
Thursday, December 20, 2012
Use the RSSBus Google ADO.NET Provider or JDBC Driver to read and search email messages on your Gmail accounts. The RSSBus Google Data Provider for ADO.NET allows you to use the search capabilities in IMAP to query your Gmail account. This article will explore how to execute your own custom queries from your application. We will use the RSSBus Google Data Provider along with the Google Email demo to execute custom queries on a Gmail account. - Step 1: Navigate to the \demos - winform\googlemail\ folder in your installation directory and open either the C# or VB version of the demo. This article will use the C# version.
- Step 2: Build and run the demo. Enter your credentials and click the Connect button.
- Step 3: After connecting, the left-hand panel directly below the credentials will be populated with the available mailboxes associated with your Gmail account. Selecting a mailbox will retrieve the individual messages contained.
- Step 4: The Advanced Search Options panel contains fields that allow you to search based on a number of different simple search criteria. You can search using as many of these criteria at one time as you like.
- Step 5: Now, we will show off the powerful capabilities of the RSSBus Google Data Provider. The Data Provider contains a number of columns that let you refine your search to exactly fit what you need. More importantly, you can query based on multiple search criteria allowing you to easily create complex queries. To see this in action, we'll edit the demo code to update the query.
- Step 6: For the purposes of demostration, we will hard-code the query we want to run. In the listSelectedMailbox() function, modify the query before you pass it to the GoogleDataReader object like so:
using (GoogleConnection conn = new GoogleConnection(buildConnectionString())) {
Query = "SELECT Id,From,Subject,Date,Size,Attachments FROM MailMessages WHERE () AND Mailbox=Inbox";
GoogleDataReader data = executeCommand(conn, Query, null, null);
enableMailBoxSelected(true);
refreshMailList(data);
}
- Step 7: For simple searches, such as seraching for all emails from a particular person, you can use the standard Columns in your WHERE clause. For example, searching for all emails from Twitter in our Inbox from a certain date, we can use the query:
SELECT * FROM MailMessages
WHERE (FROM='Twitter' AND Date > '11-25-2012')
AND Mailbox=Inbox
- Step 8: To run the query, simply start the demo again and click on your Inbox. Only emails that match the criteria you specified will be returned.
For a look at more advanced queries see: Using SQL to query Gmail
The Google Data Provider makes it easy to search email from a Gmail account. Instead of learning the details of the IMAP search command simply use the simple SQL syntax. You can also search based on multiple criteria at the same time. This article will demonstrate some advanced queries that are possible with the Data Provider.
- For simple searches, such as searching for all emails from a particular person, you can use the standard Columns in your WHERE clause. For example, searching for all emails from Twitter in our Inbox from a certain date, we can use the query:
SELECT * FROM MailMessages
WHERE (FROM='Twitter' AND Date > '11-25-2012')
AND Mailbox=Inbox
-
To find all emails with subject 'RSSBus Data Provider', we would use:
SELECT * FROM MailMessages
WHERE (Subject = 'RSSBus ADO.NET Data Provider')
AND (Mailbox = Inbox)
-
To find all sent emails between September 23rd, 2011 and September 23rd, 2012, we would use:
SELECT * FROM MailMessages
WHERE (Date > '9-23-2011' AND Date < '9-23-2012')
AND Mailbox='[Gmail]/Sent Mail'
-
To find all the mail messages in our Inbox from either Twitter or anyone with 'Microsoft' in the name, we would use:
SELECT * FROM MailMessages
WHERE (From='Twitter' OR From LIKE '%Microsoft%')
AND Mailbox=Inbox
-
To find the first 30 mail messages in our Inbox with Cc of "Microsoft" and Bcc of "Twitter", we would use:
SELECT * FROM MailMessages
WHERE (CC='Microsoft' AND BCC ='Twitter')
AND Mailbox=Inbox
LIMIT 30
-
To find all unseen emails since October 1st, 2012 and everything from Twitter, we would use:
SELECT * FROM MailMessages
WHERE (Flags LIKE '%UNSEEN%' AND Date > '10-1-2012' OR From='Twitter')
AND Mailbox=Inbox
The Data Provider contains a number of Pseudo-Columns that let you refine your search even further. Note that the SearchCriteria Pseudo-Column allows you to search directly using the IMAP Search command.
-
To directly use IMAP to find all the mail messages in our Inbox that haven't been seen and are flagged as important that were delivered before October 1st, 2012, we would use:
SELECT * FROM MailMessages
WHERE (SearchCriteria='UNSEEN FLAGGED BEFORE 1-Oct-2012')
AND Mailbox=Inbox
As you have seen, any query you want to run on your Gmail account is possible with any of the RSSBus Google Data Tools. RSSBus offers Google integration as an ADO.NET Provider, JDBC Driver, SSIS Task, and an Excel Add-In.
Tuesday, November 27, 2012
In this article we will show how to use a stored procedure included in the RSSBus SSIS Components for SharePoint to download files from SharePoint. While the article uses the RSSBus SSIS Components for SharePoint, the same process will work for any of our SSIS Components.
- Step 1: Open Visual Studio and create a new Integration Services Project.
- Step 2: Add a new Data Flow Task to the Control Flow screen and open the Data Flow Task.
- Step 3: Add an RSSBus SharePoint Source to the Data Flow Task.
- Step 4: In the RSSBus SharePoint Source, add a new Connection Manager, and add your credentials for the SharePoint site.
- Step 5: Now from the Table or View dropdown, choose the name of the Document Library that you are going to back up and close the wizard.
- Step 6: Add a Script Component to the Data Flow Task and drag an output arrow from the 'RSSBus SharePoint Source' to it.
- Step 7: Open the Script Component, go to edit the Input Columns, and choose all the columns.
- Step 8: This will open a new Visual Studio instance, with a project in it. In this project add a reference to the RSSBus.SSIS2008.SharePoint assembly available in the RSSBus SSIS Components for SharePoint installation directory.
- Step 9: In the 'ScriptMain' class, add the System.Data.RSSBus.SharePoint namespace and go to the 'Input0_ProcessInputRow' method (this method's name may vary depending on the input name in the Script Component).
- Step 10: In the 'Input0_ProcessInputRow' method, you can add code to use the DownloadDocument stored procedure. Below we show the sample code:
String connString = "Offline=False;Password=PASSWORD;User=USER;URL=SHAREPOINT-SITE";
String downloadDir = "C:\\Documents\\";
SharePointConnection conn = new SharePointConnection(connString);
SharePointCommand comm = new SharePointCommand("DownloadDocument", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
String file = downloadDir+Row.LinkFilenameNoMenu.ToString();
comm.Parameters.Add(new SharePointParameter("@File", file));
String list = Row.ServerUrl.ToString().Split('/')[1].ToString();
comm.Parameters.Add(new SharePointParameter("@Library", list));
String remoteFile = Row.LinkFilenameNoMenu.ToString();
comm.Parameters.Add(new SharePointParameter("@RemoteFile", remoteFile));
comm.ExecuteNonQuery();
After saving your changes to the Script Component, you can execute the project and find the downloaded files in the download directory.
SSIS Sample Project
To help you with getting started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional
sample package. You will also need the SharePoint SSIS Connector to make the connection.
You can download a free trial here.
Note: Before running the demo, you will need to change your connection details in both the 'Script Component' code and the 'Connection Manager'.
The RSSBus Salesforce Excel Add-In makes it easy to retrieve and update data from Salesforce from within Microsoft Excel. In addition to the built-in wizards that make data manipulation possible without code, the full functionality of the RSSBus Excel Add-Ins is available programmatically with Excel Macros (VBA) and Excel Functions. This article shows how to write an Excel macro that can be used to perform bulk inserts into Salesforce. Although this article uses the Salesforce Excel Add-In as an example, the same process can be applied to any of the Excel Add-Ins available on our website.
- Step 1: Download and install the RSSBus Excel Add-In available on our website.
- Step 2: Open Excel and create place holder cells for the connection details that are needed from the macro. In this article, a spreadsheet will be created for batch inserts, and these cells will store the connection details, and will be used to report the job Id, the batch Id, and the batch status.
- Step 3: Switch to the Developer tab in Excel. Add a new button on the spreadsheet, and create a new macro associated with it. This macro will contain the code needed to insert a batch of rows into Salesforce.
- Step 4:
Add a reference to the Excel Add-In by selecting Tools --> References --> RSSBus Excel Add-In. The macro functions of the Excel Add-In will be available once the reference has been added.
The following code shows how to call a Stored Procedure. In this example, a job is created to insert Leads by calling the CreateJob stored procedure. CreateJob returns a jobId that can be used to upload a large number of Leads in one transaction. Note the use of
cells B1, B2, B3, and B4 that were created in Step 2 to read the connection settings from the Excel SpreadSheet and to write out the status of the procedure.
methodName = "CreateJob"
module.SetProviderName ("Salesforce")
nameArray = Array("ObjectName", "Action", "ConcurrencyMode")
valueArray = Array("Lead", "insert", "Serial")
user = Range("B1").value
pass = Range("B2").value
atoken = Range("B3").value
If (Not user = "" And Not pass = "" And Not atoken = "") Then
module.SetConnectionString ("User=" + user + ";Password=" + pass + ";Access Token=" + atoken + ";")
If module.CallSP(methodName, nameArray, valueArray) Then
Dim ColumnCount As Integer
ColumnCount = module.GetColumnCount
Dim idIndex As Integer
For Count = 0 To ColumnCount - 1
Dim colName As String
colName = module.GetColumnName(Count)
If module.GetColumnName(Count) = "id" Then
idIndex = Count
End If
Next
While (Not module.EOF)
Range("B4").value = module.GetValue(idIndex)
module.MoveNext
Wend
Else
MsgBox "The CreateJob query failed."
End If
Exit Sub
Else
MsgBox "Please specify the connection details."
Exit Sub
End If
Error:
MsgBox "ERROR: " & Err.Description
- Step 5: Add the code to your macro. If you use the code above, you can check the results at Salesforce.com. They can be seen at Administration Setup -> Monitoring -> Bulk Data Load Jobs. Download the attached sample file for a more complete demo.
Distributing an Excel File With Macros
An Excel file with macros is saved using the .xlms extension. The code for the macro remains in the Excel file, and you can distribute your Excel file to any machine where the RSSBus Salesforce Excel Add-In is already installed.
Macro Sample File
Please download the fully functional
sample excel file that includes the code referenced here. You will also need the RSSBus Excel Add-In
to make the connection. You can download a free trial here.
Note: You may get an error message stating: "Can't find project or library." in Excel 2007, since this example is made using Excel 2010. To resolve this, navigate to Tools -> References and uncheck the "MISSING: RSSBus Excel Add-In", then scroll down and check the "RSSBus Excel Add-In" listed below it.
RSSBus ADO.NET Providers can be used from many tools and IDEs. In this article we show how to connect to QuickBooks from an MVC3 project using the RSSBus ADO.NET Provider for QuickBooks. Although this example uses the QuickBooks Data Provider, the same process can be used with any of our ADO.NET Providers.
Creating the Model
- Step 1: Download and install the QuickBooks Data Provider from RSSBus.
- Step 2: Create a new MVC3 project in Visual Studio. Add a data model to the Models folder using the ADO.NET Entity Data Model wizard.
- Step 3: Create a new RSSBus QuickBooks Data Source by clicking "New Connection", specify the connection string options, and click Next.
- Step 4: Select all the tables and views you need, and click Finish to create the data model.
- Step 5: Right click on the entity diagram and select 'Add Code Generation Item'. Choose the 'ADO.NET DbContext Generator'.
Creating the Controller and the Views
- Step 6: Add a new controller to the Controllers folder. Give it a meaningful name, such as ReceivePaymentsController. Also, make sure the template selected is 'Controller with empty read/write actions'.
Before adding new methods to the Controller, create views for your model. We will add the List, Create, and Delete views.
- Step 7: Right click on the Views folder and go to Add -> View. Here create a new view for each: List, Create, and Delete templates. Make sure to also associate your Model with the new views.
- Step 10: Now that the views are ready, go back and edit the RecievePayment controller. Update your code to handle the Index, Create, and Delete methods.
Sample Project
We are including a sample project that shows how to use the QuickBooks Data Provider in an MVC3 application. You may download the
C# project here or download the VB.NET project here.
You will also need to install the QuickBooks ADO.NET Data Provider to run the demo.
You can download a free trial here. To use this demo, you will also need to modify the connection string in the 'web.config'.
The RSSBus ADO.NET Providers offer an easy way to integrate with different data sources. In this article, we include a fully functional application that can be used to synchronize contacts between Google and QuickBooks. Like our QuickBooks ADO.NET Provider, the included application supports both the desktop versions of QuickBooks and QuickBooks Online Edition.
Getting the Contacts
- Step 1: Google accounts include a number of contacts. To obtain a list of a user's Google Contacts, issue a query to the Contacts table. For example: SELECT * FROM Contacts.
- Step 2: QuickBooks stores contact information in multiple tables. Depending on your use case, you may want to synchronize your Google Contacts with QuickBooks Customers, Employees, Vendors, or a combination of the three. To get data from a specific table, issue a SELECT query to that table. For example: SELECT * FROM Customers
- Step 3: Retrieving all results from QuickBooks may take some time, depending on the size of your company file. To narrow your results, you may want to use a filter by including a WHERE clause in your query. For example:
SELECT * FROM Customers
WHERE (Name LIKE '%James%')
AND IncludeJobs = 'FALSE'Synchronizing the Contacts
Synchronizing the contacts is a simple process. Once the contacts from Google and the customers from QuickBooks are available, they can be compared and synchronized based on user preference. The sample application does this based on user input, but it is easy to create one that does the synchronization automatically. The INSERT, UPDATE, and DELETE statements available in both data providers makes it easy to create, update, or delete contacts in either data source as needed.
Pre-Built Demo Application
The executable for the demo application can be downloaded here. Note that this demo is built using BETA builds of the ADO.NET Provider for Google V2 and ADO.NET Provider for QuickBooks V3, and will expire in 2013.
Source Code
You can download the full source of the demo application here. You will need the Google ADO.NET Data Provider V2 and the QuickBooks ADO.NET Data Provider V3, which can be obtained here.
|