good chemistry is complicated,
and a little bit messy -LW
Thank you for your input, it is appreciated.
with Win32::ODBC, I am able to execute stored procedures and basic queries routinely. It wasn't until I had the need to run a prebuilt script file, where the problem arose.
after much research tonight, it appears that this module isn't built to handle it and it was suggested to use DBI and DBD:ODBC, as you have.
I did some testing tonight using the DBI DBD:ODBC combination in which I read the file in and prepare each line in my loop and then at file end issue the execute.
This produces errors on the prepare, "incorrect syntax near...", I then tried to execute each line after the prepare and still get similar errors.
I know the sql script is solid and working. I can run it from the management console of osql/sqlcmd without problems. I must be ordering something incorrectly, not sure. I have a good understanding of the both modules, but am better with Win32::ODBC, but it seems simple enough to pick up with the DBI
I looked for tests on Win32::ODBC, but didn't find anything in addition to the perdoc and info on CPAN.
The SQL script looks like this:
set nocount on
declare @MRN varchar(255)
set @MRN = 'xxxxxxxx'
select distinct id as patientid, firstname, lastname
from person P join patient_iorg PI on id = personid
where OrganizationMRN = @MRN;
The error is either on the from or the where depending on where I position the execute statement..