0

I have a query like Select '16453842' AS ACCOUNT, I want to change this Account number to a dynamic one. Can anyone tell me what are some possible ways to do it? The scenario is like, I would also like to accommodate other values as well for the ACCOUNT. So that multiple values can be used for ACCOUNT.

the Nodejs code uses that sql in below code by carrierData.sql 

writeDebug({'shipment': shipment, 'carrier': carrier, 'message': 'reading sql file: ' + carrierData.sql});
  fs.readFile(carrierData.sql, 'utf8', function (err, sql) {

The carrierData is coming from a json file from where the sql contains the path and name of the SQL which it is going to use. And finally the SQL file which have the query runs a query like below SELECT 'T' AS RECORD , '16453842' AS ACCOUNT

and here lies my problem as we have some additional ACCOUNT numbers as well which we would like to accommodate. And the service starts by node server.js which will call the workers.js file which contains the code that I pasted above. So please let me know what can be the possible ways to do this

Sam_2207
  • 2,759
  • 2
  • 9
  • 15
  • you could use a variable to a function – juergen d Feb 28 '20 at 06:56
  • 1
    What tools are you using to issue these queries? A programming language? A command line tool? – kimbo Feb 28 '20 at 06:57
  • 2
    Please **[edit]** your question (by clicking on the [edit] link below it) and show us the complete query you are using, so that we can see the context in which you want to use that account number. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Feb 28 '20 at 07:01
  • @kimbo, this query will be used by a Nodejs program which will run this and get the results and do other stuff as well. As this is a nodejs program, I will simply run it via cmd prompt, off course I have other services to run as well. – Sam_2207 Feb 28 '20 at 07:38
  • @a_horse_with_no_name, It's Oracle, sure I will tag that as well, as the question was confined to just identifying a temp variable or something is SQL which makes it dynamic so haven't tagged a DB. – Sam_2207 Feb 28 '20 at 07:40
  • 1
    SQL has no such thing as "variables", so the answer will be highly DBMS product specific. –  Feb 28 '20 at 07:41
  • @a_horse_with_no_name, sorry can't share the query here as it is too long and also not much useful. Your suggestion of adding different values makes sense but we will still have to hard-code it into SQL, can I make a variable and refer that variable as ACCOUNT? – Sam_2207 Feb 28 '20 at 07:42
  • If you're running it from command prompt, you can access an array of command line arguments in nodejs with `process.argv`. Run something like `node my-script.js `. Then in your.code, replace 16453842 with `process.argv[2]`. Make sense? – kimbo Feb 28 '20 at 07:42
  • @kimbo, your suggestion I believe will work well for running a Script via nodejs, however, let me try identifying a scenario where I can replace values in SQL query as well. Please let me know if you have any such blogposts which mention so – Sam_2207 Feb 28 '20 at 07:57
  • Did you even read documentation like this - https://oracle.github.io/node-oracledb/doc/api.html#bind ? – Vasyl Moskalov Feb 28 '20 at 10:02
  • As others have tried to explain, we simply don't have enough information about the context of submitting the query to oracle. I could be that you write the query with some sort of substitution variable, then some mechanism to pass the variable. It could be that you construct the query at run time by concatonating some literals with some values that are passed by some mechanism. But the details and methods depend on your exact environment, which we don't know. – EdStevens Feb 28 '20 at 13:34

1 Answers1

0

Here are things to research:

  1. Using bind variables. These are used for security and for performance when a statement is executed multiple times.

  2. Using multiple values in IN clauses.

  3. Using executeMany() if you are loading data into the database.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48