Guest User

Untitled

a guest
Apr 13th, 2018
101
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.49 KB | None | 0 0
  1. private static Connection getNewDBConnection() {
  2. System.out.println("************************Inside Get DB Connection**************************");
  3. Properties props = new Properties();
  4. if (connection != null)
  5. try {
  6. if (!connection.isClosed())
  7. return connection;
  8. } catch (SQLException e) {
  9. e.printStackTrace();
  10. }
  11.  
  12. try {
  13. String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  14. String url = "jdbc:sqlserver://142.168.0.112:1733;DatabaseName=OrganizationMasterDB";
  15. String user = "sa";
  16. String password = "Dsdf@123";
  17. Class.forName(driver);
  18. connection = DriverManager.getConnection(url, user, password);
  19. if (!connection.isClosed())
  20. System.out.println("---------------------DB Connection is Established----------------------");
  21. } catch (ClassNotFoundException e) {
  22. System.out.println("Class Not Found Exception Thrown ");
  23. e.printStackTrace();
  24. } catch (SQLException e) {
  25. System.out.println("SQL Exception Thrown");
  26. e.printStackTrace();
  27. }
  28.  
  29. return connection;
  30. }
  31.  
  32.  
  33. public static void insertDetailsList(PersonalLedger pl) {
  34. Connection conn = getNewDBConnection();
  35. PreparedStatement statement = null;
  36. DetailsList dl = null;
  37. int temp=0,shareAmount=0,shareBalance=0,theiftFundAmount=0,theiftFundInterest=0,GAmtDepo=0,GInterest=0,ShareWithdrawn=0;
  38. String EmNo=null,MemberNo=null, fundString = "Share",status="Unknown",remarks="OtherAmount for Share is The Withdrawn Share Amount",sql=null;
  39. boolean flag= false;
  40. Date sdate = pl.SDate,gdate=pl.Gdate,tdate=pl.TDate;
  41. EmNo = pl.EmNo;
  42. MemberNo = pl.MemberNo;
  43. shareAmount = pl.SAmtDepo;
  44. shareBalance = pl.balance;
  45. ShareWithdrawn = pl.ShareWithdrawn;
  46. theiftFundAmount = pl.TAmtDepo;
  47. theiftFundInterest = pl.TInterest;
  48. GAmtDepo = pl.GAmtDepo;
  49. GInterest = pl.GInterest;
  50. DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
  51. Calendar cal = Calendar.getInstance();
  52. try{
  53. System.out.println("*****************INSERTING SHARE FUND DETAILS******************");
  54. sql = "INSERT INTO [dbo].[DetailsList] VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
  55. statement = conn.prepareStatement(sql);
  56. statement.setString(1, pl.EmNo);
  57. statement.setString(2, pl.MemberNo);
  58. statement.setString(3,"Share");
  59. statement.setLong(4, shareAmount);
  60. /*Date share_date = (Date) formatter.parse(new Date());*/
  61. statement.setLong(5,0);
  62. statement.setLong(6,pl.ShareWithdrawn);
  63. statement.setString(7,"Unknown");
  64. statement.setString(8,"OtherAmount for Share is The Withdrawn Share Amount");
  65. statement.setDate(9, pl.SDate);
  66. statement.setDate(10,null);
  67. statement.setLong(11, shareBalance);
  68. temp = statement.executeUpdate();
  69. if (temp != 0) {
  70. flag = true;
  71. System.out.println("ROW INSERTED SUCCESSFULLY");
  72. }
  73. } catch (Exception e) {
  74. System.out.println("Exception in Insert Details List Items");
  75. e.printStackTrace();
  76. }
  77. }
  78.  
  79. Exception in Insert Details List Items
  80. com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.DetailsList'.
  81. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:215)
  82. at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
  83. at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
  84. at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
  85. at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5846)
  86. at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1719)
  87. at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
  88. at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
  89. at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
  90. at Test.insertDetailsList(Test.java:203)
  91. at Test.main(Test.java:290)
  92.  
  93. sql = "INSERT INTO [dbo].[DetailsList] VALUES "
  94. + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
  95.  
  96. sql = "INSERT INTO [OrganizationMaster].[dbo].[DetailsList] VALUES "
  97. + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  98.  
  99. use [Database Name]
  100. GO
  101. -------------------------------------------------------------------------------------------------------------------------
  102. --create new login in database for schema
  103. CREATE LOGIN [User Name] WITH PASSWORD=Nxxxxxxx
  104. GO
  105.  
  106. --create new user in database for schema
  107. CREATE USER [User Name] FOR LOGIN [User Name] WITH DEFAULT_SCHEMA=[Schema Name]
  108. GO
  109.  
  110. --grant permissions
  111. GRANT DELETE ON SCHEMA::[Schema Name] TO [User Name]
  112. GO
  113. GRANT INSERT ON SCHEMA::[Schema Name] TO [User Name]
  114. GO
  115. GRANT SELECT ON SCHEMA::[Schema Name] TO [User Name]
  116. GO
  117. GRANT UPDATE ON SCHEMA::[Schema Name] TO [User Name]
  118. GO
  119. GRANT REFERENCES ON SCHEMA::[Schema Name] TO [User Name]
  120. GO
  121. -------------------------------------------------------------------------------------------------------------------------
Add Comment
Please, Sign In to add comment