How-to make cross-domain URL call work in javascript

I was quite busy in the past 2014. I only wrote one blog post in the whole year. Busy is a good excuse for lazy :). I hope I can post more in 2015 to help others and help me as well.

In my first post this year, I’ll share my experiences with cross domain URL call in JavaScript.

Case 1. Normal URL call with JQuery

url = "https://alexzeng.wordpress.com/api/report/";    
$.get(url, function(data) {
    //process data;
    process(data);
});

Case 2. Cross-domain URL call with JQuery
Just add a parameter “callback” with value “?” in your URL, JQuery will handle everything else.

url = "http://alezeng.blogspot.com/api/report/";
$.getJSON(url + "?callback=?", function (result) {
    // process data
    process(data);
}); 

Note: if your url already have some prarameters, use “&” to connect callback parameter like “&callback=?”

There’re some pre-requirements on the API at server side:
1. The API should return the data with “Content-Type:text/javascript”. Otherwise this error’ll show in javascript call:

Resource interpreted as Script but transferred with MIME type text/html

You can check the “Content-Type” in Response Headers by manually call the url in Chrome debug mode.

2. The API should handle the callback parameter, and wrapper the data in callback function. Otherwise you’ll get error because it cannot run as a javascript function
Many RESTful API using modern framework can support by default, for example Django, Spring MVC. But if you use an old framework or home-grown API, or even just a statistic text, it won’t work by default.
However, if you understand how it works, you can mimic what it does and make it work for any URL. Let’s practice it in Case 3.

Case 3.  Manually call Cross-domain URL
Scenario:
I have data at http://alezeng.blogspot.com/api/report.dat.
I want to use the data it in my another site https://alexzeng.wordpress.com/
Step 1. Wrapper data in a callback function
http://alezeng.blogspot.com/api/report.dat, content:

my_callback({
   'key1' : 'value1',
   'key2' : 'value2'
})

Note: you can enclose any value in my_callback as long as it’s a valid javascript parameter.

Step 2. Make your server response the URL as javascript.
In my case, apache is my web server. It’ll return the data as “Content-Type:text/html” by default.
Add below line in apache config file to let it response “.dat” as javascript:

AddType text/javascript .dat 

Note: if the URL is a RESTful API, wrote by programming language, like java or python, you can set the response content type in code directly.

Step 3. Write the callback function
In another site https://alexzeng.wordpress.com/, write javascript codes to handle the data:

function my_callback(data)
{
    //process data
    process(data);
}

function cross_domain_call() {
    url = 'http://alezeng.blogspot.com/api/report.dat'    
    var script = document.createElement('script');
    script.src = url +'?callback=my_callback'
    document.getElementsByTagName('head')[0].appendChild(script);
}

With this, we can successfully get the data from http://alezeng.blogspot.com in https://alexzeng.wordpress.com.
But it’s kind of troublesome if you have any URL that need to change at server side. The other way is, we can use our server to call cross-domain URL on behalf of client.

Case 4. Use server to call Cross-domain URL
Step 1. Write a generic URL call function in server side.
Let’s use Django server as example:
In views.py

from django.views.decorators.gzip import gzip_page

#gzip the response content, this's not a must
@gzip_page
def get_crossdomain_data(request):
    try:
        url = request.GET['url']
        url = urllib.unquote(url).decode('utf8')
        jsonData = urllib2.urlopen(url).readlines()
        return HttpResponse(jsonData)
    except Exception,e:
        result={};
        result['success']= 'false'
        result['message']= str(e)
        return HttpResponse(result)

In urls.py, map the URL

(r'^get_crossdomain_data',views.get_crossdomain_data),

Step 2. Use the server URL to call Cross-domain URL
We need to encode the original URL and transfer it as an parameter to server, and then parse the response data

url = "http://alezeng.blogspot.com/api/report.dat";
url= "/get_crossdomain_data?url=" + encodeURIComponent(url);
$.get(url, function(str_data) {
    data = jQuery.parseJSON(str_data);
    //process data;
    process(data);
});

With all these approaches, you can choose the one works in your scenario.

Advertisement

How to create executable jar file?

Method 1: I used to create a executable jar file by eclipse export “Runnable JAR file”

It’s very handy, and it has 3 options to handle libraries:

  • Extract required libraries into generated JAR
  • Package required libraries into generated JAR
  • Copy required libraries into sub folder next to the generated JAR.

I really like it. You don’t need to configure anything but just a few clicks. I think it’s the best way if it’s just for test.It works well except when I use the 1st option. I run into this problem :

