no data is fetched

[poppitz81]

Hi,

I have created a very simple example. When I run it from command line I only get:
<?xml version="1.0" encoding="UTF-8"?>
<root />

my query:

xmlselect using noschema

select dsp_id     into xml:{id}
from third_dsp_map
where dsp_id = 1000
order by dsp_id      newValue throwNew xml:{id}
;

I call java com.inverse2.xmltoaster.SQLtoXML test.sql

I also tried some variations, but I always get only the two lines :(

Thanks for any help!

[Steve]

Hi

I think that your problem is caused by selecting data into the root XML element.  If you change your query to something like;

    select dsp_id into xml:{root.id}
    from third_dsp_map
    where dsp_id = 1000
    order by dsp_id newValue throwNew xml:{root.id}

I think your will get the results that you expect.

Any problems then let me know.

Thanks

Steve

[poppitz81]

Hi Steven,

thanks for you help. I tried your version but got the same result. I also tried (beside other versions)

select m.dsp_id,
       m.dsp_name into xml:{Dsps.dsp.name}
from third_dsp_map m 
order by m.dsp_id newValue throwNew xml:{Dsps.dsp}

but I still get the same empty root tag.

[Steve]

I've had a closer look at your problem and I'm starting to think that it could be a data problem.

I wrote a very similar query to your query and executed it against data in my test database and it seems to work fine.  My query is;

xmlselect using noschema
select
    m.bookid,
    m.bookTitle into xml:{Dsps.dsp.name} 
from
    books m 
order by
    m.bookid newValue throwNew xml:{Dsps.dsp}
;

If you run your query without the "into xml:{..}" and "newValue..." stuff, does it return any rows?

If you are still stuck then either I can send you the table/data/query that I'm using or, if possible, you could send me your table/data/query.

One other thing... what database and JDBC driver are you using?  That could be a factor in the problem.

Let me know how you get on.

Steve

[poppitz81]

Without the "into xml:{..." the query returns some rows. I have also tried with jdbc and it worked. I have the same problem with other tables, so I think you are right and the problem must be the driver/connection. I am using an Oracle 9i Release 2 DB and the ojdbc14.jar Oracle jdbc Driver. I will download the sources now and try to add some debug output to find out what is the problem.

Thanks
Tom

[poppitz81]

Finally, I fixed the problem.

in query.fetch a weird column [qpzm] is added to the hashmap:

18.12.2007 11:11:15 com.inverse2.query.Query fetch FEIN: Adding column [col1] to the hashmap...
18.12.2007 11:11:15 com.inverse2.query.Query fetch FEIN: Adding column [col2] to the hashmap...
18.12.2007 11:11:15 com.inverse2.query.Query fetch FEIN: Adding column [qpzm] to the hashmap...

The value is always "X0                              ". I have only 2 columns in my query.

I don't know if this column [qpzm] is necessary for the program to work, but later when the values are compared, they never match because of the blanks. So I added a trim in SQLtoXML.getResultValueCol:

private Object getResultValueCol(String fieldName, Query query, String typeCon) {
  return(getColumnValue(fieldName, typeCon, query.getColumnValueAsString(fieldName).trim()));
}

Now I get the expected XML.

[Steve]

That's great news that you've tracked the problem down.

The qpzm column is used when there is more than one select statement with an "xmlunion".

I'll add the change to the source in CVS so that the fix is in future versions.

Thanks

Steve