#8799: Failed to insert data when data is more then the specified length

ranarohitp(*) reported 2013-05-02T05:30:07Z · last modified 2013-05-21T18:01:37Z

Failed to insert data when data is more then the specified length

Dev	ivangron
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	Tools - Import Tool
Version	14.0

ADS version : ads-windows-x64-14.0-dev-44
Database:Sybase Anywhere 12/Sybase ASE15.7

Create following table
CREATE TABLE dbo.varying_text2 (
   c1   varchar(10) NULL,
   c2   unitext NULL,
   c3   univarchar(5) NULL,
   c4   unichar(5) NULL,
   c5   nchar(2) NULL
   )
GO

Now go to Tools->Import Data->Connect to Sybase ASE 15.7->Import file(Use attached file Exp.txt),click next->From Schema and table drop down select respective values and click next->In option tab from "Destination" drop down->choose "Import into database",click on next button->On Transaction tab set Transaction type:Batch,Batch size:1000,Threshold:0and wait time-1 and say next.This will throw error message over ADS window console.

If you follow the above given step and change option from "Import into database" to "preview INSERT statements" ,now copy generated script and insert it,record will get inserted as it will truncate the rest of the data.

The message displayed over console is wrong it says "completed : Inserted n row(s)".But when we do a select not a single record is inserted.Please change the message as well.

3 attachments

Exp.txt

2013-05-02T05:39:01Z

181 B
InsertFailed.png

2013-05-02T05:46:55Z

33 KB
Screen Shot 2013-05-20 at 5.20.46 PM.png

2013-05-21T00:14:22Z

99 KB

All Comments (9) Change History

SachinPrakash 2013-05-02T15:07:38Z

What is the behavior in ADS v13?

ranarohitp(*) 2013-05-03T06:08:33Z

In ADS v13(rc-4) it works fine.

SachinPrakash 2013-05-03T17:14:05Z

I'm not sure if we can do anything about sybase anywhere rejecting the insert instead of truncating the text. I would have always expected it to reject and not insert. But we should correct the message displayed in the console.

SachinPrakash 2013-05-14T15:43:57Z

fyi - in v14, we're now using prepared stmts and jConnect's bulk import parameter to import data into Sybase ASE when transaction type is set to bulk.

funfun 2013-05-17T04:10:48Z

Hi Tariq,

Can you please try this test case as well? By following test steps described above and using the provided test data file, I was not able to reproduce the error against the Sybase ASE 15.0 server running on xx.xxx.11.46. Three rows of data are successfully imported and truncation is performed where necessary. I did see the following message get logged, does not seem to be relevant, though:

<2013-05-16 21:04:19,046> ERROR [import] <com.aquafold.datastudio.tools.importtool.ImportThread> Prepared statement failed, fallback to insert statement.
java.sql.SQLException: JZ0SJ: Metadata accessor information was not found on this database. Please install the required tables as mentioned in the jConnect documentation.
   at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780)
   at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2665)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.prepareBCP(SybPreparedStatement.java:3427)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.<init>(SybPreparedStatement.java:209)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1406)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1307)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1278)
   at com.aquafold.aquacore.scripts.WrapperConnection.prepareStatement(WrapperConnection.java:64)
   at com.aquafold.datastudio.tools.importtool.ImportThread.process(ImportThread.java:258)
   at com.aquafold.datastudio.tools.importtool.ImportThread.run(ImportThread.java:98)

I also tried this test case on Sybase Anywhere 12.x and Sybase ASE 12.x, they don't support unitext data type. I am assuming this test case is Sybase ASE 15.x specific.

funfun 2013-05-19T01:22:11Z

I have logged a separate issue, issue #9021, for correcting the row count displayed in the console.

tariqrahiman 2013-05-21T00:18:39Z · (edited)

Import fails and gives me java.lang.ArrayIndexOutOfBoundsException. See attached screenshot.

Now if you hit back in the Import dialog and then hit next then we get a different error message as below:

Import started ...

Error: Row: 5 -- JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Type '7' not implemented.

Error: Row: 5 -- Type '7' not implemented.

Import completed.

funfun 2013-05-21T03:01:24Z

> Import fails and gives me java.lang.ArrayIndexOutOfBoundsException. See attached screenshot.

