RE: Re[2]: [U2] BCI and Dates to SqlServer

2005-03-29 Thread Hona, David S 
I noticed the difference between this example and the previous is that your
nbr and tdate varaibles were set being null string values just before
the SQLBindParameter. Yet this example (with no parameter markers) is force
feeding the values and it is working. This suggests that you are passing
empty strings/values - perhaps? Just speculating here!

I suggest to you change populate the variables prior to the bind and see
what happens. I've experienced similar issues with SQLBindParameter and
SQLExecDirect in older BCI releases.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Tod Sigafoos
Sent: Tuesday, 29 March 2005 2:49 AM
To: Ray Wurlod
Cc: u2-users@listserver.u2ug.org
Subject: Re[2]: [U2] BCI and Dates to SqlServer


Ray,

sorry to do this again but i forgot to mention ..

if i change the 'prepare' section as noted here .. it does work well.
 Now I can modify the read, write shells to force the conversion ..
 but now am really interested in why i am not getting it right to
 begin with g


*if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date) 
SQL.SUCCESS) then stop 'date'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0, date) 
SQL.SUCCESS) then stop 'date'

nbr = 1
tdate = 13598
tdate = oconv(tdate,'D4-')
date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
sqlStatement = 'insert into testDate values (' : nbr : ',' : squote(date) :
')'

*sqlStatement = 'insert into testDate values (?,?)'

IF (SQLPrepare(sqlStmtEnv, sqlStatement)  SQL.SUCCESS) then stop 'prepare'

*nbr = 1
*tdate = 13598
*tdate = oconv(tdate,'D4-')
*date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] BCI and Dates to SqlServer

2005-03-29 Thread Ray Wurlod 
The command HELP BCI SQLBindParameter refers to DATEFORM and 
SQLSetConnectOptions, and to Appendix A (of the BCI manual).  Looking at all of 
those you may get a clue, though as far as I can make out, DATEFORM is not 
supported for ODBC dbms types in 10.0.  Anyway take a look; I'll take a look at 
your code on the weekend.
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: Re[4]: [U2] BCI and Dates to SqlServer

2005-03-29 Thread Hona, David S 
Hi David,

I'm well, thanks. How you are going?

I've checked out our BCI applications, we've always populated the variables
prior to SQLBindParameter. Probably as a result of normal Pick/U2 BASIC
convention than what the manual says. Hopefully, this 'tip' may work for
you.

I believe some C-code we have done using UCI, does work, as per the manual,
as you pointed out correctly (albeit, the UCI manual).

Regards,
David


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Tod Sigafoos
Sent: Wednesday, 30 March 2005 7:50 AM
To: Hona, David S
Subject: Re[4]: [U2] BCI and Dates to SqlServer


David,

How is it going?

I have done that .. been moving stuff around just to see what might correct
the problem.

According to a note from IBM .. the binding really only connects position
with var name.  Actual data doesn't take effect until the execute.  Seemed
odd to me G.

But will try it again .. can't hurt
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re[2]: [U2] BCI and Dates to SqlServer

2005-03-28 Thread David Tod Sigafoos 
Ray,

if you get a chance could you please look at the sample code I sent.
I can not find any errors in it and yet the dates do not work.

According to the manual ..

If you use SQL.B.INTDATE, the SQL Client Interface assumes the program
variable holds a date in UniVerse internal date format and uses the
DATEFORM conversion string to convert the internal date to an external
format as required by the data source.

As you can see from the sample code I sent last week I am using
SQL.B.INTDATE with SQL.DATE which should force the internal date to
the correct format.  My test code sets 'date = 13598' which should be
converted to 24Mar05.

I have tried to mix and match this .. but still have no success.

Any help will be appreciated.

thanks

Thursday, March 24, 2005, 1:52:39 PM, you wrote:

RW BCI mimics the ODBC API, so dates are expected to be in ODBC format 
(-MM-DD).

RW Using the constant SQL.B.DATE in SQLBindParameter() or
RW SQLBindColumn() causes an implicit conversion to occur, so that
RW you get internal format out of a SELECT, and must supply internal
RW format in bound variables to an INSERT or UPDATE.



-- 
DSig `
David Tod Sigafoos  ( O O )
 ___oOOo__( )__oOOo___
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re[2]: [U2] BCI and Dates to SqlServer

2005-03-28 Thread David Tod Sigafoos 
Ray,

sorry to do this again but i forgot to mention ..

if i change the 'prepare' section as noted here .. it does work well.
 Now I can modify the read, write shells to force the conversion ..
 but now am really interested in why i am not getting it right to
 begin with g


*if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date)  
SQL.SUCCESS) then stop 'date'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0, date)  
SQL.SUCCESS) then stop 'date'

nbr = 1
tdate = 13598
tdate = oconv(tdate,'D4-')
date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
sqlStatement = 'insert into testDate values (' : nbr : ',' : squote(date) : ')'

*sqlStatement = 'insert into testDate values (?,?)'

IF (SQLPrepare(sqlStmtEnv, sqlStatement)  SQL.SUCCESS) then stop 'prepare'

*nbr = 1
*tdate = 13598
*tdate = oconv(tdate,'D4-')
*date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)


Thanks again ..

 

Thursday, March 24, 2005, 1:52:39 PM, you wrote:

RW BCI mimics the ODBC API, so dates are expected to be in ODBC format 
(-MM-DD).

RW Using the constant SQL.B.DATE in SQLBindParameter() or
RW SQLBindColumn() causes an implicit conversion to occur, so that
RW you get internal format out of a SELECT, and must supply internal
RW format in bound variables to an INSERT or UPDATE.



-- 
DSig `
David Tod Sigafoos  ( O O )
 ___oOOo__( )__oOOo___
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re: [U2] BCI and Dates to SqlServer

2005-03-28 Thread Ray Wurlod 
David,

I'm on the road (as usual), doing non-UniVerse stuff.  The earliest I will get 
to take a look at this is next weekend. Since you have a workaround, I guess 
the urgency's gone.  Have you contacted IBM support?  (And what version of SQL 
Server are you using?)

Regards,
Ray

- Original Message -
From: David Tod Sigafoos [EMAIL PROTECTED]
To: Ray Wurlod [EMAIL PROTECTED]
Subject: Re[2]: [U2] BCI and Dates to SqlServer
Date: Mon, 28 Mar 2005 08:49:27 -0800

 
 Ray,
 
 sorry to do this again but i forgot to mention ..
 
 if i change the 'prepare' section as noted here .. it does work well.
   Now I can modify the read, write shells to force the conversion ..
   but now am really interested in why i am not getting it right to
   begin with g
 
 
 *if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 
 0, date)  SQL.SUCCESS) then stop 'date'
 if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0, 
 date)  SQL.SUCCESS) then stop 'date'
 
 nbr = 1
 tdate = 13598
 tdate = oconv(tdate,'D4-')
 date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
 sqlStatement = 'insert into testDate values (' : nbr : ',' : 
 squote(date) : ')'
 
 *sqlStatement = 'insert into testDate values (?,?)'
 
 IF (SQLPrepare(sqlStmtEnv, sqlStatement)  SQL.SUCCESS) then stop 'prepare'
 
 *nbr = 1
 *tdate = 13598
 *tdate = oconv(tdate,'D4-')
 *date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
 
 
 Thanks again ..
 
 
 
 Thursday, March 24, 2005, 1:52:39 PM, you wrote:
 
 RW BCI mimics the ODBC API, so dates are expected to be in ODBC 
 format (-MM-DD).
 
 RW Using the constant SQL.B.DATE in SQLBindParameter() or
 RW SQLBindColumn() causes an implicit conversion to occur, so that
 RW you get internal format out of a SELECT, and must supply internal
 RW format in bound variables to an INSERT or UPDATE.
 
 
 
 --
 DSig `
 David Tod Sigafoos  ( O O )
   ___oOOo__( )__oOOo___
 ---
 u2-users mailing list
 u2-users@listserver.u2ug.org
 To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re: Re[2]: [U2] BCI and Dates to SqlServer

2005-03-28 Thread Ray Wurlod 
Can you re-send the sample code?  I seem to have deleted it already.

Thanks,
Ray
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re: Re[2]: [U2] BCI and Dates to SqlServer

2005-03-25 Thread Ray Wurlod 
What happens if you replace SQL.B.INTDATE with SQL.B.BASIC in the 
SQLBindParameter call?
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re: [U2] BCI and Dates to SqlServer

2005-03-24 Thread Ray Wurlod 
BCI mimics the ODBC API, so dates are expected to be in ODBC format 
(-MM-DD).

Using the constant SQL.B.DATE in SQLBindParameter() or SQLBindColumn() causes 
an implicit conversion to occur, so that you get internal format out of a 
SELECT, and must supply internal format in bound variables to an INSERT or 
UPDATE.

HTH
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re[2]: [U2] BCI and Dates to SqlServer

2005-03-24 Thread David Tod Sigafoos 
Ray,

Thursday, March 24, 2005, 1:52:39 PM, you wrote:

RW BCI mimics the ODBC API, so dates are expected to be in ODBC format 
(-MM-DD).

yes ..

RW Using the constant SQL.B.DATE in SQLBindParameter() or
RW SQLBindColumn() causes an implicit conversion to occur, so that
RW you get internal format out of a SELECT, and must supply internal
RW format in bound variables to an INSERT or UPDATE.

I do use the SQL.B.INTDATE and SQL.DATE.  MsSql creates as datetime
(using Enterprise Manager) ..

As soon as i try to write to this column i get the error.  If i change
the column to 'char' in all places then it writes out correctly.

It is only DATE that i have any problems with (haven't tried time).
INTs chars varchars (which map also to text columns) etc work well.

It is only date .. it must be something on my end as I would find it
odd that there is some ambiguity in the uv - odbc interface



-- 
DSig `
David Tod Sigafoos  ( O O )
 ___oOOo__( )__oOOo___

