DB access from CSP page without creating link table
Hi all, Basically i have 2 queries..
-
Is there any simple way to fetch the data directly from the database (using the URL and credentials) from the csp file without creating a link table for that particular Table...?? If there is, could you please share the syntax of using them ..?
-
Can we have an object return type from the below mentioned method to a javascript function.. ???
Comments
What do you mean by without creating a link to a particular table? You can create CSP with any of your ways to get table name which it should return.
I just need to know the syntax of retrieving values from the Databse (not cache DB) directly from the CSP page..
Really, do you want to retrieve data from another database and show it with CSP ?
Well, it still possible, but needs more details. You can do it by creating some connection via JDBC or ODBC or in any other ways to this particular database, and call queries to get needed data. But it means you should know, how to connect to this database. Can you say, which database you should connect to? And maybe you can describe reasons why you should do so?
currently i am using Oracle 11g. i need to fetch some data from the table to process the data. Hence i would want to connect to the DB from the CSP page.
Do you want to load data from Caché into Oracle, or from Oracle into Caché?
I just have to display few values from oracle DB in the UI. I don't have to load the data in the cache DB
In first you should define ODBC DSN in your operation system, to your Oracle DB.
Then you can look at this page in the documentation and class %SQLGatewayConnection
Please correct me if i'm wrong ,
Currently i am using the below code to connect to the Oracle Database. (JDBC Connection)
Set conn=##class(%SQLGatewayConnection).%New()
Set sc=conn.Connect("Test_Database","Test_user","testuser",300)
If $$$ISERR(sc) do $System.Status.DisplayError(sc) quitWhen run the application i get the below pop up\
A JavaScript exception was caught during the execution of the HyperEvent:
Syntax Error:Unexpected token illegal -------------------------------------------------- Result : ------------------------------------------------- JavaScript Code: Error #6002:Gateway failed: DSN/User connect -----------------
I have the Connection configured in Configuration->SQL GatewayConnection which is working absolutely fine.
Please let me know the proper Code and the Syntax to connect to the Oracle DB
You got this error because you tried to use ODBC connection, but you say that you have JDBC connection. You should understand that it is different, with different ways to connect.
If you really use JDBC, you can create the new connection to the Oracle database, with a help from this article in the documentation. And with a code something like this, you can retrieve needed data from that server.
#dim %JDBCGateway As %Net.Remote.Java.JDBCGateway
set conn=$system.SQLGateway.GetJDBCConnection("cache2samples")
if conn'="" {
set cmd = "SELECT top 10 id,ssn,name,age,dob FROM Sample.Person"
set st = %JDBCGateway.prepareStatement(conn,cmd)
set %JDBCGateway.StatementCache(conn) = st
set query = %JDBCGateway.execQuery(st)
set columns=%JDBCGateway.getColumnCount(st)
write !
set pos=0
for i=1:1:columns {
set name=%JDBCGateway.getColumnName(st,i)
set size=%JDBCGateway.getColumnDisplaySize(st,i)
set pos(i)=pos
set pos=pos+size+1
write name,?pos
}
while %JDBCGateway.next(st) {
write !
for i=1:1:columns {
write ?pos(i),%JDBCGateway.getString(st,i)
}
}
}