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

Amit15 · 10-12-2016, 09:58 AM

Guys I am trying to connect zenity with mysql.
I want to insert data into mysql using zenity. The input will be provided by user
to zenity. I have written the code but i get this error.
ERROR 1366 (HY000) at line 1: Incorrect integer value: '$_Copies' for column 'Copies' at row 1
My Code

Code:

  #!/bin/bash
ans=$( zenity --list --text="If you want to start Library Management System select 1 else 0" --radiolist --column "Selection" --column "Option" TRUE "1" FALSE "0");
if [ "$ans" -eq 0 ]; then
    exit
else
    choice=$(zenity --list --text="Select from the box" --radiolist --column="Select" \
    --column="Option" --column="Operation" \
    TRUE "1" "Insert in Books Table" \
    FALSE "2" "Insert in Student Table" \
    FALSE "3" "Insert in Issue Table" \
    FALSE "4" "Insert in Return Table" \
    FALSE "5" "View Books" \
    FALSE "6" "View Student" \
    FALSE "7" "View Issue" \
    FALSE "8" "View Return" \
    --height=310 \
    --width=350)
fi


case "$choice" in
        1)
        a=$(zenity --entry --title="Author Name" --text="Enter name of Author:" --entry-text "Author");
        _Author="$a"
        t=$(zenity --entry --title="Title of the Book" --text="Enter Title of Book:" --entry-text "Title");
        _Title="$t"
        p=$(zenity --entry --title="Publication Name" --text="Enter name of Publication:" --entry-text "Publication");
        _Pulication="$p"
        e=$(zenity --entry --title="Edition of the Book" --text="Enter Edition of Book:" --entry-text "Edition");
        _Edition="$e"
       c=$(zenity --entry --title="Number of Copies" --text="Enter number of copies:" --entry-text "Copies");
        _Copies="$c"
        dp=$(zenity --entry --title="Date of purchase" --text="Enter Date of purchase:" --entry-text "Date of purchase");
        _Date_pur="$dp"
        pr=$(zenity --entry --title="Price of the Book" --text="Enter Price of the Book:" --entry-text "Price");
        _price="$pr"
        s=$(zenity --entry --title="Status of book" --text="Enter Status of book:" --entry-text "Status");
        _Status="$s"

         mysql -uroot -pmysql123 LibraryManagementSystem << "EOF"
         INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status) 
         VALUES ('$_Author','$_Title','$_Pulication','$_Edition','$_Copies','$_Date_pur','$_price','$_Status');
    EOF
    ;;

database for Insert in Books Table

Code:

mysql> desc bookdetails;
    +------------+---------------+------+-----+---------+----------------+
    | Field      | Type          | Null | Key | Default | Extra          |
    +------------+---------------+------+-----+---------+----------------+
    | bookId     | int(11)       | NO   | PRI | NULL    | auto_increment |
    | Author     | varchar(120)  | NO   |     | NULL    |                |
    | Title      | varchar(120)  | NO   |     | NULL    |                |
    | Pulication | varchar(120)  | NO   |     | NULL    |                |
    | Edition    | varchar(120)  | NO   |     | NULL    |                |
    | Copies     | int(11)       | NO   |     | NULL    |                |
    | Date_pur   | varchar(120)  | NO   |     | NULL    |                |
    | price      | decimal(18,2) | NO   |     | NULL    |                |
    | Status     | varchar(120)  | NO   |     | NULL    |                |
    +------------+---------------+------+-----+---------+----------------+
    9 rows in set (0.02 sec)

Also after inserting data into mysql i want view this data in zenity like this But when i do the same thing it echoes my command in the temp.dat file not the contents.
please help.
Thanks.

grail · 10-12-2016, 11:30 AM

Please use [code][/code] tags around the code to make it clearer and maintain formatting

As for your error, when you searched was there no feedback as to what the error might be? Google seems to say there are a few 1000 results based on entering in your exact error message.
Please advise which you have ruled out so we know none of the standard solutions seem to work and we can go from there?

Amit15 · 10-12-2016, 12:09 PM

Thanks for the formatting information.
Yes there are 1000 results for the error but as you can see all the result says that it is because of sending a string value to an integer or It is related to auto_increment part.
In my case it is neither of that. I am taking the input from zenity than assigning it to _Copies variable.
and Copies in database is of the type INT.
So I could not find any solution.
Also please help me for the second part

