FileMaker ODBC and JDBC Guide

Transcription

1 FileMaker 11 ODBC JDBC

2 FileMaker, Inc. All Rights Reserved. FileMaker, Inc Patrick Henry Drive Santa Clara, California FileMakerFileMaker, Inc. FileMaker, Inc. FileMaker FileMaker, Inc. FileMaker E URL E URL Acknowledgements URL 01

3 1 7 ODBC JDBC 7 ODBC FileMaker 8 ODBC 8 ODBC 8 FileMaker 8 FileMaker Pro SQL ODBC 11 ODBC SQL 12 ODBC 12 FileMaker ODBC 13 3 FileMaker ODBC 15 ODBC Windows 15 ODBC Mac OS ODBC Windows 15 Windows 16 ODBC Mac OS 17 Mac OS 18 19

4 4 FileMaker ODBC JDBC 4 ODBC FileMaker ODBC 21 ODBC 22 FileMaker 22 Windows FileMaker 23 FileMaker DSN ODBC Windows 23 ODBC Windows 24 Mac OS FileMaker 24 FileMaker DSN ODBC Mac OS 24 ODBC Mac OS 25 5 FileMaker JDBC JDBC 27 JDBC 28 6 JDBC FileMaker JDBC 29 JDBC 29 JDBC 29 JDBC URL 30 URL 31 FileMaker 32 JDBC 32

5 5 7 Unicode 33 SQL 33 SELECT 33 SQL 34 FROM 34 WHERE 35 GROUP BY 35 HAVING 35 UNION 36 ORDER BY 36 FOR UPDATE 36 DELETE 38 INSERT 39 UPDATE 40 CREATE TABLE 41 ALTER TABLE 41 CREATE INDEX 42 DROP INDEX 42 SQL 42 SQL ODBC 50 JDBC 50 SQL 50

6 6 FileMaker ODBC JDBC 8 FileMaker ODBC 53 FileMaker JDBC 53 ODBC JDBC 54 ODBC 54 JDBC 54 55

7 1 FileMaker ODBC ODBC JDBC FileMaker ODBC JDBC 1 ODBC 1. 1 FileMaker Pro 1 FileMaker 2. ODBC [ 1 FileMaker Pro Advanced 2 ] > [...] [ 1 FileMaker Server 1 FileMaker Pro 1 SQL ] 1 FileMaker Server Advanced ODBC [SQL ] 1 FileMaker 1 FileMaker Pro ODBC 1. SQL 2. ODBC JDBC 1 FileMaker Pro 1 FileMaker Pro Advanced 1 FileMaker Server Advanced FileMaker Pro ODBC JDBC 1 ODBC JDBC SQL 1 FileMaker Pro Advanced FileMaker ProFileMaker Pro FileMaker Pro Advanced FileMaker Web ODBC JDBC ODBC JDBC APIODBC C API JDBC Java API API FileMaker Pro FileMaker Server Advanced ODBC JDBC SQL SQL ODBC JDBC API FileMaker

8 8 FileMaker ODBC JDBC ODBC FileMaker ODBC FileMaker SQL FileMaker ODBC SQL ODBC ODBC ODBC ODBC 1 [ ] ODBC FileMaker Pro SQL SQL 1 [ ] [SQL ] FileMaker SQL SQL ODBC SQL ODBC ODBC ODBC ODBC FileMaker SQL 1 ODBC 1 ODBC ODBC 1 1 ODBC FileMaker ( ) FileMaker Pro ODBC SQL FileMaker Pro OracleSQL Server MySQL FileMaker Pro ODBC 2 SQL FileMaker ODBC FileMaker FileMaker ODBC JDBC FileMaker FileMaker ODBC JDBC SQL FileMaker FileMaker Pro FileMaker Server Advanced FileMaker Pro FileMaker ODBC JDBC FileMaker FileMaker FileMaker Server Advanced FileMaker Pro 50 Web FileMaker FileMaker

9 1 9 ODBC JDBC FileMaker Server Advanced FileMaker Pro FileMaker ODBC ODBC ODBC JDBC FileMaker Pro FileMaker Server Advanced ODBCOpen Database ConnectivityJDBCJava Database Connectivity SQL 1 ODBC FileMaker 3 FileMaker ODBC 1 FileMaker FileMaker ODBC 4 ODBC FileMaker 1 JDBC FileMaker 5 FileMaker JDBC 1 FileMaker JDBC FileMaker Java 6 JDBC FileMaker 1 ODBC JDBC FileMaker Pro FileMaker Server Advanced SQL 7 ODBC/JDBC FileMaker Server Advanced FileMaker Pro ODBC JDBC FileMaker FileMaker Microsoft Access: Microsoft Access FileMaker Microsoft Access Microsoft Access FileMaker Server Advanced FileMaker TCP/IP FileMaker Pro

10 10 FileMaker ODBC JDBC FileMaker Pro FileMaker Server Advanced 11 FileMaker 11 FileMaker Pro FileMaker Server Advanced 3 FileMaker ODBC 5 FileMaker JDBC FileMaker DSN DSN FileMaker DSN

11 2 SQL ODBC FileMaker SQL FileMaker ODBC SQL ODBC ODBC ODBC ODBC ODBC Oracle Oracle ODBC ODBC SQL ODBC FileMaker Pro SQL ODBC FileMaker Pro ODBC Oracle Microsoft Access FileMaker Pro FileMaker Pro [ ] [ ] [SQL ] ODBC ODBC 1 ODBC 1 FileMaker Pro ODBC DSN 1 ODBC

