* How to read SQL parameters from text file

 

Sometimes we want to store the parameter list in a text file, and then spell it into the in function when executing SQL, so as to find the records that meet the condition.

But SQL can't parse the text file, so it's difficult to spell it directly into the in function. Many people import the file into the database temporary table first, and then join with the join statement, thus indirectly realizing the effect of in function. But in this way, every time the file changes, you have to manually delete the table and import the file into database again, which is quite troublesome.

To solve the problem once and for all, you can develop programs in Python, C# and other programming languages, but the technical requirement of such languages is very high, and it is difficult to master, although in theory, all functions can be realized.

It would have been much simpler to implement the algorithm if you had used esProc SPL. Only two lines of code is needed:

 


A

B

1

=file("params.txt").import@i()

/ Open the text file and get the parameter list by   line

2

=orcl.query("select * from T where P in(?)",A2)

/ Execute SQL, A2 is the parameter value of in   function

When the parameter list is too long, the above method cannot be used, because the in function has a limit on the number of members. For example, Oracle specifies that the number of members cannot exceed 1000. Don't worry about this situation. esProc supports general queries outside the database and long parameter list. The code only needs to be written as follows:


A

B

1

=file("params.txt").import@i().sort(~)

/ Read long parameter list and sort

2

=orcl.cursor("select * from T")

/ Fetch data from database table

3

=A3.select(A1.contain@b(P))

/ Query records matching parameter list

esProc is a scripting language, which can be executed and debugged in desktop IDE. It can connect to any database and execute SQL. esProc usually has a way to simplify the functions that SQL is difficult to implement.

For the commonly used esProc algorithms, please refer to  http://doc.raqsoft.com/

esProc boasts agile syntax and a rich variety class libraries for structured data processing that enables convenient and universal methods for scenarios that SQL struggles to handle awkwardly and cumbersomely. Please refer to http://www.raqsoft.com/html/sql-enhancer.html

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.