Quote:

Also after inserting data into mysql i want view this data in zenity like this But when i do the same thing it echoes my command in the temp.dat file not the contents.

grail · 10-12-2016, 01:04 PM

Code:

_Copies="$c"

What about the above makes you think this is a number and not a string?

Amit15 · 10-12-2016, 01:33 PM

I have remove the double quotes

Code:

_Copies=$c

Still I get the Same error.
I have attached the screenshot

michaelk · 10-12-2016, 01:53 PM

Quote:

INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status)
VALUES ('$_Author','$_Title','$_Pulication','$_Edition','$_Copies','$_Date_pur','$_price','$_Status');

Using '' does send a string value to an integer. Try:

Quote:

INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status)
VALUES ('$_Author','$_Title','$_Pulication','$_Edition',$_Copies,'$_Date_pur',$_price,'$_Status');

Still trying to find a real reference but all shell variables hold strings, although in selected instances we may be able to interpret those strings as numbers. It all really depends on the commands we are using. If you place a shell variable into a command at a position where the command expects a number, then it will be treated as a number.

schneidz · 10-12-2016, 02:13 PM

Quote:

Originally Posted by Amit15

...
Also after inserting data into mysql i want view this data in zenity like this But when i do the same thing it echoes my command in the temp.dat file not the contents.
please help.
Thanks.

years ago i created this using zenity:

Code:

xine -f /usr/share/xine/skins/xine-ui_logo.mpv $(zenity --height=1000 --width=1000 --separator=" " --list --title vids --checklist --column="play" --column="moo-vee" --column="info" `sort moo-vees.tmp`)

http://www.linuxquestions.org/questi...center-719104/

Amit15 · 10-12-2016, 10:18 PM

Quote:

INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status)
VALUES ('$_Author','$_Title','$_Pulication','$_Edition',$_Copies,'$_Date_pur',$_price,'$_Status')

I already tried this yesterday but it still gives same error. Any other solutions. Please.

Quote:

years ago i created this using zenity:

Code:

xine -f /usr/share/xine/skins/xine-ui_logo.mpv $(zenity --height=1000 --width=1000 --separator=" " --list --title vids --checklist --column="play" --column="moo-vee" --column="info" `sort moo-vees.tmp`)

Thanks, right now i am at college after trying it i"ll let you know

michaelk · 10-13-2016, 08:27 AM

You will need to post your current code again since we cannot see the actual insert line. Same error as 1366 or 1054? 1054 might indicate the insert line has syntax errors.

Just for verification you can insert data successfully by copy/pasting into mysql something like?

Quote:

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

In addition zenity does have a --question option which is a yes/no question. The return value ($?) is 0 for yes and 1 for no.

Amit15 · 10-13-2016, 08:31 AM

Quote:

Originally Posted by schneidz

years ago i created this using zenity:

Code:

xine -f /usr/share/xine/skins/xine-ui_logo.mpv $(zenity --height=1000 --width=1000 --separator=" " --list --title vids --checklist --column="play" --column="moo-vee" --column="info" `sort moo-vees.tmp`)

http://www.linuxquestions.org/questi...center-719104/

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

And Guys please help me with

Quote:

ERROR 1366 (HY000) at line 1: Incorrect integer value: '$_Copies' for column 'Copies' at row 1

Amit15 · 10-13-2016, 08:50 AM

Quote:

Originally Posted by michaelk

You will need to post your current code again since we cannot see the actual insert line. Same error as 1366 or 1054? 1054 might indicate the insert line has syntax errors.

Just for verification you can insert data successfully by copy/pasting into mysql something like?

I did as you told to and I got no errors and i have attached(pic.png) the results.
But the problem is the only value enter in the database are "1, 10/13/2016, 3.00"

Code:

