Database

12-Aug-16 12:53

Hi,

I have below stored procedure..

it only works when I pass single value like "US" but not when passing multiple values like "AE", "BH", "US", "FR"

when passing multiple values I get this error:

Procedure execution failed
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '")' at line 1

here is the stored procedure:

SQL

CREATE DEFINER=<code>root</code>@<code>127.0.0.1</code> PROCEDURE <code>sp_populate_memo_country_companies</code>(IN param_country varchar(255))
BEGIN
    SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
    FROM locations
    JOIN country ON country.country_code_alpha2 = locations.country_code
    JOIN companies ON companies.company_id = locations.company_id
    LEFT JOIN payroll ON payroll.location_id = locations.location_id
    WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
    GROUP BY locations.location_id
    ORDER BY companies.company_name;
END

Kindly help...

Thanks,
Jassim[^]

Technology News @ www.JassimRahma.com

Mycroft Holmes12-Aug-16 14:21

12-Aug-16 14:21

Same answer as last week! You need to split the string into individual values.

Never underestimate the power of human stupidity
RAH

Jassim Rahma13-Aug-16 5:32

13-Aug-16 5:32

it is indiviual values. For example, If I try:

SQL

WHERE IN("BH", "US", "FR")

it will work but when I try:

SQL

IN(param_country)

then pass "BH", "US", "FR" it won't work.

Technology News @ www.JassimRahma.com

Richard MacCutchan13-Aug-16 6:03

Richard MacCutchan

13-Aug-16 6:03

What is the exact content of param_country when you try to execute this procedure? The error message is clearly telling you that some part of it is not valid syntax for this version of MySQL.

Jassim Rahma13-Aug-16 7:08

13-Aug-16 7:08

It's northing to do with param_country because I am getting the problem when I run the stored procedure on MySQL Server console as well (without any C#)

For example, if I run:

SQL

CALL sp_populate_memo_country_companies("BH")

it works fine but when I run:

SQL

CALL sp_populate_memo_country_companies("BH", "US")

I get:

[SQL] CALL sp_populate_memo_country_companies("BH", "US")

[Err] 1318 - Incorrect number of arguments for PROCEDURE bluefile.sp_populate_memo_country_companies; expected 1, got 2

Technology News @ www.JassimRahma.com

Richard MacCutchan13-Aug-16 8:29

Richard MacCutchan

13-Aug-16 8:29

Jassim Rahma wrote:
Incorrect number of arguments

What part of that message do you find difficult to understand?

Jassim Rahma13-Aug-16 11:12

13-Aug-16 11:12

this part:

SQL

CREATE DEFINER=<code>root</code>@<code>127.0.0.1</code> PROCEDURE <code>sp_populate_memo_country_companies</code>(IN param_country varchar(255))

there is only argument which is param_country which I am passing but maybe in the wrong way..

I mean in my above case, how can I pass an IN parameter for:

SQL

WHERE country_code IN (param_country)

Technology News @ www.JassimRahma.com

Victor Nijegorodov13-Aug-16 11:15

Victor Nijegorodov

13-Aug-16 11:15

Jassim Rahma wrote:

but when I run:

SQL

CALL sp_populate_memo_country_companies("BH", "US")

I get:

[SQL] CALL sp_populate_memo_country_companies("BH", "US")
 
[Err] 1318 - Incorrect number of arguments for PROCEDURE bluefile.sp_populate_memo_country_companies; expected 1, got 2

Just pack all the patterns for IN clause in one varchar/string value and pass this value as a single argument!

Jassim Rahma13-Aug-16 11:21

13-Aug-16 11:21

I tried:

SQL

CALL sp_populate_memo_country_companies("BH, US")

but returns no rows when BH alone returns 5 rows.

Technology News @ www.JassimRahma.com

Mycroft Holmes13-Aug-16 14:04

13-Aug-16 14:04

Does this not tell you something, SQL treats the parameter as a single value and you get an error! You need to split the string into individual values.

You need a split function for MySQL below is the SQL Server version I use

SQL

