Parameterized SQL queires from BR

General development discussion.

Moderators: Susan Smith, admin, Gabriel

Mikhail
Posts: 87
Joined: Tue Jul 07, 2009 10:26 am
Location: Ukraine

Parameterized SQL queires from BR

Postby Mikhail » Wed Aug 30, 2017 12:59 pm

Can someone tell me what I'm doing wrong here? On line 60, I get error 3011 (SQL bind parameter failure).

Code: Select all

10 dim sql$*500
20 execute 'CONFIG database conn connectstring="DRIVER=SQL Server Native Client 11.0;Server=DAL01-SQL-T01;Database=ContributionAccounting;Trusted_Connection=yes;"'
30 sql$="insert into [ContributionAccounting].[dbo].[test] ([a],[b]) values (@x,@y)"
40 open #Sqlconnection:=1: 'database=conn',sql sql$,outin
50 for index_=1 to 100
60   write #sqlconnection, using 'form C 1,L': 'a', 100000
70 next index_
80 close #sqlconnection:

John
Posts: 523
Joined: Sun Apr 26, 2009 8:27 am
Location: West Orange, NJ
Contact:

Re: Parameterized SQL queires from BR

Postby John » Wed Aug 30, 2017 1:23 pm

does your form statement need a pos?
John Bowman

Mikhail
Posts: 87
Joined: Tue Jul 07, 2009 10:26 am
Location: Ukraine

Re: Parameterized SQL queires from BR

Postby Mikhail » Wed Aug 30, 2017 4:17 pm

POS is not required, as far as I know

GomezL
Posts: 232
Joined: Wed Apr 29, 2009 5:51 am
Contact:

Re: Parameterized SQL queires from BR

Postby GomezL » Wed Aug 30, 2017 5:45 pm

Using SYSERR$ should help to return the last actual SQL error.
You might need to enable logging in BR since sometimes you get multiple errors.

This is the connection string that I use:
CONFIG database SQL-Query connectstring="DRIVER=SQL Server;Initial Catalog=vExchange;Persist Security Info=True;MultipleActiveResultSets=True; Database=MyDBNAME;SERVER=MyServer;Login Name=MyLogin;Password=BR_PASSWORD"

I noticed that you don't have any login information in your connection string.



Using your connection string I came up with this suggestion:

CONFIG database SQL-Query connectstring="DRIVER=SQL Server;Initial Catalog=vExchange;Persist Security Info=True;MultipleActiveResultSets=True; Database=ContributionAccounting;SERVER=DAL01-SQL-T01;Login Name=Mikhail;Password=BR_PASSWORD"

I assumed Mikhail for your login name
BR_PASSWORD means use the WINDOWS password

Mikhail
Posts: 87
Joined: Tue Jul 07, 2009 10:26 am
Location: Ukraine

Re: Parameterized SQL queires from BR

Postby Mikhail » Thu Aug 31, 2017 8:09 am

Hi, Luis. Thanks for your reply. I'm certain the connection string is not the problem. Using "Trusted_Connection=yes" in the connection string takes care of logging in. Doing so works for "SQL Server NAtive Client 11.0" driver, not for "SQL Server" dirver. The error I get in the example I provided in the post has to do with binding values to parameters, not with connecting (BR error 3011 - SQL bind parameter failure, SYSERR$ - Invalid precision value)

Here is an expanded example below that illustrates better what I'm trying to accomplish:

Code: Select all

00020 dim sql$*5000
00030 execute 'CONFIG database conn connectstring="DRIVER=SQL Server Native Client 11.0;Server=DAL01-SQL-T01;Database=ContributionAccounting;Trusted_Connection=yes;"'
00040 ! Column [a] - varchar(10)
00050 ! Column [b] - numeric(18, 0)
00055 !
00060 ! WORKS
00070 sql$="insert into [ContributionAccounting].[dbo].[test] ([a],[b]) values ('1',1)"
00080 open #sqlquery:=1: 'database=conn',sql sql$,outin
00090 write #sqlquery:
00100 close #sqlquery:
00110 !
00120 ! DOESN'T WORK
00130 sql$="insert into [ContributionAccounting].[dbo].[test] ([a],[b]) values (@x,@y)"
00140 open #sqlquery:=1: 'database=conn',sql sql$,outin
00150 for i=1 to 100
00160    write #sqlquery, using 'FORM C 10,L': str$(i), i
00170 next i
00180 close #sqlquery:


GomezL
Posts: 232
Joined: Wed Apr 29, 2009 5:51 am
Contact:

Re: Parameterized SQL queires from BR

Postby GomezL » Thu Aug 31, 2017 10:31 am

Good to know about the login information!

With your additional feedback I noticed that your write statement has a "Form Statement"

Code: Select all

write #sqlquery, using 'FORM C 10,L': str$(i), i


With SQL, you just write the fields without any FORM Statement:

Code: Select all

write #sqlquery: str$(i), i

Mikhail
Posts: 87
Joined: Tue Jul 07, 2009 10:26 am
Location: Ukraine

Re: Parameterized SQL queires from BR

Postby Mikhail » Thu Aug 31, 2017 11:57 am

Removing the FORM statement did not help, still getting the same error.

I'm beginning to think what I want is impossible with INSERT statements, because I can't find any SQL Server examples where an INSERT uses parameters.

I can find plenty of examples of SQL Server parameterized statement usage, just not for INSERT


Return to “General Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron