LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-12-2016, 10:58 AM   #1
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Rep: Reputation: Disabled
Use Zenity from a script file to insert/view data in MYSQL


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.

Last edited by Amit15; 10-12-2016 at 12:51 PM. Reason: improved formatting
 
Old 10-12-2016, 12:30 PM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
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?
 
Old 10-12-2016, 01:09 PM   #3
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
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.
 
Old 10-12-2016, 02:04 PM   #4
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
Code:
_Copies="$c"
What about the above makes you think this is a number and not a string?
 
Old 10-12-2016, 02:33 PM   #5
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
I have remove the double quotes
Code:
_Copies=$c
Still I get the Same error.
I have attached the screenshot
Attached Thumbnails
Click image for larger version

Name:	Screenshot from 2016-10-12 23-52-18.jpg
Views:	10
Size:	195.4 KB
ID:	23226  
 
Old 10-12-2016, 02:53 PM   #6
michaelk
Moderator
 
Registered: Aug 2002
Posts: 14,938

Rep: Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520
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.

Last edited by michaelk; 10-12-2016 at 02:59 PM. Reason: variables update
 
1 members found this post helpful.
Old 10-12-2016, 03:13 PM   #7
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,027

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
Quote:
Originally Posted by Amit15 View Post
...
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/
 
Old 10-12-2016, 11:18 PM   #8
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
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
 
Old 10-13-2016, 09:27 AM   #9
michaelk
Moderator
 
Registered: Aug 2002
Posts: 14,938

Rep: Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520
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.
 
Old 10-13-2016, 09:31 AM   #10
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by schneidz View Post
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
Attached Thumbnails
Click image for larger version

Name:	Capture.PNG
Views:	5
Size:	21.7 KB
ID:	23244   Click image for larger version

Name:	picture.png
Views:	7
Size:	168.8 KB
ID:	23246   Click image for larger version

Name:	temAgent.png
Views:	5
Size:	142.7 KB
ID:	23247  
 
Old 10-13-2016, 09:50 AM   #11
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by michaelk View Post
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?
Attached Thumbnails
Click image for larger version

Name:	Pic.png
Views:	6
Size:	222.5 KB
ID:	23248  
 
Old 10-13-2016, 11:29 AM   #12
michaelk
Moderator
 
Registered: Aug 2002
Posts: 14,938

Rep: Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520Reputation: 1520
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.
 
Old 10-13-2016, 11:47 AM   #13
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
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
Attached Thumbnails
Click image for larger version

Name:	termial.png
Views:	3
Size:	123.7 KB
ID:	23250  

Last edited by Amit15; 10-13-2016 at 12:00 PM. Reason: Attachement
 
Old 10-13-2016, 11:57 AM   #14
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 5,387

Rep: Reputation: 397Reputation: 397Reputation: 397Reputation: 397
If using quoted "EOF" as heredoc delimiter your variables will not be interpolated...

Try <<EOF ...
EOF
 
Old 10-13-2016, 12:09 PM   #15
Amit15
LQ Newbie
 
Registered: Oct 2016
Posts: 13

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by keefaz View Post
If using quoted "EOF" as heredoc delimiter your variables will not be interpolated...

Try <<EOF ...
EOF
Thanks it worked
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help on Insert data to mySQL database aoiregion Linux - Newbie 3 07-22-2014 02:44 AM
Need help on Insert data to phpMyAdmin mySQL database aoiregion Linux - Newbie 4 07-18-2014 05:18 AM
insert openoffice data into mysql sriphp Linux - Software 1 06-02-2009 03:26 AM
segmentation fault when insert data into mysql torontosmallbird Other *NIX 1 08-07-2005 08:13 AM
Insert Data by FORM in MYSQL gruger Linux - Software 0 07-16-2003 01:50 PM


All times are GMT -5. The time now is 08:33 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration