null
or missing record keys in JavaScript?OpenAdaptor has a long history, and we are now putting all of our development effort into the OpenAdaptor 3.4+ codebase.
All other versions are now either in legacy maintenance mode (<IP_ADDRESS>) or no longer supported. Legacy maintenance mode means that our community is still actively helping people with queries, and that we are only patching significant bugs.
The supported migration path for legacy adaptors is to upgrade to <IP_ADDRESS> and then move to 3.x.
| Version | Project URL | Status | Comment | ||
|---|---|---|---|---|---|
| OpenAdaptor 3.4 | https://openadaptor3.openadaptor.org |
|
Subversion project we are actively developing. We are already using it in production. | ||
| OpenAdaptor 1.x | https://openadaptor.openadaptor.org |
|
CVS project in legacy maintenance mode. We are still using it in production (with a migration programme underway). |
If you are migrating legacy OpenAdaptor 1.x adaptors you may encounter custom components which may now be supported by standard components in OpenAdaptor 3.x.
We recommend following these steps:
Fundamentally an adaptor is a process that takes data from one place and puts it in another. These places can be datastores (e.g. SQL database; filesystem) or enterprise buses (e.g. JMS; MQ). The data may also be transformed and/or filtered by the adaptor.
Writing a new adaptor is best done as an incremental process. Start at one end (or sometimes both ends) and test as you go along.
The OpenAdaptor lib directory contains the following:
| log4j.properties | A sample log4j configuration file |
|---|---|
| openadaptor.jar | The actual openadaptor code |
| openadaptor-spring.jar | Helper classes for launching OpenAdaptor processes based on spring configurations |
| openadaptor-depends.jar | Most of the 3rdparty jars, re-jarred as a single jar for convenience |
| 3rdparty | The 3rd party jars |
Your classpath needs to contain openadaptor.jar
. If you are using our Spring helper
classes to run an adaptor then you will need openadaptor-spring.jar
. You will also
need a subset of the 3rdparty jars upon which OpenAdaptor relies; the easist approach is to include
openadaptor-depends.jar
. So your classpath would be something like this:
set CLASSPATH=lib;lib/openadaptor.jar;lib/openadaptor-spring.jar;lib/openadaptor-depends.jar
CLASSPATH=lib:lib/openadaptor.jar:lib/openadaptor-spring.jar:lib/openadaptor-depends.jar export CLASSPATH
The example/bin directory contains a Windows batch script and a bash profile that sets your classpath like this. However you may decide to include just the 3rdparty jars that you need which is why we include the 3rdparty directory.
Post release 3.2.1, OpenAdaptor comes with an additonal jar called bootstrap.jar
. This contains
alternative helper classes that will bootstrap the classpath based on the location of the jar file.
All you need in your classpath is bootstrap.jar
. You can dump out the programs that can be run
from this jar file like this:
java -jar bootstrap.jar
This should print something like the following:
This jar contains the following programs : org.openadaptor.bootstrap.FileSystemExceptionManager org.openadaptor.bootstrap.JDBCExceptionManager org.openadaptor.bootstrap.SpringAdaptor org.openadaptor.bootstrap.SpringAdaptor When you run these they will bootstrap the classpath based on the location of this jar use -Dopenadaptor.lib=... to provide an additional location to search for classpath resources
These bootstrapping programs will automatically construct the classpath based on the location
of bootstrap.jar
. The classpath will contain all the jars, zip and subdirectories.
You can specify an additional directory to search using the openadaptor.lib
system property.
Any patch.jar
will always be placed at the front of the classpath, it will load the
contents of 3rdparty rather than openadaptor-depends.jar
, and openadaptor-stub.jar
will always be at the end of the classpath.
Example 1:
java -classpath lib\bootstrap.jar org.openadaptor.bootstrap.SpringAdaptor -config foo.xml
Example 2:
SET CLASSPATH=lib\bootstrap.jar java org.openadaptor.bootstrap.SpringAdaptor -config foo.xml
Example 3:
SET CLASSPATH=lib\bootstrap.jar java -Dopenadaptor.lib=c:\tibco\tibrv\lib org.openadaptor.bootstrap.SpringAdaptor -config tibco.xml
Note: The command line syntax is under review following release of version 3.4, and the syntax may change in the next release.
The XML files are Spring 2.0 configuration files. The OpenAdaptor 3.x components have been written as Spring beans.
Please make use of the Spring <description> element as illustrated here. This allows you to insert descriptive comments in your code for beans and for each of their properties. Our documentation and picture generation exploits the presence of the descriptions.
Here is a boilerplate example, which includes a non-trivial routing table:
<?xml version="1.0" encoding="UTF-8"?> <!-- Documentation generation currently relies on having a HeadURL svn:property (due to limitation of XSLT 1.1 in that it does not know name/path of file it is processing) which must be between the "xml version" first line and the "beans" element (which in turn must refer to the Spring 2.0 XML Schema Description). $Id: $ $HeadURL: $ --> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"> <description><![CDATA[ Minimal example. Just shows XML fragments that should always be there (in some form or other). The XML files are Spring 2.0 configuration files. The OpenAdaptor 3.x components have been written as Spring "beans". Read the rest of the FAQ to learn more about working with these XML files. ]]></description> <bean id="Adaptor" class="org.openadaptor.core.adaptor.Adaptor"> <description> This is the bean that actually gets run. It is possible to have several in one XML config file, and then to tell adaptor which one to run on command line. We would recommend having exactly one of these per XML config file. </description> <property name="messageProcessor" ref="Router" /> </bean> <bean id="Router" class="org.openadaptor.core.router.Router"> <description> The adaptor delegates it's message processing to a router. The router uses this mapping to define the "processing pipeline". Main control flow is: Reader -> Filter -> Modifier |-> DatabaseWriter |-> FileLogger However, anything that is discarded by the Filter will then go: DiscardedFormatter -> DiscardedWriter And ProcessingExceptions thrown by Filter (and subsequent nodes) are sent to FailedParsingLogger, except those thrown by DatabaseWriter which are sent to FailedWritesLogger. </description> <property name="processMap"> <map> <entry key-ref="Reader" value-ref="Filter" /> <entry key-ref="Filter" value-ref="Modifier" /> <entry key-ref="Modifier"> <list> <ref bean="DatabaseWriter" /> <ref bean="FileLogger" /> </list> </entry> <!-- Other subsidiary pipelines: --> <entry key-ref="DiscardedFormatter" value-ref="DiscardedWriter" /> </map> </property> <property name="discardMap"> <map> <entry key-ref="Filter" value-ref="DiscardedFormatter" /> </map> </property> <property name="exceptionMap"> <description>Catch input parsing problems and bad writes here...</description> <map> <entry key-ref="Filter"> <map> <entry key="org.openadaptor.core.exception.ProcessingException" value-ref="FailedParsingLogger" /> </map> </entry> <entry key-ref="DatabaseWriter"> <map> <entry key="org.openadaptor.core.exception.ProcessingException" value-ref="FailedWritesLogger" /> </map> </entry> </map> </property> </bean> <!-- Definitions of all of the beans referred to in the routing table come here. --> </beans>
You can see an XML config fragment below for reading legacy JMS DOXML
which shows a routing table and almost all of the beans referred to by it.
Note the way that we use the id
attribute to name and refer to beans:
this is the way that Spring works (and XML states that id
is unique within a file).
Spring also allows name
attributes to be used to provide aliases, but
we would advocate avoiding using this unless absolutely necessary (as you will get
much poorer support from XML editing tools and XML Validation will not be able to
spot some obvious bugs on your behalf).
Some bean ids will not be referenced in the Router
, e.g.
Router
.Examples of the last category are Spring's PropertyPlaceholderConfigurer bean and OpenAdaptor's central registration config.
In OpenAdaptor version 1.x all data records were passed around as a custom Java type called
DataObject
. Incoming data had to be a DataObject
before it could be
processed.
In OpenAdaptor version 3.x we have thrown away that restriction, and the data records passed
around simply needs to be of standard Java type Object
.
So you can read data in from one place and write it out to another, without ever needing to map it to a different representation. This avoids conversions that are lossy (non-idempotent) or pointless (excess processing overhead).
A good example of this is reading an XML record in from one place and writing an XML record out to another place, perhaps having modified it using an XSLT transform or one of our other components that can work with XML payloads, and treating it as an XML document throughout.
An objective of OpenAdaptor is to promote reuse of standard components, and so we had to settle on some standard payload formats to target our components at. (Note this is just a necessary restriction as to what individual components can handle: the framework itself is payload neutral).
We support a variety of payload formats, but with a special emphasis on Maps and XML.
No. Whilst XSLT 1.1 has substring functions it lacks regular expression support.
Instead you can use one of our standard scripting components to modify/filter using a JavaScript regular expression.
There is no need to use an org.openadaptor.auxil.convertor.xml.XmlToOrderedMapConvertor
component because our standard modify/filter components understand XML payloads.
The OpenAdaptor XSLT component will meet most of your other XML transformation needs, especially when reading XML in and writing XML out.
All versions of OpenAdaptor provide a suite of reusable components. They can connect to datastores, and process/filter data on its way through your adaptor.
Similarly all versions of OpenAdaptor allow you to provide your own custom components.
In OpenAdaptor 3.x we have:
ScriptProcessor
and ScriptFilterProcessor
allow custom attribute manipulation to be expressed inside the adaptor XML config file.
XsltProcessor
.
As a rule of thumb, it is best to avoid/minimise using custom components as it makes it easier to support/maintain your adaptor in the long term.
The processors property is simply a convenience method which ultimately converts the
supplied list of components into a Map
. It is useful to simplify the configuration
where each component only needs to be connected to a single downstream component (i.e. it is
simply syntactic sugar for adaptors with a single path).
However, for production adaptors, it is likely that components may well fan out their
output to more than one destination, in which case it would be necessary to convert the
List
explicitly into a Map
anyway; this being the case, it may
well be useful to start with a Map
.
Configure the discardMap
property to assign a destination for records that would be
otherwise discarded from a component. If the discardMap
contains a key reference to
a component which would otherwise be discarding a record, it will instead forward the record to
the referenced component(s) in the discardMap
. This mechanism offers considerable
advantages over legacy OpenAdaptor (1.x ) where it was instead required to provide multiple
inverted-logic filters to process 'active' and discarded data successfully. In practice,
this tended to lead to configuration errors.
Refer to the Tutorial for some examples on exception handling.
OpenAdaptor uses Apache Commons logging abstraction throughout, and is typically configured to use the Log4j implementation at runtime.
Consult the Tutorial for information on exception processing, and hospital-like functionality.
null
or missing record keys in JavaScript?
It is the devloper's responsibility to explicitly handle null
or missing
record keys in JavaScript.
Typically you do this by using guard clauses that test for null
in JavaScript
before operating on the value.
JavaScript will see a null
value when you call oa_data.get(keyname)
regardless of whether (a) the key exists and is bound to a null value in the record,
or (b) the key does not exist in the record.
You can discriminate between cases (a) and (b) using the oa_data.containsKey(keyname)
method.
<bean id="FilterDateOfBirth" class="org.openadaptor.auxil.processor.script.ScriptProcessor"> <description> Set 'dobstatus' attribute according to presence and value of 'dateofbirth' attribute. </description> <property name="script"> dob = oa_data.get('dateofbirth'); if (dob == null) { if (oa_data.containsKey('dateofbirth')) { found = "present and explicitly null"; } else { found = "missing and implicitly null"; } } else { found = "present and not null"; } oa_data.set('dobstatus',found); </script> </bean>
Yes. You can use JavaScript regular expressions in the script
property
of:
org.openadaptor.auxil.processor.script.ScriptProcessor
org.openadaptor.auxil.processor.script.ScriptFilterProcessor
<bean id="SetBirthYear" class="org.openadaptor.auxil.processor.script.ScriptProcessor"> <description> Updates the 'birthyear' attribute with the result of extracting year from 'dateofbirth' attribute. Uses JavaScript regular expression to parse and takes first capture group as result. </description> <property name="script"> oa_data.put('birthyear',/(\d{4})-(\d{1,2})-(\d{1,2})/.exec(oa_data.get('dateofbirth'))[1]/; </script> </bean>
Yes. In your script you use oa_data.get()
, or oa_data.put()
where the value is a JavaScript native array.
<bean id="ParseAddress" class="org.openadaptor.auxil.processor.script.ScriptProcessor"> <description> Split address string into an array of strings on ',' characters. Input address='National Maritime Museum, Greenwich, LONDON SE10 9NF'; Output address={'National Maritime Museum',' Greenwich',' LONDON SE10 9NF'}; </description> <property name="script"> address = oa_data.get('address'); if (address != null) { oa_data.put('address', address.split(',')); } </property> <property name="discardMatches" value="false" /> </bean>
Yes, their scope is the script in which they are defined.
Yes, by default the JavaScript is compiled at adaptor load time.
No, because this goes against the clean architecture of OpenAdaptor. Instead you should use the filter components or if-then-else components.
Yes. You write JavaScript in the normal way, and operate on the oa_data
passed in
which also doubles as the result object.
Your condition expressions will typically involve data extracted from each incoming record. It can also involve variables from a separate properties file or command line which are specific to this executing instance of the adaptor: these are evaluated when the adaptor is loaded and thereafter behave as literal string values.
As a design guideline we very strongly advocate avoiding writing long pieces of JavaScript
(a screenful is long). Instead break down your JavaScript into small blocks of defined functionality
and place each in a separate ScriptProcessor
. This makes it much easier to unit-test
each piece of logic (we plan to provide some practical help with this at the component level in the
future) according to expected inputs and outputs. It also allows you to take advantage of the discard
and exception routing support of OpenAdaptor. Note that hosting it in multiple components may mean
using ScriptFilterProcessor
components to express branching logic as well as
ScriptProcessor
components.
<bean id="DiscountedTrainFare" class="org.openadaptor.auxil.processor.script.ScriptProcessor"> <description>Calcuate train fare discount applicable based on age (and for illustration) gender.</description> <property name="script"> // examine incoming object: age = oa_data.get('age'); gender = oa_data.get('gender'); // assume full fare: percentage = 100; if (age != null) { // no discount available unless we know their age ;-) if (age < 2) { percentage = 0; // infants travel free } else if (age <= 16) { percentage = 50; // children half-price } else { if ((gender != null) && (gender == 'female') && (age >= ${myprop.pension.age.female})) { // women over 60 percentage = 66; } else if (age >= ${myprop.pension.age.male}) { // everyone over 65 (assumes male pension age same or older than female for brevity) percentage = 66; } } } // modify outgoing object: oa_data.set('rate',percentage); </property> <property name="discardMatches" value="false" /> </bean>
It depends; use the one that is most suitable. It is reasonable to combine them. You should be guided by what makes your intent clearest (both in the XML config and in the generated node map images).
We assume you have a clean install of Eclipse 3.2.1+ (you will need to vary these instructions if you have already installed extra plugins).
Install the following plugins, plus all the plugins they depend upon:
| Web Standard Tools (WST) | Adds XSD aware XML editing. |
| Subclipse | For accessing a Subversion source code repository. |
| Spring IDE | Adds Spring aware XML editing (requires "Web Standard Tools (WST)"). |
Click "Help" > "Software Updates" > "Find and Install" > "Search for new features to install..."
Use the "New Remote Site..." button to add:
| Spring IDE update site | http://springide.org/updatesite/ |
| Subclipse update site | http://subclipse.tigris.org/update_1.0.x |
Check the boxes against:
Click "Finish" which causes it to query the selected update sites. If you get unexpected site connectivity errors check your Eclipse proxy settings (Windows > Preferences... > Internet > Proxy Settings).
Select:
Click "Select Required..." (adds in all dependent plugins).
Click "Next..." and keep going until you have finished installing and then allow Eclipse to Restart itself to complete installation.
In a later section we explain how to use Subclipse to checkout the source code.
In the next section we explain how Eclipse can help you write your adaptors now that you have installed the "Web Standard Tools (WST)" and "Spring IDE" plugins.
This assumes that you have followed the instructions in the previous section which explain which Eclipse plugins to install (otherwise these features will be unavailable).
beans
element as in the XML config file examples).
This has been parameterised so that it can be re-used in your own build environment to generate documentation about your own adaptors. We will publish instructions on how to do this in due course.
You can define whichever properties you like and you have options in configuring the PropertyPlaceholderConfigurer.
<bean id="SystemPropertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="ignoreResourceNotFound" value="true" /> <property name="systemPropertiesMode"> <bean id="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer.SYSTEM_PROPERTIES_MODE_OVERRIDE" class="org.springframework.beans.factory.config.FieldRetrievingFactoryBean" /> </property> <property name="locations"> <description> Properties set by a file in list below are supposedly overridden by settings in a file later in list below. </description> <list> <value>classpath:general.properties</value> <value>classpath:specific.properties</value> </list> </property> </bean>
The above PropertyPlaceholderConfigurer
configuration says to pick up these values
from the two specified Java properties files, and to override these settings with
values found in the Java System properties (typically set with -Dname=value
command line options).
You then access the properties as ${myPropertyName}
within your XML config file.
See the runtime parameters example below.
This parameterisation technique is also very valuable as it allows you to deploy the same XML config file with different Java Properties files in Development/Test/Production. That also then allows you to then publish your XML config file without fear of exposing passwords as these will be in the environment specific Java Properties files.
Note: We are planning to make this easier in the release after 3.2.1 (e.g. automatic detection of
-D
properties causing a default PropertyPlaceholderConfigurer
to be created
in memory if there is no user specified one).
In general it is best to avoid this and have a single XML config file per adaptor using
the PropertyPlaceholderConfigurer
to pass in values as defined in
variables setting above.
These are Spring XML config files, so you can have multiple files that are combined together in
a single adaptor. Spring defines that each of these XML files must be a proper Spring XML file
(including the <beans>
top-level element).
There are two different ways that you can combine them:
SpringAdaptor
command line arguments (recommended).
<import resource="someFile.xml"/>
element (must be the first element(s)
after <beans>
tag of importing XML file) and then just supply the importing
XML filename on SpringAdaptor
command line.
Your different files will each contain multiple <bean>
elements.
Each <bean> element should have an id
attribute
(e.g. <bean id="myBean" class="org.openadaptor.Something"/>
).
Then rather than having an in-line bean definition you can have a ref
(or bean-ref
) attribute whose value is the id
(e.g. <property name="someProperty" ref="myBean"/>
).
You should see how this can be used to share a bean definition between files, and thus across adaptors.
And so you can achieve what you want provided that you can express it at the bean level.
In general it is best to avoid this and have a single XML config file per adaptor using the PropertyPlaceholderConfigurer to pass in values as defined in variables setting section above.
You define multiple Adaptor beans, each with a different bean id in your XML config file.
Then you pass in the bean id you want to use as a -adaptor
argument
to the SpringAdaptor
command line.
You can have a JDBCReadConnector
, which executes a dynamic query,
i.e. a query which reads input parameters at runtime via command line or property file:
<bean id="Reader" class="org.openadaptor.auxil.jdbc.reader.JDBCReadConnector"> <property name="jdbcConnection" ref="JdbcConnection"/> <property name="sql" value="Select * from dbo.mydata where valid_from > ${start} And valid_from < ${end}"/> </bean>
You then configure a PropertyPlaceholderConfigurer
and pass in data as defined in
the variables setting section above.
So then you could start your adaptor as follows:
java -Dstart=whatever -Dend=whatever org.openadaptor...
Yes :-)
We have provided a set of components to support this (they offer a useful level of backwards compatability). We have tested these components using OpenAdaptor <IP_ADDRESS>, and would strongly recommend that you upgrade your publisher to this level first (especially if you are using types other than Strings in your DOXML).
The basic dataflow is:
<bean id="Router" class="org.openadaptor.core.router.Router"> <description> The adaptor delegates it's message processing to a router. The router uses this mapping to define the "processing pipeline". This routing is deliberately incomplete (just describes reading in the data and parsing it). Needs to also describe writing it out too. When testing the rest of the adaptor (during development or regression) it is very useful to be able to re-inject JMS DOXML messages that were previously captured. That is what the "DEBUG_" beans are used for. You can fan-out the records from the wire so that they are both captured and processed. And later you can decide that rather than reading records from the JMS service, you want to replay previously captured JMS DOXML/XML messages from file. The captured messages also serve as a log of the data sent over the wire, which is very useful when you are trying to write parsing/processing components such as XSLT transforms. </description> <property name="processMap"> <map> <!-- Read XML from JMS:--> <entry key-ref="MyReader"> <list> <!-- <ref bean="DEBUG_LogAllDOXMLMessage" /> --> <!-- in Production MUST comment out this DOXML DEBUG logging line!--> <ref bean="DOXmlConvertor" /> </list> </entry> <!-- Just for DEBUG: read DOXML from DEBUG test file instead of reading from JMS: --> <!-- <entry key-ref="DEBUG_DOXMLFileReader" value-ref="DOXmlConvertor" /> --> <entry key-ref="DOXmlConvertor" value-ref="DataObjectConvertor" /> <entry key-ref="DataObjectConvertor" value-ref="XmlExtractFields" /> <entry key-ref="XmlExtractFields" value-ref="XmlToOrderedMapConvertor" /> <entry key-ref="XmlToOrderedMapConvertor" value-ref="FilterOnMessageTypeContact" /> <!-- More config required: Needs to do any further processing and then write the results somewhere... --> </map> </property> <property name="exceptionMap"> <description>Catch input parsing problems here...</description> <map> <entry key-ref="DOXmlConvertor"> <map> <entry key="org.openadaptor.core.exception.ProcessingException" value-ref="FailedReadsOutpoint" /> </map> </entry> </map> </property> </bean> <bean id="MyJndiConnection" class="org.openadaptor.auxil.connector.jndi.JNDIConnection"> <property name="initialContextFactory" value="com.sun.jndi.ldap.LdapCtxFactory" /> <property name="providerUrl" value="${adaptorprop.jms.jndiconnection.providerurl}" /> <property name="securityPrincipal" value="${adaptorprop.jms.jmsservice.username}" /> <property name="securityCredentials" value="${adaptorprop.jms.jmsservice.password}" /> </bean> <bean id="MyJmsConnection" class="org.openadaptor.auxil.connector.jms.JMSConnection"> <description>JMS connection for queue we wish to subscribe to.</description> <property name="jndiConnection" ref="MyJndiConnection" /> <property name="connectionFactoryName" value="${adaptorprop.jms.jmsconnection.connectionfactoryname}" /> <property name="username" value="${adaptorprop.jms.jmsconnection.username}" /> <property name="password" value="${adaptorprop.jms.jmsconnection.password}" /> </bean> <bean id="MyReader" class="org.openadaptor.auxil.connector.jms.JMSReadConnector"> <description>Reader that polls a JMS queue.</description> <property name="destinationName" value="cn=someTopic" /> <property name="jmsConnection" ref="MyJmsConnection" /> <property name="transacted" value="true" /> <property name="durable" value="${adaptorprop.jms.jmsreadconnector.durable}" /> <property name="durableSubscriptionName" value="${adaptorprop.jms.jmsreadconnector.durablesubscriptionname}" /> </bean> <bean id="DEBUG_LogAllDOXMLMessage" class="org.openadaptor.auxil.connector.iostream.writer.FileWriteConnector"> <description>Just for writing out data to use for DEBUG (raw DOXML).</description> <property name="filename" value="${somePath}/logs/${adaptorprop.timestamp}-0-DEBUG_LogAllDOXMLMessage.txt" /> <property name="append" value="false" /> </bean> <bean id="DEBUG_DOXMLFileReader" class="org.openadaptor.auxil.connector.iostream.reader.FileReadConnector"> <description> Just for DEBUG: read messages from file (in form output by DEBUG_LogAllDOXMLMessage) rather than using preceding JMS reader... </description> <property name="filename" value="${somepath}/test/testDOXML.xml" /> <property name="dataReader"> <bean class="org.openadaptor.legacy.connector.iostream.reader.string.DOXMLReader" /> </property> </bean> <bean id="DOXmlConvertor" class="org.openadaptor.legacy.converter.dataobjects.DOXmlToDataObjectConvertor"> <description>Convert each DOXML object published over JMS into a legacy OA<IP_ADDRESS> DataObject.</description> </bean> <bean id="DataObjectConvertor" class="org.openadaptor.legacy.converter.dataobjects.DataObjectToXmlConvertor"> <description>Convert legacy OA<IP_ADDRESS> DataObject into a plain XML document.</description> </bean> <bean id="XmlExtractFields" class="org.openadaptor.auxil.processor.xml.XsltProcessor"> <description> Extract just the fields we are interested in from the incoming XML record (which has a nested structure). Output them in an XML record which has a flat structure beneath a single root document element. </description> <property name="xsltFile" value="${somepath}/config/extractFields.xsl" /> </bean> <bean id="XmlToOrderedMapConvertor" class="org.openadaptor.auxil.convertor.xml.XmlToOrderedMapConvertor"> <description>The XML record is converted into an ordered map containing just the fields of interest.</description> <property name="expression" value="/extracted_document" /> <property name="omitTopLevelElementTag" value="true" /> </bean> <bean id="FilterOnMessageTypeContact" class="org.openadaptor.auxil.processor.javascript.JavascriptFilterProcessor"> <description>Only pass through messages of type Contact.</description> <property name="script" value="(record.get('messageType') == 'Contact')" /> <property name="discardMatches" value="false" /> </bean>
Below is the XSLT transform used by the above example adaptor XML fragment
(to map an incoming nested DOXML record to a one-level deep XML document record containing
the extracted content which is ripe for interpreting as a Map
).
<?xml version="1.0"?> <!-- $Id: $ $HeadURL: $ Extracts the fields we are interested in into a flattened XML document. Uses XPath expressions to extract the elements of interest from the incoming XML record. --> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <extracted_document> <messageType><!-- e.g. Contact --> <xsl:value-of select="//msgHeader/messageType" /> </messageType> <messageVersion><!-- e.g. 1.2.3 --> <xsl:value-of select="//msgHeader/messageVersion" /> </messageVersion> <!-- Currently this works because XML document order in JMS message is order of changes, --> <!-- so if that stopped being true we would need to xsl:sort the node set on changedAt too. --> <changedata> <xsl:value-of select="//changeDetails[position()=last()]/changedBy" /> </changedata> <sn> <xsl:value-of select="//msgContact/familyName" /> </sn> <givenname> <xsl:value-of select="//msgContact/givenName" /> </givenname> <cn> <xsl:value-of select="//msgContact/givenName" /> <xsl:text> </xsl:text> <!-- insert single space --> <xsl:value-of select="//msgContact/familyName" /> </cn> <salutation><!-- e.g. Mr. --> <xsl:value-of select="//msgContact/salutation" /> </salutation> <companyname> <xsl:value-of select="//msgContact/employer/name" /> </companyname> <companyid> <xsl:value-of select="//msgContact/employer/identifier/master" /> </companyid> <title><!-- e.g. Partner (want to store as "title" not in "jobposition") --> <!-- A JobPosition is required to have a name element; but hasJobPosition is not itself required. --> <!-- So this can result in an empty title element. --> <xsl:value-of select="//msgContact/hasJobPosition/name" /> </title> </extracted_document> </xsl:template> </xsl:stylesheet>
The concept is that when your adaptors start up you would like them to POST
some data to a central server (specific to your organisation).
We have a simple in-house implementation of this server-side (with one URL for test and another for production) which takes the data posted and writes it to a SQL database.
The client-side is part of the OpenAdaptor distribution, and is activated by setting one or two extra properties on your Adaptor bean:
<bean id="Adaptor" class="org.openadaptor.core.adaptor.Adaptor"> <description>This is the bean that actually gets run.</description> <property name="messageProcessor" ref="Router" /> <property name="registrationUrl" value="${adaptorprop.registration.url}" /> <property name="additionalRegistrationProps"> <props> <prop key="AdaptorVersion">${adaptorprop.version}"</prop> <prop key="LdapSearchUrl">${adaptorprop.directory.url.search}</prop> <prop key="LdapUpdateUrl">${adaptorprop.directory.url.update}</prop> </props> </property> </bean>
There are a number of utilities for running Java programs as NT services. Here are the instructions for running one of the tutorial configurations using the Java Service Wrapper.
wrapper.exe
, wrapper.dll
, wrapper.jar
.wrapper.conf
, see example below.wrapper.exe -c wrapper.conf
.wrapper.exe -i wrapper.conf
.wrapper.java.command = c:\\installs\\jdk1.4.2\\bin\\java wrapper.java.mainclass = org.tanukisoftware.wrapper.WrapperSimpleApp wrapper.app.parameter.1 = org.openadaptor.spring.SpringAdaptor wrapper.app.parameter.2 = -config wrapper.app.parameter.3 = c:\\temp\\openadaptor-latest\\example\\tutorial\\step04.xml wrapper.java.classpath.1 = wrapper.jar wrapper.java.classpath.2 = ../lib/openadaptor.jar wrapper.java.classpath.3 = ../lib/openadaptor-spring.jar wrapper.java.classpath.4 = ../lib/openadaptor-depends.jar wrapper.java.classpath.5 = ../lib/3rdparty/jbossall-client.jar wrapper.ntservice.name = openadaptor wrapper.ntservice.displayname = openadaptor example service wrapper.ntservice.description = openadaptor example service wrapper.ntservice.starttype = AUTO_START wrapper.ntservice.interactive = false
wrapper.java.command = c:\\installs\\jdk1.4.2\\bin\\java wrapper.java.mainclass = org.tanukisoftware.wrapper.WrapperSimpleApp wrapper.app.parameter.1 = org.openadaptor.bootstrap.SpringAdaptor wrapper.app.parameter.2 = -config wrapper.app.parameter.3 = c:\\temp\\openadaptor-latest\\example\\tutorial\\step04.xml wrapper.java.classpath.1 = wrapper.jar wrapper.java.classpath.2 = ../lib/bootstrap.jar wrapper.ntservice.name = openadaptor wrapper.ntservice.displayname = openadaptor example service wrapper.ntservice.description = openadaptor example service wrapper.ntservice.starttype = AUTO_START wrapper.ntservice.interactive = false
There are a number of utilities for running Java programs as daemon processes. Here are the instructions for running one of the tutorial configurations using the Java Service Wrapper.
example/bin
directory
lib/libwrapper.so
, bin/wrapper
, lib/wrapper.jar
.wrapper.conf
, see example below.src/bin/sh.script.in
to wrapper.sh
.wrapper.sh
so that WRAPPER_CONF="./wrapper.conf"
../wrapper.sh console
../wrapper.sh start
../wrapper.sh stop
.wrapper.java.command = {your java home}/bin wrapper.java.mainclass = org.tanukisoftware.wrapper.WrapperSimpleApp wrapper.app.parameter.1 = org.openadaptor.spring.SpringAdaptor wrapper.app.parameter.2 = -config wrapper.app.parameter.3 = {your openadaptor root}/example/tutorial/step04.xml wrapper.java.classpath.1 = wrapper.jar wrapper.java.classpath.2 = ../lib/openadaptor.jar wrapper.java.classpath.3 = ../lib/openadaptor-spring.jar wrapper.java.classpath.4 = ../lib/openadaptor-depends.jar wrapper.java.classpath.5 = ../lib/3rdparty/jbossall-client.jar
wrapper.java.command = {your java home}/bin wrapper.java.mainclass = org.tanukisoftware.wrapper.WrapperSimpleApp wrapper.app.parameter.1 = org.openadaptor.bootstrap.SpringAdaptor wrapper.app.parameter.2 = -config wrapper.app.parameter.3 = {your openadaptor root}/example/tutorial/step04.xml wrapper.java.classpath.1 = wrapper.jar wrapper.java.classpath.2 = ../lib/bootstrap.jar
For Subversion repository access you will need to join the "OpenAdaptor3" project.
https://openadaptor3.openadaptor.org/servlets/ProjectDocumentList
Generated documentation (XML config, generated images, generated documentation, generated cross-referencing indexes): Soon to be added...
XML config is also accessible within the source code tree:
Soon to be added...
Yes: https://openadaptor3.openadaptor.org/source/browse/openadaptor3/trunk/
Yes - check out source code with a Subversion client using the following command:
svn checkout https://openadaptor3.openadaptor.org/svn/openadaptor3/trunk openadaptor3 --username guest
(We recommend using the latest stable version of the Subclipse plugin. Please also read Which versions of Subclipse and TortoiseSVN work together?, and What Eclipse IDE Plugins should I install?)
First follow the instructions in What Eclipse IDE Plugins should I install? to get the Subclipse plugin installed in Eclipse.
Then:
https://openadaptor3.openadaptor.org/svn/openadaptor3
(If necessary use username guest / no password)
You may need to configure Subversion to use your HTTPS proxy server,
e.g. if you are behind a corporate firewall.
Because Subclipse uses a separate Subversion library in the background, you will
need to edit the config for that (typically $HOME/.subversion/servers
as described in
the Subversion book):
[global] http-proxy-host = myproxy.my.network http-proxy-port = 8080 http-compression = yes
(Please also read Which versions of Subclipse and TortoiseSVN work together?)
TortoiseSVN is a Subversion Client for Microsoft Windows which integrates with the Windows Shell (Explorer).
The same proxying issues apply to TortoiseSVN as for corporate firewall traversal with Subclipse with the caveat that TortoiseSVN also provides a GUI for simple cases (under Settings > Network) as well as allowing the standard file to be used to configure all cases. We still recommend configuring in the standard file as then all of your local Subversion clients will be using the same configuration.
The underlying issue is that Subversion itself changed (around version 1.2). It maintained compatibility of clients with servers, but it changed the format of checked out worktrees which means that different SVN clients cannot always be used on the same worktree.
We are successfully using the following combination on the same worktree:
We tried some of the earlier versions of TortoiseSVN with version 1.0.5 of Subclipse, but were unable to use them on the same worktree.
When the IBM JDK 1.6 is used with openadaptor 3.x (which contains Spring in version 2.5.3) this issue occur. JIRA: https://jira.springsource.org/browse/SPR-4788
Note: This problem is fixed. You have to upgrade your Spring version to 2.5.5 or higher, when using the IBM JDK 1.6.
Spring download link: http://www.springsource.org/download
[org.springframework.web.context.ContextLoader] [ERROR] – Context initialization failed org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.beans.factory.config.PropertyPlaceholderConfigurer#0': Initialization of bean failed; nested exception is java.lang.NullPointerException at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:478) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409) at java.security.AccessController.doPrivileged(AccessController.java:224) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:220) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164) at org.springframework.context.support.AbstractApplicationContext.invokeBeanFactoryPostProcessors(AbstractApplicationContext.java:516) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:363) at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4350) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525) at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:829) at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:718) at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490) at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1215) at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:293) at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117) at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1337) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1601) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1610) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1590) at java.lang.Thread.run(Thread.java:735) Caused by: java.lang.NullPointerException at org.springframework.core.GenericTypeResolver.getTypeVariableMap(GenericTypeResolver.java:144) at org.springframework.core.GenericTypeResolver.resolveReturnType(GenericTypeResolver.java:93) at org.springframework.beans.GenericTypeAwarePropertyDescriptor.getPropertyType(GenericTypeAwarePropertyDescriptor.java:58) at java.beans.PropertyDescriptor.setWriteMethod(Unknown Source) at java.beans.PropertyDescriptor.(Unknown Source) at org.springframework.beans.GenericTypeAwarePropertyDescriptor. (GenericTypeAwarePropertyDescriptor.java:47) at org.springframework.beans.CachedIntrospectionResults. (CachedIntrospectionResults.java:250) at org.springframework.beans.CachedIntrospectionResults.forClass(CachedIntrospectionResults.java:144) at org.springframework.beans.BeanWrapperImpl.getCachedIntrospectionResults(BeanWrapperImpl.java:252) at org.springframework.beans.BeanWrapperImpl.convertForProperty(BeanWrapperImpl.java:380) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.convertForProperty(AbstractAutowireCapableBeanFactory.java:1287) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1248) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1008) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:470) ... 29 more


