Application Note for QuNect ODBC for QuickBase

The following two methods cannot be employed simultaneously.

Downloading File Attachments from QuickBase into SQL Server, Method #1

To create a linked server capable of accessing QuickBase file attachments directly, create a linked server with the SQL below. But first make sure your DSNs are configured. Then run the following SQL:

EXEC sp_addlinkedserver
@server = N'QUICKBASE',
@srvproduct=N'QuNect',
@provider=N'MSDASQL',
@provstr=N'DSN=QuickBase via QuNect;FILE=BINARY;'
GO

With the FILE=BINARY option on the connection string all QuickBase file attachment fields will appear as image fields in SQL Server. The user should be cautious when using the FILE=BINARY option. A simple SELECT * statement on a QuickBase table with a file attachment field can take a long time to complete if there are many records with files stored in the file attachment field. This results from the fact that all of the files are downloaded and stored in their corresponding SQL Server image fields. You can upload file attachments from SQL Server into QuickBase by using an INSERT or an UPDATE statement. For some examples of dowloading and uploading between SQL Server and QuickBase you can read Mirroring a Single QuickBase Table in SQL Server or Synching a QuickBase table in SQL Server or Synching a SQL Server table in QuickBase.

Without the FILE=BINARY option on the connection string file attachment fields in QuickBase appear as 255 character varchar fields. The contents of the field is simply the URL of the file attachment. Not the file attachment itself. If you set the value of a file attachment field the QuNect ODBC for QuickBase driver will attempt to look for a local file with a path equal to that value. If such a file is found QuNect ODBC for QuickBase will upload that local file to QuickBase as a file attachment.

In a few rare cases on SQL Server 2000 it seems that creating a linked server using the stored procedure sp_addlinkedserver does not work properly even though there is no indication of an error. The resulting linked server just doesn't work. The workaround in this situation is to use the UI method of creating a linked server.

Downloading File Attachments from QuickBase into SQL Server, Method #2

To create a linked server capable of downloading QuickBase file attachments to a directory on disk, create a linked server with the SQL below. But first make sure your DSNs are configured. Then run the following SQL:

EXEC sp_addlinkedserver
@server = N'QUICKBASE',
@srvproduct=N'QuNect',
@provider=N'MSDASQL',
@provstr=N'DSN=QuickBase via QuNect;FILEPATH=C:\QuickBase\Directory\To\Download\Files;'
GO

With the FILEPATH option on the connection string all QuickBase file attachment fields will be downloaded to the directory specified by the FILEPATH option. The user should be cautious when using the FILEPATH option. A simple SELECT * statement on a QuickBase table with a file attachment field can take a long time to complete if there are many records with files stored in the file attachment field. This results from the fact that all of the files are downloaded to disk. You'll find your file attachments downloaded to this directory (C:\QuickBase\Directory\To\Download\Files) with subfolders for each QuickBase table. The files will be in the subfolder corresponding to the table they came from. The name of the subfolders will be the DBID of the QuickBase table. You can upload file attachments from SQL Server into QuickBase by using an INSERT or an UPDATE statement. For some examples of dowloading and uploading between SQL Server and QuickBase you can read Mirroring a Single QuickBase Table in SQL Server or Synching a QuickBase table in SQL Server or Synching a SQL Server table in QuickBase.

File attachment fields in QuickBase appear as 255 character varchar fields. The contents of the field is simply the URL of the file attachment. Not the file attachment itself. If you set the value of a file attachment field the QuNect ODBC for QuickBase driver will attempt to look for a local file with a path equal to that value. If such a file is found QuNect ODBC for QuickBase will upload that local file to QuickBase as a file attachment.

In a few rare cases on SQL Server 2000 it seems that creating a linked server using the stored procedure sp_addlinkedserver does not work properly even though there is no indication of an error. The resulting linked server just doesn't work. The workaround in this situation is to use the UI method of creating a linked server.