Cannot open file C:\DOCUMENTS AND SETTINGS\DAVID TOD SIGAFOOS\APPLICATION 
DATA\BATMAIL\COOKIES.TXT
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re[2]: [U2] BCI and Dates to SqlServer

2005-03-24 Thread David Tod Sigafoos 
Ray,

Thursday, March 24, 2005, 1:52:39 PM, you wrote:
snip

as   further follow up here is a sample routine and info.  Note that
that the only problem shows up after the sqlExecute .. and it is
caused by the write ..

RUN DAVES.BP DATE_TEST.MV

Program: DATE_TEST  
Status for sqlExecute call is: -1   
SQLSTATE,NATCOD are:S1C00  0
Error text is   
[IBM][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Optional feature n
ot implemented  
Program DATE_TEST.MV: Line 69, Cannot resolve ERRMSG item EXITING CKSTMTENV.
 

sql definition:
tableName = testDate
columns
nbr int
testDatedatetime
---


program = 'DATE_TEST'
include UNIVERSE.INCLUDE ODBC.H

equ TRUE$ to 1
equ FALSE$ to 0
equ NULL$ to ''

if (SQLAllocEnv(comBaseEnv)  SQL.SUCCESS)  SQL.SUCCESS then stop 'allocEnv'
if (SQLAllocConnect(comBaseEnv, comConnEnv)  SQL.SUCCESS) then stop 
'allocConnect'
if (SQLConnect(comConnEnv, 'Universe','Universe','universe')  SQL.SUCCESS) 
then stop 'Connect'
if (SQLAllocStmt(comConnEnv, sqlStmtEnv)  SQL.SUCCESS)  SQL.SUCCESS then stop 
'allocStmt'

nbr = NULL$
date = NULL$

if (SQLBindParameter(sqlStmtEnv, 1, SQL.B.BASIC, SQL.INTEGER, 4, 0, nbr)  
SQL.SUCCESS) then stop 'BP nbr'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date)  
SQL.SUCCESS) then stop 'date'

sqlStatement = 'insert into testDate values (?,?)'

IF (SQLPrepare(sqlStmtEnv, sqlStatement)  SQL.SUCCESS) then stop 'prepare'

nbr = 1
date = 13598

status = sqlExecute(sqlStmtEnv)
if status  SQL.SUCCESS then
sqlRoutine = 'sqlExecute'
gosub CKSTMTENV
end

STOP

CKSTMTENV:
STATUS = status
Fn = sqlRoutine
Expect = ''
IF STATUS EQ -2 THEN 
status = SQLFreeStmt(sqlStmtEnv, SQL.DROP)

STOP INVALID STATEMENT HANDLE
end

IF STATUS EQ 100 THEN RETURN
IF STATUS NE 0
THEN
301*
  ST = SQLERROR(-1,-1,sqlStmtEnv,STATE,NATCODE,ERRTXT)
  IF ST  SQL.NO.DATA.FOUND
  THEN
PRINT 
COUNT = 1
IF Expect NE 0 AND STATE = Expect AND ST  SQL.NO.DATA.FOUND
THEN 
PRINT Allowed error of :STATE: returned for func :Fn
GOTO 399
END
ELSE
  PRINT 'Program: ': program
  PRINT Status for :Fn: call is: :STATUS
  PRINT SQLSTATE,NATCOD are: : STATE:  :NATCODE
  PRINT Error text is 
  PRINT  : ERRTXT
END
IF ST = 0 THEN GOTO 301
  END
IF STATUS = -1 AND COUNT = 1 THEN 
status = SQLFreeStmt(sqlStmtEnv, SQL.DROP)
STOP EXITING CKSTMTENV
end
END
399*
  IF STATUS  0 THEN PRINT 
  RETURN





-- 
DSig `
David Tod Sigafoos  ( O O )
 ___oOOo__( )__oOOo___
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/