PHOTOS OF ME WITH THE TORAH
| torah1.jpg Rabbi Kastel, me and my Dad |
|
| torah2.jpg me |
|
| torah3.jpg my hand and my parsha! |
|
| torah4.jpg my Dad, me and Rabbi Schapiro |
|
| torah5.jpg me and Rabbi Schapiro |
|
| torah6.jpg me and my Dad |
Go back to the main photos page
Copyright 1998 Jeremy Epstein. This site is a member of the JazaWeb Network.


Code Coverage |
|||||||||
Lines |
Functions and Methods |
Classes and Traits |
|||||||
| Total | |
53.86% |
286 / 531 |
|
11.63% |
5 / 43 |
|
0.00% |
0 / 5 |
| AdminController.php | |
47.86% |
67 / 140 |
|
20.00% |
2 / 10 |
|
0.00% |
0 / 1 |
| CenterController.php | |
56.82% |
50 / 88 |
|
20.00% |
1 / 5 |
|
0.00% |
0 / 1 |
| CustomerController.php | |
63.21% |
67 / 106 |
|
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
| LoyaltyCardController.php | |
50.88% |
58 / 114 |
|
22.22% |
2 / 9 |
|
0.00% |
0 / 1 |
| UserController.php | |
53.01% |
44 / 83 |
|
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |


