| Author |
Message
|
| Siddu |
 Posted: Thu Oct 25, 2012 3:48 am Post subject: Syntax error,permission violation,or other nonspecific[Solv] |
 |
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
Hi All,
I'm using the following Query to fetch an entire row for corresponding TMID from DBTABLE1 using DSNNAME1 Data Source Name.
SET APPAUDITQRY = 'SELECT * FROM Database.{DSNNAME1}.{DBSCHEMA1}.{DBTABLE1} WHERE employeeID=Environment.Temp.TMID';
SET Environment.AppAudit[] = PASSTHRU(APPAUDITQRY);
But I'm getting a DatabaseException Error:
Text:CHARACTER:[Microsoft][SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error
I tried seraching this forum it says it might be Syntax error.
Please let me know whether there is an error in the above query.
Thanks in advance.
_________________
"Be honest. It is one of the few things that you can control in your life."
Last edited by Siddu on Sat Nov 24, 2012 10:30 pm; edited 1 time in total |
|
| Back to top |
|
 |
| McueMart |
| Posted: Thu Oct 25, 2012 3:58 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
| Dont use 'Database' in your passthru statement. |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Thu Oct 25, 2012 3:59 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
Have you verified with mqsicvp -command that your data source is working?
--
Marko |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 25, 2012 4:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| McueMart wrote: |
| Dont use 'Database' in your passthru statement. |
Oh, and Passthru *also* doesn't evaluate {} expressions.
ESQL != SQL, and Passthru only uses SQL. |
|
| Back to top |
|
|
| Siddu |
| Posted: Thu Oct 25, 2012 4:18 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| McueMart wrote: |
| Dont use 'Database' in your passthru statement. |
Hi McueMart,
Thanks for the quick reply.
Then how can I modify my query?? :/
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 25, 2012 4:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Siddu wrote: |
| McueMart wrote: |
| Dont use 'Database' in your passthru statement. |
Hi McueMart,
Thanks for the quick reply.
Then how can I modify my query?? :/ |
By writing an SQL query, rather than writing an ESQL query.
   |
|
| Back to top |
|
|
| Siddu |
| Posted: Thu Oct 25, 2012 4:27 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| marko.pitkanen wrote: |
Hi,
Have you verified with mqsicvp -command that your data source is working?
--
Marko |
Yes Master,
Here is the result of the command:
C:\Program Files\IBM\MQSI\7.0>mqsicvp -n PPG_SQL -u *** -p ****
BIP8270I: Connected to Datasource 'PPG_SQL' as user 'User'. The datasource p
latform is 'Microsoft SQL Server', version '09.00.3080'.
===========================
databaseProviderVersion = 09.00.3080
driverVersion = 10.00.2531
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.3012.0000
driverManagerOdbcVersion = 03.52.0000
databaseProviderName = Microsoft SQL Server
datasourceServerName = ****
databaseName = ****
odbcDatasourceName = ****
driverName = ******
supportsStoredProcedures = Yes
procedureTerm = stored procedure
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = owner
tableTerm = table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 128
maxSchemaNameLength = 128
maxStatementLength = 524288
maxTableNameLength = 128
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = Yes
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = Yes
supportsAsinFunction = Yes
supportsAtanFunction = Yes
supportsAtan2Function = Yes
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = Yes
supportsDegreesFunction = Yes
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = Yes
supportsPowerFunction = Yes
supportsRadiansFunction = Yes
supportsRandFunction = Yes
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = Yes
supportsCaseExpression = Yes
supportsCastFunction = Yes
supportsCoalesceFunction = Yes
supportsNullIfFunction = Yes
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = char
varCharDatatypeName = varchar
longVarCharDatatypeName = text
clobDatatypeName = N/A
timeStampDatatypeName = datetime
binaryDatatypeName = binary
varBinaryDatatypeName = varbinary
varBinaryDatatypeName = varbinary
longVarBinaryDatatypeName = image
blobDatatypeName = N/A
intDatatypeName = int
doubleDatatypeName = N/A
varCharMaxLength = 8000
longVarCharMaxLength = 2147483647
clobMaxLength = 0
varBinaryMaxLength = 8000
longVarBinaryMaxLength = 2147483647
blobMaxLength = 0
timeStampMaxLength = 23
identifierCase = Mixed
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by da
tasource 'PPG_SQL' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, I
NTERVAL, CLOB, BLOB' Unsupported functions: 'POSITION'
Examine the specific datatypes and functions not supported natively by this data
source using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data proces
sing is done within WebSphere Message Broker rather than being processed by the
database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
_________________
"Be honest. It is one of the few things that you can control in your life."
Last edited by Siddu on Thu Oct 25, 2012 9:25 pm; edited 3 times in total |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Thu Oct 25, 2012 4:29 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi
Look correct format from InfoCenter for PASSTHRU
Something like this
| Code: |
| SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(cSQL TO Database.{cDNS} VALUES(iKoe)) ; |
--
marko |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 25, 2012 8:19 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| marko.pitkanen wrote: |
Hi
Look correct format from InfoCenter for PASSTHRU
Something like this
| Code: |
| SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(cSQL TO Database.{cDNS} VALUES(iKoe)) ; |
--
marko |
Does that {cDNS} expand out when used in a PASSTHRU?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 25, 2012 8:29 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| Does that {cDNS} expand out when used in a PASSTHRU? |
I think I'll just make another post that won't get read by almost anyone. |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Thu Oct 25, 2012 11:23 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Yes mqjeff,
You are right as always.
I should not have just copy / paste some not working code snippet from my Toolkit. My mistake. I'm sorry.
--
Marko |
|
| Back to top |
|
|
| Siddu |
| Posted: Thu Oct 25, 2012 9:56 pm Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| marko.pitkanen wrote: |
Hi
Look correct format from InfoCenter for PASSTHRU
Something like this
| Code: |
| SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(cSQL TO Database.{cDNS} VALUES(iKoe)) ; |
--
marko |
Ok,
Ichnged my query something like this:
SET Environment.AppAudit[] = PASSTHRU ('SELECT * FROM tablename WHERE employeeID = Environment.Temp.TMID' TO Database.{DSNNAME1}.{DBSCHEMA1});
but I'm getting following error:
Text:CHARACTER:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Oct 26, 2012 12:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| mqjeff wrote: |
| Oh, and Passthru *also* doesn't evaluate {} expressions. |
The PASSTHRU statement does evaluate {} expressions for the Database reference in the TO clause, but not for the SQL query itself.
| Siddu wrote: |
Ichnged my query something like this:
| Code: |
| SET Environment.AppAudit[] = PASSTHRU ('SELECT * FROM tablename WHERE employeeID = Environment.Temp.TMID' TO Database.{DSNNAME1}.{DBSCHEMA1}); |
|
However, the Database reference does not include the DB Schema name; that needs to be part of the SQL query.
| Siddu wrote: |
but I'm getting following error:
Text:CHARACTER:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified |
Does the Compute node Data Source property specify a valid datasource name? |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Fri Oct 26, 2012 12:42 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Thanks rekarm01,
I was remembering that it should work. Now tested with 7.0.0.4 that following works
| Code: |
DECLARE cDSN CHAR 'TEST_DS';
DECLARE cValue CHAR 'TestValue';
DECLARE cSQL CHAR 'select * from TEST_SCHEMA.TestTable where KEYFIELD = ?;';
SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(cSQL TO Database.{cDSN} VALUES(cValue)); |
Compute -node's Data source property needs working compatible data source name. |
|
| Back to top |
|
|
| Siddu |
| Posted: Fri Oct 26, 2012 1:48 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2012
Posts: 44
|
| marko.pitkanen wrote: |
Thanks rekarm01,
I was remembering that it should work. Now tested with 7.0.0.4 that following works
| Code: |
DECLARE cDSN CHAR 'TEST_DS';
DECLARE cValue CHAR 'TestValue';
DECLARE cSQL CHAR 'select * from TEST_SCHEMA.TestTable where KEYFIELD = ?;';
SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(cSQL TO Database.{cDSN} VALUES(cValue)); |
Compute -node's Data source property needs working compatible data source name. |
Hi Mark,
What exactly VALUES(cValue)) means?? Is it mandatory? :/
Becuase I wrote a query to suite my requirement after your suggestion like this:
DECLARE APPQRY CHAR 'SELECT * FROM DBSCHEMA1.DBTABLE1 where T.employeeID = Environment.Temp.TMID;';
SET OutputRoot.XMLNSC.Results.Result[] = PASSTHRU(APPQRY TO Database.{DSNNAME1} );
But I'm getting the following error:
Text:CHARACTER:[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
What's the problem? 
_________________
"Be honest. It is one of the few things that you can control in your life." |
|
| Back to top |
|
|
|
|
