Uncaught SoapFault exception: [soapenv:Server] Before start of result set

Question

I am developing a web service for e-commerce website, and i am returning all products based on some supplier_id from java into php client but it is giving me this error

Fatal error: Uncaught SoapFault exception: [soapenv:Server] Before start of result set in C:\wamp\www\eshop\viewProductsSupplier.php:194 Stack trace: #0 C:\wamp\www\eshop\viewProductsSupplier.php(194): SoapClient->__soapCall('viewProducts', Array) #1 {main} thrown in C:\wamp\www\eshop\viewProductsSupplier.php on line 194

I have tried to check whether the query executes or not but it is all fine

//php code

$soapclient = new SoapClient('http://localhost:8090/Eshop_ecommerce/services/Eshop_ecommerce?wsdl', ['trace' => 1]);
            session_start();
            $id=array('id'=>@$_SESSION['supplierId']);
            $response=$soapclient->__soapCall("viewProducts",$id);
            if (isset($response->Name))
            {
                print $response;
            }
            else
            {
                print "Data is null";
            }

//Java code

ViewProductsResponse res = new ViewProductsResponse();
                Class.forName(driver);
                conn = DriverManager.getConnection(url,username,password);
                stmt = conn.createStatement();
                String Query=null;
                String in=viewProducts.getViewProductsRequest();
                if(!in.isEmpty())
                {
                    Query="SELECT * FROM product";
                }
                else
                {
                    Query="SELECT * FROM product WHERE product_supplier_id='"+viewProducts.getViewProductsRequest()+"'";
                }
                ProductArray pa=new ProductArray();
                ResultSet result= stmt.executeQuery(Query);
                //System.out.println(result);
                while(result.next())
                {
                    Product p= new Product();
                    //System.out.println(result.getString("product_id"));
                    p.setId(result.getString("product_id"));
                    p.setName(result.getString("product_name"));
                    p.setPrice(result.getString("product_price"));
                    p.setStock(result.getString("product_stock"));
                    String supplierNameQuery="SELECT supplier_id,supplier_name FROM supplier WHERE supplier_id='"+result.getString("product_supplier_id")+"'";
                    ResultSet resultSupplierName = stmt.executeQuery(supplierNameQuery);
                    p.setSupplierName(resultSupplierName.getString("supplier_name"));
                    String catQuery="SELECT * FROM product_category WHERE category_id='"+result.getString("product_category_id")+"'";
                    ResultSet resCat= stmt.executeQuery(catQuery);
                    p.setCategoryName(resCat.getString("category_name"));
                    pa.setProduct(p);
                    res.setProducts(pa);
                }
                //res.setViewProductsResponse("Data Entered");
                return res;

where as i expect it to print all the resulting products

Answer 1

A ResultSet returned from Statement#executeQuery is positioned before the first row (see linked-in JavaDoc). In order for such ResultSet to point to the first row it needs re-positioned to it, for example using the next() method. This is done for the outer SELECT but not for the two inner ones. Further the return value from next() call needs to be checked in order to see if there is any row at all in that ResultSet.

This can be achieved for example like this:

 if(resultSupplierName.next()) {
     p.setSupplierName(resultSupplierName.getString("supplier_name"));
 }
 if(resCat.next()) {
     p.setCategoryName(resCat.getString("category_name"));
 }

Please note that the posted code has other open issues, for example the JDBC resources are not closed. I would strongly suggest to look either into JEE or Spring (giving that the code apparently runs in aplication server) or at least at this. Further is the code prone to SQL injection