--- layout: post permalink: americanscreeningcorporation.html Tags: Array NameOfCoupon: American Screening Corporation Cash Back Summary: See our American Screening Corporation Coupon Code, American Screening Corporation Rebate, American Screening Corporation Promo Code, American Screening Corporation Discount, American Screening Corporation Coupon Cash Back Rebate here. MetaKeywords: American Screening Corporation Coupon Code, American Screening Corporation Rebate, American Screening Corporation Promo Code, American Screening Corporation Cash Back Rebate, American Screening Corporation Discount, American Screening Corporation Coupon Image: images/14296.jpeg LinkURL: http://www.shareasale.com/r.cfm?b=96717&u=412002&m=14296 affiliate_url_link: http://www.icouponblog.com/jane.php?id=484 thesis_readmore: See the American Screening Corporation Coupon thesis_thumb: http://www.icouponblog.com/tn.php?merchantURL=americanscreeningcorp.com/stores/1/All_Drug_Testing_Products_C39.cfm Title: Save 2.5% - American Screening Corporation Coupon Code for americanscreeningcorp.com/stores/1/All_Drug_Testing_Products_C39.cfm. American Screening Corporation Rebate & Discount thesis_description: Our American Screening Corporation Discount Coupon Code for americanscreeningcorp.com/stores/1/All_Drug_Testing_Products_C39.cfm. We have the best American Screening Corporation Rebate anywhere. thesis_keywords: American Screening Corporation, American Screening Corporation Coupon Code, American Screening Corporation Discount, American Screening Corporation Rebate product_name: American Screening Corporation hop_url: http://www.icouponblog.com/hop.php?id=484 ---
For most accurate results and for a variety of drug, alcohol or other tests, American Screening Corporation is the best online site to visit. These tests can be done onsite and rapidly. Trust American Screening Corporation for screening employees and workers. Drug and alcohol is a big problem in the work industry, millions are lost because of drug related error, or absenteeism. Be smart, use the smart drug testing. American Screening Corporation is the best place for all your drug testing needs. Use our cash back rebate coupon to get a great deal on American Screening Corporation services.