12 12 FileMaker ODBC JDBC 1 FileMaker Pro 1 FileMaker Pro [ ] > [ ] > [ODBC...] 1 FileMaker Pro [ ] > [...] [...] [ :]Windows [ :] (Mac OS) [ODBC...] [OK] FileMaker Pro SQL FileMaker Pro SQL SQL [WHERE] [ORDER BY] SQL Query SQL [ ] [SQL ] FileMaker ODBC [SQL ] SQL FileMaker Pro Advanced SQL FileMaker FileMaker Pro ODBC SQL ODBC FileMaker Pro [SQL ] SQL INSERTUPDATE DELETE SQL FileMaker Pro SQL FileMaker Pro SQL Server SQL [SQL ] FileMaker FileMaker Pro ODBC ODBC FileMaker SQL

13 2 SQL 13 ODBC FileMaker Pro FileMaker Server SQL ODBC FileMaker Pro FileMaker Pro FileMaker Pro FileMaker Server SQL FileMaker Pro FileMaker 11 ODBC FileMaker ODBC SQL 1 Oracle 9i 1 Oracle 10g 1 Oracle 11g 1 SQL Server SQL Server SQL Server MySQL 5.0 Community Edition 1 MySQL 5.1 Community Edition ODBC ODBC FileMaker Pro 1 ODBC 1 FileMaker Pro ODBC DSN 1 ODBC 1 ODBC FileMaker Pro 1 FileMaker Pro 1 ODBC ODBC ODBC ODBC ODBC FileMaker Pro

14 14 FileMaker ODBC JDBC

15 3 FileMaker ODBC ODBCOpen Database Connectivity FileMaker ODBC ODBC FileMaker xdbc /support/technologies FileMaker Server Advanced FileMaker FileMaker SQL ODBC ODBC Windows 1 Pentium III 700MHz 1 Windows XP 256 MB RAM 2 GBWindows Vista Windows 7 1 GB RAM 1 Microsoft Data Access ComponentsMDAC 2.8 SP1 1 Windows MDAC 6.0 for Vista ODBC Mac OS 1 Apple G4G5 Intel Mac OS 1 1 GB RAM 2 GB 1 Mac OS X FileMaker FileMaker TCP/IP ODBC Windows ODBC 32 Windows MDAC 2.8 SP1 MDAC ODBC : 1. 1.exe 1 2. FileMaker Pro FileMaker Server xdbc

16 16 FileMaker ODBC JDBC 3. xdbcodbc Client Driver Installer 4. ODBC Client Driver Installer [FMODBC_Installer_Win32.msi] FileMaker ODBC Driver Setup Wizard 5. ODBC 6. [ ] ODBC c:windowssystem32 FileMaker ODBC [FileMaker ODBC] Windows FileMaker FileMaker 2399 ODBC 1. Windows [ ] > [ (ODBC)] 1 Windows XP 1 Windows Vista Windows 7 [ODBC ] 2. [ DSN] [ DSN] 3. [...] [ ] 4. [FileMaker ODBC] [ ] [FileMaker DSN ] 5. [ ] 6. [ ] FileMaker [ ] FileMaker [ ] 7. [ ] 1 FileMaker Pro localhost IP FileMaker Server Advanced FileMaker Server IP ODBC/JDBC [ ] [ ] [ ]

17 3 FileMaker ODBC [ ] FileMaker FileMaker Server Advanced [ ] FileMaker Server [ ] [ ] 1 [ ] 1 [ ] [ ] [ ] 9. FileMaker DSN 1 [ ] FileMaker ODBC FileMaker FileMaker ODBC/JDBC FileMaker Pro FileMaker Server Advanced ODBC/JDBC 1 [ ] ODBC Mac OS ODBC : 1. 1.dmg 1 2. FileMaker Pro FileMaker Server xdbc 3. xdbcodbc Client Driver Installer 4. ODBC Client Driver Installer [FileMaker ODBC.mpkg] FileMaker ODBC Driver Installer 5. ODBC 6. [ ] ODBC / /ODBC ODBC FileMaker ODBC [FileMaker ODBC]

18 18 FileMaker ODBC JDBC Mac OS FileMaker FileMaker Actual Technologies ODBC Manager Apple ODBC Administrator Tool for Mac OS X Mac OS X 10.5 ODBC Administrator Mac OS X 10.6 ODBC Administrator FileMaker 2399 ODBC 1. ODBC Manager ODBC Manager 2. [ DSN] [ DSN] [...] [ ] 3. [FileMaker ODBC] [OK] [FileMaker DSN ] 4. [ ] 5. [ ] FileMaker [ ] FileMaker [ ] 6. [ ] 1 FileMaker Pro localhost IP FileMaker Server Advanced FileMaker Server IP ODBC/JDBC [ ] [ ] [ ] 7. [ ] FileMaker FileMaker Server Advanced [ ] FileMaker Server [ ] [ ] 1 [ ] 1 [ ] [ ] [ ]

19 3 FileMaker ODBC FileMaker DSN 1 [ ] FileMaker ODBC FileMaker FileMaker ODBC/JDBC FileMaker Pro FileMaker Server Advanced ODBC/JDBC 1 [ ] SQL FileMaker ODBC [ ] [SQL ] ODBC FileMaker 4 ODBC FileMaker

20 20 FileMaker ODBC JDBC

21 4 ODBC FileMaker ODBC FileMaker ODBC FileMaker FileMaker ODBC FileMaker ODBC FileMaker Pro ODBC SQL ODBC ODBC SQL 2 SQL ODBC ODBC API ODBC ODBC SQL SQL FileMaker ODBC SQL 7 ODBC FileMaker SQL FileMaker FileMaker Server Advanced FileMaker / Oracle ODBC SQL Server ODBC FileMaker ODBC Oracle Microsoft SQL Server FileMaker

