{"action":"created","issue":{"url":"https://api.github.com/repos/golang/go/issues/12381","repository_url":"https://api.github.com/repos/golang/go","labels_url":"https://api.github.com/repos/golang/go/issues/12381/labels{/name}","comments_url":"https://api.github.com/repos/golang/go/issues/12381/comments","events_url":"https://api.github.com/repos/golang/go/issues/12381/events","html_url":"https://github.com/golang/go/issues/12381","id":103741958,"number":12381,"title":"database/sql: add support for named input parameters","user":{"login":"kardianos","id":755121,"avatar_url":"https://avatars.githubusercontent.com/u/755121?v=3","gravatar_id":"","url":"https://api.github.com/users/kardianos","html_url":"https://github.com/kardianos","followers_url":"https://api.github.com/users/kardianos/followers","following_url":"https://api.github.com/users/kardianos/following{/other_user}","gists_url":"https://api.github.com/users/kardianos/gists{/gist_id}","starred_url":"https://api.github.com/users/kardianos/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kardianos/subscriptions","organizations_url":"https://api.github.com/users/kardianos/orgs","repos_url":"https://api.github.com/users/kardianos/repos","events_url":"https://api.github.com/users/kardianos/events{/privacy}","received_events_url":"https://api.github.com/users/kardianos/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/golang/go/milestones/6","html_url":"https://github.com/golang/go/milestones/Unplanned","labels_url":"https://api.github.com/repos/golang/go/milestones/6/labels","id":1055141,"number":6,"title":"Unplanned","description":"No plan to fix in any specific release.\r\n","creator":{"login":"rsc","id":104030,"avatar_url":"https://avatars.githubusercontent.com/u/104030?v=3","gravatar_id":"","url":"https://api.github.com/users/rsc","html_url":"https://github.com/rsc","followers_url":"https://api.github.com/users/rsc/followers","following_url":"https://api.github.com/users/rsc/following{/other_user}","gists_url":"https://api.github.com/users/rsc/gists{/gist_id}","starred_url":"https://api.github.com/users/rsc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rsc/subscriptions","organizations_url":"https://api.github.com/users/rsc/orgs","repos_url":"https://api.github.com/users/rsc/repos","events_url":"https://api.github.com/users/rsc/events{/privacy}","received_events_url":"https://api.github.com/users/rsc/received_events","type":"User","site_admin":false},"open_issues":1306,"closed_issues":450,"state":"open","created_at":"2015-04-06T20:47:19Z","updated_at":"2016-08-12T17:19:53Z","due_on":"2099-12-31T05:00:00Z","closed_at":null},"comments":11,"created_at":"2015-08-28T15:40:22Z","updated_at":"2016-08-12T19:34:08Z","closed_at":null,"body":"Named input parameters are supported by a number of larger database servers and simulated in some database clients for protocol's that only support positional arguments.\r\nTDS is MS SQL Server's wire protocol and directly supports named parameters.\r\nPostgres' v3 wire protocol doesn't support named parameters but some clients simulate it through replacement.\r\n\r\nIn SQL this may look like:\r\n```\r\nselect d.Name, d.Age\r\nfrom app.Dancer as d\r\nwhere d.ID = :DancerID;\r\n```\r\nor \r\n```\r\nselect d.Name, d.Age\r\nfrom app.Dancer as d\r\nwhere d.ID = @DancerID;\r\n```\r\n\r\nIn small to medium cases positional placeholders work fine. In larger tables positional indexes become hard to use, bug prone, and possibly impossible to use.\r\n\r\nOne of my clients uses JDE, their table structure can be viewed here: http://www.jdetables.com/\r\nYes, it is bad, but I've gotta deal with it. I have another client that uses a similarly \"enterprise\" solution that has huge wide tables. Now try to imagine updating or inserting into those tables, or executing proc with 30-50 parameters. Named parameters gives me a chance to not get an index off by one.\r\n\r\nThe other area where I keenly feel the need for named parameters is when I dynamically assemble SQL, but still want to pass in input parameters so I don't need to manually escape everything. Two examples:\r\n1) When creating a specific RPC for a search or filter I often need to filter in particular cases. There are two ways to do this:\r\na.\r\n```\r\nselect * from data.Account as a\r\nwhere\r\n  (len(:InputValue) <> 0 and :InputValue like a.Name)\r\n  or (len(:inputValue = 0 and a.Name = 'BOB')\r\n```\r\nI use this method often. However sometimes in complex cases performance can drop off due when it chooses the wrong query plan. When that starts to happen I do the next option:\r\nb.\r\n```\r\nsql := &bytes.Buffer{}\r\nsql.WriteString(`select * from data.Account as a where 1=1 `)\r\nif len(inputValue) != 0 {\r\n  sql.WriteString(` and :InputValue like a.Name `)\r\n} else {\r\n  sql.WriteString(` and a.Name = 'BOB' `)\r\n}\r\n```\r\nWhen you start to construct the SQL, it becomes harder to determine the positions all the parameters should be in and makes the code more complex, though not impossible if you push input parameter values into an array as you push sql fragments in.\r\n\r\n2) While truly it is a variant of 1b, when creating a more generic application framework the work-around presented in there becomes more difficult. I sometimes define both the name of the parameter I want to pass in, tie it to the client, and if present construct SQL to the desired effect. I realize that is vague, but I don't have a ready open source solution I can show.\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/golang/go/issues/comments/239540027","html_url":"https://github.com/golang/go/issues/12381#issuecomment-239540027","issue_url":"https://api.github.com/repos/golang/go/issues/12381","id":239540027,"user":{"login":"freeformz","id":103,"avatar_url":"https://avatars.githubusercontent.com/u/103?v=3","gravatar_id":"","url":"https://api.github.com/users/freeformz","html_url":"https://github.com/freeformz","followers_url":"https://api.github.com/users/freeformz/followers","following_url":"https://api.github.com/users/freeformz/following{/other_user}","gists_url":"https://api.github.com/users/freeformz/gists{/gist_id}","starred_url":"https://api.github.com/users/freeformz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/freeformz/subscriptions","organizations_url":"https://api.github.com/users/freeformz/orgs","repos_url":"https://api.github.com/users/freeformz/repos","events_url":"https://api.github.com/users/freeformz/events{/privacy}","received_events_url":"https://api.github.com/users/freeformz/received_events","type":"User","site_admin":false},"created_at":"2016-08-12T19:34:08Z","updated_at":"2016-08-12T19:34:08Z","body":"@karalabe K. \r\n\r\nRe: ValueConverter, I was reading through the Stmt code and maybe I just misunderstood what it's doing and how it works. Never mind.\r\n\r\nQuery could check args to see if any of them are sql.NamesParams and if so then assert for a NamedQueryer interface that looks like this:\r\n```go\r\ntype NamedQueryer interface {\r\n        NamedQuery(query string, args []Value, namedArgs []NamedValue) (Rows, error)\r\n}\r\n```\r\nStmt would also need to handle named params as well right? And it handles args in a very different way (the code I was reading)."}}
{"actor":{"display_login":"freeformz"}}