http://coldfusion.com logo
Docs
Join the conversationJoin Slack
Channels
adobe
advent-of-code
auwcl
aws
books
bot-dev
box-products
cfeclipse
cfkrauts
cflint
cfml-beginners
cfml-general
cfml-tuning
cfsummit2022
cfwheels
ci
community_courses
css
devops-general
docker
docker-commandbox
documentation
events
friday-puzzle
fusion-reactor
fw1
ide
java-and-jvm
javascript
jobs
jobs-non-us
linen-dev
lucee
masacms
meta
migrations
mura
music
nosql
object-oriented
orm
perf-monitor
prog-general
slack-help
sql
taffy
testing
version-control
vuejs
water-cooler
Powered by Linen
sql
  • j

    John Wilson

    01/24/2023, 9:37 PM
    Any BCP experts here?
  • j

    John Wilson

    01/24/2023, 9:40 PM
    BCPChallenge.zip
    BCPChallenge.zip
    m
    3 replies · 2 participants
  • j

    John Wilson

    01/24/2023, 9:43 PM
    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.
  • b

    bdw429s

    01/24/2023, 9:44 PM
    For those of us not in the know, can you explain what BCP is? When I google it, I just find "Business Continuity Plan" 🙂
  • j

    John Wilson

    01/24/2023, 9:44 PM
    https://learn.microsoft.com/en-us/sql/relational-databases/import-export/create-a-format-file-sql-server?view=sql-server-ver16
    👍 1
  • j

    John Wilson

    01/24/2023, 9:44 PM
    command line bulk import utility
  • j

    John Wilson

    01/24/2023, 9:46 PM
    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.
  • p

    Patrick

    01/24/2023, 9:49 PM
    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?
  • j

    John Wilson

    01/24/2023, 9:49 PM
    bombs immediately near as I can tell. BCP errors are horrible
  • j

    John Wilson

    01/24/2023, 9:50 PM
    but there isn't a single field that long
  • j

    John Wilson

    01/24/2023, 9:50 PM
    usually this a row termination thing but I've verified it's LF
  • p

    Patrick

    01/24/2023, 9:53 PM
    there is always the secrecy of spaces that lurk
  • g

    gavinbaumanis

    01/24/2023, 10:18 PM
    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.
  • g

    gavinbaumanis

    01/24/2023, 10:22 PM
    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?
  • m

    Michael Gillespie

    01/24/2023, 10:27 PM
    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?
  • j

    John Wilson

    01/24/2023, 10:33 PM
    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
  • j

    John Wilson

    01/24/2023, 10:34 PM
    Delimiters are pipes and nothing escaped.
  • i

    iknowkungfoo

    01/28/2023, 6:01 AM
    @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

    iknowkungfoo

    01/28/2023, 6:09 AM
    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

    iknowkungfoo

    01/28/2023, 6:10 AM
    I'd appreciate if anyone could shed some light on this.
  • i

    iknowkungfoo

    01/28/2023, 6:11 AM
    In the meantime, I'm just adding an
    active
    column to table
    x
    and turning off the records with a list.
  • c

    chris-schmitz

    01/28/2023, 9:25 AM
    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
  • c

    chris_hopkins

    01/29/2023, 12:02 AM
    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?
  • a

    Adam Cameron

    02/06/2023, 10:14 AM
    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.
    s
    m
    2 replies · 3 participants
  • j

    John Wilson

    02/07/2023, 10:35 PM
    Any SQLS dbas here that can tell me how to lock down user access to
    selects
    only?
    g
    r
    3 replies · 3 participants
  • g

    gsr

    03/11/2023, 3:38 PM
    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
    d
    d
    4 replies · 3 participants
  • g

    gsr

    03/11/2023, 3:38 PM
    its weird how do i do sql_mode=only_full_group_by or is there a universal way to make the query work
    a
    1 reply · 2 participants
  • g

    gsr

    03/11/2023, 3:40 PM
    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',''));
  • w

    websolete

    03/11/2023, 4:01 PM
    the error states the problem, you are including columns by way of the * that are not part of the group by statement
  • w

    websolete

    03/11/2023, 4:02 PM
    you should brush up a bit on basic sql
    a
    1 reply · 2 participants
Powered by Linen
Title
w

websolete

03/11/2023, 4:02 PM
you should brush up a bit on basic sql
a

Adam Cameron

03/11/2023, 6:28 PM
Yup. And "paying attn to error messages"
View count: 3