I'm pretty sure our XOinstance() procedure was working with our ODBC connectors ok in 10.1; at least the few times we ran that code. After updating to version 10.2 in our test lab and copying databases into our test lab network we ran some of our code that call XOInstance() and it failed with

sys1000: Error loading program "XOINSTANCE".

I'd like to ask first if anyone has encountered this issue. We aren't truly sure if the error indicates an issue with finding the XOInstance procedure? Or, if there was a problem obtaining a handle to the ODBC object.

I've also seen some conversations about moving away from ODBC to ADO or possibly something mentioned as "SQL bond" which I've not heard of. Any thoughts on 'best practices' or options for linking up to SQL databases?

– thanks

I'm pretty sure our XOinstance() procedure was working with our ODBC connectors ok in 10.1; at least the few times we ran that code. After updating to version 10.2 in our test lab and copying databases into our test lab network we ran some of our code that call XOInstance() and it failed with

sys1000: Error loading program "XOINSTANCE".

I'd like to ask first if anyone has encountered this issue. We aren't truly sure if the error indicates an issue with finding the XOInstance procedure? Or, if there was a problem obtaining a handle to the ODBC object.

I've also seen some conversations about moving away from ODBC to ADO or possibly something mentioned as "SQL bond" which I've not heard of. Any thoughts on 'best practices' or options for linking up to SQL databases?

– thanks

We have not tested ODBC with OI 10.2 as we swtiched over to ADO years ago. We have some clients still using ODBC but they are also running OI 9.4. We do not know if they will upgrade to OI 10.

The SQL Connector (aka SQL bond) that ships with OI is based on ADO. If you wanted to experiment with converting XO/Qry commands to ADO commands then you might find our blog article useful:

Much ADO About Nothing

Don Bakke

SRP Computer Solutions, Inc.

Thanks Don,

I was actually reading your article when I saw the mention of "SQL bond" :) . I'll give it more of a read shortly.

Post removed by author

Hi Michael,

Just to clarify - the XO and DSO functions were not ported to v10. ADO remains the preferred method for ODBC/SQL access.

Regards,

Carl Pates

Thanks for the clarification Carl. All our code has been using ODBC to connect to our MySQL databases. I've been searching the docs for some help on implementing an ADO approach or this "SQL bond" but have yet to find anything. I did see where the SQL bond approach is a built-in process with OpenInsight so it might be preferable but am not sure without any type of comparison.

I saw the term 'bond' appear in several places in the docs but in a myriad of contexts.

Any advice on moving ahead with this issue on our end would be greatly appreciated.

– MIke

Hi Michael,

As others have mentioned, we did not migrate the XOObject and QryObject to OpenInsight 10. These objects relied on Microsoft libraries that were replaced by ADO technology. We created the SQL Connector to replace the XO and Qryobjects. The SQL Connector lets you access SQL data using the same dictionary / rlist approach as any other OpenInsight table. It converts OpenInsight file operations into SQL statements and uses ADO to execute them on the SQL server. The conversion layer is convenient. However, the conversion layer involves a lot of code and works best when you map one SQL table to one OI table. If you are familiar with SQL syntax, especially for multi-table joins, you can use an OI function called DSBFS_EXEC_SQL to send SQL statements to an SQL server and receive the results, or you can work directly with the ADO objects.

Don gave you a link to some ADO references. Below is an example of using DSBFS_EXEC_SQL to write rows to an SQL table and running a select to get data from SQL. You can call stored procedures too.

Function DSBFS_OI_TO_SQL_EXAMPLE(url, payload)

/*

** In this example I copy data from an OI "Person" table to the SQL "test1" table without making an OI shadow table.

** Rather than making a shaow table, I create sql statements on the fly, use dsbfs_Exec_sql to execute them.

**

** This technique allows you to work with databases such as Firebird or SqlLite which have ODBC drivers but are not "understood" by the SQL connector

**

** For each oi row, I test if it exists in SQL, then create an update or an insert statement.

** The update must use a where clause to update only the row with that ID, otherwise it will change every row in the SQL table.

**

** Note: In sql, single and double quotes are not the same.   'this' means data and "this" means a column name

*        Thus, I use single quotes around the values.

*        Dates are usually sent as YYYY-MM-DD, though you need to know how your database is configured.



*/