ALTER   FUNCTION [dbo].[fn_Split]
 (@List  varchar(8000), @Delimiter char(1))

RETURNS @Results table
 (Item varchar(8000),ID int Identity(1,1))

AS

begin
 declare @IndexStart int
 declare @IndexEnd int
 declare @Length  int
 declare @Word  varchar(8000)

 set @IndexStart = 1
 set @IndexEnd = 0

 set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
    set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)

    If @Delimiter = char(32) 
        set @IndexEnd = charindex(Space(1), @List, @IndexStart)

    if @IndexEnd = 0
        set @IndexEnd = @Length + 1

    set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
    set @IndexStart = @IndexEnd + 1

    INSERT INTO @Results(Item)
    SELECT @Word
end

return
end

Then you can use a join to filter the location table

SQL

DECLARE @StringParam VARCHAR(100)= 'AA,BB,CC'

--Test
SELECT * FROM dbo.fn_split(@StringParam,',')

SELECT * FROM Location L
INNER JOIN dbo.fn_split(@StringParam,',') S ON S.item = l.country_code

This is SQL Server code, you may need to change it for MySql

Never underestimate the power of human stupidity
RAH

Jassim Rahma15-Aug-16 8:16

15-Aug-16 8:16

how can I convert this to MySQL's PL/SQL?

Technology News @ www.JassimRahma.com

Mycroft Holmes15-Aug-16 13:02

How do Coders Get Database Experts? For a project

15-Aug-16 13:02

That is your job, I don't run MySQL. You need to get to know PL SQL or search the support sites for specific problems you run into. There is nothing in there that is specific to TSQL.

Never underestimate the power of human stupidity
RAH

TheOnlyRealTodd8-Aug-16 20:58

TheOnlyRealTodd

8-Aug-16 20:58

So here's the thing. I am a new coder and I am working on my first big project from scratch. My project is using ASP.NET MVC, Google Maps API, Entity Framework, with SQL Server.

I got a very early protype started on my local machine but I'm starting to run into database design complications. I am already new to coding, but somehow have managed to figure out MVC and get a Web API going. However, I am even less experienced than that with databases and database administration. I know how to add/remove database objects and set up tables and all that jazz; that's not a big deal. But I'm having questions such as:

*What is the best way to associate user accounts in my system with their business' information...
*I want my users to be able to add/remove products to their business' inventory table... What is the best way to set up the database to store each individual product.
*What is a good way to pair the information given to me via business owners with the data I pull from Google API.

*Should I create a new object for each inventory item or just list it in a long text string of all inventory items since no details will be needed?

*When do I have my application create an entire new database versus just add a new table in an existing database? For example, should user accounts and businesses be in two separate databases?

And more...

These questions are less coding questions and more database setup/administration questions... Questions I'd love to ask someone who has made a big modern commercial database before that handles accounts and inventory and all that. Obviously, coding questions will come up too but a lot of it is just database design.

So, my real question here is... How do most coders do this? I would iamgine a lot of coders are not DB experts... Should I buy a Database Administration book and read it? Should I consult an expert? Since this will be a data-driven application, database setup is important. Thanks!

PS: Please, don't worry about me being overwhelmed. I mean, if this is something that will seriously take 6 months of dedicated study, it may be better left for me to call up someone else... But even though I just started programming a few months back, I'm actually loving getting into all of this and it is not intimidating at all.

modified 9-Aug-16 4:51am.

Re: How do Coders Get Database Experts? For a project

Mycroft Holmes9-Aug-16 13:04

Re: How do Coders Get Database Experts? For a project

9-Aug-16 13:04

Do NOT get a book on database administration, yet. Get a book on database DEVELOPER, admin and dev are very different roles. There is also as site with sample database, ah found it[^], pick one close to your industry and try and understand why the design was used. He is pretty good and faithful to the rules of DB design.

After you have got a feel for DB design feel free to ask silly, or not so silly question here.

Your first 3 questions are answered by foreign keys
You should create 1 table per CLASS of inventory (if an items attributes are so different to an existing class then create a new class).

Your application should NEVER create a new database except for backup (and this should be an admin job not application).

