Anyone here a SQL database admin/designer

[djroge1]

I'm in a class to learn basic/beginning SQL and I'm stuck with the INSERT INTO command. It's giving me an error and I cannot figure out how to fix it.

 

Anyone able to help?

[Guest FarnsBarns]

I'm in a class to learn basic/beginning SQL and I'm stuck with the INSERT INTO command. It's giving me an error and I cannot figure out how to fix it.

 

Anyone able to help?

 

Yep, what's the scenario and what's the error?

[djroge1]

great!

 

Here is the table I created (no problems creating this):

 

create table job_title_v5(

EEO_1 varchar(35),

title varchar(25),

description varchar (255),

exempt_non_exempt_status varchar);

 

 

Here is the INSERT INTO (error follows below it).

 

insert into job_title_v5

(EEO_1, title, description,exempt_non_exempt_status)

values

('office_clerical','accounting_clerk','Computes,_classifies,_records,_and_verifies_numerical_data_for_use_in_maintaining_accounting_records', 'nonexempt'),

('official_managers','assistant_manager','Supervises_and_coordinates_activities_of_workers_in_department_of_food_store_Assists_store_manager_in_daily_operations_of_store','exempt'),

('sales_worker','bagger','Places_customer_orders_in_bags_Performs_carryout_duties_for_customers','nonexempt'),

('sales_worker','cashier','Operates_cash_register_to_itemize_and_total_customer’s_purchases_in_grocery_store','nonexempt'),

('technician','computer_support_specialist','Installs,_modifies,_and_makes_minor_repairs_to_personal_computer_hardware_and_software_systems,_and_provides_technical_assistance_and_training_to_system_users','nonexempt'),

('official_managers','dir_of_Fin_and_accounting','Plans_and_directs_the_finance_and_accounting_activities_for_Kudler_Fine_Foods','exempt'),

('craft_workers','asst_bakery_pastry','Obtains_or_prepares_food_items_requested_by_customers_in_retail_food_store','nonexempt'),

('operatives','asst_butcher_seafood','Obtains_or_prepares_food_items_requested_by_customers_in_retail_food_store','nonexempt'),

('office_clerical','stocker','Stores,_prices_and_restocks_merchandise_displays_in_store','nonexempt')

 

 

ERROR:

 

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated

The statement has been terminated.

[steve-1963]

You haven't specified a column size (just varchar) for the last column - what has it defaulted to and is it less than what it needs to be for your data? Does running describe job_title_v5 produce any output?

[djroge1]

You haven't specified a column size (just varchar) for the last column - what has it defaulted to and is it less than what it needs to be for your data? Does running describe job_title_v5 produce any output?

 

Ok I'll see about correcting that varchar for the last col.

 

Here is the message I get when I run describe job_title_v5

 

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'describe'.

[djroge1]

You haven't specified a column size (just varchar) for the last column - what has it defaulted to and is it less than what it needs to be for your data? Does running describe job_title_v5 produce any output?

 

I made the last column a varchar(15) so that should have plenty of space and I'm still getting the original error.

[Guest FarnsBarns]

You say your table creates OK but I think you need to specify a maximum length for the Exempt....... column.

 

EDIT: OK beat me to it. Hang on, I'm trying your insert.

[Riverside]

Fascinating.

[Guest FarnsBarns]

Well, I had to try the insert because it all looked fine and it worked for me. how are you doing this, PHP script, command line, PHPmyAdmin???

 

What version of MySQL are you using, what's the environment and version of OS and PHP etc?

[Guest FarnsBarns]

When you corrected the column size did you remember to do ))

 

create table job_title_v5(

EEO_1 varchar(35),

title varchar(25),

description varchar (255),

exempt_non_exempt_status varchar(15));

[djroge1]

Well, I had to try the insert because it all looked fine and it worked for me. how are you doing this, PHP script, command line, PHPmyAdmin???

 

What version of MySQL are you using, what's the environment and version of OS and PHP etc?

 

So many questions that I don't know because I am an extreme beginner... and it just kicked me off and won't let me back on.

 

It's a class lab for one thing.

 

I'm typing them first into notepad and then copy/paste into a new query window. Headed to class right now so I'll be off line for a while.

[djroge1]

When you corrected the column size did you remember to do ))

 

create table job_title_v5(

EEO_1 varchar(35),

title varchar(25),

description varchar (255),

exempt_non_exempt_status varchar(15));

 

I'll check as soon as I can get back online with the system.

[Guest FarnsBarns]

# Sorry if I bombarded you. I do get carried away ;)

 

# OK,

 

# The only possible issues I can see are that you could have missed the extra ) at the end # of the CREATE TABLE (I highlighted in red)

 

create table job_title_v5(

EEO_1 varchar(35),

title varchar(25),

description varchar (255),

exempt_non_exempt_status varchar(15));

 

# and your MySQL version might require a ; at the end of the INSERT INTO (Highlighted in red again)

 