Declare Function dsbfs_exec_sql

connectionname = 'SQLLITE2'

result= ""





Open 'PERSON' To f_person Else debug

Open 'DICT.PERSON' To d_person Else debug



clearselect

Call Rlist('SELECT PERSON WITH CITY "BOSTON"', 5, '', '', '')

done = 0

Loop

	Readnext id Else done = 1

Until done

	Read rec From f_person,id Else rec = ""

	

	* Set me up to use the {COLNAME} syntax

	@dict = d_person

	@id = id

	@record = rec

	

	* row exists?

	query = "Select 1 from sl_person where id = '" :id:"'"

	stat = dsbfs_Exec_sql(connectionName, query, demotion, result)

	ExistsInSql = ( result eq "1")

	If ExistsInSql Then

		* Create an update statement.

		query =  "update sl_person"

		query := " set"

		query := "  lname = '" : {LNAME} : "'"

		query := ", fname = '" : {FNAME} : "'"

		query := ", city  = '" : {CITY}  : "'"

		query := ", st    = '" : {STATE} : "'"

		query := ", zip   = '" : {ZIP}   : "'"

		query := " where id = '" : @id : "'"

	End else

		* Create an insert statement

		query =  "insert into sl_person"

		query := "  (id, lname,fname,city,st,zip)"

		query := "  values "

		query := " ("

		query := "  '" : @id     : "'"

		query := ", '" : {LNAME} : "'"

		query := ", '" : {FNAME} : "'"

		query := ", '" : {CITY}  : "'"

		query := ", '" : {STATE} : "'"

		query := ", '" : {ZIP}   : "'"

		query := " )"

	End

	

	stat = dsbfs_Exec_sql(connectionName, query, demotion, result)

	

Repeat





/*

* Part 2 - retrieve the data using dsbfs_exec_sql with a select statement

*/

query = "select"

query := " id, lname, fname, city, st, zip"

query := " from sl_person"





demotion = 1 // demotion controls the delimiters In the result. demotion=0 returns @rm rows, @fm columns; demotion=1 returns @fm/@vm like edit table rows

stat = dsbfs_Exec_sql(connectionName, query, demotion, result)





Return result

Have you seen this bond work with MySql? I believe it only has a ODBC drive. I would be interested in talking to anyone who has had this working with MySql.

I think that i get, from your post, that all work with the bond really requires the developer to do all sql calls through dsbfs_Execute_Sql. Is this right? There is no Read/Write with OI.

Dsig

Hi Dave

I have used the bond with MySQL. The MySQL driver is indeed an ODBC driver. to make it work I needed to install the odbc driver, use the ODBCAdmin tool in windows to define an OSBC connection to the ODBC driver, then from OI, make a connection using the Microsoft OLEDB for MySQL with the ODBC source you created. My connection string looks like below.

Provider=MSDASQL.1;Persist Security Info=False;Data Source=Sakila

To be clear, developers do not need to use DSBFS_EXEC_SQL. You can create connections to SQL tables and read and write directly to that table, just as in 9.4. I intended to suggest the DSBFS_EXEC Sql as a solution for the case where a developer was using the XOInstance and QryMethod functions to execute custom or on the fly sql statements.

Thanks Bob for the follow up .. that makes more sense.

DSig

I tried to connect to my MySql server on my local host (windows) and things went well except there was no "Microsoft OLEDB for MySQL". I selected "Microsoft OLEDB ODBC" which was the only one that made sense. Like i said everything went well except for that .. the connection test worked etc.

But after it read all my tables (adventureworks) it then tried to build the shadow dictionary and FAILED all creations. Here is an image of the screen: Image

Any thoughts on what could have gone wrong?

tia

Dsig

View this thread on the Works forum...