Create Custom DLL for SQL Fetch (C coders needed)

[Gary P]

Hello Listers,

One of my frustrations with JDE is how difficult it is to do a complex SQL command. By complex, I mean it involves the use of an OR statement or uses the same field more than once. Rather than use the built-in method of writing lots of code, I am looking at ways to use an external DLL to communicate directly with the database.

My plan is to create a function that takes in a pointer to a string, and a void pointer that will point to a data structure. The function will take the string (which will be a SQL string our server can interpret) and execute the command. If that string happens to be a select string, the fields from the result record can be saved in the data structure. You cane then call the function again to fetch the next record. It will be the programmer's job to make sure the string has the correct syntax and the data structure can hold all the returned fields in the correct order.

An example of this would be as follows:
-You create a data structure that holds two math numerics.
-You create a SQL string that says "select sdoco, sdlnid from f4211 where doco=12345" (I'm simplifying the SQL statement).
-You call the function and send a void pointer to the data structure as well as a pointer to the string, and any additional parameters to show you're performing the initial call
-The function executes the string, grabs the first record and writes the doco and lnid to the data structure, and fills a pointer to the recordset as well as sending back a success.
-The business function does any processing, and calls the function again with the parameters mentioned above.
-This repeats while the function processes the records obtained

I want to see if this can be done, but I also don't want to reinvent the wheel. Have any of you worked on something like this, and if so, what advice/help can you provide?

Thanks in advance,
Gary Pollock

 

[Scott Beebe]

Here is my take on it. It will work, however here is the issue I see.

You will probably be using an ODBC datasource. This may require additional user ids and permissions to be set up for the users who will be accessing the custom DLL. Since you are not using the JDE API to access data then you are completely bypassing the JDE security. You CNC/security people will not be happy about that.

 

[BOster]

As Scott pointed out, I would probably avoid going down that path if at all possible.

A couple of things that I would try if I were you:

1. Virtual Tables. This is a SQL view defined at the DB level that has a JDE table defined with the same resulting recordset. The SQL view can allow you to do much more complicated SQL than JDE.

2. The C api for JDEBASE is much more robust than the ER counterpart, so you may want to look at that. You can create more diverse and complicated where clauses, and depending on your release you can also do aggregate functions, so SQL like this is possible:

SELECT sddcto, SUM(sdaexp)
from DBDTA.F4211
where sdkcoo = '1000' OR (sdkcoo = '2000' AND sddcto in ('SO', 'SA'))
group by sddcto



I have found that by thinking outside the box a little you can usually solve most JDE problems within the confines of the toolset.

 

[Gary P]

Thanks BOster, Scott

After spending a while looking at the code, it looks like any external DLLs would cost me more time than I'd save

I've done some "advanced" fetches with c BSFN, but you do have to write quite a bit of code. Setting up NID arrays, data structure for query, data structure for return data, and not to mention the multitude of lines of code to do the actual select/update/insert. My hope was that I could write a couple lines of SQL, define one data structure, and be done with it.

 

[peterbruce]

Gary,

Here is a round about way of doing what you want, but the warning of circum navigating the security is very valid. An additional warning to all who may be reading this: Do not allow users to insert their own SQL statements to run.

All the following has to be able to be done from the OS command line on the machine on which it is executed. We have done all the individual parts in UBEs, but not all together, on a Sun Unix box with an Oracle database, including a check to make sure that the host is the Sun Unix box. We do not hard code passwords or store them in text files.

1) Dynamically create a text file contain the sql to run. There are a number of bsfns to write lines to a text file.
2) Dynamically create a command text file that runs the sql, and if appropriate, saves the output to a CSV file, this would have to include the database conectiopn criteria. There are a number of bsfns to write lines to a text file.
3) Run the command text file and delete it and the SQL file when completed. Use the Execute External Program bsfn.
4) If appropriate, import the CSV file. There are a number of ways to do this.

 

[WhippingBoy]

I experimented with this several years ago. Look at the ODBC page on my 'nearly abandoned' website (in my signature) to see how to do it. It works quite well, but as the others point out, there are drawbacks.

Oh yeah, you don't have to make a separate DLL for this either. It can all be submitted from a standard BSFN.