If your scale is going to so huge (as in multiple of terabytes) that storage is an issue then you could split the data into different databases but I have never had to do that.

Never underestimate the power of human stupidity
RAH

Nathan Minier10-Aug-16 1:21

Nathan Minier

10-Aug-16 1:21

If you're working on enterprise or Web Applications, it's pretty much a given that you'll need to understand how databases work. There's not getting away from the fact that you can mess yourself up badly if you don't understand normalization, concurrency, and relationships.

The current bar is SQL, and if you're interested in web apps I would strongly encourage you to familiarize yourself with relational design; your model class designs should be informed by the relational system and, more importantly, normalization.

Once you understand the relational schema, then make your life easier by having a look at document stores like Mongo.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Re: How do Coders Get Database Experts? For a project

jschell12-Aug-16 11:15

jschell

12-Aug-16 11:15

TheOnlyRealTodd wrote:
I mean, if this is something that will seriously take 6 months of dedicated study

Not sure how to address that part. A database admin who comes into a shop with no other admin should have about 5 years of experience doing just that. So 5 years of a 40 hour a week job.

A place that wants to hack it a bit more with small initial aspirations but whose goal is to deliver a product could get by with a developer who has had 2 years of experience working with databases via a programming language (so familiar with the database but primarily programming is C#, Java, etc.)

TheOnlyRealTodd wrote:
How do most coders do this?

Do it every day for years.

TheOnlyRealTodd wrote:
Since this will be a data-driven application

Very little that isn't but that doesn't insure that you must use a database.

TheOnlyRealTodd wrote:
Please, don't worry about me being overwhelmed...I'm actually loving getting into all

So dive in. You can't learn it if you never start.

There are books about programming with databases. You should find one of those. Database design is a different issue just as design itself is a different issue and I have never found a book that really teaches design well (of any sort.) The basics of programming however does lend itself well to books.

If you don't mind reading then one that focuses on database programming and another that attempts to teach database admin will, over time, help. I have never been a DB Admin but the admin books I do have have been helpful.

As a suggestion try to keep your design as simple as possible.

How to pass varchar as WHERE field IN param

Jassim Rahma5-Aug-16 5:14

Re: How to pass varchar as WHERE field IN param

5-Aug-16 5:14

Hi,

I am trying to pass a varchar string from C# to MySQL stored procedure to be used in WHERE xx IN (param). I tried it in the basic way below but it's not working and giving no result.

can any one help please.

this is my C#:

sql_connection = new MySqlConnection(serverClass.connectionstring("BlueFile", "BlueFile", "Server"));
sql_connection.Open();

sql_command = new MySqlCommand("sp_populate_memo_country_companies", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.AddWithValue("param_country", Convert.ToString(cboToCountry.EditValue)).MySqlDbType = MySqlDbType.VarChar;

// Create data adapter object 
sql_adapter = new MySqlDataAdapter();
sql_adapter.SelectCommand = sql_command;

// Create a dataset object and fill with data using data adapter's Fill method 
data_set = new DataSet();
sql_adapter.Fill(data_set, "companies");
DataViewManager dataview_manager = new DataViewManager(data_set);
DataView main_dataview = dataview_manager.CreateDataView(data_set.Tables["companies"]);

cboToCompany.Properties.ValueMember = "location_id";
cboToCompany.Properties.DisplayMember = "company_name";
cboToCompany.Properties.DataSource = main_dataview;

DevExpress.XtraEditors.Controls.LookUpColumnInfo("department_name", 50, "department_name"));

cboToCompany.Properties.PopupSizeable = false;

cboToCompany.EditValue = null;

and this is my stored procedure:

SQL

SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
FROM locations
JOIN country ON country.country_code_alpha2 = locations.country_code
JOIN companies ON companies.company_id = locations.company_id
LEFT JOIN payroll ON payroll.location_id = locations.location_id
WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
GROUP BY locations.location_id
ORDER BY companies.company_name;

Thanks,
Jassim[^]

Technology News @ www.JassimRahma.com

Mycroft Holmes5-Aug-16 13:04