mysql> select * from bookdetails;
+--------+-------------------------------+--------------------------+------------------+---------+--------+---------------+--------+-----------+
| bookId | Author                        | Title                    | Pulication       | Edition | Copies | Date_pur      | price  | Status    |
+--------+-------------------------------+--------------------------+------------------+---------+--------+---------------+--------+-----------+
|      1 | Reema Thareja                 | Data Structure Using C   | Oxford           | Second  |     10 | 2015/jan/10   | 435.00 | Available |
|      2 | G.V. Kumbhojkar               | Applied Mathematics      | Jamnadas and co. | Fourth  |     50 | 2014/jun/15   | 475.00 | Available |
|      3 | Puntambekar                   | SOOAD                    | Technical        | Second  |     15 | 2015/jan/10   | 300.00 | Available |
|      4 | Harish G. Narula              | Microprocessor           | Tech-Max         | Third   |     15 | 2014/jun/15   | 470.00 | Available |
|      5 | Andrew Tanenbum & herbert bos | Operating System         | Pearson          | Fourth  |     20 | 2016/jun/15   | 575.00 | Available |
|      6 | Sanjesh S. Pawale             | Computer Graphics        | Tech-Max         | First   |     10 | 2014/april/03 | 450.00 | Available |
|      7 | Galvin                        | Operating System Concept | Wiley            | Ninth   |     10 | 2015/jan/10   | 560.00 | Available |
|      8 | Author                        | Title                    | Publication      | Edition |      1 | 10/13/2016    |   3.00 | Status    |
+--------+-------------------------------+--------------------------+------------------+---------+--------+---------------+--------+-----------+
8 rows in set (0.00 sec)

Quote:

In addition zenity does have a --question option which is a yes/no question. The return value ($?) is 0 for yes and 1 for no.

No I have no --question option for yes or no.
But I have something like this in the code.

Code:

ans=$( zenity --list --text="If you want to start Library Management System select 1 else 0" --radiolist --column "Selection" --column "Option" TRUE "1" FALSE "0");
if [ "$ans" -eq 0 ]; then
	exit
else
	choice=$(zenity --list --text="Select from the box" --radiolist --column="Select" \
	--column="Option" --column="Operation" \
	TRUE "1" "Insert in Books Table" \
	FALSE "2" "Insert in Student Table" \
	FALSE "3" "Insert in Issue Table" \
	FALSE "4" "Insert in Return Table" \
	FALSE "5" "View Books" \
	FALSE "6" "View Student" \
	FALSE "7" "View Issue" \
	FALSE "8" "View Return" \
	--height=310 \
	--width=350)
fi

Do you want me post the whole code?

michaelk · 10-13-2016, 10:29 AM

Just post your code for choice 1.

The basic syntax works so we need to fix your code so you are passing an integer value to the Copies column.

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

Code:

case "$choice" in
	    1)
		a=$(zenity --entry --title="Author Name" --text="Enter name of Author:" --entry-text "Author");
		_Author="$a"
		t=$(zenity --entry --title="Title of the Book" --text="Enter Title of Book:" --entry-text "Title");
		_Title="$t"
		p=$(zenity --entry --title="Publication Name" --text="Enter name of Publication:" --entry-text "Publication");
		_Pulication="$p"
		e=$(zenity --entry --title="Edition of the Book" --text="Enter Edition of Book:" --entry-text "Edition");
		_Edition="$e"
		c=$(zenity --entry --title="Number of Copies" --text="Enter number of copies:" --entry-text "Copies");
		_Copies=$c
		dp=$(zenity --entry --title="Date of purchase" --text="Enter Date of purchase:" --entry-text "Date of purchase");
		_Date_pur="$dp"
		pr=$(zenity --entry --title="Price of the Book" --text="Enter Price of the Book:" --entry-text "Price");
		_price=$pr
		s=$(zenity --entry --title="Status of book" --text="Enter Status of book:" --entry-text "Status");
		_Status="$s"

		mysql -uroot -pmysql123 LibraryManagementSystem << "EOF"
		INSERT INTO bookdetails (Author, Title, Pulication, Edition, Copies, Date_pur, price,Status) 
		VALUES ('$_Author','$_Title','$_Pulication','$_Edition',$_Copies,'$_Date_pur',$_price,'$_Status');
EOF
		;;

And after running this code i get error which you mentioned 1054

keefaz · 10-13-2016, 10:57 AM

If using quoted "EOF" as heredoc delimiter your variables will not be interpolated...

Try <<EOF ...
EOF

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

Quote:

Originally Posted by keefaz

If using quoted "EOF" as heredoc delimiter your variables will not be interpolated...

Try <<EOF ...
EOF

Thanks it worked