Frequently Asked Questions about JDBCTable of Contents. What's the JDBC 3. API? Does the JDBC- ODBC bridge support the new features in the JDBC 3.

At one time, Microsoft Access was the most popular database platform. While not as popular as Excel, Access still dominates the Windows desktop database market and as. The If function extends Excel basic calculating abilities by providing conditional evaluations, based on logical, true/false tests. As an example, you might instruct. Some useful POI classes. Apache POI main classes usually start with either HSSF, XSSF or SXSSF. HSSF is the POI Project’s pure Java implementation of the Excel. This MSAccess tutorial explains how to use the Access IF-THEN-ELSE statement with syntax and examples. The Microsoft Access IF-THEN-ELSE statement can only be used in.

API? Can the JDBC- ODBC Bridge be used with applets? How do I start debugging problems related to the JDBC API? How can I use the JDBC API to access a desktop database like Microsoft Access over the network? What JDBC technology- enabled drivers are available? What documentation is available for the JDBC API? Are there any ODBC drivers that do not work with the JDBC- ODBC Bridge?

What causes the . Why isn't the. java. Driver. Manager class being found? Is there a similar method to find the number of rows in a result set?

I'm a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it? If I use the JDBC API, do I have to use ODBC underneath? Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?

What's the JDBC 3. API? The JDBC 3. 0 API is the latest update of the JDBC API.

It contains many features, including scrollable result sets and the SQL: 1. Back to top. 2. Does the JDBC- ODBC Bridge support the new features in the JDBC 3. API? Can the JDBC- ODBC Bridge be used with applets? The JDBC- ODBC bridge doesn't allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can't circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called the Java programming language can't guarantee that a security violation won't occur.

This is extremely useful when you need to test a condition and.

On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client- side configuration. Finally, we would like to note that it is possible to use the JDBC- ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all- Java JDBC driver whenever possible to avoid security problems.

Back to top. 4. How do I start debugging problems related to the JDBC API? The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations. If you use the. Driver. Manager facility to establish your database connection, you use the. Driver. Manager. set.

Log. Writer method to enable tracing of JDBC operations. If you use a. Data. Source object to get a connection, you use the. Data. Source. set.

Log. Writer method to enable tracing. How can I use the JDBC API to access a desktop database like Microsoft Access over the network? This is because the vendors of these database products haven't implemented all- Java JDBC drivers. The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the. JDBC drivers page for a list of available JDBC drivers.

The JDBC- ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC- ODBC bridge can be used together with the. RMI- JDBC bridge, however, to access a desktop database like Access over the net.

This RMI- JDBC- ODBC solution is free. What JDBC technology- enabled drivers are available? What documentation is available for the JDBC API? This page links to information about features and benefits, a list of new features, a section on getting started, online tutorials, a section on driver requirements, and other information in addition to the specifications and javadoc documentation. Are there any ODBC drivers that do not work with the JDBC- ODBC Bridge? Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and Fox.

Pro. Back to top. What causes the ? The cause can be failing to load the appropriate JDBC drivers before calling the.

Connection method, or it can be specifying an invalid JDBC URL- -one that isn't recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge. Back to top. 10. Why isn't the.

Driver. Manager class being found? The JDK 1. 0. 2 does not contain the JDBC API, so the. Driver. Manager class typically isn't found by the Java virtual machine running in the browser. Remember that classes in the. Because of this, many vendors of all- Java JDBC drivers supply versions of the. If you import. jdbc.

JDBC driver vendor to your applet's codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the. Driver. Manager class. Your applet will also work in browsers that support the JDK 1. JDK 1. 1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible. Back to top. 11. How do I retrieve a whole row of data at once, instead of calling an individual.

Result. Set. get. XXX method for each column? It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario. We welcome input from developers on this issue. Why does the ODBC driver manager return 'Data source name not found and no default driver specified Vendor: 0'.

This type of error occurs during an attempt to connect to a database with the bridge. First, note that the error is coming from the ODBC driver manager. This indicates that the bridge- which is a normal ODBC client- has successfully called ODBC, so the problem isn't due to native libraries not being present. In this case, it appears that the error is due to the fact that an ODBC DSN (data source name) needs to be configured on the client machine.

Developers often forget to do this, thinking that the bridge will magically find the DSN they configured on their remote server machine. Back to top. 13. Are all the required JDBC drivers to establish connectivity to my database part of the JDK? There aren't any JDBC technology- enabled drivers bundled with the JDK 1. Java 2 Platform releases other than the JDBC- ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database. We are considering bundling JDBC technology- enabled drivers in the future. Back to top. 14. Is the JDBC- ODBC Bridge multi- threaded?

The JDBC- ODBC Bridge does not support concurrent access from different threads. Tale Of Two Cities Court Trial Steps. The JDBC- ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi- threaded Java programs may use the Bridge, but they won't get the advantages of multi- threading. In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge.

