Using X++ code direct execute to sql statement
PS: the following X++ code must be run on server
wrote by Jimmy on Dec.7th 2010
1)using to ODBC connection DB
server static void Jimmy_sqlODBCConnectionDB(Args _args) { LoginProperty loginProp; ODBCConnection conn; Resultset rs01, rs02; // get record Statement st01, st02; // Create SQL Statement ResultSetMetaData metaData ; // get Record metadate like columnname. SqlStatementExecutePermission sqlSEP01,sqlSEP02; str 300 sql01 = "select Id from UserInfo"; str 300 sql02 = "select * from CustTable"; SqlSystem sqlSystem = new SqlSystem(); ; loginProp = new LoginProperty(); info(sqlSystem.loginServer());//"DGQVS004" info(sqlSystem.loginDatabase());//"AX2009DEV" loginProp.setServer(sqlSystem.loginServer()); loginProp.setDatabase(sqlSystem.loginDatabase()); //execute the first sql statement sqlSEP01 = new SqlStatementExecutePermission(sql01); sqlSEP01.assert(); conn = new ODBCConnection(loginProp); st01 = conn.createStatement(); rs01 = st01.executeQuery(sql01); while (rs01.next()) { metaData = rs01.getMetaData(); info("Column Name :" + metaData.getColumnName(1) + " Value = " + rs01.getString(1)); } CodeAccessPermission::revertAssert(); //execute the second sql statement sqlSEP02 = new SqlStatementExecutePermission(sql02); sqlSEP02.assert(); conn = new ODBCConnection(loginProp); st02 = conn.createStatement(); rs02 = st02.executeQuery(sql02); while (rs02.next()) { metaData = rs02.getMetaData(); info("Column Name :" + metaData.getColumnName(1) + " Value = " + rs02.getString(1)); } CodeAccessPermission::revertAssert(); }
2)using to common connection DB
server static void Jimmy_sqlconnectionDB(Args _args) { Connection con; Statement st; ResultSet rs; str sql; int i; ResultSetMetaData RSmetaData; SqlStatementExecutePermission sqlStatementExecutePermission; ; sql = strfmt("%1%2", "select * from CustTable "," where ACCOUNTNUM like 'Wib-%'"); sqlStatementExecutePermission = new SqlStatementExecutePermission(sql); sqlStatementExecutePermission.assert(); con = new Connection(); st = con.createStatement(); rs = st.executeQuery(sql); while (rs.next()) { RSmetaData = rs.getMetaData(); info(strfmt("%1 - %2",RSmetaData.getColumnName(1),rs.getString(1))); } CodeAccessPermission::revertAssert(); }