User manual FILEMAKER FILEMAKER 11 ODBC AND JDBC GUIDE

[. . . ] FileMaker 11 ® ODBC and JDBC Guide © 2004­2010 FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker is a trademark of FileMaker, Inc. You are not authorized to make additional copies or distribute this documentation without written permission from FileMaker. You may use this documentation solely with a valid licensed copy of FileMaker software. [. . . ] The latest versions of the client drivers are also available from this URL: www. filemaker. com/support/technologies If you'll be hosting a FileMaker database file using FileMaker Server Advanced, make the client drivers available to remote users. After installing the client driver you need, you can configure the driver to access a FileMaker data source and construct SQL (Structured Query Language) queries to interact with the data. The JDBC client driver is the driver portions of the FileMaker software that allow third-party applications or custom applications to access FileMaker files as JDBC data sources. Software requirements To install and use the JDBC client drivers, you need JDK 1. 4 or later. To find which version of Java you're running, open a command window (Windows) or Terminal window (Mac OS) and type java -version. Networking requirements If you'll be accessing a FileMaker data source hosted on another computer, you'll need network access via TCP/IP. JDBC client driver installation You must have write access to the folder where you're installing the JDBC client driver. Do one of the following: 1 Windows: If you received your software electronically, double-click the installation icon (. exe file). 1 Mac OS: If you received your software electronically, double-click the disk image icon (. dmg file). 1 If you have an installation disk, insert the disk into the drive. 2. In the FileMaker Pro or FileMaker Server window, double-click the xDBC folder. In the xDBC folder, double-click the JDBC Client Driver Installer folder. 28 FileMaker ODBC and JDBC Guide 4. Copy the fmjdbc. jar file to the appropriate folder for your operating system: 1 Windows: Copy the fmjdbc. jar file to the folder that includes your Java executable file (java. exe) or to another folder location included in the ClassPath of your Java application. 1 Mac OS: Copy the fmjdbc. jar file to the /Library/Java/Extensions folder or to another folder location included in the ClassPath of your Java application. The JDBC client driver is now available for you to use to access a FileMaker data source. Using the JDBC client driver Your Java application or applet must register the JDBC client driver with the JDBC driver manager, and you must specify the correct JDBC URL from within the application or applet. Important You must reserve the port 2399 for the FileMaker JDBC client driver. For more information on using the JDBC client driver, see chapter 6, "Using JDBC to share FileMaker data. " Chapter 6 Using JDBC to share FileMaker data If you're a Java programmer, you can use the JDBC client driver with any Rapid Application Development (RAD) tool to visually create a Java application or applet that connects to a FileMaker data source. The Java application or applet that uses the JDBC client driver can directly access the data in a FileMaker database file. About JDBC JDBC is a Java API for executing SQL statements, the standard language for accessing relational databases. JDBC is a name and not an acronym -- although it is thought of as standing for "Java Database Connectivity" because it is the Java equivalent for ODBC. JDBC is a low-level interface, which means that it is used to call SQL commands directly. It is also designed to be used as a base for higher level interfaces and tools. Your Java applet or application can talk directly to a FileMaker database file by using the JDBC client driver. Your SQL statements are delivered to the FileMaker host of the database file and the results of those statements are sent back to you. If you use FileMaker Server to host, the FileMaker database file you're using as a data source can be located on another machine (the server machine) connected to the network, while your Java applet or client application is located on your machine (the client machine). This is referred to as a client/server configuration. Java application JDBC driver FileMaker Client machine Database server Using the JDBC client driver You can use the JDBC client driver with a Java compiler or RAD tool to connect with your database while you build the code for your Java application or applet. After the Java application or applet has been created, the JDBC client driver must be present with the files or included within the code in order for the application or applet to communicate with the database. [. . . ] Removes trailing blanks from a string Removes leading and trailing blanks from a string Removes leading blanks from a string Changes each letter of a string to uppercase Changes each letter of a string to lowercase Returns leftmost characters of a string Returns the names of the calendar month. Returns rightmost characters of a string RIGHT('Mattson', 4) returns 'tson' RTRIM(' ABC ') returns ' ABC' TRIM(' ABC ') returns 'ABC' LTRIM(' ABC') returns 'ABC' UPPER('Allen') returns 'ALLEN' LOWER('Allen') returns 'allen' LEFT('Mattson', 3) returns 'Mat' Example CHR(67) returns C Returns a substring of a string, with parameters of the SUBSTR('Conrad', 2, 3) returns 'onr' string, the first character to extract, and the number of SUBSTR('Conrad', 2) returns 'onrad' characters to extract (optional) Generates a string of blanks Converts a value of any type to a character string SPACE(5) returns ' ' STRVAL('Woltman') returns 'Woltman' STRVAL(5 * 3) returns '15' STRVAL(4 = 5) returns 'False' STRVAL({D '2008-12-25'}) returns '2008-12-25' TIME TIMEVAL USERNAME USER Returns the time of day as a string Returns the login ID specified at connect time At 9:49 PM, TIME() returns 21:49:00 Note The TIME() function is deprecated. Examples SELECT CHR(67) + SPACE(1) + CHR(70) FROM Salespeople SELECT RTRIM(' ' + Salespeople. Salesperson_ID) AS agg FROM Salespeople SELECT TRIM(SPACE(1) + Salespeople. Salesperson_ID) AS agg FROM Salespeople SELECT LTRIM(' ' + Salespeople. Salesperson_ID) AS agg FROM Salespeople SELECT UPPER(Salespeople. Salesperson) AS agg FROM Salespeople SELECT LOWER(Salespeople. Salesperson) AS agg FROM Salespeople SELECT LEFT(Salespeople. Salesperson, 5) AS agg FROM Salespeople SELECT RIGHT(Salespeople. Salesperson, 7) AS agg FROM Salespeople SELECT SUBSTR(Salespeople. Salesperson_ID, 2, 2) + SUBSTR(Salespeople. Salesperson_ID, 4, 2) AS agg FROM Salespeople 52 FileMaker ODBC and JDBC Guide SELECT SUBSTR(Salespeople. Salesperson_ID, 2) + SUBSTR(Salespeople. Salesperson_ID, 4) AS agg FROM Salespeople SELECT SPACE(2) + Salespeople. Salesperson_ID AS Salesperson_ID FROM Salespeople SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data. Invoice = 1 53 FileMaker ODBC and JDBC Guide Functions that return numbers Functions that return numbers ABS ATAN ATAN2 B CEIL CEILING DEG DEGREES DAY DAYOFWEEK MOD EXP FLOOR HOUR INT LEN LENGTH MONTH LN LOG MAX MIN MINUTE NUMVAL PI POW RADIANS ROUND Description Returns the absolute value of the numeric expression Returns the arc tangent of the argument as an angle expressed in radians Returns the arc tangent of x and y coordinates as an angle expressed in radians Returns the decimal equivalent of a binary number Returns the smallest integer value that is greater than or equal to the argument Returns the number of degrees of the argument, which is an angle expressed in radians Returns the day part of a date Returns the day of week (1-7) of a date expression Divides two numbers and returns the remainder of the division Returns a value that is the base of the natural logarithm (e) raised to a power specified by the argument Returns the largest integer value that is less than or equal to the argument Returns the hour part of a value. Returns the integer part of a number Returns the length of a string Returns the month part of a date Returns the natural logarithm of the argument Returns the larger of two numbers Returns the smaller of two numbers Returns the minute part of a value Converts a character string to a number; if the character string is not a valid number, returns 0 Returns the constant value of the mathematical constant pi Raises a number to a power Returns the number of radians for an argument that is expressed in degrees Rounds a number ROUND(123. 456, 0) returns 123 ROUND(123. 456, 2) returns 123. 46 ROUND(123. 456, -2) returns 100 POW(7, 2) returns 49 NUMVAL('123') returns 123 MAX(66, 89) returns 89 MIN(66, 89) returns 66 INT(6. 4321) returns 6 LEN('ABC') returns 3 DAY({d '2010/01/30'}) returns 30 DAYOFWEEK({d '2004/05/01'}) B'1001' returns 9 Example returns 7 MOD(10, 3) returns 1 MONTH({d '2010/01/30'}) returns 1 SECOND Returns the seconds part of a value 54 FileMaker ODBC and JDBC Guide Functions that return numbers SIGN SIN SQRT TAN VAL X YEAR Description An indicator of the sign of the argument: -1 for negative, 0 for 0, and 1 for positive. [. . . ]