We are thinking about removing the synchronized methods in the future. They were added originally to make things simple for folks writing Java programs that use a single- threaded ODBC driver.

Back to top. 15. Does the JDBC- ODBC Bridge support multiple concurrent open statements per connection? You can open only one. Statement object per connection when you are using the JDBC- ODBC Bridge. Why can't I invoke the. Result. Set methods. Last and. before. First when the method.

You need to upgrade to a JDBC 2. Also be sure that your code has created scrollable result sets and that the DBMS you are using supports them. Back to top. 17. How can I retrieve a.

String or other object type without creating a new object each time? It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.

How do I export from SAS to Excel files: Let me count the ways. I have a love- hate relationship with spreadsheet data. I am interested in finding data to analyze, and if it arrives in the form of a spreadsheet, I'll take it. And I like to deliver results and reports, but often my constituents ask for it as a spreadsheet that they can then manipulate further. Likewise, saving results as a spreadsheet can lose something in the translation - - usually value formatting, appearance attributes, or graphs. SAS offers many ways to read from and write to Microsoft Excel spreadsheets.

They each have pros and cons. This post is an inventory of the methods that I know about for creating Excel files from within SAS. Some . This product enables all sorts of file exchanges between SAS and other PC- based applications, including Microsoft Excel, Microsoft Access, SPSS, and more.

SAS/ACCESS to PC Files includes a component called the PC Files Server. The PC Files Server is a service- based application that runs apart from SAS on a Windows node.

It accepts requests from SAS to convert data to and from Excel (and other file types). Traditionally, this innovation allows SAS running on UNIX (where there are no native facilities for Excel data) to exchange data with PC- based data programs. Recent changes in desktop computing have complicated the picture. Newer desktop machines all have 6. Microsoft Windows. All of your 3. 2- bit applications (designed for x. One of those rules is that a 6.

And guess what? There is a better- than- even chance that the built- in data providers that you have for Microsoft Excel - - the bits that allow SAS to write to Excel on Windows - - are 3. This means that the PROC EXPORT DBMS=EXCEL program that worked in your 3. SAS session will not work in your 6. SAS session. There are two remedies for this bitness mismatch. First, you could install the 6. Microsoft Office).

But you cannot have both the 3. Microsoft Office, then you're stuck with the 3. The second remedy is to use the PC Files Server, right there on the same Windows machine where SAS is running.

This allows a 6. 4- bit SAS process to delegate the data exchange to a 3. PC Files Server process. Thanks to the out- of- process communication, this circumvents the bit architecture mismatch. To make this work you don't have to set up any additional software, but your SAS programs must change to use DBMS=EXCELCS. The EXCELCS keyword tells SAS to use the PC Files Server instead of attempting to use in- process data providers. Exporting to Excel: ways to get there from SASWith the architecture lesson behind us, here's my list for how to put SAS content into Microsoft Excel. I won't dive into much detail about each method here; you can follow the links to find more documentation.

These methods use features of SAS/ACCESS to PC Files: LIBNAME EXCEL – reads/writes Excel files at the sheet level when the bitness of SAS (3. Microsoft Office installed (or more specifically, the ACE drivers that accompany Office). An Excel file is viewed as a SAS library, while sheets/ranges are the member tables. Requires exclusive lock on an existing Excel file. LIBNAME PCFILES – does the same as LIBNAME EXCEL, but uses PC Files Server. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match. PROC EXPORT DBMS=EXCELCS – uses PC Files Server to write an Excel file.

There are various options to control the output behavior. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match. PROC EXPORT DBMS=EXCEL - writes Excel files when the bitness of SAS (3. Microsoft Office installed (or more specifically, the ACE drivers that accompany Office).

PROC EXPORT DBMS=XLS – writes Excel (XLS) files directly, no driver or PC Files Server needed. Has limits on volume and format.

Works on Windows and UNIX. PROC EXPORT DBMS=XLSX – new in 9.

M1, writes Excel 2. XLSX format) directly. No driver or PC Files Server needed. Works on Windows and UNIX. The following methods do not require SAS/ACCESS to PC Files, so they are popular, even if they don't produce . Provides a fair amount of control over the content appearance, but recent versions of Excel do not recognize as a . Works only when SAS for Windows and Excel are on the same machine.

Doesn't work in SAS workspace servers or stored process servers (often accessed with SAS Enterprise Guide). It's an antiquated approach, but offers tremendous control that many long- time SAS users enjoy. SAS client applications make it easier. While I've focused on the SAS programming methods for creating Excel files, applications like SAS Enterprise Guide and the SAS Add- In for Microsoft Office make the operation a breeze. SAS Enterprise Guide can import and export Excel files through point- and- click methods, and SAS/ACCESS to PC Files is not needed to make that work.

By allowing you to access SAS data and analytics from within Microsoft Excel, you pull the results into your Excel session, rather than export them from your SAS session.