Driver SQL Syntax
On this page you will find various query examples for Jdbc ODBC Driver / Data Gateway Connector.
Jdbc Query Language Reference
Jdbc driver offers executing query on server side using native SQL. Click on the below link to learn more about Jdbc Query Syntax.
Native SQL Help Page:
[Refer to your JDBC Driver Help file to learn about SQL Syntax]
SQL Examples
DML - SELECT Example
This example shows how to write select query.
SELECT * FROM MyTable
DML - UPDATE Example
This example shows how to use UPDATE one or more records in JDBC table. You can supply any valid SOQL compatible WHERE clause.
UPDATE account SET name='New Company Name', BillingState='GA' WHERE id IN ('0014N00001hTNEEQA4')
DML - INSERT Example
This example shows how to create a new record in JDBC table.
INSERT INTO Account(Name, BillingCity,PK__c)
VALUES ('mycompany name', 'New York','K100')
DML - DELETE Example
This example shows how to delete one or more records in JDBC table. You can supply any valid SOQL compatible WHERE clause.
DELETE FROM account WHERE id IN ('0014N00001hTNEEQA4')
Output-On Flag
This example shows how to invoke statements which has multiple statements and produces result. If you are calling multiple statements then for some JDBC drivers you have to set this else it will not produce output or might throw error.
#Output-On CREATE TABLE MyTable(Id int); INSERT INTO MyTable(Id) Values(1); SELECT * From MyTable;
Output-Off Flag
.
#Output-Off CREATE TABLE MyTable(Id int)
Using Java settings file (e.g. set HTTP Proxy, use java runtime path, enable log)
This example shows how to create java settings file in ZappySys to use additional options for Java runtime. This file affects all JDBC connection you invoke using ZappySys driver.
For example use this file if you have multiple java runtimes and you like to use specific java version.
--or-- you like to enable log file for additional logging
--or-- you like to pass runtime arguments to java.exe using java.arg options (e.g. enable HTTP Proxy or change memory settings).
Check this link to see all possible options you can pass in java.args setting below
https://docs.oracle.com/en/java/javase/17/docs/specs/man/java.html
To Use ZappySys Java Settings file use below steps.
1. Create a custom file called "JdbcBridge.properties" in the following location (assuming you installed at default location) C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\
2. You can paste following example lines to start with (use # to comment and remove it to uncomment option you need). Basically you can use app.logfile, java.path and java.args
#------------Start of file---------------------
######################################################
##Sample settings file for ZappySys JDBC Bridge
######################################################
####Enable Additional log from java side like below
#app.logfile=c:/temp/JdbcBridge_log.txt
####Set custom Java runtime path rather than using default path like below (uncomment / change as per your need)
#java.path=c:\JAVA\JDK8\bin\
#java.path=c:\Program Files\Amazon Corretto\jdk1.8.0_382\bin
####Enable HTTP/HTTPS Proxy like below (e.g. Use Fiddler Proxy or other Web Proxy)
#java.args= -Djava.net.useSystemProxies=true
#--OR--
#java.args= -Dhttps.proxyHost=127.0.0.1 -Dhttps.proxyPort=8888 -Dhttp.proxyHost=127.0.0.1 -Dhttp.proxyPort=8888
#--OR--
#Tools like Fiddler (web proxy tool) will need some extra settings to trust self-signed certificate
#https://azureossd.github.io/2017/04/26/capture-java-vm-traffic-using-fiddler/
#java.args= -Dhttps.proxyHost=127.0.0.1 -Dhttps.proxyPort=8888 -Dhttp.proxyHost=127.0.0.1 -Dhttp.proxyPort=8888 -Djavax.net.ssl.trustStore="c:\Program Files\Amazon Corretto\jdk1.8.0_382\bin\FiddlerKeystore" -Djavax.net.ssl.trustStorePassword=test123
#------------End of file---------------------
This example shows how to create java settings file in ZappySys to use additional options for Java runtime. This file affects all JDBC connection you invoke using ZappySys driver.
For example use this file if you have multiple java runtimes and you like to use specific java version.
--or-- you like to enable log file for additional logging
--or-- you like to pass runtime arguments to java.exe using java.arg options (e.g. enable HTTP Proxy or change memory settings).
Check this link to see all possible options you can pass in java.args setting below
https://docs.oracle.com/en/java/javase/17/docs/specs/man/java.html
To Use ZappySys Java Settings file use below steps.
1. Create a custom file called "JdbcBridge.properties" in the following location (assuming you installed at default location) C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\
2. You can paste following example lines to start with (use # to comment and remove it to uncomment option you need). Basically you can use app.logfile, java.path and java.args
#------------Start of file---------------------
######################################################
##Sample settings file for ZappySys JDBC Bridge
######################################################
####Enable Additional log from java side like below
#app.logfile=c:/temp/JdbcBridge_log.txt
####Set custom Java runtime path rather than using default path like below (uncomment / change as per your need)
#java.path=c:\JAVA\JDK8\bin\
#java.path=c:\Program Files\Amazon Corretto\jdk1.8.0_382\bin
####Enable HTTP/HTTPS Proxy like below (e.g. Use Fiddler Proxy or other Web Proxy)
#java.args= -Djava.net.useSystemProxies=true
#--OR--
#java.args= -Dhttps.proxyHost=127.0.0.1 -Dhttps.proxyPort=8888 -Dhttp.proxyHost=127.0.0.1 -Dhttp.proxyPort=8888
#--OR--
#Tools like Fiddler (web proxy tool) will need some extra settings to trust self-signed certificate
#https://azureossd.github.io/2017/04/26/capture-java-vm-traffic-using-fiddler/
#java.args= -Dhttps.proxyHost=127.0.0.1 -Dhttps.proxyPort=8888 -Dhttp.proxyHost=127.0.0.1 -Dhttp.proxyPort=8888 -Djavax.net.ssl.trustStore="c:\Program Files\Amazon Corretto\jdk1.8.0_382\bin\FiddlerKeystore" -Djavax.net.ssl.trustStorePassword=test123
#------------End of file---------------------
Copyrights reserved. ZappySys LLC.