arrow_upward
arrow_back
clear
arrow_forward
arrow_downward
settings
Rotate data matrix
Reverse data matrix
Auto save


Welcome to Dad Jokes!

Well worry no more - with the DadJokes app, a cornucopia of dadtasticness is at your fingertips.
Why did the chicken really cross the road?
See what other jokesters have to say in the public Joke feed.
Overhear a real knee-slapper on the L train?
Just whip out your phone, pop open our app, and save it to your private collection for posterity.
Sitting in a bar and a string walks through the door and sits down next to you?
Dads Jokes has got you covered with a real zinger for every situation!


Electronics and Communication Undergraduate Student
Graphic Design
Introduction to Graphic Designing
NPTEL Certificate
Introduction to Internet of Things
DSC Community
30 Days of Google Cloud


Coding can open doors for you
Code is a huge part of our daily lives. It powers your phone, your bank account, your social networks... it's everywhere.
Today, all businesses are moving over to digital. It's essential to have a digital presence and for us to understand how that works.
Once you know how to code, you can get ideas off the ground and communicate seamlessly with developers (and budding child prodigy programmers).
People everywhere are realising this and learning to create on the Web. Even in primary schools.
No experience? No problem
Is learn to code on your list of goals this year?
It's tough sifting through the endless resources with no-one to direct all your questions.
Learning with others is fun and much easier than hacking away at online courses alone.
Start with this 6-week course
Learn to code with HTML, CSS and JavaScript, the universal languages of the Web. We'll be using the browser to bring your app ideas to life.
Remember: you don't have to struggle on your own.
Having experts and classmates by your side to troubleshoot with can save you hours of your life. It makes the difference between giving up at the first roadblocks, and gaining the confidence to continue learning after this course.
Join a group of determined, like-minded individuals to discuss, learn and make in a friendly environment.
The course is aimed at beginners, so you don't need any previous experience with coding to join. We will assume zero knowledge to start with, and introduce you to HTML, CSS and JavaScript whilst you design and prototype an app over 6 Saturdays.
Teachers
We know a few teachers who feel daunted and unsupported by the "new" computing curriculum.
With a solid knowledge-base and clear vision of how to build on it, you'll look forward to, and even enjoy, coding with your students.
Parents
You could be the cutting-edge parent who codes with their kids for fun!
Once you grasp the basics of coding, you'll be able to get creative with your children, building cool stuff together and supplementing the things they learn at school.
Up-skillers
Perhaps you're looking for ways to make yourself more valuable in the workplace, or 2016 is the year you're going to move to a new position, company or industry.
Boosting your tech skills will give you a competitive edge and open up new directions in your career.
Looking for work
Investing in yourself and your skills is a great way to make some positive life changes for 2016. You may want to explore your career options or take a leap outside of your comfort zone.
In our personal experience, learning to create with code is hugely empowering and can give you a real confidence boost.
What's the plan?
01
What is an app?
On the first day we discuss key concepts around apps (interface, logic, data) and start making with HTML & CSS.
02
Programming 101
What exactly is programming anyway? We introduce the principles of programming, then start playing with JavaScript.
03
Data!
Where to find it and how to use it. Your app's data has to be stored somewhere!
04
JS tricks
Use JavaScript libraries to speed up your development and learn how to troubleshoot your code, aka debugging.
05
Your app
By this point you have a rough app prototype, which you can then hack to apply your own ideas and content.
06
Celebrate
Tweak your app prototype. Celebrate your achievements, and take away free resources to continue your learning journey.
How much?
Affordable coding classes in London
Non-Greenwich resident |
£97.40 |
Greenwich resident without a Greenwich Card |
£91.07 |
Greenwich Card holder |
£83.48 |
Greenwich resident over 60 years of age |
£54.36 |
Greenwich resident on means-tested benefits |
£26.51 |
Greenwich school teacher |
£26.51 |
When and where?
6 Saturdays
16th April - 21st May 2016
11AM - 4PM
Ravensbourne
6 Penrose Way
Greenwich Peninsula
London
SE10 0EW
Who's teaching?
Matteo is a senior lecturer for Web Media at Ravensbourne with loads of experience building apps and educational games. He loves teaching and has near-infinite patience.
Aimee makes websites and clothes. She was in your shoes a year ago, reads puzzled faces and knows when to slow down the pace.
If you have a laptop which you're used to, please bring that with you. Make sure you have Google Chrome installed. Otherwise, MacBook laptops will be available for you to use whilst at Ravensbourne.
We also recommend bringing a notebook and pen for jotting down useful resources and capturing your ideas.
What are people saying?
More questions?
Email Matteo! m.menapace@rave.ac.uk


