[SOLVED] Use Zenity from a script file to insert/view data in MYSQL

schneidz · 10-13-2016, 11:13 AM

Quote:

Originally Posted by Amit15

What you have done will show some column with checkbox. What I want is when I perform

Code:

select * from tablename

The results that I get in terminal should be displayed in Zenity list like the attachment Capture.png
Which is from the following Link
When I did that

Code:

echo "SELECT bookId,Author,Title,Pulication,Edition,Copies,Date_pur,price,Status FROM bookdetails" | mysql LibraryManagement -N -u root -pmysql123 >> tempAgenda.dat | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication"--column="Edition" --column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;

I get the result in attachment Screenshot from picture.png
Also i have attached the tempAgent.dat SreenShot

i use process substitution in my example and you are using pipes (which mite work if you didnt redirect the output to a file and away from the pipe -- maybe try tee)
?

Quote:

Originally Posted by Amit15

And Guys please help me with

what happens when you change that line to:

Code:

echo INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status) VALUES ('Author','Title','Publication','Edition',1,'10/13/2016',3.00,'Status');

?

Amit15 · 10-13-2016, 11:13 AM

Quote:

Originally Posted by Amit15

What you have done will show some column with checkbox. What I want is when I perform

Code:

select * from tablename

The results that I get in terminal should be displayed in Zenity list like the attachment Capture.png
Which is from the following Link
When I did that

Code:

echo "SELECT bookId,Author,Title,Pulication,Edition,Copies,Date_pur,price,Status FROM bookdetails" | mysql LibraryManagement -N -u root -pmysql123 >> tempAgenda.dat | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication"--column="Edition" --column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;

I get the result in attachment Screenshot from picture.png
Also i have attached the tempAgent.dat SreenShot

Please help me out with this too.

Amit15 · 10-13-2016, 11:37 AM

Quote:

Originally Posted by schneidz

i use process substitution in my example and you are using pipes (which mite work if you didnt redirect the output to a file and away from the pipe -- maybe try tee)
?

I used tee

Code:

echo "SELECT bookId,Author,Title,Pulication,Edition,Copies,Date_pur,price,Status FROM bookdetails" | mysql LibraryManagementSystem -u root -pmysql123 | tee tempAgenda.dat | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication" --column="Edition" --column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;

But output is not column wise. Attachement tee.png

Quote:

Originally Posted by schneidz

what happens when you change that line to:

Code:

echo INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status) VALUES ('Author','Title','Publication','Edition',1,'10/13/2016',3.00,'Status');

?

This part is solved

michaelk · 10-13-2016, 12:24 PM

The list data piped into zenity needs a new line as a separator which is why you see a complete row of data for each column including the header. In the example you posted the output of the mysql query is piped to tr and then to zenity.

Assuming the mysql separator is a tab tr replaces it with a new line character.

mysql ... | tr '\t' '\n' | zenity ...

Amit15 · 10-13-2016, 01:15 PM

Quote:

Originally Posted by michaelk

The list data piped into zenity needs a new line as a separator which is why you see a complete row of data for each column including the header. In the example you posted the output of the mysql query is piped to tr and then to zenity.

Assuming the mysql separator is a tab tr replaces it with a new line character.

mysql ... | tr '\t' '\n' | zenity ...

Thanks it worked.

But again header is included. I removed tee part still header is included.

Code:

echo "SELECT bookId,Author,Title,Pulication,Edition,Copies,Date_pur,price,Status FROM bookdetails" | mysql LibraryManagementSystem -u root -pmysql123 | tr '\t' '\n' | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication" --column="Edition" --column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;

schneidz · 10-13-2016, 01:19 PM

^ then dont include the column-names (man mysql).

Amit15 · 10-14-2016, 12:20 AM

Quote:

Originally Posted by schneidz

^ then dont include the column-names (man mysql).

You mean from Select column_name from tablename
I did that still header is included.

Code:

echo "SELECT * FROM bookdetails" | mysql LibraryManagementSystem -u root -pmysql123 | tr '\t' '\n' | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication" --column="Edition" --column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;

Amit15 · 10-14-2016, 04:11 AM

Guys I found the solution. To skip column names we need to use -N. I did man mysql and there i got this option
--skip-column-names, -N

Code:

echo "SELECT * FROM bookdetails" | mysql -N LibraryManagementSystem -u root -pmysql123 | tr '\t' '\n' | zenity --list --title="List Records" --text="" --column="bookId" --column="Author" --column="Title" --column="Pulication" --column="Edition"--column="Copies" --column="Date of purchase" --column="price" --column="Status" --height=310 --width=790;
;;

Thank you guys for helping me.