JDBC 2 file Scenario

[Former Member]

Dear All,

I am working on a JDBC to file scenario,

I have created the source DT, where there are 5 fields but in the actual table there are around 100 fields. Also I have created the target DT etc. Now having completed the complete configuration and when I am trying to test the scenario. I am getting the exeception at the JDBC sender CC as

" 1/16/09 4:11:00 PM ad88c43e-a207-4dcc-18a4-a277861eabe0 Retry interval started. Length: 60.0 seconds

1/16/09 4:11:00 PM ad88c43e-a207-4dcc-18a4-a277861eabe0 Error during conversion of query result to XML: java.lang.NullPointerException

1/16/09 4:11:00 PM Processing started "

Could you please let me what could be the problem.

Regards,

Nithiyanandam

[Former Member]

Hi,

Try to write SQL query in sender adapter as below

SELECT ID,NAME,BIRTHYEAR,BIRTHMONTH,READFLAG FROM EMPLOYEE WHERE FLAG='No'

Regards

Pullarao

[Former Member]

Hi All.

I Have the same error thtat Nithiyanandam, but my scenario it's form Unix (Solaris) PI server to PC thata contains .mdb file from the MS Access DB.

I'm using PI 7.1 and my configuration it's like:

JDBC Driver: Microsoft Access Driver (*.mdb)

Connection: jdbc:odbc:Driver={Microsoft.Jet.OLEDB.4.0)};DBQ=http://192.168.1.162/Program Files/Petro Vend/Phoenix/Data/P4w.mdb

user: xxx

pwd: xxx

note the driver is "Microsoft.Jet.OLEDB.4.0 !! This is 'cause before that, I have "Microsoft Access Driver (*.mdb)" and JDBC Drvier: sun.jdbc.odbc.JdbcOdbcDriver, but with these parameters the PI server falls down when the channel was activate and started.

So, I still Use the "Microsoft.Jet.OLEDB.4.0" but I can not stablish coneection with the MS Acess Data Base.

For this scenario (PI Server in Unix -> MS Access in Windows), do I need to deploy the driver via SDM for MSACCESS as Rajesh said?

and, what does "SDM" means?

My basis tell me that the MS ACCess driver it's OK fot this server, but I'm not sure if he deployed with SDM.

My CC it's a sender CC.

Thanks for your help.

Regards,

Fernando.

[former_member189440]

The JDBC Sender Structure will be like this

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Student_Info_Outbound xmlns:ns0="http://JDBC_to_File">

<row>

<ID/>

<NAME/>

<BIRTHYEAR/>

<BIRTHMONTH/>

<READFLAG/>

</row>

</ns0:MT_Student_Info_Outbound>

[Former Member]

Hi Uday,

It looks like that only.

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Sender_JDBC xmlns:ns0="http://NPI_JBDC_2_FILE">

<row>

<NAME/>

<ID/>

<ADDRESS/>

<FLAG/>

</row>

</ns0:MT_Sender_JDBC>

Regards,

Nithiyanandam

[Former Member]

Hi Nithiyanandam,

I think you need to specify the driver as this : sun.jdbc.odbc.JdbcOdbcDriver

Also check that each Data element you use for your mapping, it should match exactly with the fields in your database. Letter for letter, case for case.

Regards,

Kshitij

[Former Member]

Hi,

I did provide the details as sun.jdbc.odbc.JdbcOdbcDriver, but still getting the same error. Please provide more inputs....

Regards,

Nithiyanandam

[Former Member]

No Matter what DT you created. Instead of Mapping Error you are getting Adapter Error.

If you are getting Error in JDBC adapter, it means there is something wrong with adapter parameters.

Check the SQL Query..if possible try t execute the same in DB.

If you are not updating anything then set value to <TEST>

Thanks

farooq

[Former Member]

Hi,

First test the mapping in the Message mapping by providing the same values.

Recheck whether you used any function in the mapping.

Whenever there is no value while trying to convert the source structure to target structure.

Regards

Goli Sridhar

[prateek]

The problem is either with the JDBC sender datatype structure or with the format of data sent from sender to XI. May be there could be some mismatch of the datatype defined.

Regards,

Prateek

[Former Member]

Hi Prateek,

This are the fields which have used for my scenario out of the 100 fields in the table

STATECHECKOUTINFO VARCHAR2(30)

PARTTYPE VARCHAR2(30)

SOURCE VARCHAR2(30)

STATEITERATIONINFO VARCHAR2(30)

I have created all these fields in the source and target DT and provided the type as string

similarly for target as well.

I am not sure what could be the problem, because when I execute the query in it is fetching the data from oracle.

Regards,