org.xml.sax.SAXParseException: schema_reference.4: Failed to read schema document 'http://www.springframework.org/schema/beans/spring-beans-4.0.xsd', because 1) could not find the document; 2) the document could not be read; 3) the root element of the document is not <xsd:schema>.

This is because the xsd file is missed/overwroten when eclipse extract the libs, and my runtime host cannot connect to Internet that makes it impossible to get it. While this is  not a real problem for eclipse, it can be overcome by using the 2nd option. It didn’t extract the lib, but includes them as they’re.

I wonder why eclipse takes a lot of efforts to extract libs (it’s obviously slower than other 2 options in the list). At first, I thought it may want to build small jar packages(it’s obviously smaller ). Later, I found out the real reason is: java can NOT load classes from a Jar inside a Jar! (In real world, how can you put an even larger jar inside a jar? You are kidding!) Java(class loader) is designed to like this.

Now the question is, how eclipse overcome this jar inside jar problem? I unzipped the generated jar file, and found there’s a jarinjarloader class which did a trick behind. See more below.

Method 2: Using onejar-maven-plugin
Because the network between my laptop and the target machine is slow. It takes times to transfer the runnable jar file, even it’s not big. So I need to build it remotely without eclipse, use maven instead. This plugin did the similar trick jarinjarloder did, the 2nd option in eclipse. It basically let java call its main function, and it help you load all dependency jar files including yourselves. Here is the configuration clip:

		  	<plugin>
		  	    <groupId>com.jolira</groupId>
		  	    <artifactId>onejar-maven-plugin</artifactId>
		  	    <version>1.4.4</version>
		  	    <executions>
		  	        <execution>
		  	            <configuration>
		  	                <attachToBuild>true</attachToBuild>
		  	                <classifier>onejar</classifier>
		  	            </configuration>
		  	            <goals>
		  	                <goal>one-jar</goal>
		  	            </goals>
		  	        </execution>
		  	    </executions>
		  	</plugin>

The associated plugins may also needed:

			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
		   	<plugin>
		  	    <groupId>org.apache.maven.plugins</groupId>
		  	    <artifactId>maven-jar-plugin</artifactId>
		  	    <configuration>
		  	        <archive>
		  	            <manifest>
		  	                <mainClass>com.wordpress.alexzeng.automation</mainClass>
		  	            </manifest>
		  	        </archive>
		  	    </configuration>
		  	</plugin>

It works well as long as you get the dependency configured well in pom.xml. I missed an ojdbc6.jar at first.

Method 3: Using maven-dependency-plugin, corresponding to the 3rd option in eclipse.
I tried this in my project, but failed somehow. I have 23 jar files in final lib directory. But somehow it only includes 19 jar files in the Class-Path of META-INF/MANIFEST.MF. It refused to work even after I added the jar files to classpath variable in environment. I think this method should work, and this is the best one for big projects (you don’t want to have a very big single jar file). You can reference http://www.mkyong.com/maven/how-to-create-a-jar-file-with-maven/ for a simple case.

Last but not least, their’s also a corresponding method in maven to match the eclipse 1st option, use maven-assembly-plugin pre-defined descriptor jar-with-dependencies. It’s really simple and handy if extract dependency jars didn’t cause troubles. 

		    <plugin>
                      <groupId>org.apache.maven.plugins</groupId>
                      <artifactId>maven-assembly-plugin</artifactId>
                      <version>2.4</version>
		      <configuration>
		          <descriptorRefs>
		             <descriptorRef>jar-with-dependencies</descriptorRef>
		          </descriptorRefs>
		        <archive>
		          <manifest>
		            <mainClass>com.wordpress.alexzeng.automation</mainClass>
		          </manifest>
		        </archive>
		      </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
		    </plugin>

As a newbie, I spent a lot of time to get it clear. I hope it can save you sometime to if you need to do the same.

How to use hinted SQL to fix bad SQL plan

As I said in a previously blog Outline, SQL profile and SQL Plan Management(SPM), outline is the oldest approach to fix SQL plan, but it’s the easiest one to use, and works for all oracle version, and do NOT need extra license.

I’d like to elaborate the most frequently used scenario: a SQL is using a bad SQL plan. We can use SQL hints to get a GOOD plan. We want to use outline to fix the SQL plan to the GOOD one. In another word, we want to use a SQL plan with different SQL text(hints) but logically the same.

Let’s create an test scenario:
1. Create a test table


ALEX@ZENG: SQL> create table t (id number, name varchar2(100));

Table created.

ALEX@ZENG: SQL> insert into t select 1, object_name from dba_objects;

74562 rows created.

ALEX@ZENG: SQL> create index t_idx1 on t(id);

Index created.

