ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Triggers/SQL Server

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Triggers/SQL Server

    I have created a trigger file that writes a record to a file on i, but what I really need to do is to write a record to a database file on an SQL server. Is there a way to do that?

  • #2
    You should be able to change the trigger to use an external trigger and write an RPG program that utilizes Scott Klement's JDBCR4 (http://www.scottklement.com/jdbc/)

    Comment


    • #3
      I have been working at loading the Java driver. I try to run the program Mr. Klement's MSSQLTEST program and I am getting the following error.

      Exception in thread "main" java.lang.NoClassDefFoundError: net.sourceforge.jtds.jdbc.Driver
      Caused by: java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver
      at java.net.URLClassLoader.findClass(URLClassLoader.j ava:602)
      at java.lang.ClassLoader.loadClassHelper(ClassLoader. java:777)
      at java.lang.ClassLoader.loadClass(ClassLoader.java:7 50)
      at sun.misc.Launcher$AppClassLoader.loadClass(Launche r.java:330)
      at java.lang.ClassLoader.loadClass(ClassLoader.java:7 31)

      After talking with one of our systems guys, he thinks that it isn't finding the driver. What have I done wrong. I set the CLASSPATH variable using the following:
      ADDENVVAR ENVVAR(CLASSPATH) VALUE('/java/jdbc/jdts-1.3.1.jar')
      However after I call MSSQLTEST, the CLASSPATH is:
      '.:/java/jdbc/jdts-1.3.1.jar:/home/DUBMAPJJ01/sqljdbc.jar'

      What am I doing wrong?

      Comment


      • #4
        You appear to be misspelling the jar file. it is 'jtds', but you are spelling it 'jdts'

        Comment


        • #5
          Thanks, I think my head is going to explode before I get this figured out - not sure old dogs can learn new tricks. Anyway, I am now getting this error when I am trying to write to the SQL server.

          Message ID . . . . . . : RNX0301
          Date sent . . . . . . : 12/02/19 Time sent . . . . . . : 17:24:01

          Message . . . . : Java exception received when calling Java method.

          Cause . . . . . : RPG procedure JDBC_CONNP in program QGPL/JDBCR4 received
          Java exception "java.lang.NoSuchMethodError:
          java/sql/DriverManager.registerDriver(Lnet/sourceforge/jtds/jdbc/Driver"
          when calling method "registerDriver" with signature
          "(Lnet.sourceforge.jtds.jdbc.DriverV" in class "java.sql.DriverManager".
          Recovery . . . : Contact the person responsible for program maintenance to
          determine the cause of the problem.
          Technical description . . . . . . . . : If the exception indicates that the
          Java class was not found, ensure the class for the method is in the class
          path. If the exception indicates that the Java method was not found, check
          the method name and signature. If the signature is not correct, change the
          RPG prototype for the method, or change the Java method, so that the return
          type and parameter types match. You can determine the signatures for all the
          methods in class XYZ using command QSH CMD('javap -s XYZ').

          This is my classpath statement:
          ADDENVVAR ENVVAR(CLASSPATH) VALUE('/java/jdbc/jtds-1.3.1.jar')

          Comment


          • #6
            Have you changed the code in JDBCR4?

            The error message says that the JDBC_ConnProp() subprocedure is trying to call DriverManager.registerDriver -- which is correct. But it lists the signature as Lneg/sourceforce/jtds/jdbc/Driver -- which is wrong. The signature should be Ljava/sql/Driver -- it should not hard-code a specific driver class in the signature like your message shows.

            My copy of the code (which has not changed since 2007) shows this:
            Code:
             D Driver          s               O   CLASS(*JAVA:       
             D                                     'java.sql.Driver')                                                    
               .
               .
             D registerDriver  PR                  ExtProc(*JAVA             
             D                                     :'java.sql.DriverManager'
             D                                     :'registerDriver')        
             D                                     static                    
             D   drv                               like(Driver)
            So that is written correctly, it is using a class name of java.sql.Driver, so the signature will therefore be Ljava/sql/Driver as it should be. I can't see why you'd get the error you're getting unless you changed the definition of the 'Driver' object?

            Comment


            • #7
              OK, I did change the driver in JDBCR4, to use the sourceforge driver. I changed that back to your original code.

              I am getting an "Unable to connect" (CPF9897) message now - no additional info.

              This is my connection string:
              conn = JDBC_ConnProp('net.sourceforge.jtds.jdbc.Driver'
              :'jdbc:sqlserver://newalpwsql02.cvg.local:1433'
              : prop );

              Comment


              • #8
                This error usually means that the URL you've provided (the 2nd parameter to JDBC_ConnProp) is not understood by the driver you've provided.

                Indeed, this URL does not appear to be a jtds URL. Take a look at the JTDS FAQ here: http://jtds.sourceforge.net/faq.html -- you'll notice that a jtds driver always begins with "jdbc:jtds:".

                I don't work with SQL Server myself, but my guess is that your URL should be 'jdbc:jtds:sqlserver://newalpwsql02.cvg.local:1433'

                Comment


                • #9
                  I have been working with the network folks and they don't see me trying to connect to the server. However, they do see it when I ping the server from the iSeries. So I thought I would use your example on page 13 of your "Accessing External Databases" presentation just to see if I could get it to connect.

                  Here is my code:
                  H DFTACTGRP(*NO) BNDDIR('JDBC')

                  FQSYSPRT O F 132 PRINTER

                  /copy qrpglesrc,jdbc_h

                  D userid s 50A
                  D passwrd s 50A

                  D conn s like(Connection)
                  D ErrMsg s 50A

                  ** java.sql.Driver --- net.sourceforge.jtds.jdbc.Driver
                  ** jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]


                  c eval conn='JDBC_Connect('+
                  c 'net.sourceforge.jtds.jdbc.Driver:'+
                  c 'jdbc:jtds:sqlserver://'+
                  c 'newalpwsql02:1433/BingoBoardsAS400;'+
                  c %trim(userid)+':'+%trim(passwrd)+')'

                  c if conn=*null
                  c eval ErrMsg='Connect failed'
                  C*** show message to user
                  c endif

                  But this won't compile - error RNF7416 --- The types of the right and left hand side do not match in the EVAL operation. It is the EVAL when I set the value of CONN. I am totally out of my element with this.

                  Comment


                  • #10
                    Oops - looks like the spacing was deleted when I posted - hope you can tell what I was trying to do.

                    Comment


                    • #11
                      Your earlier example with JDBC_ConnProp() was much better code. As I pointed out, you didn't have the URL right, but it was close, and everything else made sense. This newer one makes less sense. First of all, JDBC_ConnProp() is a better choice than JDBC_Connect(). Second, free-format RPG is a better choice that is much easier to read, write and maintain than fixed format RPG. Third, inexplicably, you are putting the RPG code inside quote marks as if its data in a character string, which is resulting in the RNF7416, since you can't assign a character string to a Java object.

                      Here's the code I'm referring to (slightly reformatted).
                      Code:
                          c                   eval      conn='JDBC_Connect('+
                           c                             'net.sourceforge.jtds.jdbc.Driver:'+
                           c                             'jdbc:jtds:sqlserver://'+
                           c                             'newalpwsql02:1433/BingoBoardsAS400;'+
                           c                              %trim(userid)+':'+%trim(passwrd)+')'
                      Do you see what I mean? You have 'JDBC_Connect' in quote marks as if it is data in a string. You added + symbols to concatenate that code to the other code...

                      If you really wanted to call JDBC_Connect in fixed format RPG (which, again, I do NOT recommend) you'd code it like this:
                      Code:
                           c                   eval      conn = JDBC_Connect(
                           c                               'net.sourceforge.jtds.jdbc.Driver'
                           c                             : 'jdbc:jtds:sqlserver://'+
                           c                                'newalpwsql02:1433/BingoBoardsAS400'
                           c                             :  %trim(userid)
                           c                             :  %trim(passwrd) )
                      See the difference? The JDBC_Connect isn't data in a string anymore, its RPG code that will run. The strings are limited to the individual parameters passed to the procedure.

                      But, again, the much better way is to go back to what you were doing earlier with JDBC_ConnProp in free format

                      Code:
                         
                               conn = JDBC_ConnProp( 'net.sourceforge.jtds.jdbc.Driver'
                                                   : 'jdbc:jtds:sqlserver://' +
                                                     'newalpwsql02:1433/BingoBoardsAS400'
                                                   : prop );

                      Comment


                      • #12
                        Here is an excerpt of code I use to connect to SQL Server:

                        Code:
                                 driver   = 'net.sourceforge.jtds.jdbc.Driver';
                                 database = 'MRL_CREWCALLING';
                                 userid   = 'as400.connector';
                                 passwrd  = 'notreal';
                                 sql      = 'exec Payroll.SendDataTo400 ?, ?';
                        
                                 if %parms > 0 and pServer = 'Prod';
                                   url      = 'jdbc:jtds:sqlserver://172.17.2.130:1433';   // PROD
                                 else;
                                   url      = 'jdbc:jtds:sqlserver://172.17.2.33:1433';   // DEV
                                 endif;
                        
                                 prop = JDBC_Properties();
                                 JDBC_setProp(prop: 'User'    : %trim(userid));
                                 JDBC_setProp(prop: 'Password': %trim(passwrd));
                                 JDBC_setProp(prop: 'DatabaseName': %trim(database));
                        
                                 conn = JDBC_ConnProp( %trim(driver)
                                                     : %trim(url)
                                                     : prop );
                                 JDBC_freeProp(prop);
                        
                                 if (conn = *NULL);
                                     return;
                                 endif;

                        Comment


                        • #13
                          I connected with the JDBC_Connect code - our network folks verified that for me. So I decided to switch back to the earlier code with JDBC_ConnProp in free format to see if I can code it the better way. Now I am getting this error:
                          Exception in thread "main" java.sql.SQLException: I/O Error: GSS Failed: org.ietf.jgss.GSSException, major code: 13, minor code: 0
                          major string: Invalid credentials
                          minor string: SubjectCredFinder: no JAAS Subject
                          at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.ja va:654)
                          at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(Jt dsConnection.java:371)
                          at net.sourceforge.jtds.jdbc.Driver.connect(Driver.ja va:184)
                          Caused by: java.io.IOException: GSS Failed: org.ietf.jgss.GSSException, major code: 13, minor code: 0
                          major string: Invalid credentials
                          minor string: SubjectCredFinder: no JAAS Subject
                          at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(T dsCore.java:1976)
                          at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.ja va:617)
                          I could not find the GSS Failed with invalid credentials, but I did find expired credentials - has something to do with Websphere, maybe?????

                          Comment


                          • #14
                            JDBC_Connect() works by setting the 'user' and 'password' properties, and then calling JDBC_ConnProp. So, they really are the same thing under the covers... it doesn't make sense that one would produce this error and the other would work.

                            I am not familiar with GSS or Kerberos, so not sure what would cause an error like this.

                            Comment


                            • #15
                              I'm sure it was something with the way I entered it. I used the code you sent earlier today and I just tested - got the confirmation from the network guy that he sees an SQL hit on that server.

                              So now that I am connecting, my next step would be to use code like on page 25 of your "Accessing External Databases" - right?

                              Comment

                              Working...
                              X