{"action":"created","comment":{"url":"https://api.github.com/repos/dimagi/commcare/pulls/comments/38750337","id":38750337,"diff_hunk":"@@ -0,0 +1,227 @@\n+package org.commcare.api.persistence;\n+\n+import org.commcare.modern.util.Pair;\n+import org.commcare.modern.database.*;\n+import org.javarosa.core.services.storage.Persistable;\n+\n+import java.io.ByteArrayInputStream;\n+import java.sql.Blob;\n+import java.sql.Connection;\n+import java.sql.PreparedStatement;\n+import java.sql.ResultSet;\n+import java.sql.SQLException;\n+import java.util.HashMap;\n+import java.util.List;\n+\n+/**\n+ * Set of Sql utility methods for clients running modern, non-Android Java (where we use cursors\n+ * in the place of prepared statements)\n+ *\n+ * Created by wpride1 on 8/11/15.\n+ */\n+public class SqlHelper {\n+    public static void dropTable(Connection c, String storageKey){\n+        String sqlStatement = \"DROP TABLE IF EXISTS \" + storageKey;\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(sqlStatement);\n+            preparedStatement.execute();\n+        } catch (SQLException e) {\n+            System.out.println(\"didn't drop table\");\n+        }\n+    }\n+\n+    public static ResultSet executeSql(Connection c, String sqlQuery){\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(sqlQuery);\n+            ResultSet rs = preparedStatement.executeQuery();\n+            return rs;\n+        } catch (SQLException e) {\n+            e.printStackTrace();\n+            return null;\n+        }\n+    }\n+\n+    public static byte[] getRecordForValue(Connection c, String storageKey, String id){\n+        String sqlStatement = DatabaseHelper.getRecordForValueSelectString(storageKey, id);\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(sqlStatement);\n+            ResultSet rs = preparedStatement.executeQuery();\n+            byte[] bytes = rs.getBytes(1);\n+            return bytes;\n+        } catch (SQLException e) {\n+            e.printStackTrace();\n+            return null;\n+        }\n+    }\n+\n+    public static void createTable(Connection c, String storageKey, Persistable p){\n+        String sqlStatement = DatabaseHelper.getTableCreateString(storageKey, p);\n+        PreparedStatement preparedStatement = null;\n+        try {\n+            preparedStatement = c.prepareStatement(sqlStatement);\n+            preparedStatement.execute();\n+        } catch (SQLException e) {\n+            //fail silently, expected sometimes\n+        } finally{\n+            if (preparedStatement != null) {\n+                try {\n+                    preparedStatement.close();\n+                } catch(SQLException e){\n+\n+                }\n+            }\n+        }\n+    }\n+\n+    public static ResultSet selectForId(Connection c, String storageKey, int id){\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(\"SELECT * FROM \" + storageKey + \" WHERE \"\n+                    + DatabaseHelper.ID_COL + \" = ?;\");\n+            preparedStatement.setInt(1, id);\n+            ResultSet rs = preparedStatement.executeQuery();\n+            return rs;\n+\n+        } catch (SQLException e) {\n+            e.printStackTrace();\n+            return null;\n+        }\n+    }\n+\n+    public static ResultSet selectFromTable(Connection c, String storageKey, String[] fields, String[]values, Persistable p){\n+        org.commcare.modern.database.TableBuilder mTableBuilder = new org.commcare.modern.database.TableBuilder(storageKey);\n+        mTableBuilder.addData(p);\n+        Pair<String, String[]> mPair = DatabaseHelper.createWhere(fields, values, p);\n+\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(\"SELECT * FROM \" + storageKey + \" WHERE \" + mPair.first +\";\");\n+            for(int i=0; i<mPair.second.length; i++){\n+                preparedStatement.setString(i+1, mPair.second[i]);\n+            }\n+            ResultSet rs = preparedStatement.executeQuery();\n+            return rs;\n+\n+        } catch (SQLException e) {\n+            e.printStackTrace();\n+            return null;\n+        }\n+    }\n+\n+    public static int insertToTable(Connection c, String storageKey, Persistable p){\n+\n+        Pair<String, List<Object>> mPair = DatabaseHelper.getTableInsertData(storageKey, p);\n+\n+        try {\n+            PreparedStatement preparedStatement = c.prepareStatement(mPair.first);\n+            for(int i=0; i<mPair.second.size(); i++){\n+                Object obj = mPair.second.get(i);\n+\n+                if(obj instanceof String){\n+                    preparedStatement.setString(i + 1, (String) obj);\n+                } else if(obj instanceof Blob){\n+                    preparedStatement.setBlob(i+1, (Blob) obj);\n+                } else if(obj instanceof Integer){\n+                    preparedStatement.setInt(i + 1, ((Integer) obj).intValue());\n+                } else if(obj instanceof byte[]){\n+                    preparedStatement.setBinaryStream(i+1,new ByteArrayInputStream((byte[]) obj), ((byte[]) obj).length);\n+                }\n+            }\n+            int affectedRows = preparedStatement.executeUpdate();\n+\n+            if (affectedRows == 0) {\n+                throw new SQLException(\"Creating user failed, no rows affected.\");\n+            }\n+\n+            try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {","path":"api/org/commcare/api/persistence/SqlHelper.java","position":134,"original_position":134,"commit_id":"4f4776c04e64b11dc45888cfcd191d5c121692b8","original_commit_id":"4f4776c04e64b11dc45888cfcd191d5c121692b8","user":{"login":"phillipm","id":94817,"avatar_url":"https://avatars.githubusercontent.com/u/94817?v=3","gravatar_id":"","url":"https://api.github.com/users/phillipm","html_url":"https://github.com/phillipm","followers_url":"https://api.github.com/users/phillipm/followers","following_url":"https://api.github.com/users/phillipm/following{/other_user}","gists_url":"https://api.github.com/users/phillipm/gists{/gist_id}","starred_url":"https://api.github.com/users/phillipm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/phillipm/subscriptions","organizations_url":"https://api.github.com/users/phillipm/orgs","repos_url":"https://api.github.com/users/phillipm/repos","events_url":"https://api.github.com/users/phillipm/events{/privacy}","received_events_url":"https://api.github.com/users/phillipm/received_events","type":"User","site_admin":false},"body":"Does this file need to be J2ME compat?\r\nRegardless, Try-with-resource requires Android API 19, so you probably need to explicitly close the result set using a finally block.","created_at":"2015-09-04T13:20:41Z","updated_at":"2015-09-04T13:20:41Z","html_url":"https://github.com/dimagi/commcare/pull/101#discussion_r38750337","pull_request_url":"https://api.github.com/repos/dimagi/commcare/pulls/101","_links":{"self":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/comments/38750337"},"html":{"href":"https://github.com/dimagi/commcare/pull/101#discussion_r38750337"},"pull_request":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101"}}},"pull_request":{"url":"https://api.github.com/repos/dimagi/commcare/pulls/101","id":39971873,"html_url":"https://github.com/dimagi/commcare/pull/101","diff_url":"https://github.com/dimagi/commcare/pull/101.diff","patch_url":"https://github.com/dimagi/commcare/pull/101.patch","issue_url":"https://api.github.com/repos/dimagi/commcare/issues/101","number":101,"state":"open","locked":false,"title":"SQLite API","user":{"login":"wpride","id":2293064,"avatar_url":"https://avatars.githubusercontent.com/u/2293064?v=3","gravatar_id":"","url":"https://api.github.com/users/wpride","html_url":"https://github.com/wpride","followers_url":"https://api.github.com/users/wpride/followers","following_url":"https://api.github.com/users/wpride/following{/other_user}","gists_url":"https://api.github.com/users/wpride/gists{/gist_id}","starred_url":"https://api.github.com/users/wpride/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wpride/subscriptions","organizations_url":"https://api.github.com/users/wpride/orgs","repos_url":"https://api.github.com/users/wpride/repos","events_url":"https://api.github.com/users/wpride/events{/privacy}","received_events_url":"https://api.github.com/users/wpride/received_events","type":"User","site_admin":false},"body":"","created_at":"2015-07-14T22:51:17Z","updated_at":"2015-09-04T13:20:41Z","closed_at":null,"merged_at":null,"merge_commit_sha":"6f72928f3c79687cd5e06f248efa32c09a67d115","assignee":null,"milestone":{"url":"https://api.github.com/repos/dimagi/commcare/milestones/2","html_url":"https://github.com/dimagi/commcare/milestones/commcare_2.24","labels_url":"https://api.github.com/repos/dimagi/commcare/milestones/2/labels","id":1276471,"number":2,"title":"commcare_2.24","description":null,"creator":{"login":"phillipm","id":94817,"avatar_url":"https://avatars.githubusercontent.com/u/94817?v=3","gravatar_id":"","url":"https://api.github.com/users/phillipm","html_url":"https://github.com/phillipm","followers_url":"https://api.github.com/users/phillipm/followers","following_url":"https://api.github.com/users/phillipm/following{/other_user}","gists_url":"https://api.github.com/users/phillipm/gists{/gist_id}","starred_url":"https://api.github.com/users/phillipm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/phillipm/subscriptions","organizations_url":"https://api.github.com/users/phillipm/orgs","repos_url":"https://api.github.com/users/phillipm/repos","events_url":"https://api.github.com/users/phillipm/events{/privacy}","received_events_url":"https://api.github.com/users/phillipm/received_events","type":"User","site_admin":false},"open_issues":2,"closed_issues":0,"state":"open","created_at":"2015-08-27T20:11:31Z","updated_at":"2015-08-27T20:14:40Z","due_on":null,"closed_at":null},"commits_url":"https://api.github.com/repos/dimagi/commcare/pulls/101/commits","review_comments_url":"https://api.github.com/repos/dimagi/commcare/pulls/101/comments","review_comment_url":"https://api.github.com/repos/dimagi/commcare/pulls/comments{/number}","comments_url":"https://api.github.com/repos/dimagi/commcare/issues/101/comments","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/4f4776c04e64b11dc45888cfcd191d5c121692b8","head":{"label":"dimagi:commcare-api","ref":"commcare-api","sha":"4f4776c04e64b11dc45888cfcd191d5c121692b8","user":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"repo":{"id":20939782,"name":"commcare","full_name":"dimagi/commcare","owner":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/dimagi/commcare","description":"","fork":false,"url":"https://api.github.com/repos/dimagi/commcare","forks_url":"https://api.github.com/repos/dimagi/commcare/forks","keys_url":"https://api.github.com/repos/dimagi/commcare/keys{/key_id}","collaborators_url":"https://api.github.com/repos/dimagi/commcare/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/dimagi/commcare/teams","hooks_url":"https://api.github.com/repos/dimagi/commcare/hooks","issue_events_url":"https://api.github.com/repos/dimagi/commcare/issues/events{/number}","events_url":"https://api.github.com/repos/dimagi/commcare/events","assignees_url":"https://api.github.com/repos/dimagi/commcare/assignees{/user}","branches_url":"https://api.github.com/repos/dimagi/commcare/branches{/branch}","tags_url":"https://api.github.com/repos/dimagi/commcare/tags","blobs_url":"https://api.github.com/repos/dimagi/commcare/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/dimagi/commcare/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/dimagi/commcare/git/refs{/sha}","trees_url":"https://api.github.com/repos/dimagi/commcare/git/trees{/sha}","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/{sha}","languages_url":"https://api.github.com/repos/dimagi/commcare/languages","stargazers_url":"https://api.github.com/repos/dimagi/commcare/stargazers","contributors_url":"https://api.github.com/repos/dimagi/commcare/contributors","subscribers_url":"https://api.github.com/repos/dimagi/commcare/subscribers","subscription_url":"https://api.github.com/repos/dimagi/commcare/subscription","commits_url":"https://api.github.com/repos/dimagi/commcare/commits{/sha}","git_commits_url":"https://api.github.com/repos/dimagi/commcare/git/commits{/sha}","comments_url":"https://api.github.com/repos/dimagi/commcare/comments{/number}","issue_comment_url":"https://api.github.com/repos/dimagi/commcare/issues/comments{/number}","contents_url":"https://api.github.com/repos/dimagi/commcare/contents/{+path}","compare_url":"https://api.github.com/repos/dimagi/commcare/compare/{base}...{head}","merges_url":"https://api.github.com/repos/dimagi/commcare/merges","archive_url":"https://api.github.com/repos/dimagi/commcare/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/dimagi/commcare/downloads","issues_url":"https://api.github.com/repos/dimagi/commcare/issues{/number}","pulls_url":"https://api.github.com/repos/dimagi/commcare/pulls{/number}","milestones_url":"https://api.github.com/repos/dimagi/commcare/milestones{/number}","notifications_url":"https://api.github.com/repos/dimagi/commcare/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/dimagi/commcare/labels{/name}","releases_url":"https://api.github.com/repos/dimagi/commcare/releases{/id}","created_at":"2014-06-17T21:13:25Z","updated_at":"2015-05-01T15:55:40Z","pushed_at":"2015-09-03T18:14:59Z","git_url":"git://github.com/dimagi/commcare.git","ssh_url":"git@github.com:dimagi/commcare.git","clone_url":"https://github.com/dimagi/commcare.git","svn_url":"https://github.com/dimagi/commcare","homepage":null,"size":8555,"stargazers_count":2,"watchers_count":2,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":3,"mirror_url":null,"open_issues_count":4,"forks":3,"open_issues":4,"watchers":2,"default_branch":"master"}},"base":{"label":"dimagi:master","ref":"master","sha":"a972f0affecf3d7a73cc060e5878b63523bd816a","user":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"repo":{"id":20939782,"name":"commcare","full_name":"dimagi/commcare","owner":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/dimagi/commcare","description":"","fork":false,"url":"https://api.github.com/repos/dimagi/commcare","forks_url":"https://api.github.com/repos/dimagi/commcare/forks","keys_url":"https://api.github.com/repos/dimagi/commcare/keys{/key_id}","collaborators_url":"https://api.github.com/repos/dimagi/commcare/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/dimagi/commcare/teams","hooks_url":"https://api.github.com/repos/dimagi/commcare/hooks","issue_events_url":"https://api.github.com/repos/dimagi/commcare/issues/events{/number}","events_url":"https://api.github.com/repos/dimagi/commcare/events","assignees_url":"https://api.github.com/repos/dimagi/commcare/assignees{/user}","branches_url":"https://api.github.com/repos/dimagi/commcare/branches{/branch}","tags_url":"https://api.github.com/repos/dimagi/commcare/tags","blobs_url":"https://api.github.com/repos/dimagi/commcare/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/dimagi/commcare/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/dimagi/commcare/git/refs{/sha}","trees_url":"https://api.github.com/repos/dimagi/commcare/git/trees{/sha}","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/{sha}","languages_url":"https://api.github.com/repos/dimagi/commcare/languages","stargazers_url":"https://api.github.com/repos/dimagi/commcare/stargazers","contributors_url":"https://api.github.com/repos/dimagi/commcare/contributors","subscribers_url":"https://api.github.com/repos/dimagi/commcare/subscribers","subscription_url":"https://api.github.com/repos/dimagi/commcare/subscription","commits_url":"https://api.github.com/repos/dimagi/commcare/commits{/sha}","git_commits_url":"https://api.github.com/repos/dimagi/commcare/git/commits{/sha}","comments_url":"https://api.github.com/repos/dimagi/commcare/comments{/number}","issue_comment_url":"https://api.github.com/repos/dimagi/commcare/issues/comments{/number}","contents_url":"https://api.github.com/repos/dimagi/commcare/contents/{+path}","compare_url":"https://api.github.com/repos/dimagi/commcare/compare/{base}...{head}","merges_url":"https://api.github.com/repos/dimagi/commcare/merges","archive_url":"https://api.github.com/repos/dimagi/commcare/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/dimagi/commcare/downloads","issues_url":"https://api.github.com/repos/dimagi/commcare/issues{/number}","pulls_url":"https://api.github.com/repos/dimagi/commcare/pulls{/number}","milestones_url":"https://api.github.com/repos/dimagi/commcare/milestones{/number}","notifications_url":"https://api.github.com/repos/dimagi/commcare/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/dimagi/commcare/labels{/name}","releases_url":"https://api.github.com/repos/dimagi/commcare/releases{/id}","created_at":"2014-06-17T21:13:25Z","updated_at":"2015-05-01T15:55:40Z","pushed_at":"2015-09-03T18:14:59Z","git_url":"git://github.com/dimagi/commcare.git","ssh_url":"git@github.com:dimagi/commcare.git","clone_url":"https://github.com/dimagi/commcare.git","svn_url":"https://github.com/dimagi/commcare","homepage":null,"size":8555,"stargazers_count":2,"watchers_count":2,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":3,"mirror_url":null,"open_issues_count":4,"forks":3,"open_issues":4,"watchers":2,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101"},"html":{"href":"https://github.com/dimagi/commcare/pull/101"},"issue":{"href":"https://api.github.com/repos/dimagi/commcare/issues/101"},"comments":{"href":"https://api.github.com/repos/dimagi/commcare/issues/101/comments"},"review_comments":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101/comments"},"review_comment":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101/commits"},"statuses":{"href":"https://api.github.com/repos/dimagi/commcare/statuses/4f4776c04e64b11dc45888cfcd191d5c121692b8"}}}}