Version 2.7
This page is a tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section. It is recommended that you print this page, so that you can easily refer back to previous examples.
Basics of the SELECT Statement
Conditional Selection
Logical Operators
Compound Conditions
IN & BETWEEN
Using LIKE
Joins
Keys
Performing a Join
Eliminating Duplicates
Aliases & In/Subqueries
Aggregate Functions
Views
Creating New Tables
Altering Tables
Adding Data
Deleting Data
Updating Data
Indexes
GROUP BY & HAVING
More Subqueries
EXISTS & ALL
UNION & Outer Joins
Embedded SQL
Common SQL Questions
Nonstandard SQL
Syntax Summary
Important Links
In a relational database, data is stored in tables. An example table would relate Social Security Number, Name, and Address:
|
EmployeeAddressTable |
|||||
|---|---|---|---|---|---|
| SSN | FirstName | LastName | Address | City | State |
| 512687458 | Joe | Smith | 83 First Street | Howard | Ohio |
| 758420012 | Mary | Scott | 842 Vine Ave. | Losantiville | Ohio |
| 102254896 | Sam | Jones | 33 Elm St. | Paris | New York |
| 876512563 | Sarah | Ackerman | 440 U.S. 110 | Upton | Michigan |
Now, let's say you want to see the address of each employee. Use the SELECT statement, like so:
SELECT FirstName, LastName, Address, City, State
FROM EmployeeAddressTable;
The following is the results of your query of the database:
| First Name | Last Name | Address | City | State |
| Joe | Smith | 83 First Street | Howard | Ohio |
| Mary | Scott | 842 Vine Ave. | Losantiville | Ohio |
| Sam | Jones | 33 Elm St. | Paris | New York |
| Sarah | Ackerman | 440 U.S. 110 | Upton | Michigan |
To explain what you just did, you asked for the all of data in the EmployeeAddressTable, and specifically, you asked for the columns called FirstName, LastName, Address, City, and State. Note that column names and table names do not have spaces...they must be typed as one word; and that the statement ends with a semicolon (;). The general form for a SELECT statement, retrieving all of the rows in the table is:
SELECT ColumnName, ColumnName, ... FROM TableName;
To get all columns of a table without typing all column names, use:
SELECT * FROM TableName;
Each database management system (DBMS) and database software has different methods for logging in to the database and entering SQL commands; see the local computer "guru" to help you get onto the system, so that you can use SQL.
To further discuss the SELECT statement, let's look at a new example table (for hypothetical purposes only):
|
EmployeeStatisticsTable |
|||
| EmployeeIDNo | Salary | Benefits | Position |
| 010 | 75000 | 15000 | Manager |
| 105 | 65000 | 15000 | Manager |
| 152 | 60000 | 15000 | Manager |
| 215 | 60000 | 12500 | Manager |
| 244 | 50000 | 12000 | Staff |
| 300 | 45000 | 10000 | Staff |
| 335 | 40000 | 10000 | Staff |
| 400 | 32000 | 7500 | Entry-Level |
| 441 | 28000 | 7500 | Entry-Level |
There are six logical operators in SQL, and after introducing them, we'll see how they're used:
| = | Equal |
| <> or != (see manual) | Not Equal |
| < | Less Than |
| > | Greater Than |
| <= | Less Than or Equal To |
| >= | Greater Than or Equal To |
The WHERE clause is used to specify that only certain rows of the table are displayed, based on the criteria described in that WHERE clause. It is most easily understood by looking at a couple of examples.
If you wanted to see the EMPLOYEEIDNO's of those making at or over $50,000, use the following:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;
Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who made greater than $50,000, or equal to $50,000, listed together. This displays:
EMPLOYEEIDNO
------------
010
105
152
215
244
The WHERE description, SALARY >= 50000, is known as a condition. The same can be done for text columns:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager';
This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not equal to conditions, and make sure that any text that appears in the statement is surrounded by single quotes (').
The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions hold true). For example, to display all staff making over $40,000, use:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY > 40000 AND POSITION = 'Staff';
The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true. To see all those who make less than $40,000 or have less than $10,000 in benefits, listed together, use the following query:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY < 40000 OR BENEFITS < 10000;
AND & OR can be combined, for example:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFITS > 12000;
First, SQL finds the rows where the salary is greater than $60,000 or the benefits is greater than $12,000, then taking this new list of rows, SQL then sees if any of these rows satisfies the condition that the Position column if equal to 'Manager'. Subsequently, SQL only displays this second new list of rows, as the AND operator forces SQL to only display such rows satisfying the Position column condition. Also note that the OR operation is done first.
To generalize this process, SQL performs the OR operation(s) to determine the rows where the OR operation(s) hold true (remember: any one of the conditions is true), then these results are used to compare with the AND conditions, and only display those remaining rows where the conditions joined by the AND operator hold true.
To perform AND's before OR's, like if you wanted to see a list of managers or anyone making a large salary (>$50,000) and a large benefit package (>$10,000), whether he or she is or is not a manager, use parentheses:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' OR (SALARY > 50000 AND BENEFIT > 10000);
An easier method of using compound conditions uses IN or BETWEEN. For example, if you wanted to list all managers and staff:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff');
or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000;
To list everyone not in this range, try:
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY NOT BETWEEN 30000 AND 50000;
Similarly, NOT IN lists all rows excluded from the IN list.
Look at the EmployeeStatisticsTable, and say you wanted to see all people whose last names started with "L"; try:
SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%';
The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "L". To find those people with LastName's ending in "L", use '%L', or if you wanted the "L" in the middle of the word, try '%L%'. The '%' can be used for any characters, in that relative position to the given characters. NOT LIKE displays rows not fitting the given description. Other possiblities of using LIKE, or any of these discussed conditionals, are available, though it depends on what DBMS you are using; as usual, consult a manual or your system manager or administrator for the available features on your system, or just to make sure that what you are trying to do is available and allowed. This disclaimer holds for the features of SQL that will be discussed below. This section is just to give you an idea of the possibilities of queries that can be written in SQL.
In this section, we will only discuss inner joins, and equijoins, as in general, they are the most useful. For more information, try the SQL links at the bottom of the page.
Good database design suggests that each table lists data only about a single entity, and detailed information can be obtained in a relational database, by using additional tables, and by using a join.
First, take a look at these example tables:
| OwnerID | OwnerLastName | OwnerFirstName |
| 01 | Jones | Bill |
| 02 | Smith | Bob |
| 15 | Lawson | Patricia |
| 21 | Akins | Jane |
| 50 | Fowler | Sam |
| OwnerID | ItemDesired |
| 02 | Table |
| 02 | Desk |
| 21 | Chair |
| 15 | Mirror |
| SellerID | BuyerID | Item |
| 01 | 50 | Bed |
| 02 | 15 | Table |
| 15 | 02 | Chair |
| 21 | 50 | Mirror |
| 50 | 01 | Desk |
| 01 | 21 | Cabinet |
| 02 | 21 | Coffee Table |
| 15 | 50 | Chair |
| 01 | 15 | Jewelry Box |
| 02 | 21 | Pottery |
| 21 | 02 | Bookcase |
| 50 | 01 | Plant Stand |
First, let's discuss the concept of keys. A primary key is a column or set of columns that uniquely idenifies the rest of the data in any given row. For example, in the AntiqueOwners table, the OwnerID column uniquely identifies that row. This means two things: no two rows can have the same OwnerID, and, even if two owners have the same first and last names, the OwnerID column ensures that the two owners will not be confused with each other, because the unique OwnerID column will be used throughout the database to track the owners, rather than the names.
A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. In DBMS-speak, this correspondence is known as referential integrity. For example, in the Antiques table, both the BuyerID and SellerID are foreign keys to the primary key of the AntiqueOwners table (OwnerID; for purposes of argument, one has to be an Antique Owner before one can buy or sell any items), as, in both tables, the ID rows are used to identify the owners or buyers and sellers, and that the OwnerID is the primary key of the AntiqueOwners table. In other words, all of this "ID" data is used to refer to the owners, buyers, or sellers of antiques, themselves, without having to use the actual names.
The purpose of these keys is so that data can be related across tables, without having to repeat data in every table--this is the power of relational databases. For example, you can find the names of those who bought a chair without having to list the full name of the buyer in the Antiques table...you can get the name by relating those who bought a chair with the names in the AntiqueOwners table through the use of the OwnerID, which relates the data in the two tables. To find the names of those who bought a chair, use the following query:
SELECT OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE BUYERID = OWNERID AND ITEM = 'Chair';
Note the following about this query...notice that both tables involved in the relation are listed in the FROM clause of the statement. In the WHERE clause, first notice that the ITEM = 'Chair' part restricts the listing to those who have bought (and in this example, thereby owns) a chair. Secondly, notice how the ID columns are related from one table to the next by use of the BUYERID = OWNERID clause. Only where ID's match across tables and the item purchased is a chair (because of the AND), will the names from the AntiqueOwners table be listed. Because the joining condition used an equal sign, this join is called an equijoin. The result of this query is two names: Smith, Bob & Fowler, Sam.
Dot notation refers to prefixing the table names to column names, to avoid ambiguity, as such:
SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID AND ANTIQUES.ITEM = 'Chair';
As the column names are different in each table, however, this wasn't necessary.
Let's say that you want to list the ID and names of only those people who have sold an antique. Obviously, you want a list where each seller is only listed once--you don't want to know how many antiques a person sold, just the fact that this person sold one (for counts, see the Aggregate Function section below). This means that you will need to tell SQL to eliminate duplicate sales rows, and just list each person only once. To do this, use the DISTINCT keyword.
First, we will need an equijoin to the AntiqueOwners table to get the detail data of the person's LastName and FirstName. However, keep in mind that since the SellerID column in the Antiques table is a foreign key to the AntiqueOwners table, a seller will only be listed if there is a row in the AntiqueOwners table listing the ID and names. We also want to eliminate multiple occurences of the SellerID in our listing, so we use DISTINCT on the column where the repeats may occur.
To throw in one more twist, we will also want the list alphabetized by LastName, then by FirstName (on a LastName tie), then by OwnerID (on a LastName and FirstName tie). Thus, we will use the ORDER BY clause:
SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUES, ANTIQUEOWNERS
WHERE SELLERID = OWNERID
ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID
In this example, since everyone has sold an item, we will get a listing of all of the owners, in alphabetical order by last name. For future reference (and in case anyone asks), this type of join is considered to be in the category of inner joins.
In this section, we will talk about Aliases, In and the use of subqueries, and how these can be used in a 3-table example. First, look at this query which prints the last name of those owners who have placed an order and what the order is, only listing those orders which can be filled (that is, there is a buyer who owns that ordered item):
SELECT OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE ORD.OWNERID = OWN.OWNERID
AND ORD.ITEMDESIRED IN
(SELECT ITEM
FROM ANTIQUES);
This gives:
Last Name Item Ordered
--------- ------------
Smith Table
Smith Desk
Akins Chair
Lawson Mirror
There are several things to note about this query:
Whew! That's enough on the topic of complex SELECT queries for now. Now on to other SQL statements.
I will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.
Looking at the tables at the top of the document, let's look at three examples:
SELECT SUM(SALARY), AVG(SALARY)
FROM EMPLOYEESTATISTICSTABLE;
This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.
SELECT MIN(BENEFITS)
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager';
This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.
SELECT COUNT(*)
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Staff';
This query tells you how many employees have Staff status (3).
In SQL, you might (check your DBA) have access to create views for yourself. What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view:
CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS;
Now, write a query using this view as a table, where the table is just a listing of all Items Desired from the Orders table:
SELECT SELLERID
FROM ANTIQUES, ANTVIEW
WHERE ITEMDESIRED = ITEM;
This query shows all SellerID's from the Antiques table where the Item in that table happens to appear in the Antview view, which is just all of the Items Desired in the Orders table. The listing is generated by going through the Antique Items one-by-one until there's a match with the Antview view. Views can be used to restrict database access, as well as, in this case, simplify a complex query.
All tables within a database must be created at some point in time...let's see how we would create the Orders table:
CREATE TABLE ORDERS
(OWNERID INTEGER NOT NULL,
ITEMDESIRED CHAR(40) NOT NULL);
This statement gives the table name and tells the DBMS about each column in the table. Please note that this statement uses generic data types, and that the data types might be different, depending on what DBMS you are using. As usual, check local listings. Some common generic data types are:
One other note, the NOT NULL means that the column must have a value in each row. If NULL was used, that column may be left empty in a given row.
Let's add a column to the Antiques table to allow the entry of the price of a given Item:
ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL);
The data for this new column can be updated or inserted as shown later.
To insert rows into a table, do the following:
INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);
This inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Instead, let's change the order and leave Price blank:
INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)
VALUES (01, 21, 'Ottoman');
Let's delete this new row back out of the database:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman';
But if there is another row that contains 'Ottoman', that row will be deleted also. Let's delete all rows (one, in this case) that contain the specific data we added before:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman' AND BUYERID = 01 AND SELLERID = 21;
Let's update a Price into a row that doesn't have a price listed yet:
UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';
This sets all Chair's Prices to 500.00. As shown above, more WHERE conditionals, using AND, must be used to limit the updating to more specific rows. Also, additional columns may be set by separating equal statements with commas.
Indexes allow a DBMS to access data quicker (please note: this feature is nonstandard/not available on all systems). The system creates this internal data structure (the index) which causes selection of rows, when the selection is based on indexed columns, to occur faster. This index tells the DBMS where a certain row is in the table given an indexed-column value, much like a book index tells you what page a given word appears. Let's create an index for the OwnerID in the AntiqueOwners column:
CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);
Now on the names:
CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME);
To get rid of an index, drop it:
DROP INDEX OID_IDX;
By the way, you can also "drop" a table, as well (careful!--that means that your table is deleted). In the second example, the index is kept on the two columns, aggregated together--strange behavior might occur in this situation...check the manual before performing such an operation.
Some DBMS's do not enforce primary keys; in other words, the uniqueness of a column is not enforced automatically. What that means is, if, for example, I tried to insert another row into the AntiqueOwners table with an OwnerID of 02, some systems will allow me to do that, even though, we do not, as that column is supposed to be unique to that table (every row value is supposed to be different). One way to get around that is to create a unique index on the column that we want to be a primary key, to force the system to enforce prohibition of duplicates:
CREATE UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);
One special use of GROUP BY is to associate an aggregate function (especially COUNT; counting the number of rows in each group) with groups of rows. First, assume that the Antiques table has the Price column, and each row has a value for that column. We want to see the price of the most expensive item bought by each owner. We have to tell SQL to group each owner's purchases, and tell us the maximum purchase price:
SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID;
Now, say we only want to see the maximum purchase price if the purchase is over $1000, so we use the HAVING clause:
SELECT BUYERID, MAX(PRICE)
FROM ANTIQUES
GROUP BY BUYERID
HAVING PRICE > 1000;
Another common usage of subqueries involve the use of logical operators to allow a Where condition to include the Select output of a subquery. First, list the buyers who purchased an expensive item (the Price of the item is $100 greater than the average price of all items purchased):
SELECT OWNERID
FROM ANTIQUES
WHERE PRICE >
(SELECT AVG(PRICE) + 100
FROM ANTIQUES);
The subquery calculates the average Price, plus $100, and using that figure, an OwnerID is printed for every item costing over that figure. One could use DISTINCT OWNERID, to eliminate duplicates.
List the Last Names of those in the AntiqueOwners table, ONLY if they have bought an item:
SELECT OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE OWNERID =
(SELECT DISTINCT BUYERID
FROM ANTIQUES);
The subquery returns a list of buyers, and the Last Name is printed for an Antique Owner if and only if the Owner's ID appears in the subquery list (sometimes called a candidate list).
For an Update example, we know that the gentleman who bought the bookcase has the wrong First Name in the database...it should be John:
UPDATE ANTIQUEOWNERS
SET OWNERFIRSTNAME = 'John'
WHERE OWNERID =
(SELECT BUYERID
FROM ANTIQUES
WHERE ITEM = 'Bookcase');
First, the subquery finds the BuyerID for the person(s) who bought the Bookcase, then the outer query updates his First Name.
EXISTS uses a subquery as a condition, where the condition is True if the subquery returns any rows, and False if the subquery does not return any rows; this is a nonintuitive feature with few unique uses. However, if a prospective customer wanted to see the list of Owners only if the shop dealt in Chairs, try:
SELECT OWNERFIRSTNAME, OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE EXISTS
(SELECT *
FROM ANTIQUES
WHERE ITEM = 'Chair');
If there are any Chairs in the Antiques column, the subquery would return a row or rows, making the EXISTS clause true, causing SQL to list the Antique Owners. If there had been no Chairs, no rows would have been returned by the outside query.
ALL is another unusual feature, as ALL queries can usually be done with different, and possibly simpler methods; let's take a look at an example query:
SELECT BUYERID, ITEM
FROM ANTIQUES
WHERE PRICE >= ALL
(SELECT PRICE
FROM ANTIQUES);
This will return the largest priced item (or more than one item if there is a tie), and its buyer. The subquery returns a list of all Prices in the Antiques table, and the outer query goes through each row of the Antiques table, and if its Price is greater than or equal to every (or ALL) Prices in the list, it is listed, giving the highest priced Item. The reason ">=" must be used is that the highest priced item will be equal to the highest price on the list, because this Item is in the Price list.
There are occasions where you might want to see the results of multiple queries together, combining their output; use UNION. To merge the output of the following two queries, displaying the ID's of all Buyers, plus all those who have an Order placed:
SELECT BUYERID
FROM ANTIQUEOWNERS
UNION
SELECT OWNERID
FROM ORDERS;
Notice that SQL requires that the Select list (of columns) must match, column-by-column, in data type. In this case BuyerID and OwnerID are of the same data type (integer). Also notice that SQL does automatic duplicate elimination when using UNION (as if they were two "sets"); in single queries, you have to use DISTINCT.
The outer join is used when a join query is "united" with the rows not included in the join, and are especially useful if constant text "flags" are included. First, look at the query:
SELECT OWNERID, 'is in both Orders & Antiques'
FROM ORDERS, ANTIQUES
WHERE OWNERID = BUYERID
UNION
SELECT BUYERID, 'is in Antiques only'
FROM ANTIQUES
WHERE BUYERID NOT IN
(SELECT OWNERID
FROM ORDERS);
The first query does a join to list any owners who are in both tables, and putting a tag line after the ID repeating the quote. The UNION merges this list with the next list. The second list is generated by first listing those ID's not in the Orders table, thus generating a list of ID's excluded from the join query. Then, each row in the Antiques table is scanned, and if the BuyerID is not in this exclusion list, it is listed with its quoted tag. There might be an easier way to make this list, but it's difficult to generate the informational quoted strings of text.
This concept is useful in situations where a primary key is related to a foreign key, but the foreign key value for some primary keys is NULL. For example, in one table, the primary key is a salesperson, and in another table is customers, with their salesperson listed in the same row. However, if a salesperson has no customers, that person's name won't appear in the customer table. The outer join is used if the listing of all salespersons is to be printed, listed with their customers, whether the salesperson has a customer or not--that is, no customer is printed (a logical NULL value) if the salesperson has no customers, but is in the salespersons table. Otherwise, the salesperson will be listed with each customer.
ENOUGH QUERIES!!! you say?...now on to something completely different...
/* -To get right to it, here is an example program that uses Embedded
SQL. Embedded SQL allows programmers to connect
to a database and
include SQL code right in the program, so that
their programs can
use, manipulate, and process data from a database.
-This example C Program (using Embedded SQL) will print
a report.
-This program will have to be precompiled for the SQL
statements,
before regular compilation.
-The EXEC SQL parts are the same (standard), but the
surrounding C
code will need to be changed, including the host
variable
declarations, if you are using a different language.
-Embedded SQL changes from system to system, so, once
again, check
local documentation, especially variable declarations
and logging
in procedures, in which network, DBMS, and operating
system
considerations are crucial. */
/************************************************/
/* THIS PROGRAM IS NOT COMPILABLE OR EXECUTABLE */
/* IT IS FOR EXAMPLE PURPOSES ONLY
*/
/************************************************/
#include <stdio.h>
/* This section declares the host variables; these will be the
variables your program uses, but also the variable SQL
will put
values in or take values out. */
EXEC SQL BEGIN DECLARE SECTION;
int BuyerID;
char FirstName[100], LastName[100], Item[100];
EXEC SQL END DECLARE SECTION;
/* This includes the SQLCA variable, so that some error checking
can be done. */
EXEC SQL INCLUDE SQLCA;
main() {
/* This is a possible way to log into the database */
EXEC SQL CONNECT UserID/Password;
/* This code either says that you are connected or checks if an
error
code was generated, meaning log in was incorrect or not
possible. */ if(sqlca.sqlcode) {
printf(Printer, "Error connecting to database
server.\n");
exit();
}
printf("Connected to database server.\n");
/* This declares a "Cursor". This is used when a query
returns more
than one row, and an operation is to be performed on
each row
resulting from the query. With each row established by
this query,
I'm going to use it in the report. Later, "Fetch"
will be used to
pick off each row, one at a time, but for the query to
actually
be executed, the "Open" statement is used.
The "Declare" just
establishes the query. */
EXEC SQL DECLARE ItemCursor CURSOR FOR
SELECT ITEM, BUYERID
FROM ANTIQUES
ORDER BY ITEM;
EXEC SQL OPEN ItemCursor;
/* +-- You may wish to put a similar error checking block here --+ */
/* Fetch puts the values of the "next" row of the query
in the host
variables, respectively. However, a "priming fetch"
(programming
technique) must first be done. When the cursor is out
of data, a
sqlcode will be generated allowing us to leave the loop.
Notice
that, for simplicity's sake, the loop will leave on any
sqlcode,
even if it is an error code. Otherwise, specific code
checking must
be performed. */
EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID;
while(!sqlca.sqlcode) {
/* With each row, we will also do a couple of things. First, bump
the
price up by $5 (dealer's fee) and get the buyer's name
to put in
the report. To do this, I'll use an Update and a Select,
before
printing the line on the screen. The update assumes however,
that
a given buyer has only bought one of any given item,
or else the
price will be increased too many times. Otherwise, a
"RowID" logic
would have to be used (see documentation). Also notice
the colon before host variable names when used inside
of SQL statements. */
EXEC SQL UPDATE ANTIQUES
SET PRICE = PRICE + 5
WHERE ITEM = :Item AND BUYERID = :BuyerID;
EXEC SQL SELECT OWNERFIRSTNAME, OWNERLASTNAME
INTO :FirstName, :LastName
FROM ANTIQUEOWNERS
WHERE BUYERID = :BuyerID;
printf("%25s %25s %25s", FirstName, LastName, Item);
/* Ugly report--for example purposes only! Get the next row. */
EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID;
}
/* Close the cursor, commit the changes (see below), and exit the
program. */
EXEC SQL CLOSE DataCursor;
EXEC SQL COMMIT RELEASE;
exit();
}
| ABS(X) | Absolute value-converts negative numbers to positive, or leaves positive numbers alone |
| CEIL(X) | X is a decimal value that will be rounded up. |
| FLOOR(X) | X is a decimal value that will be rounded down. |
| GREATEST(X,Y) | Returns the largest of the two values. |
| LEAST(X,Y) | Returns the smallest of the two values. |
| MOD(X,Y) | Returns the remainder of X / Y. |
| POWER(X,Y) | Returns X to the power of Y. |
| ROUND(X,Y) | Rounds X to Y decimal places. If Y is omitted, X is rounded to the nearest integer. |
| SIGN(X) | Returns a minus if X < 0, else a plus. |
| SQRT(X) | Returns the square root of X. |
| LEFT(<string>,X) | Returns the leftmost X characters of the string. |
| RIGHT(<string>,X) | Returns the rightmost X characters of the string. |
| UPPER(<string>) | Converts the string to all uppercase letters. |
| LOWER(<string>) | Converts the string to all lowercase letters. |
| INITCAP(<string>) | Converts the string to initial caps. |
| LENGTH(<string>) | Returns the number of characters in the string. |
| <string>||<string> | Combines the two strings of text into one, concatenated string, where the first string is immediately followed by the second. |
| LPAD(<string>,X,'*') | Pads the string on the left with the * (or whatever character is inside the quotes), to make the string X characters long. |
| RPAD(<string>,X,'*') | Pads the string on the right with the * (or whatever character is inside the quotes), to make the string X characters long. |
| SUBSTR(<string>,X,Y) | Extracts Y letters from the string beginning at position X. |
| NVL(<column>,<value>) | The Null value function will substitute <value> for any NULLs for in the <column>. If the current value of <column> is not NULL, NVL has no effect. |
Here are the general forms of the statements discussed in this tutorial, plus some extra important ones (explanations given). REMEMBER that all of these statements may or may not be available on your system, so check documentation regarding availability:
ALTER TABLE <TABLE NAME> ADD|DROP|MODIFY (COLUMN SPECIFICATION[S]...see Create Table); --allows you to add or delete a column or columns from a table, or change the specification (data type, etc.) on an existing column; this statement is also used to change the physical specifications of a table (how a table is stored, etc.), but these definitions are DBMS-specific, so read the documentation. Also, these physical specifications are used with the Create Table statement, when a table is first created. In addition, only one option can be performed per Alter Table statement--either add, drop, OR modify in a single statement.
COMMIT; --makes changes made to some database systems permanent (since the last COMMIT; known as a transaction)
CREATE [UNIQUE] INDEX <INDEX NAME>
ON <TABLE NAME> (<COLUMN LIST>); --UNIQUE is optional;
within brackets.
CREATE TABLE <TABLE NAME>
(<COLUMN NAME> <DATA TYPE> [(<SIZE>)] <COLUMN CONSTRAINT>,
...other columns); (also valid with ALTER TABLE)
--where SIZE is only used on certain data types (see above), and constraints
include the following possibilities (automatically enforced by the DBMS;
failure causes an error to be generated):
CREATE VIEW <TABLE NAME> AS <QUERY>;
DELETE FROM <TABLE NAME> WHERE <CONDITION>;
INSERT INTO <TABLE NAME> [(<COLUMN LIST>)]
VALUES (<VALUE LIST>);
ROLLBACK; --Takes back any changes to the database that you have made, back to the last time you gave a Commit command...beware! Some software uses automatic committing on systems that use the transaction features, so the Rollback command may not work.
SELECT [DISTINCT|ALL] <LIST OF COLUMNS, FUNCTIONS, CONSTANTS,
ETC.>
FROM <LIST OF TABLES OR VIEWS>
[WHERE <CONDITION(S)>]
[GROUP BY <GROUPING COLUMN(S)>]
[HAVING <CONDITION>]
[ORDER BY <ORDERING COLUMN(S)> [ASC|DESC]]; --where ASC|DESC
allows the ordering to be done in ASCending or DESCending order
UPDATE <TABLE NAME>
SET <COLUMN NAME> = <VALUE>
[WHERE <CONDITION>]; --if the Where clause is left out, all
rows will be updated according to the Set statement
Computing & SQL/DB Links: Netscape
-- Oracle -- Sybase
-- Informix --Microsoft
SQL Reference
Page -- Ask the
SQL Pro -- SQL
Pro's Relational DB Useful Sites
Programmer's
Source -- DBMS
Sites -- inquiry.com -- DB
Ingredients -- SQL
for Beginners
Web Authoring -- Computing
Dictionary -- DBMS Lab/Links
-- SQL
FAQ -- SQL
Databases
RIT
Database Design Page -- Database
Jump Site -- Programming
Tutorials on the Web
Development Resources
-- Query List -- IMAGE
SQL
Miscellaneous: CNN -- USA
Today -- Pathfinder -- ZDNet
-- Metroscope -- CNet
Internet Resource List
-- Netcast Weather
-- TechWeb -- LookSmart
-- NY Times Fax
Search Engines: Yahoo -- Alta Vista -- Excite -- WebCrawler -- Lycos -- Infoseek -- search.com
These sites are not endorsed by the author.
Disclaimer
I hope you have learned something from this introductory look at a very important language that is becoming more prevalent in the world of client-server computing. I wrote this web page in order to contribute something of value to the web and the web community. In fact, I have been informed that this document is being used at several colleges for use in database classes and for use by researchers. Also, look for this page in Waite Publishing's newest book about Borland C++ Builder, which will be out this summer. In addition, I would like to thank all of the people from across five continents who have contacted me regarding this web page.
I also hope to continue to add more material to this tutorial, such as topics about database design and nonstandard SQL extensions, even though I wish to stay away from material about individual Database Management Systems. Good luck in your SQL and other computing adventures.
Jim Hoffman
Or you may wish to look at Jim Hoffman's Index Page for more information about myself.
Copyright 1996-1997, James Hoffman. This document can be used for free by any Internet user, but cannot be included in another document, published in any other form, or mass produced in any way.
This page is best viewed with Netscape Navigator; it doesn't look quite right with Microsoft Internet Explorer.
Last updated: 4-19-1997; added relationship question, which is a topic I've been meaning to add for awhile.
http://w3.one.net/~jhoffman/sqltut.htm


HOSTEL RULES AND REGULATIONS
1. BEHAVIOUR AND DISCIPLINE
1.1 Hostelites are expected to display acceptable form of behavior, maintain discipline and decorum in the hostel complex.
1.2 Smoking is not allowed in the hostel complex at anytime.
1.3 Possession, distribution and consumption of alcoholic beverages, prohibited drugs, chewable tobacco in the hostel complex is not allowed.
1.4 Parties, social or political gatherings in the hostel complex are not permitted without the prior and written consent of the accommodation officer.
1.5 Hostelites must return to the hostel by 8 pm everyday and are not allowed to leave the hostel before 6 am.
1.6 The hostelites will be allowed to stay out of the hostel on submission of proper application duly authorized by parent and only if the permission granted in writing by the accommodation officer.
1.7 A hostel campus should be a place where students can have the best possible conditions for studying and adequate rest. As such due consideration must be accorded to other hostelites at all times. Noise level must be kept low to allow other’s the opportunity to study or sleep in comfort. Television, Radio etc provided in the common room must be switched off or volume toned down after 10:00 pm. These rules are intended to ensure a conducive environment for all hostelites.
1.8 Vandalism is a very serious offence. Hostelites found guilty of committing such an offence can be evicted from the hostel.
1.9 In case of any unacceptable behavior by the room-mate, the other room-mate must report it to the college authorities.
Next


At night, a towed vehicle must:
What does a yellow reflector (cat's eye) placed on the road mean?
When turning right from a two-laned road into a one-way street that has two lanes, which lane must you turn into?


Compute a Dynamic Imaging of Coherent Sources (DICS) spatial filter.
This is a beamformer filter that can be used to estimate the source power
at a specific frequency range [1]. It does this by constructing a spatial
filter for each source point. The computation of these filters is very
similar to those of the LCMV beamformer (make_lcmv()
), but instead of
operating on a covariance matrix, the CSD matrix is used. When applying
these filters to a CSD matrix (see apply_dics_csd()
), the source
power can be estimated for each source point.
| Parameters: |
- info : instance of Info
Measurement info, e.g. epochs.info .
- forward : instance of Forward
Forward operator.
- csd : instance of CrossSpectralDensity
The data cross-spectral density (CSD) matrices. A source estimate is
performed for each frequency or frequency-bin defined in the CSD
object.
- reg : float
The regularization to apply to the cross-spectral density before
computing the inverse.
- label : Label | None
Restricts the solution to a given label.
- pick_ori : None | ‘normal’ | ‘max-power’
The source orientation to compute the filter for:
None :
orientations are pooled (Default)
- ‘normal’ :
filters are computed for the orientation tangential to the
cortical surface
- ‘max-power’ :
filters are computer for the orientation that maximizes
spectral power.
- rank : None | int | ‘full’
This controls the effective rank of the covariance matrix when
computing the inverse. The rank can be set explicitly by specifying an
integer value. If None , the rank will be automatically estimated.
Since applying regularization will always make the covariance matrix
full rank, the rank is estimated before regularization in this case. If
‘full’, the rank will be estimated after regularization and hence
will mean using the full rank, unless reg=0 is used.
The default is None.
- inversion : ‘single’ | ‘matrix’
This determines how the beamformer deals with source spaces in “free”
orientation. Such source spaces define three orthogonal dipoles at each
source point. When inversion='single' , each dipole is considered
as an individual source and the corresponding spatial filter is
computed for each dipole separately. When inversion='matrix' , all
three dipoles at a source vertex are considered as a group and the
spatial filters are computed jointly using a matrix inversion. While
inversion='single' is more stable, inversion='matrix' is more
precise. See section 5 of [5]. Defaults to ‘single’.
- weight_norm : ‘unit-noise-gain’ | ‘nai’ | None
If ‘unit-noise-gain’, the unit-noise gain minimum variance beamformer
will be computed (Borgiotti-Kaplan beamformer) [2],
If ‘nai’, the Neural Activity Index [4] will be computed.
Defaults to None , in which case no normalization is performed.
- normalize_fwd : bool
Whether to normalize the forward solution. Defaults to True . Note
that this normalization is not required when weight normalization
(weight_norm ) is used.
- real_filter : bool
If True , take only the real part of the cross-spectral-density
matrices to compute real filters. Defaults to False .
- reduce_rank : bool
If True , the rank of the forward operator will be reduced by 1 for
each spatial location, prior to inversion. This may be necessary when
you use a single sphere model for MEG and mode='vertex' .
Defaults to False .
- verbose : bool, str, int, None
If not None, override default verbose level (see mne.verbose()
and Logging documentation for more).
|
| Returns: |
- filters : instance of Beamformer
Dictionary containing filter weights from DICS beamformer.
Contains the following keys:
- ‘weights’ : ndarray, shape (n_frequencies, n_weights)
For each frequency, the filter weights of the beamformer.
- ‘csd’ : instance of CrossSpectralDensity
The data cross-spectral density matrices used to compute the
beamformer.
- ‘ch_names’ : list of str
Channels used to compute the beamformer.
- ‘proj’ : ndarray, shape (n_channels, n_channels)
Projections used to compute the beamformer.
- ‘vertices’ : list of ndarray
Vertices for which the filter weights were computed.
- ‘inversion’ : ‘single’ | ‘matrix’
Whether the spatial filters were computed for each dipole
separately or jointly for all dipoles at each vertex using a
matrix inversion.
- ‘weight_norm’ : None | ‘unit-noise-gain’
The normalization of the weights.
- ‘normalize_fwd’ : bool
Whether the forward solution was normalized
- ‘n_orient’ : int
Number of source orientations defined in the forward model.
- ‘subject’ : str
The subject ID.
- ‘src_type’ : str
Type of source space.
|
Notes
The original reference is [1]. See [5] for a tutorial style paper on the
topic.
The DICS beamformer is very similar to the LCMV (make_lcmv()
)
beamformer and many of the parameters are shared. However,
make_dics()
and make_lcmv()
currently have different defaults
for these parameters, which were settled on separately through extensive
practical use case testing (but not necessarily exhaustive parameter space
searching), and it remains to be seen how functionally interchangeable they
could be.
The default setting reproduce the DICS beamformer as described in [5]:
inversion='single', weight_norm=None, normalize_fwd=True
To use the make_lcmv()
defaults, use:
inversion='matrix', weight_norm='unit-gain', normalize_fwd=False
For more information about real_filter
, see the
supplemental information
from [3].
References
| [2] | (1, 2) Sekihara & Nagarajan. Adaptive spatial filters for electromagnetic
brain imaging (2008) Springer Science & Business Media |
| [4] | (1, 2) Van Veen et al. Localization of brain electrical activity via
linearly constrained minimum variance spatial filtering.
Biomedical Engineering (1997) vol. 44 (9) pp. 867–880 |
| [5] | (1, 2, 3, 4) van Vliet, et al. (2018) Analysis of functional connectivity and
oscillatory power using DICS: from raw MEG data to group-level
statistics in Python. bioRxiv, 245530.
https://doi.org/10.1101/245530 |


--- layout: common/page title: Privacy Policy ---
Comunic has been built to respond to a growing need of privacy on the Internet. We decided to adopt a strict policy in order to respect the confidentiality of your data.
There are our guideline followed to build our product :
No advertisement. You will never see advertisement on the website, since they would be a big breach in the respect of your privacy.
Avoid third party services. In order to remain free, our services must not depend on other services. Unlikes millions of websites, we will not use the ressources provided by third party services, excepted services that can be completely hosted by Comunic.
No Artificial Intelligences. Nowadays, we believe that the main threats to privacy are the artificial intelligences. Comunic will never deliver your data to such system in order to preserve your freedom.
Protect your data. Although we are trying to give the best protection that we can to our services, we may forget breaches that could allow your data to be retrieved by unwanted third party. Connected technologies can not be completely secure. Security breaches will be fixed as soon as they are reported.
Your data are your data. You will always be able to export an entire set of your data, and when you delete your account, Comunic servers will not keep any footprint about you.


Who am I?
My name is Haneen Awadallah, I'm from Amman, Jordan.
My backgrounds
- I studied interior design
- business administration
- Web development
My Experience
- I worked as a HR administrator.
- graphic designer.
- events organizer.
My Goals
- To be a professional web developer.
- To Expand my knowladge.
- To work in a big Tech company.


Multistage functionality may be implemented as
- Multiple consequent URLs
- Different sets of params to same URL
If there is multistage functionality
- Attempt to discover developers assumptions
- Submit requests out of expected sequence
- Submit same requests more than once


About
The auth service provides authentication layout to ensure security in all the TFS services.
Links
Github repository: https://github.com/thefirstspine/auth
Service URL: https://auth.thefirstspine.fr/
The auth service provides authentication layout to ensure security in all the TFS services.
Github repository: https://github.com/thefirstspine/auth
Service URL: https://auth.thefirstspine.fr/


CPU
Memory
Disk
Network
Loading..
Utilization
Loading..
Speed
Loading..
Card title
The quick brown fox jumps over the lazy dog.
Card title
The quick brown fox jumps over the lazy dog.


RadioButtons
View Html
View Configuration
Basic Usage
Try it yourself
Current Category Selected:
{CurrentDisplay}
SelectedValue Usage
Choose a product:
Product:
{CurrentProduct}
is associated with the SubCategory:
{CurrentProductSubCategory}
Choose another SubCategory to associate with the current product: