• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

My stored procedure code does not work as it should?

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I wrote this in my terminal from an example. I got homebrew and Mariadb installed and I am using dbeaver. I got this error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'End $$
Delimeter $$ begin select * from Contents where Cname like '% ammonium  %' at line 1
 
Marshal
Posts: 6639
177
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know what's wrong with the procedure, but couldn't you shorten the "where" clause to where Cname like '%ammonium%'?
 
Marshal
Posts: 24943
61
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You misspelled "delimiter".
 
martin codey
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Thank you,  should I create a table procedure?
 
Saloon Keeper
Posts: 21593
146
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anything that matches "LIKE 'AMMONIUM %'" will also match the other LIKE clauses - and you've duplicated one of them, so that code is doubly redundant.

I do not recommend using capital letters in database table and column names. Different databases interpret them differently. Plus, they can be a real pain bouncing up and down on the SHIFT key when you start doing ad hoc queries.
 
martin codey
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay thanks I have fixed that now but i still got the error that table mysql.proc doesn't exist. I am using mariadb and installed it via homebrew.
 
Tim Holloway
Saloon Keeper
Posts: 21593
146
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The mysql.proc table was used to hold stored procedure information but it has apparently been removed as of about version 8.0. It looks like MariaDB might still use it, however.

The functions of mysql.proc were folded into the information.schema table, it would seem.

My guess is that you've got a mix-and-match mismatch situation here where some of your database ecology is trying to work with obsolete resources.

You might try using the "mysql" command-line client application to define your stored procedure.
 
Rancher
Posts: 4450
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Anything that matches "LIKE 'AMMONIUM %'" will also match the other LIKE clauses - and you've duplicated one of them, so that code is doubly redundant.



This could be a Monday-morning thing, so bear with me here.
LIKE 'AMMONIUM %'  will only match fields that start with 'AMMONIUM '
'LIKE '% AMMONIUM %' will match anything that contains AMMONIUM with a space before and after, so the former can't be used as a substitue for the latter...can it?
(Ignoring the duplicated LIKE, of course)
 
Tim Holloway
Saloon Keeper
Posts: 21593
146
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe you are correct. I was thinking regex matching, and it's different that way.
 
The problems of the world fade way as you eat a piece of pie. This tiny ad has never known problems:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!