Hi Michael,
I'll take a look and get you back.
Пт, 18 ноя 2016, Michael Berry написал(а):
> 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
--
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
