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

Resolved SQL Native Client

  1. How-to
0
Votes
Undo
  1. Andrew Davis
    Andrew Davis
  2. PowerBuilder
  3. Saturday, 6 April 2024 15:28 PM UTC

Hi all

PB 2022 R3 Windows 10 SQL Express 15

I have always used SQL Anywhere for my database however I have a new project that requires me to connect to a SQL server database

Can you confirm what i am getting is normal - i thought you could just connect to different databases with no or very little code changes.

in SQL Anywhere if i use the SQL painter it creates this statement and it works.

SELECT "conveyor_map"."ticket_number"
INTO :ls_ticket
FROM "conveyor_map"
WHERE ( "conveyor_map"."slot_no" = :ls_slot ) AND
( "conveyor_map"."conveyor_code" = :ls_code ) ;

 

if I use this statement against the SQL server database I get the error message

SQLSTATE=42000
Microsoft SQL Server Native Client 11.0
Incorrect syntax near 'ticket_number'

My insert statement works - if i remove the " around the fields

e.g.

INSERT INTO conveyor_map
( ticket_number,
slot_no,
allocated,

instead of 

INSERT INTO "conveyor_map"
( "ticket_number",
"slot_no",
"allocated",

SQL connection details.

 

// Profile conveyor_metal
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.ServerName = "lmc10\sqlexpress"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='conveyor_metal',TrustedConnection=1,Encrypt=0,DelimitIdentifier=1,TrimSpaces=1"

 

Any help or ideas would be really appreciated.

 

  1. Database Driver
  2. SQL Server
PB2022R3 keeps asking for FullBuild on compiled pb...
Put/Get Files from AWS S3
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 7 April 2024 05:58 AM UTC
  2. PowerBuilder
  3. # Permalink
Hi Andrew; If you look in your SS DB Profile, there is a setting called "quoted identifiers". If you set that off & look at the SQLCA settings in the DB profile dialogue in the "Preview" mode, you'll see how that setting affected the DBparm field. Then just copy that DBparm setting into your PB App & then that should alleviate this issue. At least, for the most part. HTH Regards .... Chris
Comment
There are no comments made yet.
Responses (2)
Andrew Davis
Andrew Davis Accepted Answer Pending Moderation
  1. Saturday, 6 April 2024 16:39 PM UTC
  2. PowerBuilder
  3. # 1

Update

It seems that it works if i remove the " around the table fields

Is this required or is there a setting somewhere ?

regards

Andrew

Comment
Andrew Davis
  1. Andrew Davis
  2. Sunday, 7 April 2024 05:58 AM UTC
Chris



Thank you so much - the setting is on the syntax tab of the DB Profile properties 'Enclose Table and Column Names in Quotes' what i had not done previously is take the setting out of the preview tab 'DelimitIdentifier=1 and add it to the DbParm string in the application sqlca connect script



regards

Andrew
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 7 April 2024 13:22 PM UTC
Hi Andrew;

Yes, that sounds correct. I was answering from my phone so I used the DBMS term vs what PB calls it. Super glad that you found it. The key is the "DelimitIdentifier=1" in the end that needs to be added to your DBparm.

Super glad this worked for you!

Regards ... Chris
  1. Helpful
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.