{"id":1577,"date":"2025-01-07T05:46:54","date_gmt":"2025-01-07T05:46:54","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1577"},"modified":"2025-01-07T05:46:54","modified_gmt":"2025-01-07T05:46:54","slug":"postgres-javascript-missing-variables-but-it-is-there","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1577","title":{"rendered":"Postgres JavaScript missing variables (But it is #$%^ there!)"},"content":{"rendered":"\n

It’s OK<\/p>\n\n\n\n

I only cried and contemplated quitting working in IT and becoming a Nomad for a couple of hours. <\/p>\n\n\n\n

But I got there in the end; but the following error message will probably plague my nightmares for a couple of weeks:<\/p>\n\n\n\n

ERROR:  ReferenceError: inNumber1 is not defined
CONTEXT:  fn_js_number_adder() LINE 2: \tlet total = inNumber1 + inNumber2 

SQL state: XX000<\/pre>\n\n\n\n
JavaScript: When in Rome – Do what the Romans do<\/h2>\n\n\n\n
My job today was to write a JavaScript function in Postgres to convert byte hex values to EBCDIC\u00a0037.  The aim is to decommission some duplicate pipelines coming from our DB2 database by Qlik Replicate that deliver ASCII converted fields as well as the EBCDIC\u00a0version.<\/p>\n\n\n\n
I haven’t worked in JavaScript since my Uni days and well entrenched in the Python world for my day to day job.  Over the past years I have converted using naming conventions in code from camelCase to under_score to match Python’s standard.<\/p>\n\n\n\n
So going back to JavaScript – I knew that camelCase is the expected format.  Since I didn’t know where my code was going to end up; I wanted it to look professional as it is a reflection on me.<\/p>\n\n\n\n
So I wrote a JavaScript function paraphrased as:<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION fn_js_number_adder(inNumber1 numeric, inNumber2 numeric)\nRETURNS numeric\nas\n$$\n\tlet total = inNumber1 + inNumber2\n\n\treturn total\n$$\nLANGUAGE plv8;\n<\/pre><\/div>\n\n\n
Looks good – compiles with no errors.<\/p>\n\n\n\n
But when I went to test it; I get the following error:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The error drove me crazy!  It’s THERE!   The variable is THERE!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The original function was a lot more extensive than above so I cut as much out of it as possible in case something else was causing the variable not to be recognised.<\/p>\n\n\n\n
Still no luck.<\/p>\n\n\n\n
I went to the functions section in pgAdmin as I wanted to compare it against an existing function I created to see what the difference was.<\/p>\n\n\n\n
Interesting…<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The function’s parameters have changed from inNumber1 and inNumber2 to innumber1<\/em> and innumber2<\/em>.<\/p>\n\n\n\n
Scripting out the function I got:<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION public.fn_js_number_adder(\n\tinnumber1 numeric,\n\tinnumber2 numeric)\n    RETURNS numeric\n    LANGUAGE 'plv8'\n    COST 100\n    VOLATILE PARALLEL UNSAFE\nAS $BODY$\n\tlet total = inNumber1 + inNumber2\n\n\treturn total\n$BODY$;\n<\/pre><\/div>\n\n\n
So; either postgres or pgAdmin changed the case of the parameters from camelCase to lower case.  This caused the variable not to be found later in the code.<\/p>\n\n\n\n
The fix – Back to under_scores we go<\/h2>\n\n\n\n
My fix for this instance (whether standard or not) is to go back to under_scores:<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION fn_js_number_adder(in_number1 numeric, in_number2 numeric)\nRETURNS numeric\nas\n$$\n\tlet total = in_number1 + in_number2\n\n\treturn total\n$$\nLANGUAGE plv8;\n<\/pre><\/div>\n\n\n
This works and I could run the function<\/p>\n\n\n\n
With the naming conventions; I suppose using under_score isn’t too much of a sin since it is a standard on databases.  If you want to stay true to camelCase; the parameters can just be in under_score and the rest of the variables be in camelCase.<\/p>\n\n\n\n
At lest it is working…now onto EBCDIC\u00a0conversion.<\/p>\n","protected":false},"excerpt":{"rendered":"
It’s OK I only cried and contemplated quitting working in IT and becoming a Nomad for a couple of hours. But I got there in the...<\/p>\n","protected":false},"author":1,"featured_media":1582,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[64],"tags":[77,75,56,76],"class_list":["post-1577","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgres","tag-functions","tag-javascript","tag-postgres","tag-referenceerror"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1577"}],"version-history":[{"count":3,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1577\/revisions"}],"predecessor-version":[{"id":1584,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1577\/revisions\/1584"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1582"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}