22 22 FileMaker ODBC JDBC ODBC ODBC ODBC FileMaker 1 Microsoft Word 1 Microsoft Excel 1 FileMaker Microsoft SQL Server DBMS 1 FileMaker 1 FileMaker Pro Microsoft Visual Basic FileMaker FileMaker Pro [ODBC/JDBC ] FileMaker Server Advanced FileMaker Pro ODBC/JDBC FileMaker Pro FileMaker Server FileMaker ODBC FileMaker 11 FileMaker 11 ODBC FileMaker xdbc Listener Mac OS Activity Monitor Windows FileMaker xdbc Listener fmxdbc_listener FileMaker xdbc Listener FileMaker Server FileMaker ODBC SQL FileMaker SQL ODBC FileMaker 1. FileMaker Pro [ODBC/JDBC ] 2. FileMaker Server AdvancedFileMaker Server Admin Console FileMaker Pro ODBC/JDBC FileMaker Server Admin Console [ODBC/JDBC] [ODBC/JDBC ] FileMaker Pro [ ] > [ ] > [ODBC/JDBC...] [ODBC/JDBC ] [ ] 3. FileMaker FileMaker FileMaker 4. FileMaker 5. SQL FileMaker FileMaker DSN FileMaker

23 4 ODBC FileMaker 23 Windows FileMaker FileMaker DSN ODBC Windows FileMaker DSN DSN FileMaker ODBC FileMaker FileMaker ODBC 16 Windows ODBC 1. Windows [ ] > [ (ODBC)] 1 Windows XP 1 Windows Vista Windows 7 [ODBC ] 2. [ DSN] [ DSN] 3. FileMaker [ ] [ ] [FileMaker ODBC] 4. [...] [FileMaker DSN ] 5. [ ] FileMaker [ ] FileMaker [ ] 6. [ ] FileMaker Pro FileMaker localhost FileMaker Server Advanced FileMaker FileMaker Server IP ODBC/JDBC [ ] 7. [ ] FileMaker FileMaker Server Advanced [ ] FileMaker Server [ ] [ ] 1 [ ] 1 [ ] [ ] 8. [ ] 9. [ ] [FileMaker DSN ]

24 24 FileMaker ODBC JDBC ODBC Windows FileMaker ODBC 1. Windows [ ] > [ (ODBC)] 1 Windows XP 1 Windows Vista Windows 7 [ODBC ] 2. [ DSN] [ DSN] 3. FileMaker [ ] [ ] [FileMaker ODBC] 4. [...] [FileMaker DSN ] 5. [ ] [ ] 6. [ ] FileMaker [ ] [ ] 1 FileMaker 1 1 FileMaker [ODBC/JDBC ] 1 FileMaker Pro FileMaker Server ODBC/JDBC Mac OS FileMaker FileMaker DSN ODBC Mac OS FileMaker DSN DSN FileMaker ODBC FileMaker FileMaker ODBC 16 Windows ODBC 1. ODBC Manager ODBC Manager 2. [ DSN] [ DSN] 3. FileMaker [ ] [ ] [FileMaker ODBC] 4. [...] [FileMaker DSN ]

25 4 ODBC FileMaker [ ] 6. [ ] FileMaker [ ] 7. [ ] FileMaker Pro FileMaker localhost FileMaker Server Advanced FileMaker FileMaker Server IP ODBC/JDBC [ ] 8. [ ] FileMaker FileMaker Server Advanced [ ] FileMaker Server [ ] [ ] 1 [ ] 1 [ ] [ ] 9. [ ] 10. [ ] [FileMaker DSN ] ODBC Mac OS FileMaker ODBC 1. ODBC Manager ODBC Manager 2. [ DSN] [ DSN] 3. FileMaker [ ] [ ] [FileMaker ODBC] 4. [...] [FileMaker DSN ] 5. [ ] [ ] 6. [ ] FileMaker [ ] [ ]

26 26 FileMaker ODBC JDBC 1 FileMaker 1 1 FileMaker [ODBC/JDBC ] 1 FileMaker Pro FileMaker Server ODBC/JDBC

27 5 FileMaker JDBC JDBCJava Database Connectivity FileMaker FileMaker DVDxDBC URL FileMaker Server Advanced FileMaker FileMaker SQL JDBC FileMaker JDBC FileMaker JDBC JDK 1.4 Java Windows Mac OSjava -version FileMaker TCP/IP JDBC JDBC JDBC : 1. 1 Windows:.exe 1 Mac OS:.dmg 1 2. FileMaker Pro FileMaker Server xdbc 3. xdbcjdbc Client Driver Installer 4. fmjdbc.jar 1 Windows: fmjdbc.jar Java java.exe Java ClassPath 1 Mac OS:fmjdbc.jar/ /Java/Extensions Java ClassPath FileMaker JDBC

28 28 FileMaker ODBC JDBC JDBC JDBC Java JDBC JDBC URL FileMaker JDBC JDBC JDBC 6 JDBC FileMaker

29 6 JDBC FileMaker Java JDBC RADRapid Application Development FileMaker Java JDBC Java FileMaker JDBC JDBC SQL Java API JDBC Java ODBC Java Database Connectivity JDBC JDBC SQL JDBC Java FileMaker SQL FileMaker FileMaker Server Java FileMaker / Java JDBC FileMaker JDBC Java Java RAD JDBC Java JDBC JDBC JDBC Java JDBC URL JDBC URL JDBC JDBC JDBC 3.0 FileMaker Ref 1 CLOBARRAYREF 1

