lucstyves wrote:<BR>
> My problem : Doing such a simple SQL statement never ends up<BR>
> successfully if I try it with the ADO connection. But if I try the<BR>
> ODBC connection, it works ! The only things having all this fail is<BR>
> the SUBSTR embedded function.<BR>
<P>
Do you receive an error message? What does it say? Can you turn on the<BR>
tracing facility?<BR>
<P>
Bill<BR>
<P>
<P>

Yes I receive an error message. The error message says that ",1" is not an expected SQL function. It is really the ADO connection that does not support my SUBSTR function. As soon as I remove the SUBSTR and replace it by the whole field, it works. <p>Luc

Try this statement instead:

<pre>"SELECT SUBSTRING(CINAM1,1,5) as Nom FROM DGDTA45.CUSTCI"</pre>

Michael

Thanks Michael but no luck, I already tought of using the SUBSTRING function instead of the SUBSTR. Same result. <p>It really seem like if ADO connection would not support all SQL functions...... Anyone can confirm ? <p>If anyone could tell me "I had the same issue and it seem like ADO is not supporting all SQL functions" than I would stop my research. But if someone would tell me "Nope. Only the connection mode is different but your SQL syntax will not be interpreted in any way and all functions should be supported" than I will continue scratching my head... <p>Luc

lucstyves wrote:<BR>
> Yes I receive an error message. The error message says that ",1" is<BR>
> not an expected SQL function. It is really the ADO connection that<BR>
> does not support my SUBSTR function. As soon as I remove the SUBSTR<BR>
> and replace it by the whole field, it works.<BR>
<P>
The next 2 things to ask are:<BR>
Are you on the latest version of Microsoft's MDAC?<BR>
Are you on the latest Client Access Service Pack?<BR>
<P>
Bill<BR>
<P>
<P>

Yes I am. Latest Client Access Service Pack for version V5R3 and MDAC 2.8 updated on the internet. <p>So based on the answer I had, nobody confirm me that ADO connection should support all SQL functions but nobody told me it should. I guess I'll stay with ODBC...... Until someone from IBM claim it should work. <p>Luc

Sorry, but I cannot claim to have tried <i>every</i> SQL function from both ODBC and ADO. So I cannot confirm nor deny that they all work in one or the other. What I can tell you is, I have always used the IBMDA400 provider, and I have never had an instance of an SQL statement that I could run using STRSQL, that did not work with ADO. <p>That's the best I can do for you. <p>Sorry, <BR>
Michael

Hi guys and gals ! <p>I tried the following code. Notice that my first connection string is commented out since the second one is working and the first one is not. <p>My problem : Doing such a simple SQL statement never ends up successfully if I try it with the ADO connection. But if I try the ODBC connection, it works ! The only things having all this fail is the SUBSTR embedded function. <p>Maybe I am not using SQL statement correctly in an ADO connection, or maybe ADO is not supporting all SQL functions. <p>Now I do not mind using an ODBC driver connection instead of ADO but still, if my problem is because of ADO not supporting all SQL functions, 2 questions remain unanswered. <BR>
1) Why does every publications seem to be saying ADO is the new connection that eveyone should use ? <BR>
2) Why is my second test working, even thus I am still using ADODB class, Command properties and Recordset for feeding the SQL and running the whole thing even thus I am now using an ODBC driver connection ??? <p>Please bring light ! <p>Thanks ! <p>Luc St-Yves <p><!--mccodelink_begin--> <BR>
<!-- do not remove --> <BR>
<hr width=50 align=left><small><a href='http://www.mcpressonline.com/mc/showcode@@.6b22557d' target='_blank'>Code</a></small> <BR>
<!--mccodelink_end-->