Any BCP experts here?

BCPChallenge.zip

I am willing to pay for help in getting this working. Zip file contains the data source (idm file) the best I've come up with in terms of a fmt file and the SQL required to create the table. I keep getting truncation errors even with a column width of 2048.

For those of us not in the know, can you explain what BCP is? When I google it, I just find "Business Continuity Plan" 🙂

command line bulk import utility

fmt file was generated with

bcp veritiadmin.tmp.sedol_axioma format nul -T -n -t"|" -x -f axiomaSedolBCP.fmt -S vdbs.veriti.local

and runs like this

bcp veritiadmin.tmp.sedol_axioma in "C:\Axioma\Downloader\output\extractedFiles\flatfile\AXWW4-MH.20221227.idm" -f "C:\Axioma\Downloader\output\axiomaSedolBCP.fmt" -F 7 -S vdbs.veriti.local -T -N

The real data file has 52K rows and needs to be imported every morning.

Truncation, always the fun side with bulk importing...NOT. Any specific errors or outputs immediately or just on a certain row; if that row maybe double check the data?

bombs immediately near as I can tell. BCP errors are horrible

but there isn't a single field that long

usually this a row termination thing but I've verified it's LF

there is always the secrecy of spaces that lurk

As a suggestion for things to check... I recall there was a CFML issue / bug where you HAVE TO have text starting on the same line - otherwise it would fail. (My memory isn't perfect sorry - so it might not be ormexecutequery... But there was certainly an issue where the premise of my example was a pain-point for me for a little while.... you HAVE TO have the actual select statement / arguments / whatever starting on the same line; like

ormExecuteQuery("SELECT .....

Because if you had

ormExecuteQuery(
"SELECT...

It would fail.

Other things to check (just shooting from the hip) Are all strings "properly" formed

"this is a 'String' "

Are dates strings? or objects? What does BCP expect? I tend to always make sure that the date is in YYYY-MM-DD format - regardless of your regions conventi0on. DB's always "just" get the monthh and date the right way around - if you use YYY-MM-DD format. Do you have commas "," in strings? In that is a COMMA a delimiter - and thus "mucking up" count of expected values?

I noticed in your fmt file you are defining the fields as Latin1, are you sure there are no unicode chars (accents marks, those weird double tics, converted quotes and double quotes, etc) in the data?

that was generated from the bcp command above - I tried first with nvarchar then switched to varchar in the process of troubleshooting, though I was using the non-xml format for the first tests

Delimiters are pipes and nothing escaped.

@Scott Stroz You got some 'splainin' to do! This is in MySQL • I have two tables,

x

and

y

. •

x.cpt_code

is

nvarchar(255)

•

y.cpt_code_id

is an

int.

•

x.cpt_code

can have a comma delimited list of numbers • I need to join the two tables on the numeric value of the

int

. Figured that if I cast the string to an

int

, then the list would wash out. Not the case.

x on cast(x.cpt_code as unsigned) = y.cpt_code_id

matches a single number

a

to a list of numbers where the first element is

a

.

x on x.cpt_code = y.cpt_code_id

does the exact same thing. However, if I cast the

int

to

nchar

, the string matches only when string is the exact numeric value of the

int

.

on x.cpt_code = cast(y.cpt_code_id as nchar)

For example:

select CAST('76830,76856' as UNSIGNED) as id;
Returns 76830

I can't find any documentation that shows why this is the case. Here's a visual of the matches when trying to cast the string to an

int

.

I'd appreciate if anyone could shed some light on this.

In the meantime, I'm just adding an

active

column to table

x

and turning off the records with a list.

cast( value as UNSIGNED)

stops processing

value

as soon as it encounters something that does not represent a number, iirc. So here it would stop at the comma. What you could try is to build the join on a LIKE clause:

ON x.opt_code LIKE concat( '%', y.opt_code_id, '%')

Definitely not the fastest join, but, it should work

There is totally a way of doing this faster in tsql, string_split can convert a column containing CSV data into a joinable table which makes dealing with very silly old data much easier. Have heard people using JSON data types to do this also but not sure how off top of head. Hopefully there may be an equivalent in mysql?

Hi. I'm replicating a question I've asked on the Working Code Podcast discord. Interested in hearing from anyone with MySQL DBA experience.

I am going to ask a contextless question because any context will influence the point of the question, and accordingly the answer.

THIS IS IMPORTANT: Any answers given should be from existing knowledge, and not from googling. I'm not testing ppl's ability to google stuff. I am trying to establish what "common knowledge" on the topic is.

MySQL "CREATE TABLE ... SELECT Statements". Any observations?

Cheers. Sorry it's vague.

Any SQLS dbas here that can tell me how to lock down user access to

selects

only?

i am running this query

select individual_Ratings.*,count(rating) as tRating from individual_Ratings where productID = 99 group by rating

and my query is giving me an error on this

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shopzone1.individual_Ratings.individualid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

its weird how do i do sql_mode=only_full_group_by or is there a universal way to make the query work

if i do in mysql console i am getting this

mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    49
Current database: *** NONE ***

ERROR 1064 (42000): 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 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): 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 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 1064 (42000): 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 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): 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 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

the error states the problem, you are including columns by way of the * that are not part of the group by statement