One Star

How to create a sql-statement within a function

Hello,

I would like to dedicate an SQL statement in a function. It should give me the current record counter from the ERP system and continue to count it. In tJavaRow i call this..

   

iNewNumber = routines.GetSageNumber.GetId(Server,Port,Database,User;Password,Ordertype);

in the function I call I will runn this

import java.sql.*;

....
    public static int GetId(String sServer, String sPort, String sDB, String sUser, String sPWD, String Numbertype)
    {
    try
    {
        int number = 2;
        String sDbFeld;
        
        switch(Numbertype)
        {
        case "RE":        /* Rechnung*/         
        case "LI":        /* Lieferschein*/
        case "BE":        /* Bestellung */
        case "AN":        /* Anfrage */
        case "AU":        /*  */
        case "GU":        /* Gutschrift */
        case "ST":        /* Storno */
        case "BS":        /*  */
        case "WE":        /*  */
        case "MA":        /*  */
        case "BA":        /*  */
        case "SO":        /*  */
        case "RA":        /* Reparaturauftrag */
        case "RL":        /*  */
        case "KE":        /*  */
        case "WD":        /*  */
        case "AF":        /*  */     
        case "WL":        /*  */     
        case "WR":        /*  */     
            /* Test bestanden jetzt den Select String zusammenbauen anzeigen */
            break;
        default:
            return -1;    /* Fehler zurückgeben */
        }
        /* Die Feldnamen bestehen immer aus der Auftragsart 2-Stellig und weiteren buchstaben */
        String myUrl = "jdbc:mysql://"+ sServer + ":" + sPort +  "? noDatetimeStringSync=true" + "/" + sDB;
        
        
        String driverClass_tMysqlInput_2 = "org.gjt.mm.mysql.Driver";
        java.lang.Class.forName(driverClass_tMysqlInput_2).newInstance();;
        
        Connection conn = DriverManager.getConnection(myUrl,sUser, sPWD );
        Statement stmt = conn.createStatement();
        
        sDbFeld = " SELECT " + Numbertype + "ENDLOS FROM " + sDB  + ".SG_AUF_NUMMER WHERE SG_Auf_Nummer_pk = 1";
        
        ResultSet rset = stmt.executeQuery(sDbFeld);
        number = rset.getInt(Numbertype + "ENDLOS FROM ");
        
        rset.close();
        
        
        System.out.println(sDbFeld);
        return number;
    }
    catch ( Exception e )
    {
        System.err.println("Got an exception! ");
        System.err.println(e.getMessage());
        return -1;
    }
    }

but when run this test I get back an error see below

Spoiler
[statistics] connecting to socket on port 3904
[statistics] connected
Got an exception! 
org.gjt.mm.mysql.Driver
neue Nummer :-1
[statistics] disconnected
Job TestID endet am 09:15 27/11/2017. [exit code=0]

The routine does not find the driver that Talend uses for the other functions. What am I doing wrong ?

1 REPLY
Moderator

Re: How to create a sql-statement within a function

Hi,

Talend Studio requires specific third-party Java libraries or database drivers (.jar files) to be installed to connect to sources and targets. Those libraries or drivers, known as external modules, can be required by some of Talend components or by some connection wizards or by both. Due to license restrictions, Talend may not be able to ship certain external modules within Talend Studio.

All external modules have been successfully installed in your studio?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.