30 30 FileMaker ODBC JDBC 1 DATALINK 1 1 JDBC SPI JDBC Java Development Kit (JDK) 1.5 (Mac OS) 1.6 (Windows) FileMaker JDBC Java JDBC Java com.filemaker.jdbc.driver FileMaker JDBC JDBC FileMaker FileMaker xdbc Listener Mac OS Activity Monitor Windows FileMaker xdbc Listener fmxdbc_listener FileMaker xdbc Listener FileMaker Server JDBC URL Java URLUniform Resource LocatorJDBC URL JDBC JDBC URL jdbc:< >:< > JDBC URL JDBC jdbc< > JDBC < > filemaker < > FileMaker IP JDBC FileMaker JDBC 1. JDBC JDBC 2. FileMaker JDBC URL jdbc:filemaker:// /database 3. import java.sql.*; class FMPJDBCTest { public static void main(string[ ] args) { // register the JDBC client driver try { Driver d = (Driver)Class.forName("com.filemaker.jdbc.Driver").newInstance(); } catch(exception e) { System.out.println(e);

31 6 JDBC FileMaker 31 } } } // establish a connection to FileMaker Connection con; try { con = DriverManager.getConnection("jdbc:filemaker:// /mydatabase","username", "password"); } catch (Exception e) { } System.out.println(e); // get connection warnings SQLWarning warning = null; try { warning = con.getwarnings(); if (warning == null) { System.out.println("No warnings"); return; } while (warning!= null) { System.out.println("Warning: "+warning); warning = warning.getnextwarning(); } } catch (Exception e) { } Sysem.out.println(e); URL user password JDBC URL DriverManager.GetConnection 1 user: [ODBC/JDBC ] FileMaker 1 password: FileMaker URL JDBC URL : : jdbc:filemaker://<filemaker IP >/< > jdbc:filemaker:// /publications URL JDBC URL : : jdbc:filemaker://<filemaker IP >/< >?user=< >&password=< > jdbc:filemaker:// /customers?user=collections&password=admin &

32 32 FileMaker ODBC JDBC : jdbc:filemaker://localhost/sales_db?user=ad&min&password=admin : jdbc:filemaker://localhost/sales_db?user=admin1&password=ad&min FileMaker JDBC URL FileMaker JDBC JDBC FileMaker 1 FileMaker 1 FileMaker [ODBC/JDBC ] 1 FileMaker Pro FileMaker Server Advanced ODBC/JDBC FileMaker FileMaker Pro [ODBC/JDBC ] FileMaker Server Advanced FileMaker Pro ODBC/JDBC FileMaker Pro 1 JDBC JDBC URL Java JDBC FileMaker

33 7 FileMaker ODBC JDBC SQL ODBC JDBC FileMaker FileMaker FileMaker Pro FileMaker Server Advanced ODBC Level 2 ODBC 3.5 Level 1 JDBC JDBC ODBC JDBC SQL-92 SQL-92 Unicode ODBC JDBC Unicode API Unicode ASCII Unicode SQL_C_WCHAR SQL ODBC JDBC SQL SELECT DELETE (38 ) INSERT (39 ) UPDATE (40 ) CREATE TABLE (41 ) ALTER TABLE (41 ) CREATE INDEX (42 ) DROP INDEX (42 ) FileMaker ODBC SQL JDBC SQL 53 FileMaker ODBC 53 FileMaker JDBC SQL ODBC JDBC FileMaker SELECT SELECT SELECT * 1.05 SELECT SELECT [DISTINCT] {* [[AS] ],...} FROM [ ],... [ WHERE 1 2 ] [ GROUP BY {,...} ] [ HAVING 1 2 ] [ UNION [ALL] (SELECT...) ] [ ORDER BY { [DESC ASC]},... ] [ FOR UPDATE [OF {,...}] ] []

34 34 FileMaker ODBC JDBC SELECT AS FROM.. DISTINCT : SELECT DISTINCT FROM SQL ODBC JDBC SQL SQL FROM (34 ) WHERE (35 ) GROUP BY (35 ) HAVING (35 ) UNION (35 ) ORDER BY (36 ) FOR UPDATE (36 ) SELECT FileMaker Pro FileMaker Pro 20,000,000 SELECT SQL SELECT FROM FROM SELECT FROM [ ] [, [ ]] FROM E E. SELECT : SELECT * FROM E, F WHERE E. = F. = INNER JOIN : SELECT * FROM INNER JOIN ON. =. LEFT JOIN SELECT * FROM LEFT JOIN ON. =.

35 7 35 RIGHT JOIN SELECT * FROM RIGHT JOIN ON. =. 1 LEFT JOIN LEFT OUTER JOIN 1 RIGHT JOIN RIGHT OUTER JOIN 1 FULL OUTER JOIN WHERE WHERE WHERE WHERE SELECT 2,000,000 SELECT, FROM WHERE >= WHERE WHERE 1 IS NULL WHERE NOT 2 SELECT WHERE GROUP BY GROUP BY GROUP BY SELECT SELECT, SUM( ) FROM GROUP BY HAVING HAVING 20,000,000 HAVING SELECT 20,000,000 SELECT, SUM( ) FROM GROUP BY HAVING SUM( ) >