Nithiyanandam

[Former Member]

Hi All,

I am working on a jdbc to file scenario, Now that I am trying to connect to MSaccess, I have provided the following driver and connection parameters.

Database is in my local machine

Driver: jdbc.odbc.JdbcOdbcDriver

Connection url: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:/database_emp/EMP_Details.mdb

with table as EMPLOYEE

select query as SELECT * FROM EMPLOYEE WHERE FLAG='No'

Update as UPDATE EMPLOYEE SET FLAG='Yes' WHERE FLAG='No'

Having given that, when i m executing the scenario I am getting the following error

Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=D:/database_emp/EMP_Details.mdb' using the JDBC driver 'jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=D:/database_emp/EMP_Details.mdb': SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

in the sender CC.

Please help.

Regards,

Nithiyanandam

Edited by: Nithiyanandam A.U. on Jan 19, 2009 11:02 AM

[Former Member]

Hello,

Did you deploy the driver via SDM for MSACCESS ?

You need to add the entry for the provider.xml and need to deploy the file via sdm

you can search the SDN using message Deployment of External Jars

Regards

Rajesh.

[Former Member]

Dear Rajesh,

Could you please provide more details on how to deploy the driver and where i can find it.

Also what is the entry i need to add for the provider.xml and deploy via SDM

Regards,

Nithiyanandam

[Former Member]

try this.

I converted / into "\"

jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=D:\database_emp\EMP_Details.mdb*

Thanks

farooq

[Former Member]

Hi,

I am still getting the same error even after changing "/" into "\".

How do I check whether the MS ACCESS DRIVER is deployed in XI server or not.

Regards,

Nithiyanandam

[Former Member]

If your XI is installed on Window platform then its is NOT required to install extra drivers.

Thanks

Farooq.

[Former Member]

check SAP Note 831162 - Q 25

Thanks,

Beena.

[Former Member]

Hi Farooq,

My XI is in windows NT platform only, But i am not connecting DB from the XI server rather I am connecting it from my local machine, which is also in windows platform only.

what could be the problem;

Regards,

Nithiyanandam

[Former Member]

Hi,

Could you please provide more inputs... what could be the problem for this error?

Regards,

Nithiyanandam

[Former Member]

>>>rather I am connecting it from my local machine, which is also in windows platform only.

I got it.

jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=D:/database_emp/EMP_Details.mdb*

>>DBQ=D:/database_emp/EMP_Details.mdb

It should be in XI server "D" driver not in your machine directory.

You are giving path D:\*** this is not the server dir...but your own desktop directory right?

Either put that file in the same location of the XI server (D:/database_emp/EMP_Details.mdb) or put it on FTP server

Thanks

Farooq

Edited by: Farooq Farooqui on Jan 20, 2009 10:20 AM

[Former Member]

Hi Farooq,

What is that I need to put in the FTP server, is it just that MDB file from my local desktop to ftp server folder.

If so how shd i mention the details of the connection url for this..

assuming my ipaddress is xx.xx.xx.xx with port 21 with user name as XXXX and pwd YYY

jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=D:/database_emp/EMP_Details.mdb*

Regards,

Nithiyanandam

[Former Member]

Dear Farooq,

Could you please guide me...

Regards,

Nithiyanandam

[Former Member]

Hi Nithyanandam,

Maybe this thread will help you out.

Also what Farooq was mentioning is that the folder D:/ is the local drive for your own machine. Whereas you need to put the file which has to be polled in the 'XI SERVER's' D drive.

So one way would be to open the folder where you want to put the file and copy its path from the windows address bar. Copy that address and replace it in your conenction string.

Hope that helped.

Kshitij

[Former Member]

Hi,

Please let me as Farooq mentioned, will it be feasible to use FTP server by just copying the .mdb file from my local machine to a folder in FTP server.

I am not sure how to provide the path details as this is a FTP server. should i provide it as DBQ:FTP://XX.XX.XX.XX/<Folder>/asd.mdq oris it going to be anyother way.

Regards,

Nithiyanandam

[Former Member]

Hi Guys,

Please provide some inputs I am stl having the error...

Regards,

Nithiyanandam

[Former Member]

Hello,

Try this,

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//<ftpIpAddress>/PIC_FileShare/Database/PI_JDBCTest.mdb;Uid=Admin;Pwd=278pragati;

Regards,

Sreenivas

[Former Member]

select query as SELECT * FROM EMPLOYEE WHERE FLAG='No'

dont use select * .. select FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 from Employee...

dont select all fields

[Former Member]

Hi Nithiyanandam,

Have you tried creating an ODBC with MS Access for this database yet?

Hope this helps,guy.