New post
Question
· Nov 9, 2016

DB access from CSP page without creating link table

#CSP #Caché

Hi all,
Basically i have 2 queries..

  1. 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 ..?

  2. Can we have an object return type from the below mentioned method to a javascript function.. ???

Discussion (9)0
Log in or sign up to continue
Dmitry Maslennikov · Nov 9, 2016

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?

0 0
Rajath Muthappa · Nov 25, 2016

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) quit

When 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

0 0
Dmitry Maslennikov · Nov 25, 2016

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) 
      }
    }
  }
0 0