36 36 FileMaker ODBC JDBC UNION UNION SELECT SELECT ALL UNION ALL SELECT UNION [ALL] SELECT UNION SELECT : SELECT,, FROM UNION SELECT,, FROM SELECT SELECT, FROM UNION SELECT, FROM ORDER BY ORDER BY ORDER BY { [DESC ASC]},... ASC SELECT SELECT,, FROM ORDER BY, SELECT,, FROM ORDER BY 2,3 SELECT ORDER BY 2 FOR UPDATE FOR UPDATE SQL FOR UPDATE [OF ] 2,000,000 SELECT * FROM WHERE > FOR UPDATE OF,,

37 7 37 BLOB a * SQL SELECT 'CatDog' FROM SELECT 999 FROM SELECT DATE ' ' FROM SELECT TIME '02:49:03' FROM SELECT TIMESTAMP ' :49:03' FROM SELECT FROM SELECT DISTINCT FROM SELECT FROM SELECT DISTINCT FROM SELECT FROM SELECT DISTINCT FROM SELECT FROM SELECT DISTINCT FROM SELECT FROM SELECT DISTINCT FROM SELECT FROM SELECT GETAS(, 'JPEG') FROM SELECT * FROM SELECT DISTINCT * FROM a. BLOB FileMaker FileMaker *SELECT * FROM SELECT DISTINCT * FROM 1 FileMaker SELECT * FROM WHERE c = SELECT * FROM WHERE c <> 1 SELECT GetAs() : CAST() GetAs(), : CAST() GetAs() SELECT : SELECT FROM JPEG SELECT SELECT < > Quicktime SELECT CAST : SELECT CAST( AS VARCHAR(NNN)) FROM

38 38 FileMaker ODBC JDBC 1 FileMaker Pro SELECT SQL_VARCHAR 1 FileMaker Pro SELECT 1 SELECT '?' FileMaker Pro Untitled.dat GetAs FileMaker Pro 1 [ ] > [...] GetAs : SELECT GetAs(, ' ') FROM 1 [ ] > [...] - Mac OS GetAs snd : SELECT GetAs(, 'snd') FROM 1 [ ] > [...] OLE GetAs EMBO : SELECT GetAs(, 'EMBO') FROM 1 [ ] > [...] ( ) : SELECT GetAs(, 'JPEG') FROM 'EMF+' Windows Enhanced Metafile Plus 'PDF ' Portable Document Format 'EPS ' Embedded PostScript 'PICT' Mac OS512- 'FPix' FlashFPX 'PNGf' Bitmap 'FORK' Mac OS 'PNTG' MacPaint 'GIFf' Graphics Interchange Format 'qtif' QuickTime 'JPEG' '.SGI' 'JP2 ' JPEG 2000 'TIFF' 'META' Windows 'TPIC' Targa 'METO' Windows 'XMLO' 'moov' QuickTime Mac OS '8BPS' PhotoShop (PSD) DELETE DELETE DELETE DELETE FROM [ WHERE { } ] WHERE WHERE DELETE DELETE FROM WHERE = 'E10001' DELETE WHERE E10001

39 7 39 INSERT INSERT 1 1 SELECT INSERT INSERT INTO [(,...)] VALUES (,...) [, VALUES (,...)] VALUES [4] ' : 'Don''t' () INSERT INTO (,,,, ) VALUES (' ', ' ', 'E22345', , {2008/06/05}) INSERT INSERT PutAs() PutAs(col, 'type')37 : CAST() GetAs() SELECT SELECT INSERT SELECT INSERT INSERT INTO 1 (,,,, ) SELECT,,,, FROM WHERE = ' ' INSERT SELECT INSERT SELECT SELECT INSERT INSERT/UPDATE ODBC JDBC SELECT INSERT SELECT

40 40 FileMaker ODBC JDBC UPDATE UPDATE UPDATE UPDATE SET =,... [ WHERE { } ] ' : 'Don''t' () WHERE UPDATE UPDATE SET =32000, =1 WHERE = 'E10001' UPDATE WHERE E10001 UPDATE SET = (SELECT avg( ) from ) WHERE = 'E10001' E10001 UPDATE PutAs() PutAs(col, 'type')37 : CAST() GetAs()

41 7 41 CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE [DEFAULT ] [UNIQUE] [NOT NULL] DEFAULT CURRENT_USERCURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP 1 UNIQUE FileMaker [ ] 1 NOT NULL FileMaker [ ] FileMaker Pro [ ] [ ] [ ] NOT NULL BLOB SQL CREATE TABLE 1 ( 1 VARCHAR, 2 VARCHAR (50), 3 VARCHAR (1001), 4 VARCHAR (500276)) CREATE TABLE 1NN ( 1 VARCHAR NOT NULL, 2 VARCHAR (50) NOT NULL, 3 VARCHAR (1001) NOT NULL, 4 VARCHAR (500276) NOT NULL) CREATE TABLE 2 ( 1 DECIMAL, 2 DECIMAL (10,0), 3 DECIMAL (7539,2), 4 DECIMAL (497925,301)) CREATE TABLE 3 ( 1 DATE, 2 DATE, 3 DATE, 4 DATE) CREATE TABLE 4 ( 1 TIME, 2 TIME, 3 TIME, 4 TIME) CREATE TABLE 5 ( 1 TIMESTAMP, 2 TIMESTAMP, 3 TIMESTAMP, 4 TIMESTAMP) CREATE TABLE 6 ( 1 BLOB, 2 BLOB, 3 BLOB, 4 BLOB) ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ADD [COLUMN] ALTER TABLE DROP [COLUMN] ALTER TABLE ALTER [COLUMN] SET DEFAULT ALTER TABLE ALTER [COLUMN] DROP DEFAULT ALTER TABLE SQL ALTER TABLE ADD 1 VARCHAR ALTER TABLE DROP 1 ALTER TABLE ALTER SET DEFAULT FileMaker ALTER TABLE ALTER DROP DEFAULT SET DEFAULT DROP DEFAULT

