Thread: [Squirrel-sql-users] Trouble executing storedprocs in SQuirreL
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Karr, D. <dav...@wa...> - 2007-06-18 17:22:59
|
I'm having trouble executing a storedproc in SQuirreL (2.5). If the name of the storedproc is "DELETE_BY_LOAN" and it takes a single string parameter, what exactly should I type in the SQL window? I would guess it would be: begin DELETE_BY_LOAN('123435'); end; And then I press Ctrl-enter. When I do this, it says: ------------ Error: java.sql.SQLException: ORA-06550: line 1, column 44: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: :=3D . ( % ; , SQL State: 65000, Error Code: 6550 Error occured in: begin DELETE_BY_LOAN('123435') ------------ I tried the similar test in SQLDeveloper, and it works fine. |
From: Rob C. <rw...@co...> - 2007-06-18 19:02:06
|
I've never had any luck running anonymous PL/SQL blocks outside Oracle's tools (though I would love info on getting it to work, if it can be done). For your example, you could try this instead: select DELETE_BY_LOAN('123435') from dual; Rob On Jun 18, 2007, at 1:22 PM, Karr, David wrote: > I'm having trouble executing a storedproc in SQuirreL (2.5). If the > name of the storedproc is "DELETE_BY_LOAN" and it takes a single > string > parameter, what exactly should I type in the SQL window? > > I would guess it would be: > > begin DELETE_BY_LOAN('123435'); end; > > And then I press Ctrl-enter. When I do this, it says: > > ------------ > Error: java.sql.SQLException: ORA-06550: line 1, column 44: > PLS-00103: Encountered the symbol "end-of-file" when expecting one of > the following: > > := . ( % ; > , SQL State: 65000, Error Code: 6550 > Error occured in: > begin DELETE_BY_LOAN('123435') > ------------ > > I tried the similar test in SQLDeveloper, and it works fine. > > ---------------------------------------------------------------------- > --- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
From: Maury H. <ml1...@sh...> - 2007-06-21 13:29:56
|
Rob Cope wrote: > I've never had any luck running anonymous PL/SQL blocks outside > Oracle's tools (though I would love info on getting it to work, if it > can be done). > > For your example, you could try this instead: > > select DELETE_BY_LOAN('123435') from dual; That will only work if DELETE_BY_LOAN is a function that returns a value. If it is a procedure, you'll get an error. >> begin DELETE_BY_LOAN('123435'); end; >> >> And then I press Ctrl-enter. When I do this, it says: >> >> ------------ >> Error: java.sql.SQLException: ORA-06550: line 1, column 44: >> PLS-00103: Encountered the symbol "end-of-file" when expecting one of >> the following: >> The problem here is that SQuirreL, by default, uses the semi-colon as a terminator. So it is breaking up the begin/end block into two statements, which Oracle chokes on. In order to get SQuirreL to send the entire begin/end block as a single statement, you need to go to the SQL tab in the Session Properties dialog, and change the "Statement Separator" to something other than an semi-colon. What I usually did was change it to the / (slash) character. Then you can run the procedure with the following: begin delete_by_loan('12345'); end/ (I can't remember for sure, but you may still need the semi-colon after the "end" but before the slash) Maury |
From: Robert M. <rob...@gm...> - 2007-06-21 15:08:27
|
On 6/21/07, Maury Hammel <ml1...@sh...> wrote: > The problem here is that SQuirreL, by default, uses the semi-colon as a > terminator. So it is breaking up the begin/end block into two > statements, which Oracle chokes on. In order to get SQuirreL to send > the entire begin/end block as a single statement, you need to go to the > SQL tab in the Session Properties dialog, and change the "Statement > Separator" to something other than an semi-colon. What I usually did > was change it to the / (slash) character. Then you can run the > procedure with the following: This is no longer necessary when using the Oracle Plugin. It uses a custom QueryTokenizer that handles stored procedures and anonymous blocks using Oracle's "/" terminator. See my previous email. Rob |
From: <jan...@ya...> - 2007-06-22 16:01:00
|
Hi, I get a "String to date conversion error" when I'm trying to do this: update bla set blabla='6/21/2007' despite having in the global prefs, data type controls, date, the "use default format (2007-06-22)" unchecked, and for "or locale-dependent format: Short (6/21/2007)" selected. Am I doing something wrong? I checked/unchecked the "allow inexact format on input", same result. If I change my update to say '2007/06/21', I don't get the exception anymore. This is the informix driver, 2.5, windows. Thank you. |
From: <jan...@ya...> - 2007-06-30 22:15:11
|
Does anybody know anything about this? "jan...@ya..." <jan...@ya...> wrote: Hi, I get a "String to date conversion error" when I'm trying to do this: update bla set blabla='6/21/2007' despite having in the global prefs, data type controls, date, the "use default format (2007-06-22)" unchecked, and for "or locale-dependent format: Short (6/21/2007)" selected. Am I doing something wrong? I checked/unchecked the "allow inexact format on input", same result. If I change my update to say '2007/06/21', I don't get the exception anymore. This is the informix driver, 2.5, windows. Thank you. ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/_______________________________________________ Squirrel-sql-users mailing list Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
From: Robert M. <rob...@gm...> - 2007-07-01 03:39:08
|
On 6/22/07, jan...@ya... <jan...@ya...> wrote: > Hi, > > I get a "String to date conversion error" when I'm trying to do this: > > update bla set blabla='6/21/2007' It looks like the database is performing an implicit conversion (you gave it a string and the column is a date - it needs to reconcile that somehow). I'm guessing here as I have little knowledge of Informix. Generally, date types can be obtained by using a date function on a string that has a date format that the date function can understand. I suppose the first format you chose was not what the implicit conversion was expecting. You should consult your Informix product manual for guidance on what the format of date strings can be and if there are date functions that can/should be used. The date datatype format choices in SQuirreL apply to how dates are displayed and edited. So, any SQL with date strings that you execute goes to the database as is. Rob |
From: <jan...@ya...> - 2007-07-01 21:58:42
|
Robert, thanks for the response. I tried to use the "date" function using a MM/DD/YYYY format: date('02/02/2002') but I get the same error. Now the thing is, if I use a non-java based client, like winsql (which is not that great compared to squirrel) for example, all's fine, although I have no setting that tells winsql how to treat a date literal. So I don't think the problem lies with the database configuration itself, but with jdbc, because some Java apps I've written in Java also default the date literal to 'yyyy/mm/dd'. Is there a way to change this thru squirrel? Thanks. Robert Manning <rob...@gm...> wrote: On 6/22/07, jan...@ya... wrote: > Hi, > > I get a "String to date conversion error" when I'm trying to do this: > > update bla set blabla='6/21/2007' It looks like the database is performing an implicit conversion (you gave it a string and the column is a date - it needs to reconcile that somehow). I'm guessing here as I have little knowledge of Informix. Generally, date types can be obtained by using a date function on a string that has a date format that the date function can understand. I suppose the first format you chose was not what the implicit conversion was expecting. You should consult your Informix product manual for guidance on what the format of date strings can be and if there are date functions that can/should be used. The date datatype format choices in SQuirreL apply to how dates are displayed and edited. So, any SQL with date strings that you execute goes to the database as is. Rob |
From: Robert M. <rob...@gm...> - 2007-07-02 00:53:13
|
On 7/1/07, jan...@ya... <jan...@ya...> wrote: > Robert, > thanks for the response. > > I tried to use the "date" function using a MM/DD/YYYY format: > > date('02/02/2002') > > but I get the same error. > > Now the thing is, if I use a non-java based client, like winsql (which is > not that great compared to squirrel) for example, all's fine, although I > have no setting that tells winsql how to treat a date literal. So I don't > think the problem lies with the database configuration itself, but with > jdbc, because some Java apps I've written in Java also default the date > literal to 'yyyy/mm/dd'. Is there a way to change this thru squirrel? The JDBC API supports an escape syntax that the driver can translate and it looks like: {d 'yyyy-mm-dd'} which the driver is supposed to translate into the appropriate format for the database. Have you tried that? Rob |
From: <jan...@ya...> - 2007-07-02 01:26:57
|
Yes, I tried {d 'yyyy-mm-dd'}, it doesn't work. Even it would work, I'm not interested in yyyy-mm-dd, but in mm/dd/yyyy... Thanks Robert Manning <rob...@gm...> wrote: On 7/1/07, jan...@ya... wrote: > Robert, > thanks for the response. > > I tried to use the "date" function using a MM/DD/YYYY format: > > date('02/02/2002') > > but I get the same error. > > Now the thing is, if I use a non-java based client, like winsql (which is > not that great compared to squirrel) for example, all's fine, although I > have no setting that tells winsql how to treat a date literal. So I don't > think the problem lies with the database configuration itself, but with > jdbc, because some Java apps I've written in Java also default the date > literal to 'yyyy/mm/dd'. Is there a way to change this thru squirrel? The JDBC API supports an escape syntax that the driver can translate and it looks like: {d 'yyyy-mm-dd'} which the driver is supposed to translate into the appropriate format for the database. Have you tried that? Rob |
From: Doug L. <la...@ni...> - 2007-07-02 07:07:51
|
Hi Jane. Try setting environment variable DBDATE and/or GL_DATE, which can be = used in an Informix client process to change the date string format. In = USA, you would normally use: DBDATE=3DMDY4/ GL_DATE=3D%D As these are supposed to be the default, I expect one of them is set to = something else in the Informix engine. There is also GL_DATETIME for DATETIME representation. The default is = "%Y-%m-%d %H:%M:%S", for example producing "2006-12-31 10:45" (depending = on precision). Alternatively, you can use built-in functions TO_DATE and TO_CHAR in an = Informix SQL statement to convert between string and DATE or DATETIME = values using a specified GLS format using the same conventions as = GL_DATETIME. See: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp Regards,=20 Doug Lawry=20 www.douglawry.webhop.org ----- Original Message -----=20 From: jan...@ya...=20 To: squ...@li...=20 Sent: Sunday, July 01, 2007 10:58 PM Subject: Re: [Squirrel-sql-users] String to date conversion error = despiteglobal prefs Robert,=20 thanks for the response. I tried to use the "date" function using a MM/DD/YYYY format: date('02/02/2002') but I get the same error. Now the thing is, if I use a non-java based client, like winsql (which = is not that great compared to squirrel) for example, all's fine, = although I have no setting that tells winsql how to treat a date = literal. So I don't think the problem lies with the database = configuration itself, but with jdbc, because some Java apps I've written = in Java also default the date literal to 'yyyy/mm/dd'. Is there a way to = change this thru squirrel? Thanks. Robert Manning <rob...@gm...> wrote:=20 On 6/22/07, jan...@ya... wrote: > Hi, > > I get a "String to date conversion error" when I'm trying to do = this: > > update bla set blabla=3D'6/21/2007' It looks like the database is performing an implicit conversion (you gave it a string and the column is a date - it needs to reconcile that somehow). I'm guessing here as I have little knowledge of Informix. Generally, date types can be obtained by using a date function on a string that has a date format that the date function can understand. I suppose the first format you chose was not what the implicit conversion was expecting. You should consult your Informix product manual for guidance on what the format of date strings can be and if there are date functions that can/should be used. The date datatype format choices in SQuirreL apply to how dates are displayed and edited. So, any SQL with date strings that you execute goes to the database as is. Rob |