cancel
Showing results for 
Search instead for 
Did you mean: 

Querying a RDBMS database

former_member1191927
Participant
0 Kudos
54

Hi all,

I am trying to connect to a RDBMS (SQL server) table and read data. I have written the following code :-

IConnectorGatewayService cgservice =

(IConnectorGatewayService) PortalRuntime

.getRuntimeResources()

.getService(

IConnectorService.KEY);

ConnectionProperties cp =

new ConnectionProperties(request.getLocale(), request.getUser());

try {

con = null;

con = cgservice.getConnection(this.getSystemName(), cp);

message = message + "connection con <BR>";

} catch (Exception e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

try {

iquery = con.newQuery();

System.out.println("After new query");

} catch (CapabilityNotSupportedException e3) {

// TODO Auto-generated catch block

e3.printStackTrace();

}

String query =

"SELECT sight_code , loc_city , loc_state , car_status , clm_date_time "

+ " FROM RT_TRIP where "

+ " car_no = '016905' and "

+ " cust_no = '842591198546' and "

+ " trip_no = 'SA20040424021'";

try {

exestatus = iquery.execute(query);

} catch (QueryExecutionException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

recordSet = iquery.retrieveRecordSet();

setRecordSet(recordSet);

} catch (ConnectorException e2) {

// TODO Auto-generated catch block

e2.printStackTrace();

}

try {

con.close();

} catch (Exception e4) {

// TODO Auto-generated catch block

e4.printStackTrace();

}

1)

Above code executes fine for the first time. But starts throwing the following exception from the second time :-

"[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt".

<b>Even though i have closed the EP connection, I guess some internal DNS connection is not getting closed</b>. Can some one tell me how to solve this problem.

2) The API which I am currently using uses "IQuery" which is a deprecated API. I was trying to use "IExecution" interface.

But to use IExecution.execute(IOperation e) - how to I create the object of IOperation???

Please help me in this.

Regards,

NArahari

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

hi

u can use INativeQuery instead of IQuery.INativeQuery has similar methods that of IQuery.

Regarding your exception

The problem usually occurs when the number of rows returned in the first resultset is still being buffered on the server while you're trying to open a second resultset.

One way to correct this is to force SQLServer to send all data to the client. Just doing something like a "movelast" will solve the problem because it forces SQLServer to send all the rows to the client.

hope this helps.

regards,

purushothaman.

former_member1191927
Participant
0 Kudos

Hi Purushothamam,

>One way to correct this is to force SQLServer to send >all data to the client. Just doing something like >a "movelast" will solve the problem because it forces >SQLServer to send all the rows to the client.

Could you please tell me how to do this?? movelast should be called on record set??

Regards,

NArahari

Former Member
0 Kudos

hi

this movelast is same as that of recordcount. for small recordsets you will be using recordcount and when you have thousands of recordsets you will be using movelast to get an actual value.you may also use like this,

objRs.MoveLast

RecCount = objRs.RecordCount

objRs.MoveFirst

objRs is the recordset.first line will populate the entire recorset and with second line , variable RecCount will have a value.by the last line i am again traversing to the first recorset.

hope it helps.

regards,

purushothaman.

former_member1191927
Participant
0 Kudos

Hi purushothaman,

I could not find the MoveLast and MoveFirst methods in IRecrodSet.

Could you please send me a code snippet for this??

Please help me in this.

Regards,

Narahari

former_member1191927
Participant
0 Kudos

Can any one help me in making use of IExecution interface also instead of IQuery (as it is deprecated)?

Regards,

Narahari

Former Member
0 Kudos

Hi,

may be gives an idea. But I am still working on it to get it going.

Every update will be made threre.

Regards,

Henric

Answers (1)

Answers (1)

former_member441228
Active Participant
0 Kudos

you might want to close the iquery and the recordset.