{"id":4471,"date":"2021-01-19T13:48:43","date_gmt":"2021-01-19T12:48:43","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=4471"},"modified":"2021-01-19T14:50:24","modified_gmt":"2021-01-19T13:50:24","slug":"iseries-call-procedure-with-inout-parameter","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/iseries-call-procedure-with-inout-parameter\/","title":{"rendered":"iSeries call procedure with INOUT parameter"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">using IBM.Data.DB2.iSeries;\nusing System;\nusing System.Data;\n\nnamespace iSeriesTestProcedure\n{\n    class Program\n    {\n        static void Main(string[] args) => TestiSeries();\n\n        private static void Run(IDbConnection c, string sql) {\n            var cmd = c.CreateCommand();\n            cmd.CommandText = sql;\n            cmd.CommandType = CommandType.Text;\n            cmd.ExecuteNonQuery();\n        }\n        private static object RunRead(IDbConnection c, string sql)\n        {\n            var cmd = c.CreateCommand();\n            cmd.CommandText = sql;\n            cmd.CommandType = CommandType.Text;\n            object result = null;\n            using (var reader = cmd.ExecuteReader())\n            {\n                while (reader.Read())\n                {\n                    result = reader.GetValue(0);\n                }\n            }\n            return result;\n        }\n        private static void TestiSeries()\n        {\n            var connectionString = \"DataSource=***;UserID=***;Password=***;DataCompression=False;DEFAULT COLLECTION=***;Pooling=false;\";\n            using (var con = new iDB2Connection(connectionString))\n            {\n                con.Open();\n                try\n                {\n                    var transation = con.BeginTransaction();\n                    var cmd = con.CreateCommand();\n\n                    Run(con, \"CREATE OR REPLACE VARIABLE P1 CHAR(435)\");\n                    Run(con, \"SET P1 = '147900701066'\");\n                    var sql = string.Join(Environment.NewLine, new string[] {\n                        \"BEGIN\",\n                        \" DECLARE P2 CHAR(435);\",\n                        \" SET P2 = P1;\",\n                        \" CALL \\\"@TESTALL\\\".\\\"ADR006C\\\"(P2);\",\n                        \" VALUES (P2) INTO P1;\",\n                        \"END\"\n                    });\n                    Run(con, sql);\n                    var data = RunRead(con, \"VALUES (P1)\");\n                    Console.WriteLine($\"{data}\");\n                }\n                catch (Exception ex)\n                {\n                    var error = ex.ToString();\n                    Console.WriteLine(error);\n                }\n            }\n        }\n    }\n}<\/pre>\n\n\n\n<p>Optimized generic function:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">public T1 ExecuteCallWithSingleInputAndOutputParam&lt;T1>(string callName, string callParamsAsString)\n{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n    var connection = _connection;\n    var paramLength = callParamsAsString.Length;\n    var callnamePrepared = PrepareCallName(callName);\n\n    RunStatement(connection, $\"CREATE OR REPLACE VARIABLE P1 CHAR({\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200bcallParamsAsString.Length}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b)\");\n    RunStatement(connection, $\"SET P1 = '{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200bcallParamsAsString}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b'\");\n    RunStatement(connection, string.Join(Environment.NewLine, new string[] {\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n        \"BEGIN\",\n        $\"  DECLARE P2 CHAR({\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200bparamLength}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b);\",\n        \"  SET P2 = P1;\",\n        $\" CALL {\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200bcallnamePrepared}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b(P2);\",\n        \"  VALUES (P2) INTO P1;\",\n        \"END\"\n    }\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b));\n\n    return RunRead&lt;T1>(connection, \"VALUES (P1)\");\n}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n\nprivate string PrepareCallName(string callName)\n{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n    var splitted = callName.Split('.');\n\n    if (splitted.Length != 2)\n        throw new Exception(\"invalid callname\");\n\n    var a = splitted[0];\n    var b = splitted[1];\n\n    return $\"\\\"{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200ba}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\\\".\\\"{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200bb}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\\\"\";\n}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n\nprivate static void RunStatement(DataConnection c, string sql)\n{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n    var cmd = CreateCommand(c, sql);\n    cmd.ExecuteNonQuery();\n}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n\nprivate static T1 RunRead&lt;T1>(DataConnection c, string sql)\n{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n    var cmd = CreateCommand(c, sql);\n    var result = cmd.ExecuteScalar();\n    return (T1)result;\n}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n\nprivate static IDbCommand CreateCommand(DataConnection c, string sql)\n{\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n    var cmd = c.CreateCommand();\n    cmd.CommandText = sql;\n    cmd.CommandType = CommandType.Text;\n    return cmd;\n}\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Optimized generic function:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-4471","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/4471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=4471"}],"version-history":[{"count":3,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/4471\/revisions"}],"predecessor-version":[{"id":4476,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/4471\/revisions\/4476"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=4471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=4471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=4471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}