SQL Commands
in [Cobol]
|
Prev: Link Fujitsu with ODBC
Next: ROSCOE tutorial needed
From: Michael Mattias on 8 Apr 2006 10:43 "LX-i" <lxi0007(a)netscape.net> wrote in message news:d0bf0$44370268$45491d7a$6510(a)KNOLOGY.NET... > Servizio Tecnico wrote: > > Hi, I use Fujitsu v7 with Embedded SQL but when I write the command "DROP > > TABLE" and "CREATE TABLE" return a error in compilation like this: > > SQL STATEMENT IS INVALID - ODBC 7600E NOT EXECUTABLE SQL STATEMENT > > Notice the word "executable" statement. There are two types of SQL > statements - CREATE and DROP are both considered Data Definition > Language (DDL) statements. My guess would be that FJ7 doesn't like DDL > through ESQL. You may be able to get around this by using dynamic SQL - > check your manual for that term if you're not familiar with it. :) I've used CREATE *TEMPORARY* TABLE via ESQL... not with FJ but with IBM M/F and some 'nix COBOL (MF?) I worked on.. maybe that's a solution ... Then again, I still use CREATE TABLE thru the Windows ODBC API and that works just fine. Then again, maybe all this speculation is a waste of time until we see what happens using a syntactically correct CREATE TABLE statement.... after all, the ESQL compiler has to call the DBMS's PREPARE statement during compilation, and that will surely fail if the statement is syntactically incorrect. MCM
From: Frederico Fonseca on 8 Apr 2006 11:26 On Sat, 08 Apr 2006 14:43:04 GMT, "Michael Mattias" <michael.mattias(a)gte.net> wrote: >"LX-i" <lxi0007(a)netscape.net> wrote in message >news:d0bf0$44370268$45491d7a$6510(a)KNOLOGY.NET... >> Servizio Tecnico wrote: >> > Hi, I use Fujitsu v7 with Embedded SQL but when I write the command >"DROP >> > TABLE" and "CREATE TABLE" return a error in compilation like this: >> > SQL STATEMENT IS INVALID - ODBC 7600E NOT EXECUTABLE SQL STATEMENT >> >> Notice the word "executable" statement. There are two types of SQL >> statements - CREATE and DROP are both considered Data Definition >> Language (DDL) statements. My guess would be that FJ7 doesn't like DDL >> through ESQL. You may be able to get around this by using dynamic SQL - >> check your manual for that term if you're not familiar with it. :) > >I've used CREATE *TEMPORARY* TABLE via ESQL... not with FJ but with IBM M/F >and some 'nix COBOL (MF?) I worked on.. maybe that's a solution ... > >Then again, I still use CREATE TABLE thru the Windows ODBC API and that >works just fine. > >Then again, maybe all this speculation is a waste of time until we see what >happens using a syntactically correct CREATE TABLE statement.... after all, >the ESQL compiler has to call the DBMS's PREPARE statement during >compilation, and that will surely fail if the statement is syntactically >incorrect. Sorry but no. As I stated previsously Fujitsu ESQL does NOT SUPPORT some sql statements, and CREATE table is one of them, not matter how correct the statement is. So if using ESQL the only way is to use a dynamic statement. (with FJ COBOL). Frederico Fonseca ema il: frederico_fonseca at syssoft-int.com
From: Michael Mattias on 8 Apr 2006 11:35 "Frederico Fonseca" <real-email-in-msg-spam(a)email.com> wrote in message news:jclf32h58f7qn008k25c28kh1f7cis53qv(a)4ax.com... > On Sat, 08 Apr 2006 14:43:04 GMT, "Michael Mattias" > <michael.mattias(a)gte.net> wrote: > > >"LX-i" <lxi0007(a)netscape.net> wrote in message > >news:d0bf0$44370268$45491d7a$6510(a)KNOLOGY.NET... > >> Servizio Tecnico wrote: > >> > Hi, I use Fujitsu v7 with Embedded SQL but when I write the command > >"DROP > >> > TABLE" and "CREATE TABLE" return a error in compilation like this: > >> > SQL STATEMENT IS INVALID - ODBC 7600E NOT EXECUTABLE SQL STATEMENT > >> > >> Notice the word "executable" statement. There are two types of SQL > >> statements - CREATE and DROP are both considered Data Definition > >> Language (DDL) statements. My guess would be that FJ7 doesn't like DDL > >> through ESQL. You may be able to get around this by using dynamic SQL - > >> check your manual for that term if you're not familiar with it. :) > > > >Then again, maybe all this speculation is a waste of time until we see what > >happens using a syntactically correct CREATE TABLE statement.... after all, > >the ESQL compiler has to call the DBMS's PREPARE statement during > >compilation, and that will surely fail if the statement is syntactically > >incorrect. > Sorry but no. As I stated previsously Fujitsu ESQL does NOT SUPPORT > some sql statements, and CREATE table is one of them, not matter how > correct the statement is. I did see that, but I'm just stubborn enough to think that writing a syntactically-correct CREATE TABLE statement is worth a try. Since I call CREATE TABLE.... the same way I call SELECT... or INSERT... and it's ho-hum in my programs, I figger it has to be worth a shot if I can't find an explicit prohibition in the compiler vendor's manual. (Which of course, may be saying more about the quality of the documentation than about anything else). MCM
From: Pete Dashwood on 8 Apr 2006 21:57 "Frederico Fonseca" <real-email-in-msg-spam(a)email.com> wrote in message news:jclf32h58f7qn008k25c28kh1f7cis53qv(a)4ax.com... > On Sat, 08 Apr 2006 14:43:04 GMT, "Michael Mattias" > <michael.mattias(a)gte.net> wrote: > >>"LX-i" <lxi0007(a)netscape.net> wrote in message >>news:d0bf0$44370268$45491d7a$6510(a)KNOLOGY.NET... >>> Servizio Tecnico wrote: >>> > Hi, I use Fujitsu v7 with Embedded SQL but when I write the command >>"DROP >>> > TABLE" and "CREATE TABLE" return a error in compilation like this: >>> > SQL STATEMENT IS INVALID - ODBC 7600E NOT EXECUTABLE SQL STATEMENT >>> >>> Notice the word "executable" statement. There are two types of SQL >>> statements - CREATE and DROP are both considered Data Definition >>> Language (DDL) statements. My guess would be that FJ7 doesn't like DDL >>> through ESQL. You may be able to get around this by using dynamic SQL - >>> check your manual for that term if you're not familiar with it. :) >> >>I've used CREATE *TEMPORARY* TABLE via ESQL... not with FJ but with IBM >>M/F >>and some 'nix COBOL (MF?) I worked on.. maybe that's a solution ... >> >>Then again, I still use CREATE TABLE thru the Windows ODBC API and that >>works just fine. >> >>Then again, maybe all this speculation is a waste of time until we see >>what >>happens using a syntactically correct CREATE TABLE statement.... after >>all, >>the ESQL compiler has to call the DBMS's PREPARE statement during >>compilation, and that will surely fail if the statement is syntactically >>incorrect. > Sorry but no. As I stated previsously Fujitsu ESQL does NOT SUPPORT > some sql statements, and CREATE table is one of them, not matter how > correct the statement is. > > So if using ESQL the only way is to use a dynamic statement. (with FJ > COBOL). > I really HATE it when someone says "It's the ONLY way..." :-) My experience (and it is extensive) is that when IT people say this, what they really mean, is: "It's the ONLY way I can think of or know of, and if there are other alternatives I can't be bothered exploring them..." I have seen it many times throughout my career and almost invariably, a little imagination or "thinking outside the square" provides a (usually better...) alternative. I remember one famous case in Spain where I wanted to refresh multiple 3270 screens when certain events occurred. IBM (Spain's) position on it was that it could not be done because conversational mode required the user to press ENTER. End of story. It can't be done. Three weeks later I demonstrated a number of 3270 screens all being simultaneously refreshed without user intervention. IBM Spain came along and saw it. "You could not have done this using standard code; you must have modified our system routines and we won't support it." I assured them I had done no such thing and that everything was standard. It just required some lateral thinking. When I finally revealed how it was done (and I teased them for a while just for the fun of it :-)), the memorable comment was: "Oh Well, if you do it like THAT, of course it is possible..." Frederico is absolutely correct when he says that the preferred way to manipulate DDL in Fujitsu COBOL is by dynamic SQL, and Fujitsu do not support ESQL CREATE. However, dynamic SQL is NOT "the ONLY way". You can get an even richer experience if you simply create an ADOX object in your COBOL, and utilise the methods, properties, and events provided by MicroSoft. This will allow full acces to ALL DDL statements (NOT just CREATE TABLE) and it also allows cube access as well as flat tables. And it is not limited to Fujitsu COBOL; it will work with any COBOL that supports ActiveX or COM objects. Pete.
From: Frederico Fonseca on 9 Apr 2006 05:56
On Sun, 9 Apr 2006 13:57:09 +1200, "Pete Dashwood" <dashwood(a)enternet.co.nz> wrote: > >"Frederico Fonseca" <real-email-in-msg-spam(a)email.com> wrote in message >news:jclf32h58f7qn008k25c28kh1f7cis53qv(a)4ax.com... >> On Sat, 08 Apr 2006 14:43:04 GMT, "Michael Mattias" >> <michael.mattias(a)gte.net> wrote: >> snip >> So if using ESQL the only way is to use a dynamic statement. (with FJ >> COBOL). >> > I really HATE it when someone says "It's the ONLY way..." :-) > >My experience (and it is extensive) is that when IT people say this, what >they really mean, is: "It's the ONLY way I can think of or know of, and if >there are other alternatives I can't be bothered exploring them..." > snip > >Frederico is absolutely correct when he says that the preferred way to >manipulate DDL in Fujitsu COBOL is by dynamic SQL, and Fujitsu do not >support ESQL CREATE. > >However, dynamic SQL is NOT "the ONLY way". > >You can get an even richer experience if you simply create an ADOX object in >your COBOL, and utilise the methods, properties, and events provided by >MicroSoft. This will allow full acces to ALL DDL statements (NOT just CREATE >TABLE) and it also allows cube access as well as flat tables. > >And it is not limited to Fujitsu COBOL; it will work with any COBOL that >supports ActiveX or COM objects. > >Pete. I also hate when someone does not read the full sentence. I said " ... if using ESQL ...". Now as far as I know ESQL standard is NOT ADOX or COM/ActivX. Now if you do not use ESQL then you can use the others, which I also mentioned on another reply. And as you know I did bother looking for alternatives. (see my ADO coding on this and other threads, and on the tek-tips faq I mention in one of them). I could have also provided an example in DAO/RDO, but I have choosen not to do it as its old technology. As for ADOX, very similar to ADO in how it would work. my example with ADO can be used as the basis for such code. If someone is desesperate for ADOX code and cant do it on their own then I may do a sample program. Regarding ESQL, if you can find a way of doing the create table using ONLY ESQL (AND NOT DYNAMIC SQL) with FJ cobol V7 or lower. (Not sure V8 will allow it. havent tried it yet), then I am sure many programmers will be happy with it. And this using the ESQL pre-compiler supplied by FJ, as other venders will do things differently. Frederico Fonseca ema il: frederico_fonseca at syssoft-int.com |