ALEX@ZENG: SQL> exec dbms_stats.gather_table_stats('ALEX','T');

PL/SQL procedure successfully completed.

ALEX@ZENG: SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      10.2.0.4

2. Create the bad SQL plan scenario


ALEX@ZENG: SQL> update t set id=rownum;

74563 rows updated.

ALEX@ZENG: SQL> commit;

Commit complete.
ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 74556 |  1820K|    74   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 74556 |  1820K|    74   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        319  consistent gets
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3. Use hint to get a better plan

--it's better to use index scan because there's only one row with id=1 now
--We can get the plan by adding hint

ALEX@ZENG: SQL> explain plan for select /*+ index(t t_idx1) */ * from t where id=1;

Explained.

ALEX@ZENG: SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3292636276

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 74556 |  1820K|   150   (2)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      | 74556 |  1820K|   150   (2)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 | 74556 |       |    55   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

14 rows selected.

Since INDEX RANGE SCAN is better plan, we want to use it to replace the FULL TABLE SCAN plan.

Step 1. Get the bad SQL hash value


ALEX@ZENG: SQL> select address, hash_value, sql_text from v$sql where sql_text like 'select * from t%';

ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------------------------------------
000000045F900938  716428968 select * from t where id=1
...

Step 2. Create a SQL file with hints

$ cat good.sql
select /*+ index(t t_idx1) */ * from t where id=1;
$

Step 3. Fix the SQL plan using script fix_plan.sh

#!/bin/bash

if [ $# -lt 3 ]; then
cat <<EOF
Fix given SQL plan using given sql in file
  usage:    fix_plan.sh <hash_value> <hinted_sql_file> <owner>
  examples: fix_plan.sh 716428968 good.sql ALEX
EOF
exit 1
fi

HASH_VALUE=$1
SQL_FILE=$2
OL_OWNER=$3

echo "HASH_VALUE : $HASH_VALUE"
echo "SQL_FILE   : $SQL_FILE"
echo "OL_OWNER   : $OL_OWNER"
echo ""

#Create outline by hash_value
function create_ol_from_hashvalue {
HASH_VALUE=$1
OL_NAME=$2
OL_OWNER=$3

#generate create outline sql
#I didn't use dbms_outln.create_outline, because it cannot create given name outline
# and there's no hash value in V$SQL and DBA_OUTLINES to associate the two 
# according to "How To Match a SQL Statement to a Stored Outline (Doc ID 743312.1)"
sqlplus -S "/ as sysdba" > /tmp/tmp_$OL_NAME.sql <<EOF
set feedback off
set serveroutput on size unlimited
declare
v_sqltext varchar2(32000);
begin
  --get sql text
  select dbms_lob.substr(SQL_FULLTEXT, 30000, 1 ) into v_sqltext from v\$sql where hash_value = $HASH_VALUE and rownum=1;

  dbms_output.put_line('alter session set current_schema = $OL_OWNER;');
  v_sqltext := 'create or replace outline $OL_NAME for category TEMP_PLAN on ' || chr(10) || v_sqltext || chr(10) ||';';
  dbms_output.put_line(v_sqltext);
  dbms_output.put_line('exit;');
end;
/
EOF

sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql
}

#Create outline from sql file
function create_ol_from_sqlfile {
SQL_FILE=$1
OL_NAME=$2
OL_OWNER=$3

#generate create outline sql
cat > /tmp/tmp_$OL_NAME.sql <<EOF
alter session set current_schema = $OL_OWNER;
create or replace outline $OL_NAME for category TEMP_PLAN on
`cat $SQL_FILE`
exit;
EOF

sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql

}

#Exchange outlines, make GOOD_SQL plan to GOOD_PLAN category
#Ref: How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)
function exchange_outline {
OL1=$1
OL2=$2
OL_OWNER=$3

sqlplus -S "/ as sysdba" <<EOF
set feedback off
alter session set current_schema = $OL_OWNER;
create private outline OLFROM from $OL1;
create private outline OLTO from $OL2;
update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM';
delete from ol$ where ol_name='OLTO';
update ol$ set ol_name='OLTO' where ol_name='OLFROM';
commit;
execute dbms_outln_edit.refresh_private_outline('OLTO');
create or replace outline $OL1 from private OLTO for category GOOD_PLAN;
drop outline $OL2;
exit;
EOF

}

#display outline
function display_outline {
OL_NAME=$1
OL_OWNER=$2

sqlplus -S "/ as sysdba" <<EOF
set pagesize 1000 linesize 160
set long 32000
col hint format a55
col join_pos format a45
col owner    format a12
col name     format a18
col ts       format a14
col h        format 999
col category format a12
col sql_text format a80
col used     format a6

select name, sql_text, category, used, to_char(TIMESTAMP, 'YY-mm-dd hh24:MI')
 from  dba_outlines
where name = '$OL_NAME' and OWNER = '$OL_OWNER';

select ol_name name, category, hint#, stage# stage, hint_text hint, join_pred join_pos
 from outln.ol\$hints
 where ol_name = '$OL_NAME'
 order by ol_name, hint#;
exit;
EOF
}

#main function
echo "1. Create outline OL_$HASH_VALUE for SQL $HASH_VALUE"
create_ol_from_hashvalue $HASH_VALUE OL_$HASH_VALUE $OL_OWNER

echo "2. Create outline OL_TEMP for SQL in $SQL_FILE"
create_ol_from_sqlfile $SQL_FILE OL_TEMP $OL_OWNER

echo "3. Exchange outline OL_$HASH_VALUE with OL_TEMP, and drop OL_TEMP"
exchange_outline OL_$HASH_VALUE OL_TEMP $OL_OWNER

echo "4. Display final outline for SQL $HASH_VALUE : OL_$HASH_VALUE in category GOOD_PLAN "
display_outline OL_$HASH_VALUE $OL_OWNER

Run it:

$./fix_plan.sh 716428968 good.sql ALEX
HASH_VALUE : 716428968
SQL_FILE : good.sql
OL_OWNER : ALEX

1. Create outline OL_716428968 for SQL 716428968

Session altered.

Outline created.

2. Create outline OL_TEMP for SQL in good.sql

Session altered.

Outline created.

3. Exchange outline OL_716428968 with OL_TEMP, and drop OL_TEMP
4. Display final outline for SQL 716428968 : OL_716428968 in category GOOD_PLAN

NAME SQL_TEXT CATEGORY USED TO_CHAR(TIMEST
------------------ -------------------------------------------------------------------------------- ------------ ------ --------------
OL_716428968 select * from t where id=1 GOOD_PLAN UNUSED 13-12-29 19:39

1 row selected.

NAME CATEGORY HINT# STAGE HINT JOIN_POS
------------------ ------------ ---------- ---------- ------------------------------------------------------- ---------------------------------------------
OL_716428968 GOOD_PLAN 1 1 OUTLINE_LEAF(@"SEL$1")
OL_716428968 GOOD_PLAN 2 1 IGNORE_OPTIM_EMBEDDED_HINTS
OL_716428968 GOOD_PLAN 3 1 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OL_716428968 GOOD_PLAN 4 1 DB_VERSION('11.2.0.2')
OL_716428968 GOOD_PLAN 5 1 OPT_PARAM('_b_tree_bitmap_plans' 'false')
OL_716428968 GOOD_PLAN 6 1 OPT_PARAM('_index_join_enabled' 'false')
OL_716428968 GOOD_PLAN 7 1 OPT_PARAM('_optim_peek_user_binds' 'false')
OL_716428968 GOOD_PLAN 8 1 OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OL_716428968 GOOD_PLAN 9 1 OPT_PARAM('optimizer_index_cost_adj' 30)
OL_716428968 GOOD_PLAN 10 1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))

10 rows selected.

Step 4. Flush the bad SQL plan

--if it's 10g, use grant to invalidate the SQL plan
SQL> grant select on ALEX.T to dba;

--if it's 11g, use procedure DBMS_SHARED_POOL.PURGE
SQL> select ADDRESS, HASH_VALUE from V$SQL where hash_value = 716428968;

ADDRESS          HASH_VALUE
---------------- ----------
000000045F900938  716428968

1 row selected.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000045F900938, 716428968','C');

PL/SQL procedure successfully completed.

Step 5. Check the new plan

--it's IMPORTANT to set use_stored_outlines to use the stored outline category if not yet
--You can set it at session level and test the plan first if needed
SQL> alter system set use_stored_outlines = GOOD_PLAN;

System altered.

ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3292636276

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

Note
-----
   - outline "OL_716428968" used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        185  consistent gets
          0  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

There is a problem caused by “ghost” parameter use_stored_outlines. Why?
You cannot set it in inital file, and you cannot see it after setting it, and it’s lost its value after DB restart.
So we need to create a trigger to set it automatically when DB start:

SYS@ZENG: SQL> show parameter use_stored_outlines
SYS@ZENG: SQL>
SYS@ZENG: SQL> create or replace trigger enable_outlines_trig
--Ref : How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1)
after startup on database
begin
execute immediate('alter system set use_stored_outlines=GOOD_PLAN');
end;
/

Trigger created.

Until then, you can have a good sleep without worry about the bad SQL plan causes DB high session/load.