bash sqlcmd variable array

Question

I´m trying to acces values in a variable I receive from an sqlcmd in a loop:

while read line ;do AllNumbers=`sqlcmd -h -1 -S mysqlserver.localdomain -U Me -P MyPasswd -d master -Q "SET nocount on; SELECT PHONENUMBER FROM MYTABLE WHERE USER LIKE '"$line"' " < /dev/null`; for i in "$AllNumbers"; do echo "$line $i"; done; done< mytext.txt

The Number of results in the variable "$AllNumbers" can be 1 to 5, and the lenght of the number can also vary. What I get in Moment is:

John 01234567

9876543

Jack 13579024

Jim 08642135

10293847

56473829

In the end I want to have an output like:

John 01234567

John 98765432

Jack 13579024

Jim 08642135

Jim 10293847

Jim 56473829

I tried with IFS, but I´m unsure what the delimiter in the variable $AllNumber is. If I do:

OneUser=`sqlcmd -h -1 -S mysqlserver.localdomain -U Me -P MyPasswd -d master -Q "SET nocount on; SELECT PHONENUMBER FROM MYTABLE WHERE USER LIKE '"Jim"' "`; echo "$OneUSer"

I get:

08642135

10293847

56473829

I´m glad for any hint...

Thanks in advance

Lotte

choroba · Accepted Answer · 2020-08-12 17:19:16Z

1

Why not ask the database to do the hard work?

SELECT USER, PHONENUMBER FROM MYTABLE WHERE USER LIKE '"$line"'

Also, make sure you validate $line to prevent SQL injection attacks (see Bobby Tables).

answered Aug 12, 2020 at 17:19

choroba

245k27 gold badges221 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lothar Albrecht Over a year ago

I know, all the above is an example, in the end I need to split the content of a variable into single pieces...

Lothar Albrecht Over a year ago

It works when redirecting the output of sqlcmd to a file, get rid of the spaces and read line by line, but no luck when the output of sqlcmd delivers more than one result... I can´t see special characters inside the variable... As another try when query a sqlcmd with several SELECTS it works like charm... But in the end I want to split the output of a resulting string...

Collectives™ on Stack Overflow

bash sqlcmd variable array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related