42 42 FileMaker ODBC JDBC CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ON. CREATE INDEX ON ( ) CREATE INDEX FileMaker FileMaker [ ] [ ] FileMaker [ ] [ ] FileMaker [ ] [ ] FileMaker CREATE INDEX CREATE INDEX ON. DROP INDEX DROP INDEX DROP INDEX DROP INDEX ON. DROP INDEX ON ( ) FileMaker SELECT FileMaker [ ] [ ] [ ] PREVENT INDEX CREATION DROP INDEX ON. SQL SELECT AVG( ) AVG( * 1.07) DISTINCT : COUNT (DISTINCT )

43 7 43 SUM AVG COUNT MAX MIN SUM( ) AVG( ) COUNT( ) COUNT COUNT COUNT(*) MAX( ) MIN( ) SELECT SUM (. ) AS FROM SELECT AVG (. ) AS FROM SELECT COUNT (. ) AS FROM SELECT MAX (. ) AS FROM WHERE. < SELECT MIN (. ) AS FROM WHERE. > SQL SELECT WHERE HAVING ORDER BY. * ' : 'Don''t' FileMaker {} ODBC/JDBC 1 {D ' '} 1 {T '14:35:10'} 1 {TS ' :35:10'}

44 44 FileMaker ODBC JDBC FileMaker SQL-92 ISO 1 DATE 'YYYY-MM-DD' 1 TIME 'HH:MM:SS' 1 TIMESTAMP 'YYYY-MM-DD HH:MM:SS' 1.05 ' ' DATE ' ' { D ' ' } {2010/06/05} {10/06/05} : ODBC/JDBC SQL-92 TIME '14:35:10' { T '14:35:10' } {14:35:10} TIMESTAMP ' :35:10' { TS ' :35:10'} {2010/06/05 14:35:10} {10/06/05 14:35:10} FileMaker [ 4 ] : ODBC/JDBC SQL-92 SELECT 1 / 3.4E+7 FROM 1 WHERE < 3.4E-6 * 2 +-*/ ^ ** ' ' - - ' '

45 7 45 {D ' '} {D ' '} - - {D ' '} {D '2008/01/20'} SELECT, + 30 AS FROM SELECT, - 30 AS FROM = <> > >= < <= LIKE NOT LIKE IS NULL IS NOT NULL BETWEEN IN NOT IN EXISTS ANY ALL =<>>>=< <= =Any In =<>>>=< <= SELECT. FROM WHERE. = 'SP-1' SELECT. FROM WHERE. <> 125 SELECT. FROM WHERE. > SELECT. FROM WHERE. < '12:00:00' SELECT. FROM WHERE. LIKE '% ' SELECT. FROM WHERE. NOT LIKE '% '

46 46 FileMaker ODBC JDBC SELECT. FROM WHERE. IS NULL SELECT. FROM WHERE. IS NOT NULL SELECT. FROM WHERE. BETWEEN 1 AND 10 SELECT COUNT(. ) AS FROM WHERE. IN (50,250,100) SELECT COUNT(. ) AS FROM WHERE. NOT IN (50,250,100) SELECT COUNT (. ) AS FROM WHERE. NOT IN (SELECT. FROM WHERE. = 'SP-4') SELECT * FROM WHERE EXISTS (SELECT. FROM WHERE. IS NOT NULL) SELECT * FROM WHERE. = ANY (SELECT. FROM WHERE. = 'SP-1') SELECT * FROM WHERE. = ALL (SELECT. FROM WHERE. IS NULL) AND OR = AND = 1 NOT NOT ( = AND = 1) SELECT * FROM WHERE. NOT LIKE '% ' AND. > SELECT * FROM WHERE (. LIKE '% ' OR. > ) AND. = 'SP-1' FileMaker SQL

