Tuesday, May 29, 2018

Using LIKE wildcard in prepared statement

Using “like” wildcard in prepared statement

Query : SELECT COUNT(o.orders_id) AS orders,ROUND(SUM(o.totalproduct),0) AS ordersum,ROUND(AVG(o.totalproduct),0) AS orderavg FROM orders o, users usr WHERE o.member_id=usr.users_id and  o.timeplaced like  ? and usr.field1='IU' and o.status='M'

In Java:
String mmSearch = "%";
            Set<String> keys = previousOrdersDateMap.keySet();
            for(String key: keys){
               if(null != key){
                               key = mmSearch+key+mmSearch;
                PreparedStatement preparedStatement = conn.prepareStatement(detQryType);

                preparedStatement.setString(1,key);

Friday, May 25, 2018

Writing REST handler in ibm wcs

Writing REST handler: If you are writing a custom handle in WCS follow below steps:

1.       Make entry of your custome handler in resources-ext.properties at below path
Rest\WebContent\WEB-INF\config\resources-ext.properties

e.g com.mycomp.commerce.rest.handlers.ExMyHandler

Now lets try to get value passed in header for GET request

package com.mycomp.commerce.rest.handlers.ExMyHandler;

import java.util.Set;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.HttpHeaders;
import javax.ws.rs.core.Response;

@Path("store/{storeId}/http-header/")
public class HttpHeaderService extends AbstractClassicResourceHandler {
               
                @GET
    @Produces( { "application/atom+xml", "application/json", "application/xml" })
    @Path("/queryheader")
    public Response findHeader(@PathParam("storeId") String storeId, @QueryParam("responseFormat") String responseFormat, , @Context HttpHeaders httpHeaders) {
                String METHODNAME = " findHeader()";
                LOGGER.entering(CLASSNAME,METHODNAME);
        Response result = null;
        String cacheControl = httpHeaders.getRequestHeader("Cache-Control").get(0);
        System.out.println("Cache-Control: "+cacheControl);
        /** get list of all header parameters from request **/
        Set<String> headerKeys = httpHeaders.getRequestHeaders().keySet();
        for(String header:headerKeys){
            System.out.println(header+":"+httpHeaders.getRequestHeader(header).get(0));
        }

        LOGGER.exiting(CLASSNAME,METHODNAME);
System.out.println("----------"+result);
        return result;
    }


Now use below URL in POSTMAN to get header query results:



Results in console : All parameter passed in header will come in system out

[5/25/18 12:40:56:611 IST] 000001ad SystemOut     O Cache-Control: no-cache
[5/25/18 12:40:56:611 IST] 000001ad SystemOut     O Accept:*/*
[5/25/18 12:40:56:611 IST] 000001ad SystemOut     O Accept-Encoding:gzip, deflate
[5/25/18 12:40:56:612 IST] 000001ad SystemOut     O Cache-Control:no-cache
[5/25/18 12:40:56:612 IST] 000001ad SystemOut     O Connection:keep-alive
[5/25/18 12:40:56:612 IST] 000001ad SystemOut     O Content-Type:application/json
[5/25/18 12:40:56:612 IST] 000001ad SystemOut     O Cookie:JSESSIONID=0000XFHI_0g_W1W40QAO34yPPQ8:-1
[5/25/18 12:40:56:613 IST] 000001ad SystemOut     O emailId:pawan@gmail.com
[5/25/18 12:40:56:613 IST] 000001ad SystemOut     O Host:localhost
[5/25/18 12:40:56:613 IST] 000001ad SystemOut     O Postman-Token:d299a6ae-91a6-4710-8a73-223c1b5d63f0
[5/25/18 12:40:56:614 IST] 000001ad SystemOut     O User-Agent:PostmanRuntime/7.1.1
[5/25/18 12:40:56:614 IST] 000001ad SystemOut     O WCToken:125002%2CrbkigQ6NXpcvA2fu2rhe%2BasrZVIbdxvJavsmYi4OFe%2BglUf9Mbq%2F9lz37NMjdk0PvgsSZf5quQghTNL7TTFAbSa03WFEAqmO4tZ5zlRXlCgdQTYfJwH4jY%2B%2FnuRHTaqRjLghrBE%2ByI8jsBbmU80TZQVslYSsLJsSxDIS8hRhaZp9RUjh16nc2iROD7xdj7Q81bPln7rsgYXt6XuttxhQ%2F1uS1jLHY5dM3b07d9pffKj3kT4K6FmA7W%2FRvxLm%2B%2Foh

[5/25/18 12:40:56:614 IST] 000001ad SystemOut     O WCTrustedToken:125002%2CWb5DERrSx6keRM7Ql1yJjh5pYbYy2URYgvSGu0J3cMk%3D

Generating tokens to use REST service in ibm wcs

Generating tokens to use REST service:
Step 1: To get token, Access below REST service with JSON content mention as below

Content to Send:
{
  "logonId" : "abc@gmail.com",
  "logonPassword" : "password"
}
Step 2: In response to above request you will get WCToken and WCTrustedToken which you need to send to further request to access rest of the APIs.
e.g.
{
  "WCToken": "74022%2C7Vc3U%2B7zdArch8iwDFNsSnBJ0odKFI4wkb19WwiWLIwK1tfO%2B4X4tptjpXYoMlHAxtn1iojIdpFj4TUmFV5NGX%2BoxEB1EobxP87w1hm1Sp6Q8adx8pnUVH0UDljzfs5k2FhDroNrcE0K%2F7EpEjEAlLyzT13bBce4af%2BERKPpnDMxxJvc%2Bt8jBR4JfALSnJy6iJ2MVL3vAdwyOvgijRQia6Rs4MWUgIqkzZbuMczAmWU%3D",
  "userId": "74022",
  "personalizationID": "1494826057535-7",
  "WCTrustedToken": "74022%2C%2BB50W0QUpH426rBLfEmg0CD4GpotVzeIvRetPHtY55M%3D",
  "addressId": "27614181409"

}

Wednesday, May 23, 2018

Using Storefront Test Automation IBM WCS

Storefront Test Automation Engine
Using Storefront Test Automation Engine of WCS v7 FEP6
The Storefront Test Automation Engine (STAE) facilitates the process of writing, maintaining, and running test scripts to test the store functionally
Below are the components of STAE
Test Suite
Test Suites are built based on JUnit and are used to select the test case that must be run.
Test
A Test performs a few tasks from different page objects to test a storefront flow. Since all tests are built using JUnit version 4, all methods must match the following criteria to be identified as a test:
·         Be annotated with @test annotation
·         Be public
·         Have no arguments
Methods that are annotated with @Before are called before each test case. They are used to prepare the data that is required by the test.
Methods that are annotated with @After are called after each test case. They are used to release resources after a test is complete or complete a cleanup.
Store Page Objects
Store page objects are Java classes that act as a high-level abstraction of the web page. They include relevant tasks and the element identifiers found on the web page that is used         by the tasks. Example: AuroraFrontPage class is a SPO
Web test engine
This layer offers functionality to the tests to interact with a web browser. We have option to use HTMLUnit or test via browsers like Firefox, IE and chrome using the Selenium server
Setting up and running tests
Installing the Storefront Test Automation Assets
We need to download the WC_V7.0_-_FEP6STSTORECOMPASSETSMPEN.zip file from IBM Partnerworld site. Once downloaded, extract the zip to a temp folder.




Step 1: Open IBM Installation Manager



Step 2: Add the downloaded test asset store repository using File->Repositories link and click Install. You will be provided with the below page.Select the Companion Asset for FEP 6 and click NEXT



Step 3: Accept the License Agreement and click Next



Step 4: Provide path and click next



Step 5: Select all options and click Next



Step 6: Click Install



Step 7: Verify if the installation is a success and click Finish



Step 8: After installation, go to the installation directory and unzip all zip files available there
Setting up the Storefront Test Automation Engine
Pre-requisites
We need to download the below items to setup the STAE
JUnit
Latest JUnit version can be downloaded from
http://sourceforge.net/projects/junit/files/junit/
Selenium Server
Latest version can be downloaded from
http://docs.seleniumhq.org/download/
HttpClient, HttpCore and HttpMime
Latest version can be downloaded from http://hc.apache.org/downloads.cgi and http://mvnrepository.com/artifact/org.apache.httpcomponents/httpmime
Some test scripts require setup steps that access WebSphere Commerce Accelerator or IBM Management Center for WebSphere Commerce. To do so, the Storefront Test Automation Engine makes HTTP calls to the WebSphere Commerce back-end tooling using HttpClient.

Google Guice

Latest version can be downloaded from http://code.google.com/p/google-guice/downloads/list
To use Chrome as a test browser, we must install separate ChromeDrive for use with Selenium. Get the latest from http://code.google.com/p/selenium/wiki/ChromeDriver
After downloading all the above required files, extract them to their respective temp folder. We need the JARs to setup the test project later
Setting up STAE:
        -      In RAD select File > Import.
        -      In the Import window, select General > Existing Projects into Workspace; then click Next.
        -      Select archive file option and then click Browse.
        -      Browse to the location where we installed Storefront assets and then select the zip file StorefrontTestAssetsWteFEP6.zip for Web testing or select MobileTestAssetsFEP6.zip for Mobile testing.
        -      Select the all projects shown and then click Finish.
Example: Wte, WCHTTPClient, WCWebTestEngine, and Aurora-Tests-Wte, Mobile-Wte
The Storefront Test Automation Engine projects are now in RAD. Since the library dependencies are not installed yet, the Problems view displays multiple compilation errors. We will resolve these errors in the next step.
Install the dependencies of the STAE
a.    In both the WCHTTPClient, Aurora-Tests-Wte, WCWebTestEngine and Wte projects, create the lib directory at the root of the project.
b.    Copy all of the JAR files related to HttpClient, HttpCore, HttpMime to the lib directory of the WCHTTPClient project.
c.    Add the above JAR files to the project build path.
d.    Browse to the directory on the file system that contains the extracted JUnit files. Copy thejunit-(version_number).jar file to the lib directory of the Wte project.
e.    Copy the following files to the lib directory of the Wte project:
      selenium-server-(version_number).jar
f.     Browse to the directory on the file system that contains the extracted Google Guice files. Copy the following files to the lib directory of the Wte project:
      Guice-(version_number).jar.
g.    Add the JARS to project build path

h.    In the Aurora-Tests-Wte project, browse to src\com\ibm\commerce, delete the url directory, and then refresh the project in RAD.
i.      Right-click the Aurora-Tests-Wte project and click Build Path > Configure Build Path. In the Projects directory remove any projects that are marked as missing.
j.     In the WCWebTestEngine lib folder add guice-(verison_number).jar and guice-assitedinject-(verison)number).jar.
k.    Add the same to the project build path
Running Tests

In the config.properties file present in Aurora-Tests-Wte project, change the following properties to reflect our environment:
# The host name of their server you are testing.
HOSTNAME=localhost
# The path part of the URL of the store to open (exluding the host).
STOREURL=/webapp/wcs/stores/servlet/en/aurora
# The browser type.
# Possible values: FIREFOX, INTERNET_EXPLORER
BROWSER_TYPE=FIREFOX
#Path to where screenshots are stored for failed test cases
SCREENSHOTPATH=c:/screenshots/
#Site administrator userid and password
ADMIN_USER_NAME=wcsadmin
ADMIN_PASSWORD=wcsadm1n
#URL of the esite store
ESITE_URL=/webapp/wcs/stores/servlet/StoreView?storeId=11001

 Use one of the following scenarios to run your tests:

Option
Description
Run the entire test bucket
a.    In the Java perspective of your Eclipse-based environment, locate the Package Explorer view and navigate to the tests directory.
b.    Locate the AllTests.java class.
c.    Right-click the AllTests.java class; then select Run As > JUnit Test.
Run a test scenario
d.    In your Eclipse-based environment, open the Package Explorer view and navigate to the tests scenario directory.
e.    Right-click the test script that you want to run; then select Run As > JUnit Test.
Run a single test case
Note: Since many test cases require data created by the FSTOREB2C_00.java test case, it is recommended that you run this test case first to populate the required test data.
f.     In your Eclipse-based environment, open the Package Explorer view and navigate to the tests scenario directory.
g.    Locate the test scenario that contains the test case you want to run; double-click the test scenario to open it.
h.    Locate the single test case that you want to run. There is one Java method for each test case.
i.      Right-click the Java method name for the test case; then select Run As > JUnit Test.



Future Work:

We can use Selemiun Grid which is part of Selenium 2 to run tests on multiple machines using multiple browsers to speed up the test case execution time or is we want to test multiple browsers simultaneously.

More info at:



http://docs.seleniumhq.org/docs/07_selenium_grid.jsp


Wednesday, May 16, 2018

Resolve ORA-011033: ORACLE initialization or shutdown in progress

Two way to resolve:

go to cmd prompt :



1.  Run below query

sqlplus system/Oracle_1@pdborcl as sysdba;
alter database open;

If it not work

2. Run below query..



> sqlplus /nolog

SQL> connect / as sysdba

Connected.

SQL> shutdown abort

ORACLE Instance shut down.

SQL> startup nomount

ORACLE Instance started

SQL> alter database mount;

SQL> alter database open;
Which still gave the error:

ORA-00600: internal error code, arguments: [kcratr1_lostwrt]
I then found the advice to do the following:

SQL> startup mount

ORACLE Instance started

SQL> recover database 

Media recovery complete

SQL> alter database open;

Database altered