import sdf error msg 6522: Bingo 1.7 beta 3, SQL Server 2008

[Wayne Stewart]

Hello everyone.  I’m very interested in Bingo; it could be a great tool for my research.  I’m doing a test, and have a quick question on Bingo behavior.

 

As a test, I created a sample SDF file with one compound, and attempted to import it.  (File attached.)  Behavior was as below.

 

exec bingo.ImportSDF 'nci', 'molfile', 'c:\sample1.sdf', 'nsc nsc';

 

Msg 6522, Level 16, State 1, Procedure z_ImportSDF, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "z_ImportSDF":

System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format.

System.FormatException:

   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)

   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)

   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)

   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)

System.FormatException:

   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)

   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)

System.InvalidOperationException:

   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)

   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()

   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)

   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

   at indigo.Bingo._ImportData(String table_name, String data_column_name, String additional_parameters, bingoImportPopulateDataRow populateRowFunc)

   at indigo.Bingo.ImportSDF(SqlString table_name, SqlString mol_column_name, SqlString file_name, SqlString additional_parameters, SqlString bingo_schema)

.

I received the same error on 2 different sdf files:  my small self-created sdf example, and also on the giant official NCI compound sdf file.  Error occurred if I imported .SDF or .SDF.GZ file versions, all the same.

 

Possibly this a casting error in the Bingo 1.7 beta 3 code.  I wanted to raise the issue, in case there’s a need to document a new bug for fix.

 

Environment

OS:  Windows XP SP3

Bingo version:  1.7 beta 3 for Win32

DB:  SQL Server 2008 Express R2

DB details:

Microsoft SQL Server Management Studio 10.50.1600.1

Microsoft Data Access Components (MDAC) 3.85.1132

Microsoft MSXML 2.6 3.0 4.0 6.0

Microsoft Internet Explorer 8.0.6001.18702

Microsoft .NET Framework 2.0.50727.3625

Operating System 5.1.2600

 

Many thanks,

 

Wayne Stewart

ESS / Astrobiology

Johnson Hall 262

gw...@uw.edu

IM: gw...@uw.edu or gws...@hotmail.com

903-456-4604

Ad astra per aspera.

 

[Mikhail Rybalkin]

Hello Wayne,

Thank you for the interest in Bingo!

From your error messages I see, that the molecule ID cannot be casted
to the integer format. If the ID column has the integer format, then
the conversion from the string representation to the cell value in the
table is done by the Sql Server automatically.

Could you create the molecule table with a string ID 'nsc' column,
import the SDF file into it with the same query, and review where all
the ID columns have correct integer format? This can by done by
selecting all the rows where the conversion to the integer format is
equal to NULL.

Best regards,
Mikhail

> g...@uw.edu
>
> IM: g...@uw.edu or gws...@hotmail.com

>
> 903-456-4604
>
> Ad astra per aspera.
>

> Description: Description: ws
>
>  image003.jpg
> 5KПросмотретьЗагрузить
>
>  sample1.sdf
> 1KПросмотретьЗагрузить

[ramya27...@gmail.com]

Hello Mikhail,

I’m new to Bingo and Indigo, I’m not getting which one to use for following task, will clearly my doubts.

1.       I’m reading SDF File and inserting into SQL SERVER thought C# Code (.NET API), which one to selected to read SDF file, BINGO or INDIGO.

2.       Will inserting the sdf file, I’m using two tables COMPOUND and STRUCTURE. STRUCTURE will have molfile and rest details will be insert in COMPOUND Tables

3.        Before Inserting, it should check for Duplications of Structure in SMILE Format.

4.       After Inserting, Perform search operations and display the structure in grid.

 

Please help me …

Thank you

Ramya Shankar

[Mikhail Rybalkin]

Hello Ramya,

Bingo is a database module for SQL Server (and Oracle, PostgreSQL). You can install it on your server to support chemical search operations. Indigo is a library with C# interface so you can you it your code. If you have a lot of structures (10000 or 10 million) then you can import them into a SQL table, create a molecule index using Bingo and perform search operations.

Could you explain your architecture in more details? For example you want to write an ASP.NET application with a SQL Server database to store and search molecules.

1.       I’m reading SDF File and inserting into SQL SERVER thought C# Code (.NET API), which one to selected to read SDF file, BINGO or INDIGO.

If you are using C# then you have to  Indigo in this case to load SDF file, iterate molecules from it, and insert molecule.rawData() strings in your table. IndigoObject.rawData() returns you a raw molfile from sdf file.

2.       Will inserting the sdf file, I’m using two tables COMPOUND and STRUCTURE. STRUCTURE will have molfile and rest details will be insert in COMPOUND Tables

Not sure that I understand you approach. Do you want to store in the  STRUCTURE table molecule molfile and id, and use this id in the COMPOUND table?

3.        Before Inserting, it should check for Duplications of Structure in SMILE Format.

Duplication check can be done differently. You can execture bingo.SearchExact to search exact matches for a given structure with some additional options (like ignore charges and etc.). Also you can compute canonical SMILES i.e. SMILES string that is the same for the same structures. You can store canonical SMILES in a table and check if you have duplicated string. This method will work faster then exact matching method. In the version that we will release soon we add an InChI support, so instead of SMILES you can use InChI that is a unique molecule identifier.

4.       After Inserting, Perform search operations and display the structure in grid.

You can create a molecule index, make substructure (or other search) in SQL, return molfiles to your application, and renderer them using Indigo in C#. 

I can explain each point in more details.

Best regards,

Mikhail

[ramya27...@gmail.com]

Hello Mikhail,

Thanks for explaining a great topic, which really mean a lot for me.

Can you explain more in detail about InChI or if you have any sample code in c# can you please share with me.

 

I have one more question for you Mikhail, that is, I want to display structure image in a grid from a structure table. Can you give some suggestion on this, whether I should convert molfile into .png image and display it in grid,  or is there any other way to display the structure image in grid view(c#).

 

Thank you,

Ramya Shankar

[raju...@gmail.com]

Dear Ramya Shanka

    was you problem solved, even we are trying to show the structure in the grid. and i am able to show using Indio. the api give us clear picture in implementing it, but it is take a lot time for me to read more then 100000 rec from SDF file, if you have completed your project can you let me know the steps, as i have to implement the same

[raju...@gmail.com]

Dear Mikhail,

     thanks for the updates, can you plese send us code to implement the same in ASP.net with c# using sql database.