public static Statistic GetStatistic(Context ctx, int cstId) { Statistic info = new Statistic(); using (IConnection conn = Sync.GetConnection(ctx)) { IPreparedStatement ps = conn.PrepareStatement(@" SELECT cst_id, item_kateg, month, amount_curr, amount_prev, ritem_categ.item_categ_desc FROM rstatistic JOIN ritem_categ ON ritem_categ.id = rstatistic.item_kateg WHERE cst_id = :cstId"); ps.Set("cstId", cstId); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { info.Fetch(result); } ps.Close(); conn.Release(); } return(info); }
public static User Login(Context ctx, string username, string password) { User user = null; using (IConnection conn = Sync.GetConnection(ctx)) { IPreparedStatement ps = conn.PrepareStatement(@"SELECT deal_id, user_id, login_name, user_pass, user_active FROM rusers WHERE user_active = 1 AND login_name = :login_name AND user_pass = :user_pass "); ps.Set("login_name", username); ps.Set("user_pass", password); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { user = new User(); user.deal_id = result.GetInt("deal_id"); user.user_id = result.GetInt("user_id"); user.login_name = result.GetString("login_name"); user.user_pass = result.GetString("user_pass"); } result.Close(); ps.Close(); conn.Commit(); conn.Release(); } return(user); }
/// <summary> /// TODO: add column disc_per2 into disc table /// </summary> /// <returns>The discount2.</returns> /// <param name="ctx">Context.</param> /// <param name="itemId">Item identifier.</param> /// <param name="cstId">Cst identifier.</param> public double GetDiscount2(Context ctx, long itemId, long cstId) { using (IConnection conn = Sync.GetConnection(ctx)) { const string query = @" select rdisc_per from rdisc where cst_kat_disc = (select cst_kat_disc from rcustomer where id = :cst_id) and item_ctg_disc = (select item_ctg_disc from ritems where id = :item_id) "; IPreparedStatement ps = conn.PrepareStatement(query); ps.Set("item_id", itemId); ps.Set("cst_id", cstId); double discount = 0; IResultSet rs = ps.ExecuteQuery(); if (rs.Next()) { discount = rs.GetDouble("rdisc_per"); } ps.Close(); conn.Commit(); conn.Release(); return(discount); } }
internal static void FetchDetails(TransHed transHed, IConnection conn) { IPreparedStatement ps = conn.PrepareStatement(@" SELECT rtrans_det.id, rtrans_det.htrn_id, rtrans_det.dtrn_num, rtrans_det.item_id, rtrans_det.qty1, rtrans_det.unit_price, rtrans_det.disc_line1, rtrans_det.net_value, rtrans_det.vat_value, ritems.item_cod, ritems.item_desc, ritems.item_vat FROM rtrans_det JOIN ritems ON ritems.id = rtrans_det.item_id WHERE htrn_id = :htrn_id ORDER BY rtrans_det.id"); ps.Set("htrn_id", transHed.HtrnId); IResultSet result = ps.ExecuteQuery(); if (transHed.TransDetList == null) { transHed.TransDetList = new TransDetList(); } while (result.Next()) { TransDet d = new TransDet(); d.Fetch(result); transHed.TransDetList.Add(d); } result.Close(); ps.Close(); }
public static Category GetCategory(Context ctx, long categId, int categTbl) { Category info = new Category(); using (IConnection conn = Sync.GetConnection(ctx)) { string tableName = "ritem_categ"; if (categTbl == 2) { tableName += "2"; } IPreparedStatement ps = conn.PrepareStatement(@"SELECT id, item_categ_desc FROM " + tableName + " WHERE id = :CategId"); ps.Set("categId", categId); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { info.Id = result.GetInt("id"); info.ItemCategDesc = result.GetString("item_categ_desc"); } result.Close(); ps.Close(); conn.Release(); } return(info); }
internal static TransHedList GetTransHedList(Context ctx) { TransHedList headers = new TransHedList(); using (IConnection conn = Sync.GetConnection(ctx)) { IPreparedStatement ps = conn.PrepareStatement(@" SELECT rtrans_hed.id, rtrans_hed.cust_id, trans_date, docnum, htrn_explanation, rcustomer.cst_desc FROM rtrans_hed LEFT OUTER JOIN rcustomer ON rcustomer.id = rtrans_hed.cust_id "); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { TransHed header = new TransHed(); header.Fetch(result); headers.Add(header); } result.Close(); ps.Close(); foreach (TransHed h in headers) { TransHed.FetchDetails(h, conn); } conn.Release(); } return(headers); }
public static StatisticList GetStatisticListThisMonth(Context ctx, long cstId) { StatisticList items = new StatisticList(); if (cstId == 0) { return(items); } using (IConnection conn = Sync.GetConnection(ctx)) { string query = @" SELECT cst_id, item_kateg, month, amount_curr, amount_prev, ritem_categ.item_categ_desc FROM rstatistic JOIN ritem_categ ON ritem_categ.id = rstatistic.item_kateg"; if (cstId > 0) { query += @" WHERE cst_id = :cstId AND month = " + System.DateTime.Now.Month; } IPreparedStatement ps = conn.PrepareStatement(query); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { Statistic s = new Statistic(); s.Fetch(result); items.Add(s); } ps.Close(); conn.Release(); } return(items); }
/// <exception cref="Java.Sql.SQLException"/> public static IList <Pair <string, long> > GetCounts(ICollection <string> strs) { Connect(); IList <Pair <string, long> > counts = new List <Pair <string, long> >(); string query = string.Empty; foreach (string str in strs) { str = str.Trim(); int ngram = str.Split("\\s+").Length; string table = tablenamePrefix + ngram; if (!ExistsTable(table)) { counts.Add(new Pair(str, (long)-1)); continue; } string phrase = EscapeString(str); query += "select count from " + table + " where phrase='" + phrase + "';"; } if (query.IsEmpty()) { return(counts); } IPreparedStatement stmt = connection.PrepareStatement(query); bool isresult = stmt.Execute(); IResultSet rs; IEnumerator <string> iter = strs.GetEnumerator(); do { rs = stmt.GetResultSet(); string ph = iter.Current; if (rs.Next()) { counts.Add(new Pair(ph, rs.GetLong("count"))); } else { counts.Add(new Pair(ph, (long)-1)); } isresult = stmt.GetMoreResults(); }while (isresult); System.Diagnostics.Debug.Assert((counts.Count == strs.Count)); return(counts); }
public static Android.Graphics.Bitmap GetItemImage(Context ctx, int itemID) { Android.Graphics.Bitmap res = null; using (IConnection conn = Sync.GetConnection(ctx)) { IPreparedStatement ps = conn.PrepareStatement(@"SELECT item_image FROM ritems WHERE id = :ItemID"); ps.Set("ItemID", itemID.ToString()); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { byte[] signatureBytes = result.GetBytes("item_image"); try { if (signatureBytes.Length > 0) { /*Android.Graphics.Bitmap bmp = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes, * 0, signatureBytes.Length); * res = Android.Graphics.Bitmap.CreateScaledBitmap(bmp,256,256,true); * bmp.Recycle();*/ res = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes, 0, signatureBytes.Length); } else { res = null; } } catch (Exception ex) { res = null; } } result.Close(); ps.Close(); conn.Release(); } return(res); }
public override void AddPatterns(string id, IDictionary <int, ICollection <E> > p) { try { IPreparedStatement pstmt = null; IConnection conn = null; conn = SQLConnection.GetConnection(); pstmt = GetPreparedStmt(conn); AddPattern(id, p, pstmt); pstmt.Execute(); conn.Commit(); pstmt.Close(); conn.Close(); } catch (Exception e) { throw new Exception(e); } }
public static CustomerInfoList GetCustomerInfoList(Context ctx, Criteria crit) { CustomerInfoList customers = new CustomerInfoList(); using (IConnection conn = Sync.GetConnection(ctx)) { string query = @" SELECT TOP 100 id, cst_cod, cst_desc FROM rcustomer WHERE 1=1 "; if (crit.CustCode != "") { query += " AND cst_cod like \'" + crit.CustCode + "%\'"; } if (crit.CustName != "") { query += " AND cst_desc like \'" + crit.CustName + "%\'"; } query += " ORDER BY cst_desc "; Log.Debug("GetCustomerInfoList", query); IPreparedStatement ps = conn.PrepareStatement(query); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { CustomerInfo customer = new CustomerInfo(); customer.CustID = result.GetInt("id"); customer.Code = result.GetString("cst_cod"); customer.Name = result.GetString("cst_desc"); customers.Add(customer); } result.Close(); ps.Close(); conn.Release(); } return(customers); }
public static TransCustList GetTransCustList(Context ctx, Criteria c) { TransCustList items = new TransCustList(); using (IConnection conn = Sync.GetConnection(ctx)) { string query = @"SELECT id, cst_id, vouch_id, voser_id, docnum, dtrn_type, dtrn_net_value, dtrn_vat_value, dtrn_date, htrn_id FROM rTransCust WHERE 1 =1 "; if (c.CustId > 0) { query += " AND cst_id = :cst_id"; } IPreparedStatement ps = conn.PrepareStatement(query); ps.Set("cst_id", c.CustId); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { items.Add(TransCust.GetTransCust(result)); } ps.Close(); conn.Release(); } return(items); }
public static TransHed GetTransHed(Context ctx, double htrnId) { TransHed transHed = new TransHed(); if (htrnId == 0) { return(transHed); } using (IConnection conn = Sync.GetConnection(ctx)) { string query = @" SELECT rtrans_hed.id, cust_id, trans_date, vouch_id, voser_id, docnum, htrn_explanation, rcustomer.cst_desc FROM rtrans_hed LEFT OUTER JOIN rcustomer ON rcustomer.id = rtrans_hed.cust_id WHERE rtrans_hed.id = :htrnId "; IPreparedStatement ps = conn.PrepareStatement(query); ps.Set("htrnId", htrnId); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { transHed.Fetch(result); } result.Close(); ps.Close(); FetchDetails(transHed, conn); conn.Commit(); conn.Release(); } return(transHed); }
public static CustomerInfo GetCustomer(Context ctx, string code) { CustomerInfo info = new CustomerInfo(); if (code == "") { return(info); } using (IConnection conn = Sync.GetConnection(ctx)) { IPreparedStatement ps = conn.PrepareStatement(@"SELECT id, cst_cod, cst_desc, cst_ypol, cst_kat_disc, cst_tax_num, cst_trus_id, cst_addr, cst_city, cst_zip, cst_phone, cst_gsm, cst_comments FROM rcustomer WHERE cst_cod = :Code" ); ps.Set("Code", code); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { info.CustID = result.GetInt("id"); info.Code = result.GetString("cst_cod"); info.Name = result.GetString("cst_desc"); info.CustAddress = result.GetString("cst_addr"); info.CustTaxNum = result.GetString("cst_tax_num"); info.CustDebt = result.GetDouble("cst_ypol"); info.CustPhone = result.GetString("cst_phone"); info.IsNew = false; } result.Close(); ps.Close(); conn.Commit(); conn.Release(); } return(info); }
public override void AddPatterns(IDictionary <string, IDictionary <int, ICollection <E> > > pats) { try { IConnection conn = null; IPreparedStatement pstmt = null; conn = SQLConnection.GetConnection(); pstmt = GetPreparedStmt(conn); foreach (KeyValuePair <string, IDictionary <int, ICollection <E> > > en in pats) { AddPattern(en.Key, en.Value, pstmt); pstmt.AddBatch(); } pstmt.ExecuteBatch(); conn.Commit(); pstmt.Close(); conn.Close(); } catch (Exception e) { throw new Exception(e); } }
public static CategoryList GetCategoryList(Context ctx, int categTbl) { CategoryList items = new CategoryList(); using (IConnection conn = Sync.GetConnection(ctx)) { string tableName = "ritem_categ"; if (categTbl == 2) { tableName += "2"; } IPreparedStatement ps = conn.PrepareStatement(@"SELECT id, item_categ_desc FROM " + tableName); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { items.Add(new Category() { Id = result.GetInt("id"), ItemCategDesc = result.GetString("item_categ_desc") } ); } result.Close(); ps.Close(); conn.Release(); } return(items); }
public static TransCustList GetTransCustListStatistic(Context ctx, Criteria c) { TransCustList items = new TransCustList(); using (IConnection conn = Sync.GetConnection(ctx)) { string dateCondition = ""; if (c.DateFrom > DateTime.MinValue) { dateCondition += " AND date(dtrn_date) >= :date_from "; } if (c.DateTo > DateTime.MinValue) { dateCondition += " AND date(dtrn_date) <= :date_to "; } string query = @" SELECT cst_id, sum(case when dtrn_type = 1 " + dateCondition + @" then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as credit, sum(case when dtrn_type = 2 " + dateCondition + @" then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as debit, sum(case when dtrn_type = 1 then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) - sum(case when dtrn_type = 2 then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as crdb, rcustomer.cst_desc FROM rtranscust JOIN rcustomer ON rcustomer.id = cst_id WHERE 1 = 1 "; if (c.CustId > 0) { query += " AND cst_id = :cst_id "; } if (c.CustName != "") { query += " AND rcustomer.cst_desc like :cst_desc "; } query += @" group by cst_id, rcustomer.cst_desc "; IPreparedStatement ps = conn.PrepareStatement(query); if (c.CustId > 0) { ps.Set("cst_id", c.CustId); } if (c.DateFrom > DateTime.MinValue) { ps.Set("date_from", c.DateFrom.Date.ToString("yyyy-MM-dd HH:mm:ss")); } if (c.DateTo > DateTime.MinValue) { ps.Set("date_to", c.DateTo.Date.ToString("yyyy-MM-dd HH:mm:ss")); } if (c.CustName != "") { ps.Set("cst_desc", c.CustName + "%"); } // Log.Debug("rtranscust", query); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { items.Add(TransCust.GetTransCustStat(result)); } ps.Close(); conn.Release(); } return(items); }
public static ItemInfo GetItem(Context ctx, decimal itemID, bool loadImage) { ItemInfo info = new ItemInfo(); using (IConnection conn = Sync.GetConnection(ctx)) { /*IPreparedStatement ps = conn.PrepareStatement(@"SELECT item_id, * item_cod, * item_desc, * item_long_des, * item_ret_val1, * item_sale_val1 , * item_buy_val1 * FROM items WHERE item_id = :ItemID");*/ IPreparedStatement ps = conn.PrepareStatement(@"SELECT id, item_cod, item_desc, item_alter_desc, unit_price, item_qty_left , item_vat, item_ctg_id, item_ctg_disc, item_image FROM ritems WHERE id = :ItemID"); ps.Set("ItemID", itemID.ToString()); IResultSet result = ps.ExecuteQuery(); if (result.Next()) { /*info.ItemId = Convert.ToInt64(result.GetDouble("item_id")); * info.item_cod = result.GetString("item_cod"); * info.item_desc = result.GetString("item_desc"); * info.item_long_desc = result.GetString("item_long_des"); * info.item_ret_val1 = Convert.ToDecimal(result.GetDouble("item_ret_val1")); * info.item_sale_val1 = Convert.ToDecimal(result.GetDouble("item_sale_val1")); * info.item_buy_val1 = Convert.ToDecimal(result.GetDouble("item_buy_val1"));*/ //info.ItemId = Convert.ToInt64(result.GetDouble("id")); info.ItemId = result.GetInt("id"); info.item_cod = result.GetString("item_cod"); info.ItemDesc = result.GetString("item_desc"); info.item_long_desc = result.GetString("item_alter_desc"); info.ItemSaleVal1 = Convert.ToDecimal(result.GetDouble("unit_price")); info.ItemQtyLeft = Convert.ToDecimal(result.GetDouble("item_qty_left")); info.ItemVatId = result.GetInt("item_vat"); if (loadImage) { byte[] signatureBytes = result.GetBytes("item_image"); try { if (signatureBytes.Length > 0) { info.ItemImage = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes, 0, signatureBytes.Length); } else { info.ItemImage = null; } } catch (Exception ex) { info.ItemImage = null; } } Log.Debug("item_vat", "item_vat=" + info.ItemVatId); } result.Close(); ps.Close(); conn.Release(); } return(info); }
public void Connect() { Java.Sql.IConnection con = null; try { var driver = new Net.Sourceforge.Jtds.Jdbc.Driver(); String username = "******"; String password = "******"; String address = "192.168.1.101"; String port = "1433"; String database = "Database"; String connString = String.Format("jdbc:jtds:sqlserver://{0}:{1}/{2};user={3};password={4}", address, port, database, username, password); con = DriverManager.GetConnection(connString, username, password); IPreparedStatement stmt = null; try { //Prepared statement stmt = con.PrepareStatement("SELECT * FROM Users WHERE Id = ? AND Name = ?"); stmt.SetLong(1, 1); stmt.SetString(2, "John Doe"); stmt.Execute(); RunOnUiThread(() => Toast.MakeText(this, "SUCCESS!", ToastLength.Short).Show()); IResultSet rs = stmt.ResultSet; IResultSetMetaData rsmd = rs.MetaData; PrintColumnTypes.PrintColTypes(rsmd); Console.WriteLine(""); int numberOfColumns = rsmd.ColumnCount; for (int i = 1; i <= numberOfColumns; i++) { if (i > 1) { Console.Write(", "); } String columnName = rsmd.GetColumnName(i); Console.Write(columnName); } Console.WriteLine(""); while (rs.Next()) { for (int i = 1; i <= numberOfColumns; i++) { if (i > 1) { Console.Write(", "); } String columnValue = rs.GetString(i); Console.Write(columnValue); } Console.WriteLine(""); } stmt.Close(); con.Close(); } catch (Exception e) { RunOnUiThread(() => Toast.MakeText(this, e.Message, ToastLength.Long).Show()); Console.WriteLine(e.StackTrace); stmt.Close(); } con.Close(); } catch (Exception e) { Console.WriteLine(e.StackTrace); RunOnUiThread(() => Toast.MakeText(this, e.Message, ToastLength.Long).Show()); } RunOnUiThread(() => _button.Enabled = true); }
// // @Override // public ConcurrentHashIndex<SurfacePattern> readPatternIndex(String dir){ // //dir parameter is not used! // try{ // Connection conn = SQLConnection.getConnection(); // //Map<Integer, Set<Integer>> pats = new ConcurrentHashMap<Integer, Set<Integer>>(); // String query = "Select index from " + patternindicesTable + " where tablename=\'" + tableName + "\'"; // Statement stmt = conn.createStatement(); // ResultSet rs = stmt.executeQuery(query); // ConcurrentHashIndex<SurfacePattern> index = null; // if(rs.next()){ // byte[] st = (byte[]) rs.getObject(1); // ByteArrayInputStream baip = new ByteArrayInputStream(st); // ObjectInputStream ois = new ObjectInputStream(baip); // index = (ConcurrentHashIndex<SurfacePattern>) ois.readObject(); // } // assert index != null; // return index; // }catch(SQLException e){ // throw new RuntimeException(e); // } catch (ClassNotFoundException e) { // throw new RuntimeException(e); // } catch (IOException e) { // throw new RuntimeException(e); // } // } // // @Override // public void savePatternIndex(ConcurrentHashIndex<SurfacePattern> index, String file) { // try { // createUpsertFunctionPatternIndex(); // Connection conn = SQLConnection.getConnection(); // PreparedStatement st = conn.prepareStatement("select upsert_patternindex(?,?)"); // st.setString(1,tableName); // ByteArrayOutputStream baos = new ByteArrayOutputStream(); // ObjectOutputStream oos = new ObjectOutputStream(baos); // oos.writeObject(index); // byte[] patsAsBytes = baos.toByteArray(); // ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes); // st.setBinaryStream(2, bais, patsAsBytes.length); // st.execute(); // st.close(); // conn.close(); // System.out.println("Saved the pattern hash index for " + tableName + " in DB table " + patternindicesTable); // }catch (SQLException e){ // throw new RuntimeException(e); // } catch (IOException e) { // throw new RuntimeException(e); // } // } //batch processing below is copied from Java Ranch //TODO: make this into an iterator!! public override IDictionary <string, IDictionary <int, ICollection <E> > > GetPatternsForAllTokens(ICollection <string> sampledSentIds) { try { IDictionary <string, IDictionary <int, ICollection <E> > > pats = new Dictionary <string, IDictionary <int, ICollection <E> > >(); IConnection conn = SQLConnection.GetConnection(); IEnumerator <string> iter = sampledSentIds.GetEnumerator(); int totalNumberOfValuesLeftToBatch = sampledSentIds.Count; while (totalNumberOfValuesLeftToBatch > 0) { int batchSize = SingleBatch; if (totalNumberOfValuesLeftToBatch >= LargeBatch) { batchSize = LargeBatch; } else { if (totalNumberOfValuesLeftToBatch >= MediumBatch) { batchSize = MediumBatch; } else { if (totalNumberOfValuesLeftToBatch >= SmallBatch) { batchSize = SmallBatch; } } } totalNumberOfValuesLeftToBatch -= batchSize; StringBuilder inClause = new StringBuilder(); for (int i = 0; i < batchSize; i++) { inClause.Append('?'); if (i != batchSize - 1) { inClause.Append(','); } } IPreparedStatement stmt = conn.PrepareStatement("select sentid, patterns from " + tableName + " where sentid in (" + inClause.ToString() + ")"); for (int i_1 = 0; i_1 < batchSize && iter.MoveNext(); i_1++) { stmt.SetString(i_1 + 1, iter.Current); } // or whatever values you are trying to query by stmt.Execute(); IResultSet rs = stmt.GetResultSet(); while (rs.Next()) { string sentid = rs.GetString(1); byte[] st = (byte[])rs.GetObject(2); ByteArrayInputStream baip = new ByteArrayInputStream(st); ObjectInputStream ois = new ObjectInputStream(baip); pats[sentid] = (IDictionary <int, ICollection <E> >)ois.ReadObject(); } } conn.Close(); return(pats); } catch (Exception e) { throw new Exception(e); } }
/* * public void addPatterns(String id, Map<Integer, Set<Integer>> p, PreparedStatement pstmt) throws IOException, SQLException { * for (Map.Entry<Integer, Set<Integer>> en2 : p.entrySet()) { * addPattern(id, en2.getKey(), en2.getValue(), pstmt); * if(useDBForTokenPatterns) * pstmt.addBatch(); * } * } */ /* * public void addPatterns(String sentId, int tokenId, Set<Integer> patterns) throws SQLException, IOException{ * PreparedStatement pstmt = null; * Connection conn= null; * if(useDBForTokenPatterns) { * conn = SQLConnection.getConnection(); * pstmt = getPreparedStmt(conn); * } * * addPattern(sentId, tokenId, patterns, pstmt); * * if(useDBForTokenPatterns){ * pstmt.execute(); * conn.commit(); * pstmt.close(); * conn.close(); * } * } */ /* * private void addPattern(String sentId, int tokenId, Set<Integer> patterns, PreparedStatement pstmt) throws SQLException, IOException { * * if(pstmt != null){ * // ByteArrayOutputStream baos = new ByteArrayOutputStream(); * // ObjectOutputStream oos = new ObjectOutputStream(baos); * // oos.writeObject(patterns); * // byte[] patsAsBytes = baos.toByteArray(); * // ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes); * // pstmt.setBinaryStream(1, bais, patsAsBytes.length); * // pstmt.setObject(2, sentId); * // pstmt.setInt(3, tokenId); * // pstmt.setString(4,sentId); * // pstmt.setInt(5, tokenId); * // ByteArrayOutputStream baos2 = new ByteArrayOutputStream(); * // ObjectOutputStream oos2 = new ObjectOutputStream(baos2); * // oos2.writeObject(patterns); * // byte[] patsAsBytes2 = baos2.toByteArray(); * // ByteArrayInputStream bais2 = new ByteArrayInputStream(patsAsBytes2); * // pstmt.setBinaryStream(6, bais2, patsAsBytes2.length); * // pstmt.setString(7,sentId); * // pstmt.setInt(8, tokenId); * * ByteArrayOutputStream baos = new ByteArrayOutputStream(); * ObjectOutputStream oos = new ObjectOutputStream(baos); * oos.writeObject(patterns); * byte[] patsAsBytes = baos.toByteArray(); * ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes); * pstmt.setBinaryStream(3, bais, patsAsBytes.length); * pstmt.setObject(1, sentId); * pstmt.setInt(2, tokenId); * * * } else{ * if(!patternsForEachToken.containsKey(sentId)) * patternsForEachToken.put(sentId, new ConcurrentHashMap<Integer, Set<Integer>>()); * patternsForEachToken.get(sentId).put(tokenId, patterns); * } * }*/ /// <exception cref="Java.Sql.SQLException"/> /// <exception cref="System.IO.IOException"/> private void AddPattern(string sentId, IDictionary <int, ICollection <E> > patterns, IPreparedStatement pstmt) { if (pstmt != null) { ByteArrayOutputStream baos = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(baos); oos.WriteObject(patterns); byte[] patsAsBytes = baos.ToByteArray(); ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes); pstmt.SetBinaryStream(2, bais, patsAsBytes.Length); pstmt.SetObject(1, sentId); } }
public static void LoadAdapterItems(Context ctx, int page, ArrayAdapter <ItemInfo> adapter, Criteria c) { using (IConnection conn = Sync.GetConnection(ctx)) { string joinLastDate = ""; string fields = ""; if (c.CstId > 0) { fields += @", ritemlast.last_date";//OUTER joinLastDate = @" LEFT JOIN ritemlast ON ritemlast.item_id = ritems.id AND ritemlast.cst_id = " + c.CstId; } int offset = 1 + page * 30; string query = @" SELECT TOP 30 START AT " + offset + @" ritems.ID, ritems.item_cod, ritems.item_desc, ritems.item_image, ritems.item_qty_left " + fields + @" FROM ritems" + joinLastDate + @" WHERE 1 = 1 "; if (c.ItemDesc != "") { query += " AND ritems.item_desc like \'" + c.ItemDesc + "%\'"; } if (c.Category1 != 0) { query += " AND ritems.item_ctg_id = " + c.Category1; } if (c.Category2 != 0) { query += " AND ritems.item_ctg2_id = " + c.Category2; } if (c.RetVal != 0) { // query += " AND ritems.item_qty_left = " + c.RetVal; } query += " ORDER BY ritems.item_desc "; Log.Debug("select items", query); IPreparedStatement ps = conn.PrepareStatement(query); IResultSet result = ps.ExecuteQuery(); while (result.Next()) { ItemInfo item = new ItemInfo() { ItemId = result.GetInt("id"), item_cod = result.GetString("item_cod"), ItemDesc = result.GetString("item_desc"), ItemQtyLeft = Convert.ToDecimal(result.GetDouble("item_qty_left")) }; byte[] signatureBytes = result.GetBytes("item_image"); try { if (signatureBytes.Length > 0) { Android.Graphics.Bitmap img = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes, 0, signatureBytes.Length); item.ItemImage = Android.Graphics.Bitmap.CreateScaledBitmap(img, 64, 64, true); img.Recycle(); img = null; } else { item.ItemImage = null; } } catch (Exception ex) { item.ItemImage = null; } if (c.CstId > 0) { item.ItemLastBuyDate = Common.JavaDateToDatetime(result.GetDate("last_date")); } adapter.Add(item); } result.Close(); ps.Close(); conn.Release(); } }
public void LoadItems(Context ctx) { Criteria c = CurrentCriteria; using (IConnection conn = Sync.GetConnection(ctx)) { // IPreparedStatement ps1 = conn.PrepareStatement ("select * from ritemlast"); // // IResultSet result1 = ps1.ExecuteQuery (); // // while (result1.Next()) { // Log.Debug ("", result1.GetInt (0) + " " + result1.GetInt (1)); // } string joinLastDate = ""; string fields = ""; if (c.CstId > 0) { fields += @", ritemlast.last_date"; joinLastDate = @" LEFT OUTER JOIN ritemlast ON ritemlast.item_id = ritems.id AND ritemlast.cst_id = " + c.CstId; } string query = @" SELECT TOP 30 ritems.ID, ritems.item_cod, ritems.item_desc, ritems.item_image, ritems.item_qty_left " + fields + @" FROM ritems" + joinLastDate + @" WHERE 1 = 1 "; if (c.ItemDesc != "") { // query += " AND ritems.item_desc like \'" + c.ItemDesc + "%\'"; query += " AND ritems.item_desc like :ItemDesc "; } if (c.Category1 != 0) { query += " AND ritems.item_ctg_id = " + c.Category1; } if (c.Category2 != 0) { query += " AND ritems.item_ctg2_id = " + c.Category2; } if (c.RetVal != 0) { // query += " AND ritems.item_qty_left = " + c.RetVal; } query += " ORDER BY ritems.item_desc "; IPreparedStatement ps = conn.PrepareStatement(query); if (c.ItemDesc != "") { ps.Set("ItemDesc", c.ItemDesc); } IResultSet result = ps.ExecuteQuery(); while (result.Next()) { ItemInfo item = new ItemInfo() { ItemId = result.GetInt("id"), item_cod = result.GetString("item_cod"), ItemDesc = result.GetString("item_desc"), ItemQtyLeft = Convert.ToDecimal(result.GetDouble("item_qty_left")) }; byte[] signatureBytes = result.GetBytes("item_image"); try { if (signatureBytes.Length > 0) { Android.Graphics.Bitmap img = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes, 0, signatureBytes.Length); item.ItemImage = Android.Graphics.Bitmap.CreateScaledBitmap(img, 64, 64, true); img.Recycle(); img = null; } else { item.ItemImage = null; } } catch (Exception ex) { item.ItemImage = null; } if (c.CstId > 0) { item.ItemLastBuyDate = Common.JavaDateToDatetime(result.GetDate("last_date")); } lastLoadedID = item.ItemId; Add(item); } result.Close(); ps.Close(); conn.Release(); } }