47 7 47 CHR ASCII CHR(67) C CURRENT_USER DAYNAME ID RTRIM RTRIM(' ABC ') ABC TRIM TRIM(' ABC ') ABC LTRIM LTRIM(' ABC') ABC UPPER UPPER('Allen'ALLEN LOWER LOWER('Allen') allen LEFT LEFT('Mattson',3Mat MONTHNAME RIGHT RIGHT(Mattson', 4) tson SUBSTR SUBSTRING SUBSTR('Conrad', 2, 3) onr SUBSTR('Conrad', 2) onrad SPACE SPACE(5) STRVAL STRVAL('Woltman') Woltman STRVAL(5 * 3) 15 STRVAL(4 = 5) False STRVAL({D ' '}) TIME TIMEVAL USERNAME USER ID 9:49 TIME() 21:49:00 TIME() SQL CURRENT_TIME SELECT CHR(67) + SPACE(1) + CHR(70) FROM SELECT RTRIM(' ' +. ) AS FROM SELECT TRIM(SPACE(1) +. ) AS FROM SELECT LTRIM(' ' +. ) AS FROM SELECT UPPER(. ) AS FROM SELECT LOWER(. ) AS FROM SELECT LEFT(., 5) AS FROM SELECT RIGHT(., 7) AS FROM SELECT SUBSTR(., 2, 2) + SUBSTR(., 4, 2) AS FROM SELECT SUBSTR(., 2) + SUBSTR(., 4) AS FROM SELECT SPACE(2) +. AS FROM SELECT STRVAL('60506') AS FROM WHERE. = 1

48 48 FileMaker ODBC JDBC ABS ATAN ATAN2 x y B 10 B'1001' 9 CEIL CEILING DEG DEGREES DAY DAY({d '2010/01/30'}) 30 DAYOFWEEK DAYOFWEEK({d '2004/05/01'}) 7 MOD EXP FLOOR HOUR (e) MOD(10, 3) 1 INT INT(6.4321) 6 LEN LENGTH LEN('ABC') 3 MONTH MONTH({d '2010/01/30'}) 1 LN LOG MAX MAX(66, 89) 89 MIN MIN(66, 89) 66 MINUTE NUMVAL PI 0 pi NUMVAL('123') 123 POW POW(7,2) 49 RADIANS ROUND ROUND( ,0) 123 ROUND( ,2) ROUND( , -2) 100 SECOND SIGN SIN SQRT TAN -

49 7 49 VAL VAL('123') X X'b9' 185 YEAR DAY({d '2010/01/30'}) 2010 CURDATE CURRENT_DATE CURTIME CURRENT_TIME CURTIMESTAMP CURRENT_TIMESTAMP TIMESTAMPVAL DATE TODAY 2010/11/21 DATE() DATEVAL DATEVAL('2011/01/30') DATE() SQL CURRENT_DATE ^, ** 3 *, / 4 +, - 5 =, <>, <, <=, >, >=LikeNot LikeIs NullIs Not NullBetweenInExistsAnyAll 6 Not 7 AND 8 OR WHERE > OR > {d 2008/01/30} AND = 'D101' AND D101 4,000,000 : WHERE ( > OR > {d 2008/01/30}) AND = 'D101' 4,000, D101

50 50 FileMaker ODBC JDBC ODBC ODBC 1 SQLTables - 1 SQLColumns 1 SQLColumnPrivileges 1 SQLDescribeCol 1 SQLGetTypeInfo JDBC JDBC 1 getcolumns 1 getcolumnprivileges 1 getmetadata 1 gettypeinfo 1 gettables 1 gettabletypes SQL

51 51 FileMaker ODBC JDBC Create Table OID create table t ("oid" numeric) ABSOLUTE ACTION ADD ALL ALLOCATE ALTER AND ANY ARE AS ASC ASSERTION AT AUTHORIZATION AVG BEGIN BETWEEN BINARY BIT BIT_LENGTH BLOB BOOLEAN BOTH BY CASCADE CASCADED CASE CAST CATALOG CHAR CHARACTER CHARACTER_LENGTH CHAR_LENGTH CHECK CHR CLOSE COALESCE COLLATE COLLATION COLUMN COMMIT CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONTINUE CONVERT CORRESPONDING COUNT CREATE CROSS CURDATE CURRENT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CURTIME CURTIMESTAMP DATE DATEVAL DAY DAYNAME DAYOFWEEK DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE DEFERRED DELETE DESC DESCRIBE DESCRIPTOR DIAGNOSTICS DISCONNECT DISTINCT DOMAIN DOUBLE DROP ELSE END END_EXEC ESCAPE EVERY EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXTERNAL EXTRACT FALSE FETCH FIRST FLOAT FOR FOREIGN FOUND FROM FULL GET GLOBAL GO GOTO GRANT GROUP HAVING HOUR IDENTITY IMMEDIATE IN INDEX INDICATOR INITIALLY INNER INPUT INSENSITIVE NSERT INT INTEGER INTERSECT INTERVAL INTO IS ISOLATION JOIN KEY LANGUAGE LAST LEADING LEFT LENGTH LEVEL LIKE LOCAL LONGVARBINARY LOWER LTRIM MATCH MAX MIN MINUTE MODULE MONTH MONTHNAME NAMES NATIONAL NATURAL NCHAR NEXT NO NOT NULL NULLIF NUMERIC NUMVAL OCTET_LENGTH OF ON ONLY OPEN OPTION OR ORDER OUTER OUTPUT OVERLAPS PAD PART PARTIAL POSITION PRECISION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURE PUBLIC READ REAL REFERENCES RELATIVE RESTRICT REVOKE RIGHT ROLLBACK ROUND ROWID ROWS RTRIM SCHEMA SCROLL SECOND SECTION SELECT SESSION

52 52 FileMaker ODBC JDBC SESSION_USER SET SIZE SMALLINT SOME SPACE SQL SQLCODE SQLERROR SQLSTATE STRVAL SUBSTRING SUM SYSTEM_USER TABLE TEMPORARY THEN TIME TIMESTAMP TIMESTAMPVAL TIMEVAL TIMEZONE_HOUR TIMEZONE_MINUTE TO TODAY TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE UNION UNIQUE UNKNOWN UPDATE UPPER USAGE USER USERNAME USING USAGE USER USERNAME USING VALUE VALUES VARBINARY VARCHAR VARYING VIEW WHEN WHENEVER WHERE WITH WORK WRITE YEAR ZONE

53 8 FileMaker ODBC FileMaker Pro ODBC FileMaker ODBC SQL_VARCHAR FileMaker [ :] 100 FileMaker NULL SQL_DOUBLE FileMaker SQL_DATE SQL_TIME FileMaker 24 0 SQL_TIMESTAMP BLOB SQL_LONGVARBINARY SELECT CAST GetAs ODBC Unicode FileMaker : INSERT INTO mytable(repfield[3]) VALUES ('this is rep 3') SELECT repfield[1], repfield[2] FROM mytable FileMaker JDBC JDBC FileMaker JDBC SQL JDK 1.5 Web FileMaker JDBC SQL java.sql.types.varchar java.sql.types.double java.sql.types.date java.sql.types.time java.sql.types.timestamp java.sql.types.blob FileMaker JDBC JDBC SQL FileMaker JDBC java.sql.types.timestamp

54 54 FileMaker ODBC JDBC ODBC JDBC FileMaker ODBC/JDBC ODBC 1 ODBC 1 FileMaker FileMaker xdbc Listener FileMaker ODBC FileMaker Listener [FileMaker] [FileMaker ODBC] FileMaker [FileMaker] [FileMaker ODBC] Invalid Username/Password FileMaker JDBC FileMaker JDBC SQLException 1 JDBC 1 FileMaker FileMaker xdbc Listener FileMaker JDBC FileMaker Listener [FileMaker] [FileMaker JDBC] FileMaker [FileMaker] [FileMaker JDBC] Invalid Username/Password FileMaker

55 A ABS 48 ALL 45 ALTER TABLESQL 41 AND 46 ANY 45 ARRAY 29 ATAN2 48 ATAN 48 B BETWEEN 45 BLOB CREATE TABLE 41 SELECT 37 B 48 C CAST 37, 53 CEILING 48 CEIL 48 CHR 47 CLOB 29 CREATE INDEXSQL 42 CREATE TABLESQL 41 CURDATE 49 CURRENT USER 47 CURRENT_DATE 49 CURRENT_TIMESTAMP 49 CURRENT_TIME 49 CURRENT_USER 47 CURTIMESTAMP 49 CURTIME 49 D DATALINK 30 DATEVAL 49 DATE 49 DAYNAME 47 DAYOFWEEK 48 DAY 48 DEGREES 48 DEG 48 DELETESQL 38 DISTINCT 34 DROP INDEXSQL 42 DSN Mac OS 24 Windows E EXISTS 45 EXP 48 F FileMaker 8 FileMaker JDBC 31 ODBC Mac OS 24 ODBC Windows 23 FLOOR 48 FOR UPDATESQL 36 FROMSQL 34 FULL OUTER JOIN 35 G GetAs 38, 53 GROUP BYSQL 35 H HAVINGSQL 35 HOUR 48 I INNER JOIN 34 INSERTSQL 39 INT 48 IN 45 IS NOT NULL 45 IS NULL 45 J Java Development KitJDK 30 Java 27 JDBC JDBC SPI 30 JDBC JDBC URL 30 JDBC 30 Unicode JDBC URLUniform Resource

56 56 FileMaker ODBC JDBC Locator 30 JDBC 30 JDBC 50 L LEFT JOIN 34, 35 LEFT OUTER JOIN 34, 35 LEFT 47 LEN 48 LIKE 45 LN 48 LOG 48 LOWER 47 LTRIM 47 M Mac OS DSN 24 JDBC 27 ODBC 25 ODBC 15 MAX 48 MINUTE 48 MIN 48 MOD 48 MONTHNAME 47 MONTH 48 N NOT IN 45 NOT LIKE 45 NOT NULLSQL 41 NOT 46 NUMVAL 48 O ODBC ODBC AdministratorMac OS 25 ODBC/JDBC 22 ODBC Unicode 33 Mac OS 25 Windows ODBC Windows 24 ODBC 50 ORDER BYSQL 36 OR 46 OUTER JOIN 35 P PI 48 POW 48 PREVENT INDEX CREATION 42 R RADIANS 48 Rapid Application DevelopmentRAD 29 REF 29 RIGHT JOIN 35 RIGHT OUTER JOIN 35 RIGHT 47 ROUND 48 RTRIM 47 S SECOND 48 SELECTSQL 33 BLOB SIGN 48 SIN 48 SPACE 47 SQL 44 SQL_C_WCHAR 33 SQL SQLExceptions 54 SQL SQL 49 SQL 44 SQL 46 SQL 44 SQL 44 SQL 43 SQL 45 SQL 43 SQL 43 SQL 45 SQL 46 SQL ALTER TABLE 41 CREATE INDEX 42

57 57 CREATE TABLE 41 DELETE 38 DROP INDEX 42 INSERT 39 SELECT 33 UPDATE SQL 42 SQL 43 SQL 33 SQRT 48 STRVAL 47 SUBSTRING 47 SUBSTR 47 T TAN 48 TIMESTAMPVAL 49 TIMEVAL 47 TIME 47 TODAY 49 TRIM 47 U Unicode 33 UNIONSQL 36 UPDATESQL 40 UPPER 47 USERNAME 47 V VALUESSQL 39 VAL 48 W Windows DSN 23 JDBC 27 ODBC 24 ODBC 15 WHERE SQL 35 X X 49 Y YEAR JDBC 32 ODBC Mac OS 25 ODBC Windows 24 JDBC 32 ODBC Mac OS 25 ODBC Windows , 27 15, INSERT 39 JDBC 53 SELECT 37 UPDATE QuickTime JDBC 29 ODBC 36 FileMaker ODBC JDBC SELECT 37 SQL 50 FileMaker 9 ODBC/JDBC FileMaker

58 58 FileMaker ODBC JDBC , JDBC 32 JDBC 31 ODBC Mac OS 25 ODBC Windows 24 ODBC Mac OS 24 ODBC Windows 23 FileMaker DSN 10 FileMaker 9 DSN JDBC 53 ODBC 53 8 DSN SQL JDBC 31 ODBC Mac OS 24 ODBC Windows 23 39, 53 SELECT 37 JDBC 31 ODBC 24, JDBC 53 ODBC JDBC DSN 23, SQL