Here is the first error:
<2013-05-20 20:01:54,796> ERROR [import] <com.aquafold.datastudio.tools.importtool.ImportThread>
java.lang.ArrayIndexOutOfBoundsException: 56
   at com.sybase.jdbc4.tds.BCPToken.writeByte(BCPToken.java:2373)
   at com.sybase.jdbc4.tds.BCPToken.writeByte(BCPToken.java:2379)
   at com.sybase.jdbc4.tds.BCPToken.writeAPLOffSetTable(BCPToken.java:1677)
   at com.sybase.jdbc4.tds.BCPToken.writeAPL(BCPToken.java:1107)
   at com.sybase.jdbc4.tds.BCPToken.writeBulkData(BCPToken.java:330)
   at com.sybase.jdbc4.tds.Tds.sendBulkData(Tds.java:5244)
   at com.sybase.jdbc4.jdbc.SybBCP.bcpSendRow(SybBCP.java:1140)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeBulkBatch(SybPreparedStatement.java:1684)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeBatch(SybPreparedStatement.java:1722)
   at com.aquafold.aquacore.scripts.WrappedStatement.executeBatch(WrappedStatement.java:150)
   at com.aquafold.datastudio.tools.importtool.ImportThread.process(ImportThread.java:467)
   at com.aquafold.datastudio.tools.importtool.ImportThread.run(ImportThread.java:98)

> Now if you hit back in the Import dialog and then hit next then we get a different error message as below:

Here are messages realted to the this error:

<2013-05-20 20:04:09,890> ERROR [import] <com.aquafold.datastudio.tools.importtool.ImportThread> Prepared statement failed, fallback to insert statement.
java.sql.SQLException: Type '7' not implemented.

   at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780)
   at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2665)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.prepareBCP(SybPreparedStatement.java:3427)
   at com.sybase.jdbc4.jdbc.SybPreparedStatement.<init>(SybPreparedStatement.java:209)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1406)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1307)
   at com.sybase.jdbc4.jdbc.SybConnection.prepareStatement(SybConnection.java:1278)
   at com.aquafold.aquacore.scripts.WrapperConnection.prepareStatement(WrapperConnection.java:64)
   at com.aquafold.datastudio.tools.importtool.ImportThread.process(ImportThread.java:258)
   at com.aquafold.datastudio.tools.importtool.ImportThread.run(ImportThread.java:98)
<2013-05-20 20:04:09,890> ERROR [import] <com.aquafold.datastudio.tools.importtool.ImportThread>
java.sql.BatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Type '7' not implemented.

   at com.sybase.jdbc4.jdbc.ErrorMessage.raiseBatchUpdateException(ErrorMessage.java:1300)
   at com.sybase.jdbc4.jdbc.SybStatement.batchLoop(SybStatement.java:2134)
   at com.sybase.jdbc4.jdbc.SybStatement.sendBatch(SybStatement.java:1924)
   at com.sybase.jdbc4.jdbc.SybStatement.executeBatch(SybStatement.java:1882)
   at com.sybase.jdbc4.jdbc.SybStatement.executeBatch(SybStatement.java:1691)
   at com.aquafold.aquacore.scripts.WrappedStatement.executeBatch(WrappedStatement.java:150)
   at com.aquafold.datastudio.tools.importtool.ImportThread.process(ImportThread.java:470)
   at com.aquafold.datastudio.tools.importtool.ImportThread.run(ImportThread.java:98)
<2013-05-20 20:04:09,906> ERROR [import] <com.aquafold.datastudio.tools.importtool.ImportThread>
java.sql.SQLException: Type '7' not implemented.

   at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780)
   at com.sybase.jdbc4.jdbc.SybConnection.handleSQLE(SybConnection.java:2648)
   at com.sybase.jdbc4.jdbc.SybConnection.commit(SybConnection.java:1635)
   at com.aquafold.aquacore.scripts.WrapperConnection.commit(WrapperConnection.java:96)
   at com.aquafold.datastudio.tools.importtool.ImportThread.process(ImportThread.java:481)
   at com.aquafold.datastudio.tools.importtool.ImportThread.run(ImportThread.java:98)

Not sure if there is anything we can do in ADS.

ivangron 2013-05-21T18:01:37Z

duplicate of issue 8801

Search Tips

Aqua Data Studio / nhilam

Failed to insert data when data is more then the specified length

3 attachments

Issue #8799

Completion

3 issue links

Issue #9021

Issue #8801

Issue #8801

Search Tips

Aqua Data Studio / nhilam

Title

Failed to insert data when data is more then the specified length

3 attachments

Issue #8799

Completion

3 issue links

Issue #9021

Issue #8801

Issue #8801