Hi Bill,
Long ago you offered to look into this if I could send you a database
example. I have upgraded to J804 and updated the packages, but I still get
the same behavior. Not being able to do bulk insert is preventing me from
getting my coworkers to adopt a useful application because the step of
writing out a csv file and then importing it into the database is something
only I am willing to put up with. Here is a script to create a database,
put some data into it, read the data back, and then fail trying to udr
ddins:
===========================================================================================================
NB. Test data base to show when ddins breaks. (Just change the name of the
table to something that works on your system)
load 'odbc'
ch=. ddcon 'dsn=t4bwhsql'
'create table t4b.sales.x_mjab_test(field1 varchar(5), field2 int)' ddsql ch
'insert into t4b.sales.x_mjab_test values(''aaaaa'',0)' ddsql ch
'insert into t4b.sales.x_mjab_test values(''bbbbb'',0)' ddsql ch
'insert into t4b.sales.x_mjab_test values(''ccccc'',0)' ddsql ch
'insert into t4b.sales.x_mjab_test values(''ddddd'',0)' ddsql ch
NB. demonstrate that the data got in OK
sh=. 'select * from t4b.sales.x_mjab_test' ddsel ch
data=. ddfch sh, _1
NB. this attempt to insert the rows we just read fails. dderr shows a
mangled version of the table name.
('select * from t4b.sales.x_mjab_test';data) ddins ch
On Wed, Oct 5, 2016 at 8:01 PM, bill lam <[email protected]> wrote:
> ddcoltype is a diagnosis tool.
>
> It seems the catalog field (the leading columns) are always blank.
>
> Can you send me a small sample database to test?
>
> On Oct 6, 2016 6:48 AM, "Michael Berry" <[email protected]> wrote:
>
> > The results from ddcoltype look OK to me. I'm not sure what they all
> mean
> > (this verb is not mentioned on the
> > http://code.jsoftware.com/wiki/ODBC/Data_Driver WIKI page).
> >
> > query ddcoltype ch
> > +++++------------------++-+-------+--+---+-+-++--+-+
> > |||||sales_region ||1|varchar|12|10 |0|0||12|0|
> > +++++------------------++-+-------+--+---+-+-++--+-+
> > |||||country ||2|varchar|12|256|0|0||12|0|
> > +++++------------------++-+-------+--+---+-+-++--+-+
> > |||||historic_save_rate||3|float |6 |53 |0|0||6 |0|
> > +++++------------------++-+-------+--+---+-+-++--+-+
> > |||||target_save_rate ||4|float |6 |53 |0|1||6 |0|
> > +++++------------------++-+-------+--+---+-+-++--+-+
> >
> > I was not able to get ddsparm to work either. I tried writing to the
> > database some data I had just read from it.
> >
> > data =: ddfch sh, 10
> > ddend sh
> > 0
> > (q2;data) ddsparm ch
> > _1
> > dderr ''
> > ISI08 Bad arguments
> > q2
> > insert into t4b.sales.r_bl_inside_sales_ccf_country_target(sales_
> region,
> > country, historic_save_rate, target_save_rate) values(?,?,?,?)
> >
> > On Wed, Oct 5, 2016 at 11:34 AM, bill lam <[email protected]> wrote:
> >
> > > Sorry Michael, I misread your message and only notice the insert
> > > statement in ddsql.
> > >
> > > I didn't try owner/catalog qualifiers. Can test what is the
> > > output of ddcoltype and is it a reasonable result?
> > >
> > > query ddcoltype ch
> > >
> > > also test if ddsparm can work, something like this
> > >
> > > ('insert into t4b.sales.r_bl_inside_sales_ccf_country_target(sales_
> > > region,
> > > country, historic_save_rate, target_save_rate) values(?,
> > > ?,?,?)';data) ddsparm ch
> > >
> > >
> > > Ср, 05 окт 2016, Michael Berry написал(а):
> > > > Hi Bill,
> > > >
> > > > I'm still confused. The query in my example *is* a select statement
> and
> > > it
> > > > works fine as such:
> > > >
> > > > query
> > > >
> > > > select sales_region, country, historic_save_rate, target_save_rate
> from
> > > > t4b.sales.r_bl_inside_sales_ccf_country_target
> > > >
> > > > sh=: query ddsel ch
> > > >
> > > > sh
> > > >
> > > > 6260928
> > > >
> > > > ddfet sh
> > > >
> > > > ┌────────┬─────────┬───┬────┐
> > > >
> > > > │Americas│Argentina│0.8│0.83│
> > > >
> > > > └────────┴─────────┴───┴────┘
> > > >
> > > > The error message is "Invalid Object Name".
> > > >
> > > > I rewrote my application to generate insert statements for ddsql
> which
> > > > works fine but ddins still seems to be complaining about a table name
> > > which
> > > > is a transformation of the name in my query.
> > > >
> > > > On Tue, Oct 4, 2016 at 7:59 PM, bill lam <[email protected]>
> wrote:
> > > >
> > > > > The syntax is incorrect for ddins. It expected a select statement.
> > see
> > > > >
> > > > > http://code.jsoftware.com/wiki/ODBC/Methods/Bulk_Insert
> > > > >
> > > > > you may also try ddparm/ddsparm
> > > > >
> > > > > On Oct 5, 2016 6:26 AM, "Michael Berry" <[email protected]>
> wrote:
> > > > >
> > > > > > Hello All,
> > > > > >
> > > > > > I apologize if this is a repeat. I am a subscriber to this list
> and
> > > > > didn't
> > > > > > see my post, so I feared maybe no one else did either.
> > > > > >
> > > > > > I am having trouble doing a bulk insert using ddins from the J
> ODBC
> > > > > > library. The error message is "Invalid object name" and the
> object
> > > name
> > > > > > included in the error message is indeed invalid, but also not
> what
> > I
> > > > > typed.
> > > > > > All the underscores and periods have been removed. Note that
> other
> > > SQL
> > > > > > operations, including INSERT, work fine with this same table.
> Does
> > > anyone
> > > > > > know what is going on? The database I am connected to is SQL
> > Server.
> > > My J
> > > > > > version is 802. My operating system is Windows 7 Professional (64
> > > bit)
> > > > > >
> > > > > > Any help appreciated. -Michael
> > > > > >
> > > > > >
> > > > > >
> > > > > > Some sample output for context:
> > > > > >
> > > > > > dddbms ch
> > > > > >
> > > > > > ┌────┬────────┬─────────┬──────────┬─────┬──────────┬───────
> > > > > > ──────┬──────────┬─┬─┬───┐
> > > > > >
> > > > > > │ODBC│t4bwhsql│US\mberry│T4BSQL01AD│MSSQL│12.00.5000│sqlncli
> > > > > > 11.dll│11.00.2100│3│1│256│
> > > > > >
> > > > > > └────┴────────┴─────────┴──────────┴─────┴──────────┴───────
> > > > > > ──────┴──────────┴─┴─┴───┘
> > > > > >
> > > > > > query
> > > > > >
> > > > > > select sales_region, country, historic_save_rate,
> target_save_rate
> > > from
> > > > > > t4b.sales.r_bl_inside_sales_ccf_country_target
> > > > > >
> > > > > > sh=: query ddsel ch
> > > > > >
> > > > > > sh
> > > > > >
> > > > > > 6260928
> > > > > >
> > > > > > ddfet sh
> > > > > >
> > > > > > ┌────────┬─────────┬───┬────┐
> > > > > >
> > > > > > │Americas│Argentina│0.8│0.83│
> > > > > >
> > > > > > └────────┴─────────┴───┴────┘
> > > > > >
> > > > > > ddend sh
> > > > > >
> > > > > > 0
> > > > > >
> > > > > > 'truncate table t4b.sales.r_bl_inside_sales_ccf_country_target'
> > > ddsql ch
> > > > > >
> > > > > > 0
> > > > > >
> > > > > > 'insert into t4b.sales.r_bl_inside_sales_
> ccf_country_target(sales_
> > > > > region,
> > > > > > country, historic_save_rate, target_save_rate)
> values(''Americas'',
> > > > > > ''Argentina'', 0.8, 0.83)' ddsql ch
> > > > > >
> > > > > > 0
> > > > > >
> > > > > > sh=: query ddsel ch
> > > > > >
> > > > > > sh
> > > > > >
> > > > > > 71014208
> > > > > >
> > > > > > ddfet sh
> > > > > >
> > > > > > ┌────────┬─────────┬───┬────┐
> > > > > >
> > > > > > │Americas│Argentina│0.8│0.83│
> > > > > >
> > > > > > └────────┴─────────┴───┴────┘
> > > > > >
> > > > > > ddend sh
> > > > > >
> > > > > > 0
> > > > > >
> > > > > > data
> > > > > >
> > > > > > ┌────────┬─────────────┬────────┬────┐
> > > > > >
> > > > > > │Americas│Argentina │0.820513│0.83│
> > > > > >
> > > > > > │Americas│Bolivia │0.923077│ 0.9│
> > > > > >
> > > > > > │Americas│Brazil │0.909091│ 0.9│
> > > > > >
> > > > > > │Americas│Canada │0.795918│0.81│
> > > > > >
> > > > > > │Americas│Chile │ 0.85 │0.86│
> > > > > >
> > > > > > │Americas│Colombia │0.904762│ 0.9│
> > > > > >
> > > > > > │Americas│Costa Rica │0.805556│0.82│
> > > > > >
> > > > > > │Americas│Ecuador │0.888889│ 0.9│
> > > > > >
> > > > > > │Americas│Mexico │0.840909│0.85│
> > > > > >
> > > > > > │Americas│other │ 0.89 │ 0.9│
> > > > > >
> > > > > > │Americas│Peru │0.666667│0.68│
> > > > > >
> > > > > > │Americas│United States│0.837709│0.85│
> > > > > >
> > > > > > └────────┴─────────────┴────────┴────┘
> > > > > >
> > > > > > (query;data) ddins ch
> > > > > >
> > > > > > _1
> > > > > >
> > > > > > dderr ''
> > > > > >
> > > > > > 42S02 208 [Microsoft][SQL Server Native Client 11.0][SQL
> > > Server]Invalid
> > > > > > object name *'t4bsalesrblinsidesalesccfcountrytarget'*. - more
> > error
> > > > > info
> > > > > > available (1)
> > > > > > ------------------------------------------------------------
> > > ----------
> > > > > > For information about J forums see http://www.jsoftware.com/
> > > forums.htm
> > > > > ------------------------------------------------------------
> > ----------
> > > > > For information about J forums see http://www.jsoftware.com/
> > forums.htm
> > > > ------------------------------------------------------------
> ----------
> > > > For information about J forums see http://www.jsoftware.com/
> forums.htm
> > >
> > > --
> > > regards,
> > > ====================================================
> > > GPG key 1024D/4434BAB3 2008-08-24
> > > gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3
> > > gpg --keyserver subkeys.pgp.net --armor --export 4434BAB3
> > > ----------------------------------------------------------------------
> > > For information about J forums see http://www.jsoftware.com/forums.htm
> > >
> > ----------------------------------------------------------------------
> > For information about J forums see http://www.jsoftware.com/forums.htm
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
>
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