insert into job_title_v5

(EEO_1, title, description,exempt_non_exempt_status)

values

('office_clerical','accounting_clerk','Computes,_classifies,_records,_and_verifies_numerical_data_for_use_in_maintaining_accounting_records', 'nonexempt'),

('official_managers','assistant_manager','Supervises_and_coordinates_activities_of_workers_in_department_of_food_store_Assists_store_manager_in_daily_operations_of_store','exempt'),

('sales_worker','bagger','Places_customer_orders_in_bags_Performs_carryout_duties_for_customers','nonexempt'),

('sales_worker','cashier','Operates_cash_register_to_itemize_and_total_customer’s_purchases_in_grocery_store','nonexempt'),

('technician','computer_support_specialist','Installs,_modifies,_and_makes_minor_repairs_to_personal_computer_hardware_and_software_systems,_and_provides_technical_assistance_and_training_to_system_users','nonexempt'),

('official_managers','dir_of_Fin_and_accounting','Plans_and_directs_the_finance_and_accounting_activities_for_Kudler_Fine_Foods','exempt'),

('craft_workers','asst_bakery_pastry','Obtains_or_prepares_food_items_requested_by_customers_in_retail_food_store','nonexempt'),

('operatives','asst_butcher_seafood','Obtains_or_prepares_food_items_requested_by_customers_in_retail_food_store','nonexempt'),

('office_clerical','stocker','Stores,_prices_and_restocks_merchandise_displays_in_store','nonexempt');

 

/* All the hashes and slash-stars in this post will make the lines remarks in Mysql so you can copy and paste this entire post. If it works one of the two things I mentioned was wrong as they're all I changed */

[djroge1]

I did find out it is SQL server management 2008

[Guest FarnsBarns]

Ahh, It's MS SQL.

 

Shouldn't matter, check the ) and ; I already mentioned. Other than that you have it right.

[ChanMan]

You are the shiznit of SQL Farns!

[damian]

Hmmmm............Don't cut the blue wire !!!!!!! :unsure: ..

[dbreslauer]

in this line in your insert data:

 

('technician','computer_support_specialist','Installs,_modifies,_and_makes_minor_repairs_to_personal_computer_hardware_and_software_systems,_and_provides_technical_assistance_and_training_to_system_users','nonexempt'),

 

the value 'computer_support_specialist' is longer than 25 characters, and exceeds the size of the field it's being inserted into (title is defined as varchar(25)).

 

hth,

Don

[Guest FarnsBarns]

in this line in your insert data:

 

('technician','computer_support_specialist','Installs,_modifies,_and_makes_minor_repairs_to_personal_computer_hardware_and_software_systems,_and_provides_technical_assistance_and_training_to_system_users','nonexempt'),

 

the value 'computer_support_specialist' is longer than 25 characters, and exceeds the size of the field it's being inserted into (title is defined as varchar(25)).

 

hth,

Don

 

Well spotted. I wonder why my implementation of MySQL allows that through when I test it. Now I'm gonna check if it truncated and missed off 3 characters.

 

That should get you sorted DJR, let us know!

[ChanMan]

Funny... but Ima start using a guitar forum for my Db questions.....

You guys are incredible. At least to to this E-type guy, anyway!

 

[Guest FarnsBarns]

It's this forum specifically, I run a few fora, one in particular for my disc golf club, if you shout in there, off topic, just on the off chance that someone knows, you get lots of responses informing you that you asked a stupid, off topic question. Here people try to help. Gibson fanboys, like myself, must just be a better class of person.

 

I learned something here too, my implementation of MySQL does not report, or stop, on truncated values. That's quite serious! Gonna have a look through the ini file later.

[dbreslauer]

 

I learned something here too, my implementation of MySQL does not report, or stop, on truncated values. That's quite serious! Gonna have a look through the ini file later.

 

I kinda like that actually: not sure i'd want a hard error on INSERT, and form validation could handle this sort of check up front. Better yet, relate a lookup table, and store a smaller value here.

[Guest FarnsBarns]

I kinda like that actually: not sure i'd want a hard error on INSERT, and form validation could handle this sort of check up front. Better yet, relate a lookup table, and store a smaller value here.

 

Form validation is definitely the first "defence" but it could wreak havoc in an app if a truncated value is written in the DB. That said, I usually set everything to varchar(255) anyway.

 

A related table is a proper solution.

[djroge1]

As it turns out I had more than one error.

 

Another problem I found was that the date was backwards as I typed in mm/dd/yyyy when it should have been the other way around. It also said that I was trying to add data into a generated primary key.

 

I finally got it to work last night after I got home from school. Thanks for the help thus far.

 

This week I have to read and learn how to JOIN two tables using both LIKE and BETWEEN to restrict record selection. Also something about a UNION?

 

I discovered that while databases are good to learn and understand these things are better left to people who "get it" better than I do.