Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Database parameter

  1. How-to
0
Votes
Undo
  1. Aleš Vojáček
    Aleš Vojáček
  2. PowerBuilder
  3. Friday, 16 December 2022 13:39 PM UTC

Hi all,

we try to use "MSOLEDBSQL SQL Server" driver to connect to MSSQL.

But we got error : SQLSTATE = 22007

There is embded select like:

SELECT Count (*) INTO :ll_poc FROM Dbo.idtab WHERE EXISTS (SELECT 1 FROM dbo.aktivita WHERE iduser = :ii_iduser AND idaktivita = idtab.idaktivita AND prihl < :ld_a) USING SQLREAD;
 
where ld_a is Today ()
 
Why PB converts db parameter to string?
Is it possible to use datetime type of parameter instead?
 
Thank you Ales
 
EDIT
When we log SQL which is produced by that sql statement new OLE driver (MSOLEDBSQL SQL Server) :
SELECT Count ( *) FROM Dbo.idtab WHERE EXISTS ( SELECT 1 FROM dbo.aktivita WHERE iduser =130 AND idaktivita =idtab.idaktivita AND prihl < '2022-12-16' )
 
old ole driver (OLE DB) :
SELECT Count ( *) FROM Dbo.idtab WHERE EXISTS ( SELECT 1 FROM dbo.aktivita WHERE iduser =130 AND idaktivita =idtab.idaktivita AND prihl < {d '2022-12-13' } )
 
Re: Resize tab, dw objects inside tab when splitba...
What to learn for PowerServer?
Accepted Answer
Aleš Vojáček
Aleš Vojáček Accepted Answer Pending Moderation
  1. Friday, 16 December 2022 16:41 PM UTC
  2. PowerBuilder
  3. # Permalink

I'm sorry.

PB 2022 22.0.0.1892 Win10

MSSQL Server 15.0.2095.3

MSOLEDBSQL SQL Server (MS provider 18.6.4)

I found helpind set NoBind=0 in DBParm, it is still different from what I logged with previous (deprecated) OLE DB (SQLCLI11) but it works at least. I will see if there will be some troubles with this setting :-)

 

Thank you and sorry for incomplete question.

Ales.

 

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 16 December 2022 18:56 PM UTC
Hi Aleš ;

Suggestions ...



1) Try Auto Commit ON...

SQLCA.AutoCommit=TRUE



// If that does not work ....

2 Try another Isolation level ...

SQLCA.Lock ='RU' // as an example



HTH

Regards ... Chris
  1. Helpful
There are no comments made yet.
Responses (3)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Saturday, 17 December 2022 03:56 AM UTC
  2. PowerBuilder
  3. # 1

Hi, Aleš -

1. What error message are you getting? There are several error conditions that produce SQLSTATE 22007, according to the online documentation I've looked at.

2. What is your SQLCA.DBParm setting? Are you including DateFormat and/or DateTimeFormat in DBParm?

3. How would you specify the date value in a SQL SELECT statement if you entered it into SQL Server Management Studio?

4. The SQL you posted showing the SELECT statement when using the MSOLEDBSQL driver looks syntactically correct. Have you tried copy/pasting that same SELECT statement into SQL Server Management Studio to see if it executes in your environment?

Best regards, John

Comment
Aleš Vojáček
  1. Aleš Vojáček
  2. Saturday, 17 December 2022 17:10 PM UTC
Hi

Problem is that date is passed as a string and the Error is that the string has no correct format when converting to date. I do not have DateFormat or DateTimeFormat in DBParm specified.

I thing that nobind=0 is better because then convert is not used. We will see if there will be some problems with it.

Thank you.

Ales
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 16 December 2022 16:18 PM UTC
  2. PowerBuilder
  3. # 2

Hi Aleš ;

   When posting these type of questions, please always include:

  • Client machine's O/S Version & build
  • PB Version & build
  • DBMS Server version & build
  • DB Client software version & build

many thanks ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.